Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Amber Bell]

Up

0

Down

::

Hello Thomas, I worked with Microsoft Copilot to create a SQL View to help you! I included all 10 Account segment columns. You can edit that out:

SELECT 
    PM10100.TRXSORCE AS [Transaction Source],
    PM10100.VCHRNMBR AS [Voucher Number],
    PM10100.VENDORID AS [Vendor ID],
    PM10100.DEBITAMT AS [Debit Amount],
    PM10100.CRDTAMNT AS [Credit Amount],
    GL00100.ACTDESCR AS [GL Account Description],
    RTRIM(
        ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_10), '')
    ) AS [Account Number]
FROM PM10100
JOIN GL00100 ON PM10100.DSTINDX = GL00100.ACTINDX

UNION ALL

SELECT 
    PM30600.TRXSORCE AS [Transaction Source],
    PM30600.VCHRNMBR AS [Voucher Number],
    PM30600.VENDORID AS [Vendor ID],
    PM30600.DEBITAMT AS [Debit Amount],
    PM30600.CRDTAMNT AS [Credit Amount],
    GL00100.ACTDESCR AS [GL Account Description],
    RTRIM(
        ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_10), '')
    ) AS [Account Number]
FROM PM30600
JOIN GL00100 ON PM30600.DSTINDX = GL00100.ACTINDX;