Can someone help with a CASE WHEN formula? I'm trying to create a formula on a transaction saved search when the item name ends with "-S" then to show the quantity, but if it's not "-S" leave it blank. Internal ID Item Name: itemid Quantity: quantity (this is the Sales Order Line level qty) I have this, but it's not working: CASE WHEN SUBSTR({itemid}, LENGTH({itemid}) - 1, 2) = '-S' THEN {quantity} ELSE '' END

You want to use NULL instead of leaving the end blank. Tim's formula does that.

Thank you both. When I tried to run the saved search now it's giving me an invalid expression error. To give you more background info, On the transaction saved search, I want to have separate columns for sizes. One formula for S, the other for M, the other for L, and so on. Our item names end with the size. For example, one will end with "-S" or "-L". So for the saved search, I'd like to have Column S show me the quantity for just Smalls and Column M just the qty for mediums. I'm hoping this makes sense and gives more clarity.

What Tim said, esp if your ItemIDs are numbers. That may be what's causing your invalid expression as you're sourcing the wrong field. Using Item Name (or whatever the field is where the names have the -S appended should solve your issue).

Thank you both for all your help! It worked! Very grateful

You can also use LIKE which will search any part of the text to find your string. You also don’t need the ELSE NULL. For example CASE WHEN {item} LIKE ‘%-S%’ THEN {quantity} END