web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Get the Table that con...
Microsoft Dynamics GP (Archived)

Get the Table that contains the NOTES on Payable Payments, Payable Transaction Window

(0) ShareShare
ReportReport
Posted on by ogmarvin21 205
In the Payable Payments, Payable Transaction window besides the voucher no. or payment no. there is a note button in where you click it contains a note, sometimes it doesn't depends on who wrote that record, but I want to know in what Table in the database is it stored? Or is it stored in the database at all? I need it because I want it to be shown on a report. Thanks in advance

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,633 Moderator on at

    SY03900 in the Company database.  You'll have to link the Note Index column in SY03900 to the Note Index in the payables tables.

  • ogmarvin21 Profile Picture
    ogmarvin21 205 on at

    I found it sir, thanks, now how do you make an SQL Query in which it shows the data of VNDCHKNM, VCHRNMBR, DOCDATE, DOCAMNT, TRXDSCRN, PPSAMDED, TXTFIELD, APPLDAMT, APFRDCNM, APTVCHNM in one table in which it shows all of transactions and its notes alongside it in order, this might be off topic but I hope somebody helps

  • Suggested answer
    soma Profile Picture
    soma 24,410 on at

    Hi, the below code is used to pull all the Payables & Payments(Work, Open & Hist) transactions with Note index details. 

    SELECT * FROM
    (SELECT
    PT.[Vendor ID],
    VM.VNDCHKNM,
    PT.[Voucher Number],
    PT.[Document Type],
    PT.[DOCUMENT DATE],
    PT.[Document Amount],
    PT.[TRX Source],
    PT.[PPS Amount Deducted],
    NM.TXTFIELD
    --APPLDAMT, APFRDCNM, APTVCHNM
    FROM PayablesTransactions PT --PAYABLES TRANSACTIONS WORK/OPEN/HIST
    LEFT OUTER JOIN PM00200 VM --VENDOR MASTER
    ON PT.[Vendor ID]= VM.VENDORID
    LEFT OUTER JOIN SY03900 NM --RECORD NOTE MASTER
    ON NM.NOTEINDX = PT.[NOTE INDEX]

    UNION ALL

    SELECT
    MPW.VENDORID [Vendor ID],
    VM.VNDCHKNM,
    MPW.VCHRNMBR [Voucher Number],
    'Payment',
    MPW.DOCDATE [DOCUMENT DATE],
    MPW.DOCAMNT [Document Amount],
    MPW.TRXDSCRN [TRX Source],
    MPW.PPSAMDED [PPS Amount Deducted],
    NM.TXTFIELD
    FROM PM10400 MPW --MANUAL PAYMENT WORK
    LEFT OUTER JOIN PM00200 VM --VENDOR MASTER
    ON MPW.VENDORID = VM.VENDORID
    LEFT OUTER JOIN SY03900 NM --RECORD NOTE MASTER
    ON NM.NOTEINDX = MPW.NOTEINDX) PAYABLES

    Note: To get the values for the column(APPLDAMT, APFRDCNM & APTVCHNM), join the tables PM10200 & pm30300 to the end of the above query.

    Please let me know if you need any other details.

    Hope this helps!!!

  • ogmarvin21 Profile Picture
    ogmarvin21 205 on at

    I have a new problem, Im studying your code and its confusing, sorry im a beginner in GP and SQL so I now have a table that displays

    VENDORID / DOCDATE / VCHRNMBR / TRXDSCRN / TXTFIELD / NOTEINDX

    all of them came from PM20000 and SY03900 and this is my code

    CREATE VIEW dbo.CheckRegRp2t

    AS

    SELECT     dbo.PM20000.VENDORID, dbo.PM20000.DOCDATE,

    dbo.PM20000.VCHRNMBR, dbo.PM20000.TRXDSCRN, dbo.SY03900.TXTFIELD,

    dbo.SY03900.NOTEINDX

    FROM dbo.PM20000

    INNER JOIN dbo.SY03900

    ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX

    but I want it to also include the data on PM30200 including its VENDORID, DOCDATE, VCHRNMBR, TRXDSCRN into one table. I don't want to put another column with the same name, I just want the data to go into the columns I already made

  • Verified answer
    soma Profile Picture
    soma 24,410 on at

    Try the below code,

    CREATE VIEW dbo.CheckRegRp2t

    AS

    SELECT     dbo.PM20000.VENDORID, dbo.PM20000.DOCDATE,

    dbo.PM20000.VCHRNMBR, dbo.PM20000.TRXDSCRN, dbo.SY03900.TXTFIELD,

    dbo.SY03900.NOTEINDX

    FROM dbo.PM20000

    INNER JOIN dbo.SY03900

    ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX

    UNION ALL

    SELECT     dbo.PM30200.VENDORID, dbo.PM30200.DOCDATE,

    dbo.PM30200.VCHRNMBR, dbo.PM30200.TRXDSCRN, dbo.SY03900.TXTFIELD,

    dbo.SY03900.NOTEINDX

    FROM dbo.PM30200

    INNER JOIN dbo.SY03900

    ON dbo.SY03900.NOTEINDX = dbo.PM30200.NOTEINDX

    Hope this helps!!!

  • ogmarvin21 Profile Picture
    ogmarvin21 205 on at

    Hey there!! at first there was an error regarding the dbo.PM30200.TXTFIELD, it showed an error since there was no TXTFIELD column inside dbo.PM30200 but ichanged it back to dbo.PM30200.SY03900 and it worked

    Thanks a lot guys! Oh and uhm if ever I might encounter another problem haha I might reply back in here.

  • Suggested answer
    soma Profile Picture
    soma 24,410 on at

    Yes. Typo error,  I have changed above code.

    Note: If my answer solved your problem then mark it as a verified. Because this will helps to some others who having the same problem.

    Good Luck!!!

  • ogmarvin21 Profile Picture
    ogmarvin21 205 on at

    OK so probably last question just in case the ones upstairs might need it, what if I wanted to add another table in there lets say PM00200 what would the code be like?

  • Suggested answer
    soma Profile Picture
    soma 24,410 on at

    Try the below code.

    CREATE VIEW dbo.CheckRegRp2t

    AS

    SELECT     dbo.PM20000.VENDORID, dbo.PM00200.VNDCHKNM, dbo.PM20000.DOCDATE,

    dbo.PM20000.VCHRNMBR, dbo.PM20000.TRXDSCRN, dbo.SY03900.TXTFIELD,

    dbo.SY03900.NOTEINDX

    FROM dbo.PM20000

    INNER JOIN dbo.SY03900

    ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX

    INNER JOIN dbo.PM00200

    ON dbo.PM00200.VENDORID = dbo.PM20000.VENDORID 

    UNION ALL

    SELECT     dbo.PM30200.VENDORID, dbo.PM00200.VNDCHKNM, dbo.PM30200.DOCDATE,

    dbo.PM30200.VCHRNMBR, dbo.PM30200.TRXDSCRN, dbo.SY03900.TXTFIELD,

    dbo.SY03900.NOTEINDX

    FROM dbo.PM30200

    INNER JOIN dbo.SY03900

    ON dbo.SY03900.NOTEINDX = dbo.PM30200.NOTEINDX

    INNER JOIN dbo.PM00200

    ON dbo.PM00200.VENDORID = dbo.PM30200.VENDORID 

    Note: If my answer solved your problem then mark it as a verified. Because this will helps to some others who having the same problem.

    Hope this helps!!!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

#2
Shravan Attelli Profile Picture

Shravan Attelli 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans