Report Data Definition forbids adding a table as a Data Source twice. And Report Data Definition forbids multiple child relationships to the same Data Source. What is the best way to accomplish my business goal?
BUSINESS GOAL: Use APR to deliver an AR Invoice to (A) the person identified in Order Entry as the sold-to contact (join from Erp.OrderHed.PrcConNum to Erp.CustCnt, then grab Erp.CustCnt.EmailAddress for the output) and to (B) the person identified in Customer Tracker as the Primary Billing contact (join from Erp.Customer.PrimBCon to Erp.CustCnt, then grab Erp.CustCnt.EmailAddress for the output).
BLOCK #1: Report Data Definition generates a server error when adding a table twice; this limitation has been confirmed in other threads on this site.
BLOCK #2: As described above, Epicor gives the friendly error “There cannot be two or more Parent tables for the same Child table” when defining a second child relationship in Report Data Definition.
The only idea I have is to build the query I want into a BAQ, then add the BAQ as a data source.
Other ideas?
(My topic is essentially the same as the one at the following link , but I don’t understand that solution, if it in fact is a solution. https://www.epiusers.help/t/rdd-multiple-parent-table-join-a-child-table/45204)
I have the same challenge for emailing invoices to contact listed at the Customer and ShipTo level. Both type of contacts are stored and listed in the CustCnt table. For now I will need to create two APR styles with two separate RDDs.
I tried looking for an existing link to the CustCnt table in the out of the box RDD for the ARform but it did not exist.
I even tried giving the second (CustCnt) an alias name but received a server error on save.
I thought about creating an alias table in the same schema for the CustCnt but decided not to change the Erp schema.
@asmar , you can get the CustCnt as a Linked Table off of the InvcDtl table. Then you could add the CustCnt table to the RDD and join that to another table (InvcHead, Customer, CustomerShipTo, etc.).
Thank you John for your great suggestion. I was able to fine the link, and included link/table does list the key column(s) for the email address of the contact. I am testing this now.
I included the missing columns. Unfortunately, the included previous columns and newly selected return a null value. Also, I am not sure why, but the CustCnt table has more than one row for the selected invoice/customer. So any Invoiced line relationship is going to be a one to many.
What if there isn’t a linked table? I have a similar issue where I am trying to link to PartRev to get RevShortDesc for JobMtl but I also want to get the RevShortDesc for JobAsmbl - both within the JobTraveler RDD.
So a table needs a relationship to get into the RDL to be able to query against there? So, in theory, I could link it just using Company and I’d get the whole table there and then I could modify my query within the RDL?
When you add the table to the RDD with a relationship (Company, PartNum, other), SSRS creates a GUID table of the added table containing the relevant rows.
In the RDL, you will need to add relationship to the GUID table in order to select the relevant columns you need .
When adding the table to the RDD, be sure to include the relevant columns, company, partnum, partrev and so on, before you add the relationship in the RDD.
I did do it. And yes it was TERRIBLE for performance!! So we went a different route. I ended up joining to the JobMtl table which covered all my job materials… then I ended up making a BPM to always write the RevShortDesc to the UserChar4 field in Job Head - so I could show that on the report as well.
