Saved Search To Return All Lines in an Sales Order If Any Item Location=X?

I have a user that needs to see a list of sales orders with all line items where any line item on the SO is set to fulfill from one specific warehouse.

If the SO does not have that location on any line, then it should be excluded from the results.

It does. however need to return all the items and locations on the SO.

I’ve tried using a numeric formula where

CASE WHEN {item.location}=’X’ then 1 else 0 end

But all I get in the results are the lines set to fulfill out of Warehouse X and not all the other lines.

Any ideas?





Rookie Asked on February 11, 2021 in Administration.
Add Comment
1 Answer(s)

You could try using another CASE WHEN statement in another formula field (under results) that counts items not from that warehouse:

CASE WHEN {item.location} = ‘X’ then 0 else 1 end

If you group the sales order and do sums on the two Formula fields you would get a quick overview of sales orders that have items from X and NOT X.

You’d still have to drill down into the order to see the specific items, but at least you’d know.

You can still filter out any orders that have zero items shipping from Warehouse X:

Do a summary criteria with a Sum formula that has to be greater than 0 on your original statement: CASE WHEN {item.location} =’X’ then 1 else 0 end

Hope that helps!

Rookie Answered on January 12, 2022.
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   Become a Sponsor