BAQ grouping help

Epicor ERP 10
1

So I have a BAQ that I want to count the number of rows after it’s been grouped, essentially giving me how many different combinations there are. That works fine. Next, for all of the rows that have only one combination, I need the field that I am looking at to be populated. So I’m trying a calculated field that says, if the count equals one, then display the value of the field. If it’s not 1, then display and empty string.

The problem is, if I try to put that field into the calculation, then tells me it can’t use it, because it’s not part of the grouping. But if I include that field, that I can’t count the number of rows with that specific combination, because the field that I am trying to count would make the row unique, and I get 1 for everything. Here’s a basic example.

Raw data
parent child status
A 1 x
A 1 x
A 2 y
B 1 x
B 1 y

grouped by parent and child, counting by unique status
A1 count 1
A2 count 1
B1 count 2

I can get that working fine, now I need to add a column for the unique status when the count is 1
A1 1 status X
A2 1 status y
B1 2 status ‘’

This is where is fails because I have grouping set up for parent and child, and the count as an aggregate, but if I try to do a conditional statement:

(case when CountRows = 1 then GroupingSuggestions.UD08_ShortChar02 else ‘’ end)

Then I get this error.

Severity: Error, Table: , Field: , RowID: , Text: Column ‘GroupingSuggestions.UD08_ShortChar02’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I pull that field from the row without it being in the group by clause? I tried basically duplicating the sub queries that make this up in order to lookup the one field, but it’s a union that takes a long time, and it didn’t want to finish.

Any suggestions?

2

Put the first query where you group and add the group count field into a subquery. Then do a topmost query that selects from that subquery and adds the new calculated field to it.

3

That’s what I tried. Unfortunately, the list is a union of job material and job assembly, so I end up with 2 of those then, and that bogs down the query so bad that it won’t finish, and I didn’t want to try to maintain 2 unions if I didn’t have to. I was looking for a more efficient way.

What I ended up doing was adding the parent part number into the UD table that is housing the status field. It already had the child part in it, and my process for keeping them in sync had the parent part available, I just wasn’t adding it to the UD table. By adding that in, I don’t have to run the union(s) to find the parent part numbers so it’s all on one table so it should be easier.

We’ll see if I can get it figured out. I wish the JobEntry BO didn’t take so long to maintain UD fields, so I didn’t have to do all of this monkeying around.

4

Since it’s only going to display that field when there is 1 record, then just get the MAX of the field in question when you display it

(case when CountRows = 1 then MAX(GroupingSuggestions.UD08_ShortChar02) else ‘’ end)

1 Like
5

Works like a charm. I knew there had to be an easy way.