Skip to main content

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? 

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.


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, @brendan91. I’ve got it looking a bit better, but I still have 1 duplicate of each item. Still trying to figure it out. I don’t have any expiration date on the sales prices, and I only have one sales price option for each one. Not sure what’s happening yet. :-)

 


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, @brendan91. I finally got rid of the duplicates by setting the exact date of our sales price worksheet. If we do another price update in our system, I’ll just update the date, or there will again be duplicates.

Thanks again for being so helpful.


Reply