Description
Sometimes, when you have stock shortages, the better answer for the company would be to move the order to another branch that can fulfil it without transfers, back to backs, and more.
That may lead to branch manager commission issues but, assuming that’s not an problem, here’s how you can do it.
Solution
In this example, two of the three order lines are out of stock (as indicated by the icon).
You can now switch to my ‘Stock Availability’ tab and see which branches can fulfill this order.
In this case, you might decide to move the order to Sunderland. You could load this view with whatever data or rules you need to consider as part of the decision.
Work Out The Availability
The order isn’t saved at this point, so it’s impossible to interrogate the database for the line information. So we have added a new ‘LISTSUM’ style feature to iQ so that we can build a list of product ID’s and quantities:
Editor’s note: In TFS #37552, check out add support for a LISTTOSTRING function in expressions and calculations that can turn a list into a formatted string.
You can then send that list to a stored procedure to look up availability.
The calculated property looks like this:
You can then add a SQL calculated property to call my stored procedure, pass the item data, and then return a list of information. For this example, the property looks like this.
Note that new property and Connected Property Names:
Editor’s note: In TFS #39942, check out add an option to Custom SQL Properties to have their values refreshed when other properties on the item have changed.
You don’t want that query/grid to refresh more than necessary so you can force a refresh when the TotalQuantity changes (in this case). That covers lines being added, lines being removed or lines having quantity changed.
In the document below, you will find the code from the stored procedure that does all the work. This can be modified for your requirements. There’s a SQL function that’s included at the end.
Attachment: Procedure-Showing Stock Availability for Whole Order