Help

Re: Script: delete records based on a number field

482 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Khamhui
4 - Data Explorer
4 - Data Explorer

Hello everyone, 

I am having some issues working on a script that I made using Copilot. 

Here's the reason why I am making it: 

I have a base which is composed of two tables: Table A and B. I am doing this because, in my company, we review conversations from customer service in order to improve it. 

Basically, Table A is composed of a form, and we get to see the results to the different questions and one big score (which is a formula field). 

I made Table B, so a second team can work on conversations which have a QA Score below 80. 

To do this, I need to create an automation which is composed of 3 steps: 

1. When a record is updated in Table A (the QA score appears after grading the conversation)
2. Create a record in Table B and add some information: customer's name and Table A QA Score (it's a number field now)
3. Since any record will be added to Table B, I made a script to delete any records with a QA Score above 80. 

This is where I run in some issues. The script seems to delete any records whatever the score which defeat the purpose of it.

Here's the script:

console.log(`Hello, ${"BPO QA"}!`);

let table = base.getTable("BPO QA");

// Debugging: Log the "Table A QA Score" of all records
let allRecords = await table.selectRecordsAsync();
allRecords.records.forEach(record => {
console.log(`Record ID: ${record.id}, Table A QA Score: ${record.getCellValue("Table A QA Score")}`);
});

let query = await table.selectRecordsAsync({
fields: ["Table A QA Score"],
filterByFormula: "{Table A QA Score} > 80"
});

let recordsToDelete = query.records;

let outputText = `Records to be deleted:\n`;
recordsToDelete.forEach(record => {
outputText += `Table A QA Score: ${record.getCellValueAsString("Table A QA Score")}\n`;
});
console.log(outputText);

// Delete records
await table.deleteRecordsAsync(recordsToDelete);
console.log(`Records deleted successfully.`);


Do you have any idea on how to solve this? Thank you so much!

1 Reply 1

This is an automation script, right?  If so, `filterByFormula` doesn't work here I'm afraid

If I were you I'd create a view that filtered to show all the records I wanted deleted, and then grab all the records from that view and delete them; seems simpler