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
