Friday, April 5, 2019

Display Location Data as Columns in an Item Saved Search

When Multi-Location Inventory feature is turned on, Location data (e.g. On Hand, On Order, In Transit, Committed, etc.) is displayed on a per-row basis when used in an Item Saved Search. The ability to use Location as a column in a Saved Search is still filed as Enhancement# 120472. As an alternate solution, users can use advanced formula.

To illustrate, assume that a user wants to show Location On Hand on each column for a Saved Search and that there are three Inventory Locations: Missouri, California and New York.

1.    Navigate to Transactions>Management>Saved Searches>New

2.    Select Item

3.    On the Results tab add the following fields:

Name (Summary Type = Group)

Display Name (Summary Type = Group)

Formula(Numeric) = DECODE({inventorylocation}, 'Missouri', {locationquantityonhand}) (Summary Type = Maximum)

Formula(Numeric) = DECODE({inventorylocation}, 'California', {locationquantityonhand}) (Summary Type = Maximum)

Formula(Numeric) = DECODE({inventorylocation}, 'New York', {locationquantityonhand}) (Summary Type = Maximum)

4.    Hit Preview or Save and Run

Users can enter values for Custom Label and Summary Label for each formula added to change the column headers. Also, users can add other fields on the Results tab as long as a specific Summary Type (e.g. Group) is selected.

NOTE: The number of formula that needs to be added on the Results tab depends on the number of Inventory Locations. If a user has another location, say Alabama then a new formula needs to be added: DECODE({inventorylocation}, 'Alabama', {locationquantityonhand}) (Summary Type = Maximum)

1 comment:

  1. How can you show a final column to show the total qty of all locations combined?