By using this website, you agree to our Terms of Use (click here)
I have the basics of generic inquiries down. If we sell apples, I can return all sales orders with apples. If we sell oranges too, I can return all sales orders that have oranges on them. (add SOLine table, then in results grid display the InventoryID_Description). I can use an OR condition to display all SOs that have either an apple or an orange on them.
The problem comes when I want to display only the SOs for customers who bought both and apple and and orange on the same SO. Using an AND condition on SOLine.InventoryID returns no data, presumably because no one line on my sales orders has both and apple and an orange. How can I find every sales order that includes both apples and oranges?
Thanks!!!
Hi Drogge,
Thanks for joining ARC.
Are you just looking for if they purchased Apples and Oranges or could it be any combination of two items?
Tim,
Only if they buy and apple and an orange, or two apples and an orange, ore three oranges and an apple. If they buy an apple and a banana, that sales order shouldn't show up in this report.
Thanks!
Got it.
I'm thinking that you could compare the MIN InventoryID to the MAX InventoryID to see if they are the same. That would tell you which orders to keep
Generic Inquiry
I don't think you can do this cleanly in Generic Inquiry because you have to be able to use the result of an aggregate calculation in a formula to filter them out.
Use the SOLine DAC Table and group on OrderType and OrderNbr to get one record per order.
Then you could grab the MIN InventoryID and display it using this on the Results Grid tab:
But that won't work because we need to look at both MIN InventoryID and MAX InventoryID.
So you'd probably want to use a formula like this:
=IIf(Min([SOLine.InventoryID])=Max([SOLine.InventoryID]),'No','Yes')
Which would give you this as a result:
You can compare the previous screenshot to the actual data set which looks like this:
The problem is that you can't filter on the More Than 1 SKU column because you will get this error message:
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
Report Designer
It's a little cleaner in Report Designer because you could still group by OrderType and OrderNbr then suppress the printing of the group section for records with only one SKU like this:
Here is what the report looks like on my demo data:
Here is the sample timreporttest001.rpx Report Designer file that put together if you want to use it as a starting point.
https://timrodman.s3.us-east-2.amazonaws.com/forums/3-timreporttest001.rpx
But I'm not sure if you can get a total count using a formula. I think you can, but I haven't tried it yet. Maybe you can try it and let me know.
Excel or Power BI
This kind of thing is really best done in Excel or Power BI in my opinion.
Excel and Power BI are really good at working with aggregates in their formulas.