I have a generic inquiry that shows the average cost of each finish good item.
Where I am running into an issue is we have some items that are “bundles”.
For instance say I have a Red Widget that has an Inventory ID of RWidget, and the average cost of one Red Widget is $5.00, I also sell this item as a bundle of 3 and I an Inventory ID of RWidgetX3 for the bundled item.
NOTE: These bundles are not entered as a kit and are not a “stock item”, the Inventory ID of RWidgetX3 simply tells the picker to pull 3 of this item for the order.
The issue I am running into is that these bundles don’t have an average cost in the system.
In my generic inquiry I created a column called X3 and I am using the following code to flag the bundled items. =iif(Substring([InventoryItem.InventoryCD],7,2)='X3','True','False') This is working as is expected, I get a list of all the Inventory IDs, and the ones that end in “X3” show as True in my X3 Column, and the rest show as False.
What I would like to do is expand the code so that if the Inventory ID ends in X3 that generic inquiry will pull the average cost for the base Inventory ID and multiply it by 3 to give the average cost for the bundle.
Inventory ID Description Average Cost
RWidget Red Widget $5.00
RWidgetX3 Red Widget Bundle $15.00
Thank you for any assistance...
Best answer by Laura02View original