Feb 27, 2022 10:20 PM
Hi all,
I have a form which sends data into a grid view. One of these columns has 7 options as a single select each with a rating number followed by a rating name. I created a column within the grid view that runs a script I wrote which essentially checks the input column and isolates the rating number in the new column for quick data pulls. However as our form inputs increase, I created an automation that would run this script every time a form was submitted and lately I have been getting the 15 mutations error. Would anyone be able to help me review my code and see if I wrote an unsustainable program?
let table = base.getTable("Intakes/Submissions");
let view = table.getView("Partner Creator Survey External");
let csat = await view.selectRecordsAsync({fields: table.fields});
for (let line of csat.records) {
let csatRating = line.getCellValue("CSAT Creator");
switch(csatRating.name) {
case "1. Very dissatisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(1)});
break;
case "2. Dissatisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(2)});
break;
case "3. Somewhat dissatisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(3)});
break;
case "4. Neither satisfied nor dissatisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(4)});
break;
case "5. Somewhat satisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(5)});
break;
case "6. Satisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(6)});
break;
case "7. Very satisfied":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(7)});
break;
case "8. Not applicable":
await table.updateRecordAsync(line.id, {"CSAT Score": Number(8)});
break;
}
}
Solved! Go to Solution.
Feb 28, 2022 10:26 AM
Based on your script, every time a new record is added to your table, every single record in the Intakes/Submissions has the value of its {CSAT Creator}
field checked. Is that supposed to be the case? It seems like your script would only need to check the new record and not every record in the table.
But also, is there a reason this is done with a script/automation and not a formula? If {CSAT Score}
is just supposed to be the number at the start of the {CSAT Creator}
field, any of the following formulas would achieve that result without eating up your limited number of Automation runs per month:
1. Using SWITCH() if you want the most basic solution
SWITCH(
{CSAT Creator},
"1. Very dissatisfied", 1,
"2. Dissatisfied", 2,
"3. Somewhat dissatisfied", 3,
"4. Neither satisfied nor dissatisfied", 4,
"5. Somewhat satisfied", 5,
"6. Satisfied", 6,
"7. Very satisfied", 7,
"8. Not applicable", 8
)
2. Using VALUE() if you want to cut to the chase
IF({CSAT Creator}, VALUE({CSAT Creator}))
3.^ that, but adding LEFT() to grab just the front bit before converting to a number)
IF({CSAT Creator}, VALUE(LEFT({CSAT Creator}, 2)))
4. Using REGEX for ambiance
IF({CSAT Creator}, VALUE(REGEX_EXTRACT({CSAT Creator}, "\\d+", "")))
^^^ Each of the formulas will result in effectively the same result, however I would recommend either option 1 or 2 in this instance. 3 and 4 will work fine for single selects and/or positive integers less than 1,000 (no commas or decimals), but get less reliable for multiselects or larger numbers/non-integers
Feb 28, 2022 10:26 AM
Based on your script, every time a new record is added to your table, every single record in the Intakes/Submissions has the value of its {CSAT Creator}
field checked. Is that supposed to be the case? It seems like your script would only need to check the new record and not every record in the table.
But also, is there a reason this is done with a script/automation and not a formula? If {CSAT Score}
is just supposed to be the number at the start of the {CSAT Creator}
field, any of the following formulas would achieve that result without eating up your limited number of Automation runs per month:
1. Using SWITCH() if you want the most basic solution
SWITCH(
{CSAT Creator},
"1. Very dissatisfied", 1,
"2. Dissatisfied", 2,
"3. Somewhat dissatisfied", 3,
"4. Neither satisfied nor dissatisfied", 4,
"5. Somewhat satisfied", 5,
"6. Satisfied", 6,
"7. Very satisfied", 7,
"8. Not applicable", 8
)
2. Using VALUE() if you want to cut to the chase
IF({CSAT Creator}, VALUE({CSAT Creator}))
3.^ that, but adding LEFT() to grab just the front bit before converting to a number)
IF({CSAT Creator}, VALUE(LEFT({CSAT Creator}, 2)))
4. Using REGEX for ambiance
IF({CSAT Creator}, VALUE(REGEX_EXTRACT({CSAT Creator}, "\\d+", "")))
^^^ Each of the formulas will result in effectively the same result, however I would recommend either option 1 or 2 in this instance. 3 and 4 will work fine for single selects and/or positive integers less than 1,000 (no commas or decimals), but get less reliable for multiselects or larger numbers/non-integers
Feb 28, 2022 11:12 AM
@Kamille_Parks You are a lifesaver. I’m not sure why I was trying something so complicated.