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).
Any suggestions please?
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:
Cust_CustID Cust1_CustID Cust1_UDfield
Cust’s ABC… and ZYX… have QWERTY in their UD field.
BTW - You know there is a built system for specifying the parent of a customer?
Thanks Calvin. There is no other logic for that UD field except holding Parent Customer ID or Child Entity name.
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.
No, I am completely new to Epicor.
I believe there should be toggle within the Epicor to identify parent same as other systems.
Would you have more information on this?
@Dek_Reddy the functionality @ckrusen is referencing is called National Account. I would search for that in Epicor’s documentation to learn more.
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.
When I mentioned “Parent/Child” it was from seeing it mentioned in the online help:
I’m guessing that’s actually the National Accounts functionality.
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.
Thank you, it does make sense to report from National Account as I am looking for AR info rolledup to Parent customer.
We have defined Relationship for top level customer (P-C/P-C GC) I think this link will help to build report.
I am going to start heavy reading on this topic, please share if you have report examples of these kind.
Unfortunately the Help function has been disabled in our Epicor I have started looking into Rls tables to get some info.