Our accounting department like to verify the GL account used on a PO when entering an AP Invoice. However there is no easy way to do this. Has anyone developed a report or screen that display the GL Accounts on a PO when entering AP Invoices. Sometimes, the GL account needs to be changed or corrected and viewing things after the fact is problematic.
The data you want is in the TranGLC table. RelatedFile=PORel, Key1=PONum, Key2=POLine, Key3=PORelNum the Acct to be used is in the GLAccount field.
(Those are off the top of my head and may not be exactly right)
We had issues with this being blank on Purch types of “Other” (PUR-UKN). So I made a report that filtered to the GL Acct that would be used if none were specified for a PUR-UKN PORel. It ran nightly, emailing the results to purchasing. They’d get an email only when there were “bad GL accts”
That is more or less the main issue(s) we are having. Thanks for the idea and information.
Do you remember what the relationship was between the 2 tables? I do not see a relationship between the TranGLC table and PORel table
Something like:
- TranGLC.RelatedFile=“PORel”
- TranGLC.Key1=PORel.PONum
- TranGLC.Key2=PORel.POLine
- TranGLC.Key3=PORel.PORelNum
If that’s not right start off with just a table criteria of TranGLC.RelatedFile Like 'PO%' to verify the filename for the PORel table (99.99% sure it is the same as the table name. But only 99% sure that the table name really is “PORel”)
Okay, thanks
Hi Calvin,
Here you wrote * TranGLC.RelatedFile=“PORel”
how to link that I cannot find any field as PORel
Hi Rick,
Can you please explain me this connection, I am not able to link the “PoRel” and “tranGLC”
We ended up giving up on this report. We were never able to get it working correctly. If you have any luck, I would be interested.
“PORel” is a text string.
That statement means to limit the records from table TranGLC, to those where column RelatedFile has the value “PORel”