By using this website, you agree to our Terms of Use (click here)
Hello,
I'm trying to create a GI that shows all orders that only have a inventory ID that starts with "6". These items are our Holiday gift boxes.
I don't want any orders to show up where someone orders a gift box plus additional items.
If the order includes multiple gift boxes (60001 and 60002 for example) then it should appear on the list.
If the order includes 60001 and another item 10003, then it should not be included.
Does anyone have any thoughts on how I could get this started?
Thanks!
This is not what you asked for specifically, but in SQL you could do this:
SELECT * FROM
SOOrder o
LEFT JOIN SOLine l ON (o.id = l.OrderNbr)
WHERE
o.id NOT IN (
SELECT sub_o.id FROM
SOOrder sub_o
LEFT JOIN SOLine sub_l ON (sub_o.id = sub_l.OrderNbr)
WHERE
sub_l.InventoryID NOT LIKE '6%'
)
I'm still very new to Acumatica, so I'm not sure if Gi supports sub-queries. I will look into that for my own curiosity unless someone else can chime in.
Also, this could be manually turned into a View in the database so it would possibly become compatible with GI, but I think you'd need a DAC for that... might be a good candidate for a Customization Project. However, I'd love know if there's an easier way too!
Join SOOrder to SOLine and SOLine to InventoryItem
You can then set a condition the InventoryCD starts with 6
You could also add an attribute to the item called Holiday Boxes and filter the condition based off the attribute.
@alconroy The tricky part is that he does not want any orders that also contain line items that don't start with 6.
A condition of starting with 6 returns orders that have both gift boxes and non-gift boxes.
Exactly, that's where I've been having trouble. This works for showing only line items that start with 6, but it still has order numbers on there that have other items.
Instead of setting the condition so that it starts with 6, what about setting a condition so that it excludes items that start with 1-5 (assuming that 6 is the highest start), or excludes 1-5 and 7-9 ... would that do the trick without overthinking?
@anne-r-trockman This issue isn't getting items that start with 6 to show. The trouble is showing items at the Order Level that ONLY include items that start with 6 at the line item level.
I want to show orders where people ONLY bought gift boxes (items that start with 6), so it needs to exclude the orders where someone bought a gift box and something else.
@anne-r-trockman unfortunately, that still puts us back in the same spot where it returns orders that contain both.
My example only has the Acumatica test data but the same applies if we only want InventoryID's that start with "A".
The issue is that the orders in the red boxes are still being returned even though we, say, excluded "B-Z"
I sometimes take a two step approach if the outcome is that specific.
Create a column in the results list that checks for the 6 and with the group by at the order level if a non 6 occurs on any line then max returns. =Max(IIf( Left( [InventoryItem.InventoryCD], 1) = '6', 0, 999))
Then just use a filter on the GI output to only show the 0
@richard-heap I was trying to see if I could avoid filters, but this gets the job done. I wonder about how it scales for larger datasets since this will require a full results scan. Now if we could add a "HAVING" clause to it, then we'd get full performance.
Working with the tools they give us. 😀
Perhaps take a different approach and just select orders with the item 6 on it, sum the soline.qty on the lines and compare to the total soorder qty. If different then clearly other things on it.....