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

I'd expect the field to be

{item}

, not

{itemid}

@Koon Kabob Your formula works for me when using

item.name

over

itemid

Thank you. 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.

@Koon Kabob A numeric formula where I leave out

ELSE ''

works for me

Like @erictgrubaugh suggested except you need to test the position is not '0' so like this I think.

CASE WHEN
  REGEXP_INSTR({item}, '.*-S$') != '0'
THEN
  {quantity}
ELSE
  ''
END

Thank you all so much! It works 🙂 Hoping you all win the lottery today lol