Can I create a saved search to identify available inventory at 2 separate inventory locations in one search?

I need to create a saved search based on criteria at 2 different inventory locations.

Specifically identifying items available greater than or =1 at location 1 and inventory available = 0 at location 2. I am stuck and horrible with expressions

Rookie Asked on December 10, 2020 in Saved Searches.
Add Comment
5 Answer(s)

Hi,

Are you only wanting to look at two locations or is this heading in the direction of wanting to do something more general?

Thanks,

Chris

Intermediate Answered on December 11, 2020.
Add Comment

I just need to look at these 2 locations…  I’ve tried a few different formulas, nothing working…

 

 

Rookie Answered on December 11, 2020.
Add Comment

 

not sure how complex your rules are,   hopefully this helps

use a numeric formula on criteria where value =1 ……… when the “when” statements are =1 then  your rules are met.

case when {inventorylocation} like ‘%Quiet%’ and {locationquantityavailable} =0 then 1 when {inventorylocation} like ‘%Beac%’ and {locationquantityavailable} >1 then 1 else 0 end

 

Rookie Answered on December 11, 2020.
Add Comment

Hi again,

I’ve been having a think about this and I think this solution will get you exactly what you’re looking for.

Firstly, it’s important to understand that we’re always going to get two rows per item because we’re joining to two different locations and so we’re going to need to do some grouping.

After that, we’re looking for each row to meet a separate criterium and so we’ll give it a score of 1 if it does. Then, if we add up the scores, the items that have a score of 2 will have both lines matching our criteria.

To achieve this, you need to use summary criteria like this:

RE: Can I create a saved search to identify available inventory at 2 separate inventory locations in one search?

CASE WHEN {inventorylocation.internalid}=1 AND NVL({locationquantityavailable},0)>=1 THEN 1 WHEN {inventorylocation.internalid}=2 AND NVL({locationquantityavailable},0)=0 THEN 1 ELSE 0 END

Then in Results, choose a summary type of Group.

I hope that helps.

Thanks,

Chris

Intermediate Answered on December 14, 2020.
Add Comment

THIS WORKED!!! YOU ARE AMAZING! Thank you so very much Chris….

 

Rookie Answered on December 14, 2020.

No problem and I’m really pleased we’ve found you a solution.

on December 14, 2020.

The only problem I’ve having is  what I’m putting in the standard criteria and the results in order to group. Will grouping eliminate the 2 rows/item or will we have to filter after the fact?

 

 

on December 15, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor