Inventory Query Window - Advanced Expression Builder

The following article discusses how to use the "Custom Search Expression" builder in the Inventory Item Query window.  Connected 11.2 or greater is required to use this feature.

ARTICLE CONTENTS

• What is the "Custom Expression Builder" and How Does it Work?
• How does the "Custom Expression Builder" Work?
• Example: Custom Expression for Two Inventory Analysis Codes

What is the "Custom Expression Builder" and How Does it Work?

The Custom Expression Builder is a powerful feature that can be used to create queries that go beyond the capabilities of the quick filters for restocking or quantity metrics.

Fields such as Max Qty or Component Quantities required for current Manufacture Work Orders can be used in custom search expressions. The Custom Expression Builder also works alongside the quick filters expression builder along with quick filter fields, such as Committed and purchase order quantities.

Specific parameters can be used to build queries that are based on manufacturing (Work Order) data for the purposes of restocking those components, which cannot be done by the existing quick filters for items, item types, and dates. The quick filters that can be used are shown in the following screen.

How does the "Custom Expression Builder" Work?

For the Custom Expression Builder to be active, the "Search locations" checkbox must first be selected. Once selected, the "Custom search expression" checkbox is active and can be enabled. When the "Custom Search expression" checkbox is enabled the "On Hand Quantity" quick filters are removed, as they will be replaced by the custom expression entered. 

Adding an expression is similar to building a math or Excel formula. To select data fields, click, as shown in the following screen, to display and select from the available quantity fields.  Refer to the example section of this article to see how operators are added between each field.

Other inventory fields can also be used in the expression and they can be added by using "[ ]" around the selected field.

Example:

@[INIT_ANALYSIS]='EQUIP' This expression indicates that the Inventory Item Analysis Code data field must only contain the code "EQUIP".   Reference: Configuring Analysis (Category) Codes

NOTE: The above example can be done using an Item Quick Filter. However, if 2 or more specific analysis codes are required, then a search expression must be used.

Using Connected Views will help save queries so they can quickly be recalled and used as needed.

Example: Custom Expression for Two Inventory Analysis Codes

The following example looks to restock only items from two specific analysis codes in warehouse location 1.

The expression looks for items where the Available quantity plus the quantity already on Purchase order (in inventory units) less any quantities allocated to a sales order is less than the minimum quantity assigned to that item AND have either the analysis code of "EQUIP" OR "SOFT"

Custom Expression:

@[Available Qty]+@[PO Qty]-@[SO Qty]<@[Minimum Qty]@&[INIT_ANALYSIS]="EQUIP"@~[INIT_ANALYSIS]="SOFT"

NOTE: The above expression can be copied and pasted directly into your Connected Inventory Query window.  Remember to change or remove the Analysis Code filters of "EQUIP" and "SOFT" to ones that match the Inventory Analysis codes used in your company data.

NOTE: The syntax of @& is an AND function and the @~ is the OR function.