Adding POOrder to Journal Transactions for Accounts Report
Community,
I am attempting to add the table POOrder to the Journal Transactions for Account report in Report Writer. The field that I have been asked to pull in is the POOrder.OrderDesc field.
My struggle is finding the right way to tie the table in with relationships.
This is the current relationship set up:
I have looked through the DACs and have not been able to find a way to link the POOrder table to any of these other tables.
Does anyone have any ideas on how to make this work?
Thank you in advance for any help!
Page 1 / 1
Hello @tveld ,
Purchase Orders do not post to GL; they do not produce any transactions to any accounts. Therefore the Purchase Order table cannot be directly joined to GL Account reports. There is no relationship.
I think it will be easier to create a report from the PO Receipt and include the GL account numbers, than to begin in GL and worm your way to the PO header Description (by way of inventory). Purchase Order Receipts post to GL, but it’s tricky because the PO Receipt creates an Inventory Receipt and it’s the Inventory Receipts that post to GL.
In GL Tran we have GL batch number (link on the left side) and IN Batch number, labeled “Ref. Number”. Join GLTran to INTran on gltran.refnumber = Intran.batchnumber, and join on line numbers.
INTran contains the PO Receipt Number - join Intran to PoReceiptLine on receipt number and Receipt type and Line Number.
Now you have your PO Number and with that you can join PoReceiptLine to the Purchase Order Header (POOrder)and snag the PO Description.
Good luck!
Laura
@Laura02 ,
That all makes sense! I wondered honestly if it was possible. Thank you for walking through that for me so I do have a better understanding!
You’ve been very helpful!
Thank you,
Trisha
@Laura02 ,
One more question on this topic. I am getting back to this and trying to make this work.
You mentioned at the start of your response to create a report from PO Receipt. So, should I start from scratch on this report and start with the PO Receipt and then add in the tables you recommended, then add the others that are in the current report? I just need to add the PO description to the report. And I need to keep all the other current information on that report.
I apologize, this seems like a silly question. I'm still pretty new to Acumatica in general as an end user and writing these reports.
Thank you again for your help and getting me pointed in the right direction on this report. I appreciate your assistance.
Trisha
@Laura02 ,
Not sure if you see the question above. I have been trying to work on this report and get it to work.
You mentioned at the start of your response to create a report from PO Receipt. So, should I start from scratch on this report and start with the PO Receipt and then add in the tables you recommended, then add the others that are in the current report? I just need to add the PO description to the report. And I need to keep all the other current information on that report.
I apologize, this seems like a silly question. I'm still pretty new to Acumatica in general as an end user and writing these reports.
Thank you,
Trisha
Hi,
I did miss your recent post. No need to apologize - we are all new sometimes!
In my opinion, it’s not easy to connect GL transaction table to PO Header because PO’s do not post directly to GL. Therefore, you will be connecting GLTran to INtran on account and batch number. Then using the IN batch number to connect to the PO Receipt header table, then connecting PO Receipt to PO Receipt Lines, then using the PO Receipt lines to connect to the original Purchase Order header and obtain the Description field. lIf someone knows a faster way, I hope they will join this conversation.]
What are your exact requirements? If one report needs to include transactions of all modules, all sources, and show ending balance for the account on each line, then start with a copy of GL Transactions for Account and join as described above.
If a new report could be used separately from Journal Transactions for Account, to analyze purchasing transactions only, and show the PO Description, such as to analyze one account like Supplies expense, then a simpler report joining only POReceiptLine joined to POOrder table may work for you and would likely be simpler to build.
Laura
@Laura02 ,
The whole purpose of this is to get that PO Description added to that report. They are adding information in that header that they want to see on this report.
In this screenshot, the highlighted area is what they want to see. The reason for wanting this added is because of all the clicks to get to the information.
The rest of the information on the report is what they want to see. I knew that trying to connect these tables would be a challenge, before I reached out here.
I’m wondering if that last option that you mentioned might work.
