You know you've got a doozy of a saved search when ChatGPT offers a solution that requires SuiteScript rewrite with a subquery!
I could use some advice--even if the advice is "can't do that from a saved search".
SETUP: We are using multi-location inventory. Goal is to have a saved search returning one line per assembly/inventory item, with the SUM of their available stock at selected locations only, and the past 30 days of their quantities sold on order confirmation lines.
What I'm finding is the order confirmation search ends up getting quantities multiplied by the number of inventory locations.
Feels like if this was SQL, I could do an outer JOIN to a subquery on the item ID.
I'm starting with an Item search with the following:
Criteria:
- Inactive is false
- Type is any of Assembly, Inventory Item
- Inventory Location Inactive is False
- Inventory Location is any of (a selected list of locations)
- Transaction : Date is on or after 30 days ago
- Transaction : Main Line is false
- Transaction Type :Order Confirmation
Results:
- Internal ID : Group
- Formula (Numeric) : Sum of {transaction.quantity}
- Formula (Numeric) : Maximum : NVL({quantityavailable},0)+NVL({quantityonorder},0)+NVL({custitem_cm_qty_pending_approval_vis},0)-NVL({quantitybackordered},0)
- Description (and several other text fields) : Group
- Location On Hand : Sum
If I run this same search pulling only inventory or only sales quantities, it works fine. If it were SQL I think I'd be doing a JOIN with a DISTINCT subquery along the way.
Have I just hit the limits here? Or is there some elegant expression building feature that would get this across the finish line?