In a purely general sense, I am looking to find what parts my customers do not buy. I am able to get what they do buy very easily but every time I try to flip it to what they don’t buy I get a zero, which I know is incorrect.
Specifically, I needed to group parts and due ot how things were set up before I came on-board, in testing it was easier to just use the Part Reference since it was not being used. So I created 13 groups and grouped the parts accordingly. Then I have Part–>InvcDtl–>Customer and my BAQ. Display fields are CustId and refCategory. I tried to i use a Sub Query and then in the SubQuery Criteria input Part reference category Not = to any values of Part reference Category subqueryB. Which does not help me at all. Any ideas on what Ia m doing wrong or could do better with this?
@lgraham Thanks for the baq! Been playing with it and is very similar to what i had originally wrote.
@knash I know this is going ot sounds dumb but how do I do the from first subquery part? IS it under the SubQuery criteria? Tried that and wouldn’t let me. So trying to figure out how to invoke that.
After running this and thinking on it, It struck me that I am using ref categories and not part numbers. And we have multiple part number sin a ref category. Thus the output is skewed a little. I am thinking I would have to add the Ref category to the InvcDtl table. thoughts?
I have to group these to keep them from multiplying because I am using the Invcdtl table. It also does this with the orderdtl table as well. But it works out. I have 13 categories and most customers never buy all the categories.
I then create a subquery. This one will show the part ref cat and we will use the first query as a filter for the 2nd. Well, that’s what Ia m trying to do. What I get as the result is blanks for Ref Cat.
You either need to make a calculated field that brings in the result from the subquery, or you need to bring the subquery into the top level as a table
yeah, the calculated field won’t work because you will return more than one row. You’ll have to bring in the subquery to the top level.
I think that you might need to vary your approach here. I think You need to start with a list of customers in your top level. Then have a subquery that has the InvcDtl, part table, and reference category maintenance. Include all of the reference category table (an outside join) In the sub, do a group by on customer number, and reference category. Then do a count of the invoice dtl lines in a calculated field.
That will give you a count of lines within each category for customer that bought things, and 0’s for the reference categories that they didn’t buy. If you don’t care about customers with no details, you can filter there (using the subquery filter) and make that your top level. If you want to include all of your customers then tie that to a top level with the customer list from the top table.
If you want to get the subquery into one row, only group by customer number and add a case statement for each calculated field so it only counts from one reference category. Then you will have a column for each reference category and you can filter easily from there.
That BAQ is very close to what I am looking for. If I could just get all the Ref Cat to populate that would be perfect. In my system, it shows what they have purchased and only a few are zero’s. But otherwise, case statements is my choice of poison. Getting fairly decent at those. Much appreciated.
