Does anyone know how to have the sales price from the “Sales Prices” show up as a column on the Inventory Items query?
I have been able to do it, but it duplicates the inventory items many times. Here is how I built the relationships:
What am I missing?
Best answer by bzelinski
View original
+1
It’s going to duplicate a line for every sales price attached to the item. So, if there is a wholesale price, then that requires a line, etc. To achieve what you want, you will need to make a condition for sales class = ‘RETAIL’ as well as one that considers whether the price is active. I think I have one that has a condition which requires the line to be greater than price creation date and less than expiration date.
With these two conditions, you should have only one line per item.
As long as you make it a left join onto the inventory screen, you should not have to worry about losing inventory items that don’t have a retail sales price.
+1
You also might consider adding sales prices as a side panel instead with the item ID as the parameter. This way, the user can click on the line items in the GI and then view/edit the any sales prices in the side panel.
I also have a sales prices side panel in the sales order entry screen which uses the customer’s price class as a parameter to show all items available to that customer. This is helpful in sales.
Thanks,
+1
Are you using CustSalesPriceID rather than SalesPriceID? I’ve just tried the following conditions and I’m not getting any duplicates.
I don’t think the other tables you have connected would cause any issues either. If you still have the issues, feel free to share the xml file
Thanks again,
Thanks again for being so helpful.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.