How to create by formula on a saved search a specific combination of items and locations For example, show only results of specifics items for an specific location, and other items for another specific location

AI generated answer (using internal Slack threads):

To create a specific combination of items and locations in a saved search using a formula, you can use the

DECODE

or

CASE

function in your formula. Here are two examples: 1. Using

DECODE

function: You can use the

DECODE

function to return the available quantity for each location. For example, if you want to show items where the inventory available is greater than or equal to 1 at Location 1 and the inventory available is equal to 0 at Location 2, you can use the following formulas:

Formula (numeric) DECODE({inventorylocation},1,{locationquantityavailable},0) >= 1

Formula (numeric) DECODE({inventorylocation},2,{locationquantityavailable},0) = 0

2. Using

CASE

function: If you want to display the quantity available in a specific location in a transaction search, you can use the

CASE

function. For example, if you want to show the quantity available in "Phoenix" location, you can use the following formula:

CASE WHEN {inventory.location} = "Phoenix" THEN {location.quantity.available} END

You can also use the

CASE

function to avoid duplication of numerical values in your search. For example, if you want to grab the location on hand from the item record based on the location on the transaction, you can use the following formula:

CASE WHEN {location.id} = {item.inventorylocation.id} THEN 1 ELSE 0 END

Then, in the criteria of the search, you can set it up like this:

CASE WHEN {location} = {item.inventory location} THEN 1 ELSE 0 END Is equal to 1

This will ensure that you only get 1 row instead of it being duplicated multiple times. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******