Can someone help with a CASE WHEN formula?
I'm tr...
# suiteanalyticsk
Koon Kabob
03/12/2024, 2:47 PMCan 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
a
Anthony OConnor
03/12/2024, 4:41 PMI think that's going to give you a 2 character string STARTING at the S...
I think you'd need
Copy code
CASE WHEN SUBSTR({itemid}, LENGTH({itemid}) - 3, 2) = '-S' THEN {quantity} ELSE '' ENDAnthony OConnor
03/12/2024, 4:54 PMactually you probably want to use at negative number as the second SUBSTR argument... rather the using LENGTH.. if you use a negative number it counts from the end of the string you passed in
Copy code
CASE WHEN
SUBSTR( {itemid}, -2, 2) = '-S'
THEN {quantity}
ELSE '' ENDAnthony OConnor
03/12/2024, 4:54 PMwhat kind of formula field are you using?
Anthony OConnor
03/12/2024, 4:56 PMbecause I think
{quantity} Copy code
CASE WHEN
SUBSTR( {itemid}, -2, 2) = '-S'
THEN {quantity}
ELSE NULL END❤️ 1
k
Koon Kabob
03/14/2024, 6:07 PMYes, you're right! I made the change and it worked! Thank you so much!
👍 1
12 Views