We need a saved search that has the following:
- Available inventory per item per location.
- The total quantity of uncommitted inventory for each item/location (from unapproved sales orders).
- It has to include uncommitted quantity from kit item orders.
- We want to filter for only the item/locations where {available} - {total uncommitted} < {some number, hard coded or possibly the preferred stock level}.
- It would be great if we can use this search in a custom reminder on a dashboard.
- Also, we prefer to have a list of links to all the unapproved sales orders on the saved search results.
I tried a few different directions but bumped into multiple issues:
- Depending on the type of summary I'm trying to do, I may end up with an Available Quantity that is multiplied by the number of sales orders or something else.
- In order to get the total quantity of uncommitted inventory, I have to make some summary.
- When I do a summarized order search and add 'Item : Location Available', it will not include the quantities from kit items. When I do an item search and add in a Sum of 'Transaction : Quantity', it includes the quantities from kit items. I don't know why I get these different results.
- I can't figure out how to compare the non-summarized Available Quantity to the summarized Total Uncommitted.
- It looks like summarized searches are not a great fit for custom reminders.
- I figured out how to include the list of linked orders using HTML, I'm not sure though how that will work if I have to change other parts of the search.
REPLACE(NS_CONCAT(NVL2({transaction.internalid}, '<a href=https://system.netsuite.com/app/accounting/transactions/salesord.nl?id=' || {transaction.internalid} || ' target=_blank>' || {transaction.number} || '</a>', '')), ',', '<br>')
If anyone could please help with even a part of this request, it's greatly appreciated.