BAQ - Get Row Count of siblings with condition

Epicor ERP 10
1

Hello,
I’m creating a BAQ that lists out InvcDtl rows. I need to get a RowCount for all InvcDtl rows for this InvoiceNum that have a PartNum that starts with 2 characters and ends in a specific character as this defines which commission rate needs to be applied (1-10 = Rate1, 11+ = Rate2, etc) to the extended price for this row.

I’m having trouble getting this info. I’m not sure if I need to use an InnerSubQuery and then link it to the my first query or if this can be handled in CalculatedFields?

I’m running into " Possible unauthorized query" in both cases.

Thank you in advance for your time and effort.
Greg

2

Use an inner sub query to count the rows of InvcDtl
The inner sub q will just have the InvcDtl table, and have 4 displayed fields: Company, InvcNum, and two calc fields
The 2 calc fields are Count(InvcLine) and one that returns just the parts of the PN you want. Something like LEFT(PartNum, 2) + RIGHT(PartNum, 2)
All fields except the COUNT() one will have their Group box checked.

The top query will connect to this sub q.