I am a new to this forum and also learning BAQ’s. Hope someone can help with below query
I am building a BAQ to show all Customers with AR info. We have a UD Field to identify Parent CustomerID or Child Entity Name in the same field, this makes me difficult to build a AR Rollup to show TopLevel Customers(Parent).
First off, using that one field to identify either the Child or the Parent, was a mistake. But that’s what you’ve got to work with.
Is there any other logic behind the value of that UD field? Any other way to tell who the parent is? What if Cust A’s UD value is B, and Cust B’s UD value is A? How to which is which. And any BAQ that would try to determine the parent/child relationship might get stuck on that circular reference.
If the UD field was well defined (say it always holds the CustID of the Parent), then you BAQ would have the Customer table in it twice (the second instance would be automatically named Customer1), with the relationships:
Customer.Company = Customer1.Company
Customer.CustID = Customer1.UDfield
Make the relationship join type: “All rows from Customer”
That would give all the customers, and when no Child exists for a customer, the Customer1 fields would be blank. You would also get several rows for the parent customer, one for each Child customer. Like:
Thanks Calvin. There is no other logic for that UD field except holding Parent Customer ID or Child Entity name.
For example:
If Customer ‘A’ has Multiple child entities: Parent UD Field contains CustID always starting with ABC01, Child UD filed contains Customer Name XYZ and they have a relationship P-C defined in National Account.
If Customer ‘B’ has no Entities: Customer is created with CustID ABC02.
I’ve not used the National Account functionality. It appears to be primarily for billing (since it is under the Billing tab in Customer Maintenance). So using that might cause you more problems, as I assume your using the UD field just for reporting.
Thanks @jkane, We use National Account primarily for billing. I will read more about it if there is an option.
@ckrusen, Reporting and UD field ID is the same CustID used in other systems to identify customer. May I know which system built you were talking about to previously?
@Dek_Reddy yes, you can use National Account for Billing but it can also be used for reporting. I have used it in an old position for reporting only and it worked well.
We have been using National Account functionality. This is a good way to identify parent and child customers. You can have tiered or non tiered relationships. This allows for credit sharing, reporting and payer billto relationships.
You should read the help. It is a standard functionality in Epicor and is very useful. Using it does not have any other effect on using Epicor. Once you have these defined you can use the various Rls* tables to look for the information you want.