Skip to main content

I need a formula to verify a single-line text field only contains productId's separated by commas with <=30 comma separated productId's.

OK: <=30 comma separated 8-character productIds

Not OK: >30 productId's; <8 or >8 character productId's, not comma separated

example (with 5 comma separated productId's)

correct: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9

incorrect:
7 productId's: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9,H4VPMWY7,MRKM1Y6F

5 productId's with missing commas: B0D5FHXP B0D86NWH BCVPRWY7 DRKM1T5G GK3DHGH9

7 productId's with semi-colons: B0D5FHXP;B0D86NWH;BCVPRWY7;DRKM1T5G;GK3DHGH9

 

Assuming the field with productIds is called 'Products,' this would work:

 

 

IF(Products,

IF(

REGEX_MATCH(Products,

'(^[A-Z0-9]{8})(,[A-Z0-9]{8}){0,29}$'

),

"Valid",

"Invalid"

)

)

 

 

Here is a great website for learning about Regular Expressions.

If any other RegExperts want to chime in with a more elegant expression, please let me know! I'm still refining my skills in this area and am open to seeing other ways of going about it.

PS:

The first example you gave of an incorrect set of field input (7 productId's) should be valid given the original constraints, no? (Having less than or equal to 30 comma separated 8-character  productId's). Did you mean to remove a character from one of the product Id's to communicate that one single product Id having 7 characters should be invalid? This is what I assumed to be the case.

Assuming the field with productIds is called 'Products,' this would work:

 

 

IF(Products,

IF(

REGEX_MATCH(Products,

'(^[A-Z0-9]{8})(,[A-Z0-9]{8}){0,29}$'

),

"Valid",

"Invalid"

)

)

 

 

Here is a great website for learning about Regular Expressions.

If any other RegExperts want to chime in with a more elegant expression, please let me know! I'm still refining my skills in this area and am open to seeing other ways of going about it.

PS:

The first example you gave of an incorrect set of field input (7 productId's) should be valid given the original constraints, no? (Having less than or equal to 30 comma separated 8-character  productId's). Did you mean to remove a character from one of the product Id's to communicate that one single product Id having 7 characters should be invalid? This is what I assumed to be the case.


@Kenneth_Raghuna tysm! I'm a beginner when it comes to RegEx! Is there a way to indicate if greater than 30 products, show by how many they are over?

@Kenneth_Raghuna tysm! I'm a beginner when it comes to RegEx! Is there a way to indicate if greater than 30 products, show by how many they are over?


This formula would do it, but there are many limitations to it:

 

IF(LEN(Products) - LEN(SUBSTITUTE(Products, ",", "")) + 1 > 30,

LEN(Products) - LEN(SUBSTITUTE(Products, ",", "")) + 1 - 30

)

 

This formula assumes that the # of Products = # of Commas + 1

It cannot determine whether every comma separated value is valid or not (it is not strictly counting the number of 8-character product IDs separated by commas-- a <8 or >8 character productId would still count as a product).

At this point I would revisit the base design itself to see if there's a smarter way to achieve what you're looking for that is more robust and built with potential future use cases in mind.

Are the productIds coming from a known list of valid IDs? If so, it would be better to use a linked record field instead of a single line text field for the products.

Here's what you would do:

  1. Create a second table listing all possible product Ids, and link it to the first table. Going this route, the product IDs themselves would always be valid (no chance for <8 or >8 characters).
  2. Create a Count field on the first table to count the number linked records (products).
  3. Create a formula field with an IF() statement that checks for validity, and tells you how many over you are if its invalid:

 

IF({Count} < 30, 'Valid',

"Invalid: " & {Count} - 30 & " products over limit."

)​

 

This formula would do it, but there are many limitations to it:

 

IF(LEN(Products) - LEN(SUBSTITUTE(Products, ",", "")) + 1 > 30,

LEN(Products) - LEN(SUBSTITUTE(Products, ",", "")) + 1 - 30

)

 

This formula assumes that the # of Products = # of Commas + 1

It cannot determine whether every comma separated value is valid or not (it is not strictly counting the number of 8-character product IDs separated by commas-- a <8 or >8 character productId would still count as a product).

At this point I would revisit the base design itself to see if there's a smarter way to achieve what you're looking for that is more robust and built with potential future use cases in mind.

Are the productIds coming from a known list of valid IDs? If so, it would be better to use a linked record field instead of a single line text field for the products.

Here's what you would do:

  1. Create a second table listing all possible product Ids, and link it to the first table. Going this route, the product IDs themselves would always be valid (no chance for <8 or >8 characters).
  2. Create a Count field on the first table to count the number linked records (products).
  3. Create a formula field with an IF() statement that checks for validity, and tells you how many over you are if its invalid:

 

IF({Count} < 30, 'Valid',

"Invalid: " & {Count} - 30 & " products over limit."

)​

 


@Kenneth_Raghuna First off, your second solution is perfect! Sadly, it would rely on a user inputting the correct productIds, which is an uphill worfklow-education battle (for me). I'm going to keep it in my back pocket as an future option. Again, thank you so much for this additional approach!

Reply


Terms & Conditions