I’ve been asked to add the AP Invoice Description to the General Ledger report for PJ journal entries. The only actual table in the RDD is the Company table. I tried adding the GLJrnDtl and APInvHed tables to the RDD and then joining them to the Header dataset in the report definition. I added the Description of the APInvHed (with an alias since there are multiple description fields in the various sources) to the actual query along with the list of fields for the dataset but it never displays any values.
What am I missing?
I see what you are saying now. I had this same problem awhile back because the tables in the RDD are temp tables created by Epicor. So even though you reference them with relationships on a phsyical table. You still wont see the data since its being lost upon run time. What I had to do was go into the expression of the dataset and add an extension with query syntax. But in your case the reason why you are not receiving data my guess. Is that I am assuming you joined the Temp Header table via “GLACCOUNT” on “ERP.GLJrnDtl”. The problem is that if you look into that table it does not record information for the “APINVOICENUM” field. So you have no reference back to the “ERP.APInvHed” table. I do not see any other reference you can join back on to tie in the invoice number to get the description.
FROM GLedgerLevels_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN Header_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company
AND T1.GLAccount = T2.GLAccount
LEFT OUTER JOIN Detail_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company
I don’t have any relationship set up for the 2 new tables (although I tried that also with the relationship set to output). I see 2 new data sources in the report for these tables.
The hard part is determining what part of the Desc is the Vendor name, and what part is the Invoice. You can’t even assume the vendor name starts at the first non number. Invoice 12345 from Vendor “3M Industries”, would show as “IV:123453M Industries”.
Which would decode as Invoice 123453, from vendor “M Industries”
I think you are going to have to try and link the GL entry to the APInvcDtl, via the TranGL table
Can I link GLJnlDtl to the Detail datasource on Company, GLAccount, JournalCode, JournalNum and JournalLine and then link APInvHead to GLJnlDtl on Company, Invoice and Vendor?
I think JournalNum is reset to 1 each fiscal year. Which means that JournalCode-JournalNum-JournalLine is NOT unique. You might have to skip that if its not in any of the existing RDD tables, and figure out another way to make sure the TranGLC record is the right one.
There may be another solution in a different thread. Some users have modified the posting rule so the journal entries have the description that you want instead of trying to rebuild a description. Paul Gardner did a talk on this at the 2017 Insights. Maybe someone can dig up the handout…
We have some parts that are set up with the Primary UOMs as EA. (each). These parts are actually a case of the product. There is also a CS (case) UOM on these parts that has a Conversion Factor of 1. Some trading partners order with a UOM of CS and the quantity is the number of cases they are ordering. Some trading partners are ordering with a UOM of EA. and the quantity is the number or units within the case they are ordering so the actual number of the part that should be shipped is equal to UOM EA. divided by the number of units in a case.
For example one trading partner is ordering 10 CS and another trading partner is ordering 120 EA. of the same product. Both of these equate to 10 of the part to be shipped since the part is a case of 12 individual items.
Is there a way to set up Customer Specific UOMs or some other way to set this up in the system so that the correct number of parts get shipped?
Hi Calvin,
i tried your method, however i get the error message while running the new RDD.
i think it it because it had conflict with table relationship “Detail2OneTimeGLDetail” as it had the same relationship field with “TranGLCtoDetail”
One thing I’d do is make the Table Relationship have a type of Output. This will make sure it gets the original records even when a matching record cannot be found in TranGLC.
Hi Calvin,
attached is my setup of RDD for your kind perusal. i have set accordingly based on your advice,
however i still get the error messages. Thanks.