What is the best way to bring fields from the item table and customer tables together. I am trying to create a search or report that will show the list items ordered along with the quantities order in a given period, and the qty on hand, on order, available, and the reorder point from the item record. While I can get all of this information on the same saved search, the only issue is showing the locations at the top of the page, with their respective quantities. I keep getting only the location that had the sales order.

When you say the location on top of the page, you mean like in a Report segmented by location(location columns)?

Use DECODE({inventorylocation},’each location’,{locationavailable},0). You will have this for each column and enter the location you want the values. Depending on how your search is built you may need to prefix those field values with item.

Yes, the locations at the top like in a report.

Hi @Mike Hagan have you tried the Sales Order by Item standard report? I just checked and you can filter that report by Location and customize it to add item fields like Current Quantity on Hand and Reorder Point.

I will try that, thanks.

Let me know if it works out :)

So far, I am not able to get the locations at the top, nor am I able to get the location quantities. The report, both summary style and detail shows the quantities for all locations, not just what was filtered to.

Have you tried setting Column to Location on the bottom part of the page and click Refresh? That should show the locations at the top as columns. Also did you add additional filters when you customize the report? (don't forget to check the Show Filter checkbox)

The locations are now showing, however the on hand, available, on order and reorder point quantities are still showing for the whole company,

Yes but if you do that in the Detail view does it show the per order/breakdown per item?

No, it still shows total quantity for company. I have tried to filter on location in 3 different ways, and it still shows the same. The only thing the detail view gives me is the quantity ordered with invoice data for the particular location I drill into. But no location inventory data.

There are two similar fields. “On Hand” and “Location On Hand”. The former will always show the total company holding. The latter will show by location. Try suing the latter in your columns.