Dynamics Community Forum Thread Details

Joining tables across Financial and Purchasing series to obtain Invoice Paid Date and expense account

Hello,

First post in this community. I am trying to set up a report in Excel that pulls data for GL expense accounts, debit amount, and credit amount from the Financial series, and the Invoice Receipt Date and another field to join the two on from the Purchasing series.

I'm running GP version 18.5.1596. I do not have administrative credentials, so I cannot directly connect to the SQL database. I have mostly been able to get by with importing existing .odc connections into Excel workbooks using SQL queries and connection strings, but I am new to working with tables and joins in GP and need some help with this task.

There is an existing smartlist that would serve this purpose, but as far as I know, there's no way to keep that data live, and it must be manually run and imported into excel whenever it needs to be updated. Not only is this process slow (about 25 rows/second), but the .odc connections update data almost instantly, are live, and refresh whenever I open the workbook, which is why I prefer it.

The report should show the expense accounts, debit and credit amounts, and the "invoice paid date", which appears to exist in a table in the purchasing series. This is different than the "TRX Date" field, which is in the Financial series and is equal to the date the payables transaction was entered into GP.

I glanced at the smartlist designer window for the "Billing Report" smartlist in the Purchasing series, and it shows that there are three left joins between four tables:

PM Distribution History File
Account Master
PM Paid Transaction History File
PM Apply to History File

Which of these tables would I need to fit my purpose? Are there prebuilt .odc connections in the install directory, i.e. <install directory>\Excel Reports\Data Connecitons\<company name>\Purchasing? There are 50 files present in the Purchasing series, and I'm not sure which one(s) contain the fields I'm after. If they do, what are those tables called, and how can i figure out which .odc connection files contain the data i'm looking for?

Furthermore, how do I know what the fields are called in my SQL query? Thanks in advance.

Categories:

Microsoft Dynamics GP

I believe the only 2 tables you would need are PM Distribution History File (PM30600) and PM Paid Transaction History File (PM30200) (which might be same as PM Paid Transaction History File). You'd probably want to add GL Account String (GL00105) to get "friendly name" of account number instead of just account index from PM30600.

I use Yudin's site a quick reference: victoriayudin.com/.../

Ian Grieve's site is helpful to get GP table display name, technical name, etc of tables/columns. For example:

gptables.azurecurve.co.uk/.../

There's a field in PM30200 called DINVPDOF which is Date Invoice Paid Off and I think is what you need.

Query like this might do it (this just quickly off the cuff).

SELECT TOP 100

RTRIM(PMTrxHist.VENDORID) VENDORID,

RTRIM(PMTrxHist.VCHRNMBR) VCHRNMBR,

RTRIM(PMTrxHist.DOCNUMBR) DOCNUMBR,

CAST(PMTrxHist.DINVPDOF AS DATE) DINVPDOF,

RTRIM(GLAcctString.ACTNUMST) ACTNUMST,

PMTrxHistDist.DEBITAMT,