Skip to main content

I have a somewhat complicated formula I wish to create, and as a formula n00b, I don't know where to start. Here's the problem I'm trying to solve:

1. I have a series of tags I enter into a single text field, where each tag is separated by a comma. I have two conditions to meet before I list a product with the tags, otherwise, the Make.com automation won't work, and I'll have to spend a fair amount of time deleting a partially created listing and re-editing the record in Airtable. These conditions are 1) I can't have more than 13 CSV tags in the field, and 2) I can't have any duplicate tags.

2) I would like to create a formula that displays an ❌ in the formula field if the tags CSV field has more than 13 tags AND/OR duplicate tags. If both conditions are met, display a ✅.

Thanks in advance to all the formula wizards out there willing to help!

 

Hi @John_Anderson ,

the first condition is quite easy to cover in a formula. You just "count" the number of commas in a cell and if it is above 12 (13 Items) it will give you a positive.

Formula: LEN({String}) - LEN(SUBSTITUTE({String}, ",", ""))>12

The formula outputs a "1" if the count is above 13 and a "0" if the count is below 13. 

The second condition is trickier. I don't see how it can be resolved cleanly in a formula. You might have to use a script for doing this. Just add the "scripting" extension and add the code below. It will populate a result field name with "Yes" if it has duplicates and "No" if it does not. If you updated the records, you would need to rerun the script (could also be combined with an automation).

let table = base.getTable("Your Table");
let query = await table.selectRecordsAsync();
let fieldName = "String";
let resultFieldName = "More than 13";

for (let record of query.records) {
let str = record.getCellValue(fieldName);
let hasDuplicates = "No";

if (str) {
// Split the string by commas and trim each item
let items = str.split(',').map(item => item.trim());
let uniqueItems = new Set(items);
if (uniqueItems.size !== items.length) {
hasDuplicates = "Yes";
}
}

await table.updateRecordAsync(record.id, {
[resultFieldName]: hasDuplicates
});
}

Please make sure that if you run it, you update the first, third, and fourth lines of the code to include your table name, your "String Field" and your "Results" field.

Let me know if it works! 

Best, 
Patrick @MoninoSolutions

E-Mail: patrick@moninosolutions.com 

Hi @John_Anderson ,

the first condition is quite easy to cover in a formula. You just "count" the number of commas in a cell and if it is above 12 (13 Items) it will give you a positive.

Formula: LEN({String}) - LEN(SUBSTITUTE({String}, ",", ""))>12

The formula outputs a "1" if the count is above 13 and a "0" if the count is below 13. 

The second condition is trickier. I don't see how it can be resolved cleanly in a formula. You might have to use a script for doing this. Just add the "scripting" extension and add the code below. It will populate a result field name with "Yes" if it has duplicates and "No" if it does not. If you updated the records, you would need to rerun the script (could also be combined with an automation).

let table = base.getTable("Your Table");
let query = await table.selectRecordsAsync();
let fieldName = "String";
let resultFieldName = "More than 13";

for (let record of query.records) {
let str = record.getCellValue(fieldName);
let hasDuplicates = "No";

if (str) {
// Split the string by commas and trim each item
let items = str.split(',').map(item => item.trim());
let uniqueItems = new Set(items);
if (uniqueItems.size !== items.length) {
hasDuplicates = "Yes";
}
}

await table.updateRecordAsync(record.id, {
[resultFieldName]: hasDuplicates
});
}

Please make sure that if you run it, you update the first, third, and fourth lines of the code to include your table name, your "String Field" and your "Results" field.

Let me know if it works! 

Best, 
Patrick @MoninoSolutions

E-Mail: patrick@moninosolutions.com 


The script works perfectly! Thank you so much for the help, Patrick! You are a true wizard 🙏

Reply


Terms & Conditions