- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 14, 2025 03:28 PM
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
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 14, 2025 05:20 PM
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:
- 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).
- Create a Count field on the first table to count the number linked records (products).
- 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."
)​
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 14, 2025 04:04 PM - edited ‎Feb 14, 2025 04:11 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 14, 2025 04:43 PM
@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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 14, 2025 05:20 PM
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:
- 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).
- Create a Count field on the first table to count the number linked records (products).
- 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."
)​
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 16, 2025 02:25 PM
@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!
