Hello.
I am seeking assistance with consolidating specific column values in a One-To-Many table relationship. Specifically, I am building a BAQ that shows Customer Data, and also needs to include a calculated field (or fields) to indicate which Document Names are found in the CustomerDocs table. Ultimately, this will go into a Dashboard, so keeping it slim is definitely preferable. Here’s what I have right now:
As you can see, Customer 1017 has three records in CustomerDocs, resulting in three rows in the output. I would like to learn how to “flatten” this so there is only one row for every customer, and checkboxes (or some other indication) identifying the various document types for that customer. My best guess is that I will need a subquery, but after several unsuccessful attempts I’m hoping the Epicor community can help with this.
Thanks, Tony G.
Hi Tony,
I think you can make this work with the String_Agg() function. You can create a customer BAQ with the CustomerDocs inside the String_Agg. Use it in a calculated field expression like this:
String_Agg(OrderHed.OrderNum, ', ')
Make sure you group on your customerID, and your aggregated string should return all the docs they have listed. Now if you want to break that string agg up into individual check boxes, that is another step.
Create calculated fields for each document check box (850, 855 etc) The calculated field expression would just check to see if its value appears in the string agg. If my calculated field above is called “Orders”, I can test that with an expression like this:
iif(Orders LIKE '%12345%', 1,0)
This would be the expression for the bit field you want to check. You need one of these for every type of document you want a check box for.
This seems a little clunky to me, so I bet there is a more elegant way to do it.
I hope this helps!
I’ll need some more clarification since I haven’t yet got my head around the concept of Group By in a BAQ. For starters, I see that in your example, you reference OrderHed.OrderNum, but my BAQ only has Customer and CustomerDocs. I tried replacing that with Customer.CustID, and checked Group By in the Column Select tab, and Sort By CustID, but the Analyze Test always gives a SQL error.
I read through the BAQ Course doc, and see that in all the examples where Group By is referenced they always create a Calculated Field using Count(), but this didn’t seem to help either.
I’m sure it’s something simple – I just need a gentle push in the right direction so I can do one simple query successfully.
If you have access to the Education Environment have a look for a BAQ ID of Monthly Sales, that should give you a good starter on how to do this in a simple way
If you can’t see it, let me know and I will spin up my 10.2.700 VM to locate and post
At first don’t do any group by. Put in all customers, and all their documents. In the BAQ Analyzer you can take a look at those results, and then right click on the header row and turn on group by. choose to group by customer name or ID, for example. This will collapse all your results into a list of just the customers. Then you can expand that list to show you what each customer has for documents.
Once you use the analyze tab to figure out which field(s) you want to group by, go back to your top level, and turn on the group by checkbox for every field in the list. In your case, at first, every group by check box should be clicked. You only leave the group by box unchecked for calculated fields that aggregate the data in some way. You mentioned count(), there is also sum(), avg(), first(), and many others. String_Agg() is one of the ways you can aggregate data.
So, you have built up a BAQ (show us the SQL), it should include the Customer and CustomerDocs tables, and they should be joined by the customer num. However, if you want this grouping to work correctly, then don’t include any fields from the CustomerDocs table. This may mean that you have to remove that field from the BAQ you already created. That is ok. You are going to add it back in as part of the aggregate calculated field. It will be the only field that is not checked “group by”.
It should be something as simple as this:
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[CustNum] as [Customer_CustNum],
[Customer].[Name] as [Customer_Name],
(String_Agg(CustomerDocs.DocumentName, ',')) as [Calculated_DocList]
from Erp.Customer as Customer
inner join Erp.CustomerDocs as CustomerDocs on
Customer.Company = CustomerDocs.Company
and Customer.CustNum = CustomerDocs.CustNum
group by [Customer].[CustID],
[Customer].[CustNum],
[Customer].[Name]
