Skip to main content

If statements categorizing products from first # in serial #

  • July 25, 2023
  • 6 replies
  • 47 views
H
Forum|alt.badge.img+3

Hello!

I am trying to create if statements to categorize products.  For example: if the product id is 02568, it will be category a; if its 12487 it will be category b; if 25777 it will be category c, an so forth.  However, each product id number is not the same.  So, category A would have serial numbers like 03577 as well as 06554 and so forth.  Each category starts with the same first number.  First problem is trying to identify in airtable only the first number to categorize.  I tried doing this: 

if({Product ID #}=0, "A", IF({Product ID #}=1, "B", IF({Product ID #}=2, "C"))))
However, airtable says "Sorry, there was a problem saving this field. Invalid formula. Please check your formula text."  Please help!
 
My second issue that I'm trying to fix is for airtable is to not drop the 0 in the category A product ID.  Please help!  
 
Thank you so much!

6 replies

BillH
Forum|alt.badge.img+24
  • BillH
  • Brainy
  • July 25, 2023

If Product ID # can be changed to a Single Line Text that will solve the issue with dropping the leading 0.  

Then if({Product ID #}=0...) can become if(LEFT({PRODUCT ID #}, 1)="0"...) 

H
H
Forum|alt.badge.img+3
  • Helen_Harrell
  • Author
  • Participating Frequently
  • July 25, 2023

If Product ID # can be changed to a Single Line Text that will solve the issue with dropping the leading 0.  

Then if({Product ID #}=0...) can become if(LEFT({PRODUCT ID #}, 1)="0"...) 


Is there a way to extract just one number from a string of numbers without doing it manually?

BillH
Forum|alt.badge.img+24
  • BillH
  • Brainy
  • July 25, 2023

I don't understand the question, or at least the specifics of it.  

What I suggested would return the first character in the string. 

If you're looking for the character at a specific position I would use a combination a of LEFT() and RIGHT() - RIGHT(LEFT({field name}, 4),1) should give you the character in the 4th position, but if 10 people respond they could probably give you 10 different variations. 

FIND() and SEARCH() can tell you if a specific character occurs in a string, and SEARCH() returns the location.

 

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • July 25, 2023

For example, this formula could be.
In this formula, the product ID needs to be String field.

SWITCH(LEFT({Product ID #},1), "0", "A", "1", "B", "2", "C", BLANK() )

 

H
Forum|alt.badge.img+3
  • Helen_Harrell
  • Author
  • Participating Frequently
  • July 26, 2023

For example, this formula could be.
In this formula, the product ID needs to be String field.

SWITCH(LEFT({Product ID #},1), "0", "A", "1", "B", "2", "C", BLANK() )

 


What is the point of having the Blank() in here?

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • July 26, 2023

SWITCH(expression, [pattern, result ... , default])

The last argument is an optional default value that is not necessarily needed.
For example, you could put an error message here.

Terms & ConditionsAccessibility statement