Skip to main content
Solved

Merging records with multiple parameters (script)

S
Forum|alt.badge.img+2

Hello, 

I'm trying to merge records in my table when 3 criteria match. 

I've been able to identify which records are duplicates, but I am having trouble merging them. Logically I believe my code should do what I want it do, I am not super familiar with asynch and I think that is what is messing me up. 

When I run this code it throws: 

Syntax error:
await is only valid in async functions and the top level bodies of modules [script.js:33:9]
 
I've been reading through documentation and discussion posts, but I'm not sure what is happening to cause it to throw an exception. Any help would be appreciated. 

 

 

var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); //let records = query.records; let upPN; let upEM; let upID; //identify duplicates let duplicates = query.records.filter((record) => { return query.records.find((potentialDupe) => { let fName = record.getCellValue("First Names") === potentialDupe.getCellValue("First Names"); let lName = record.getCellValue("Last Name") === potentialDupe.getCellValue("Last Name"); let resi = record.getCellValue("Residence") === potentialDupe.getCellValue("Residence"); let ident = record.id !== potentialDupe.id; if (fName && lName && resi && ident){ if(record.getCellValue("Phone Number") == null && potentialDupe.getCellValue("Phone Number") !== null){ upPN = potentialDupe.getCellValue("Phone Number"); upID = potentialDupe.id; } if(record.getCellValue("Email Address") == null && potentialDupe.getCellValue("Email Address") !== null){ upEM = potentialDupe.getCellValue("Email Address"); upID = potentialDupe.id; } return record.id; } //if await table.updateRecordAsync(record, { "Phone Number": upPN, "Email Address" : upEM, }) await table.deleteRecordAsync(potentialDupe.id); })//return });//let console.log(duplicates)

 

Best answer by dilipborad

Hello @seaTurtlesRUS ,

If the duplication detection and removal process is manual then First, check this extension Dedupe - Apps - Airtable Marketplace as @ScottWorld mentions. If that works it's best to use it, there are lots of flexibility in field selections.

Otherwise, Try this.

The error message you're encountering is that you're using the await keyword inside a callback function that is not marked as async. Specifically, the await keyword is used inside the .filter() method's callback function, which is not itself an async function.

To fix this issue, you'll need to refactor your code because .filter() cannot be directly used with asynchronous operations like await. Instead, you can use a loop to iterate over the records and perform the necessary asynchronous operations.

Here's a revised version of your code that addresses this issue:

 

var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); let duplicates = []; // To keep track of duplicates // Use a for loop to iterate over the records for (let i = 0; i < query.records.length; i++) { let record = query.records[i]; for (let j = 0; j < query.records.length; j++) { let potentialDupe = query.records[j]; let fName = record.getCellValue("First Names") === potentialDupe.getCellValue("First Names"); let lName = record.getCellValue("Last Name") === potentialDupe.getCellValue("Last Name"); let resi = record.getCellValue("Residence") === potentialDupe.getCellValue("Residence"); let ident = record.id !== potentialDupe.id; if (fName && lName && resi && ident) { // Initialize update variables inside the loop let upPN = null; let upEM = null; let upID = null; if (record.getCellValue("Phone Number") == null && potentialDupe.getCellValue("Phone Number") !== null) { upPN = potentialDupe.getCellValue("Phone Number"); upID = record.id; // Update this to record.id since you're updating the current record } if (record.getCellValue("Email Address") == null && potentialDupe.getCellValue("Email Address") !== null) { upEM = potentialDupe.getCellValue("Email Address"); upID = record.id; // Update this to record.id as well } // Perform updates if there are fields to update if (upPN || upEM) { await table.updateRecordAsync(upID, { "Phone Number": upPN, "Email Address": upEM, }); await table.deleteRecordAsync(potentialDupe.id); duplicates.push(record); // Add to duplicates list if any action is performed } } } } console.log(duplicates);

 

A few key changes and considerations in this revised code:

  1. Loop Structure: The code now uses nested loops to compare each record against every other record. This allows for asynchronous operations within the loop using await.

  2. Update Variables Initialization: The variables upPN, upEM, and upID are initialized inside the loop to ensure they are reset for each record comparison.

  3. Updating the Correct Record: When updating a record or deciding which ID to use for updates, it's important to target the correct record (record.id).

  4. Duplicates List: The duplicates array now tracks records for which updates or deletions have been performed.

Note:- I've used some help from GenAI here. But that ok if it solves our problems. 

👍

View original
Did this topic help you find an answer to your question?

3 replies

ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8796 replies
  • February 17, 2024

Sorry, I don't know Javascript, but have you tried Airtable's dedupe extension? It will do what you're looking for.

dilipborad
Forum|alt.badge.img+20
  • dilipborad
  • Brainy
  • 216 replies
  • Answer
  • February 17, 2024

Hello @seaTurtlesRUS ,

If the duplication detection and removal process is manual then First, check this extension Dedupe - Apps - Airtable Marketplace as @ScottWorld mentions. If that works it's best to use it, there are lots of flexibility in field selections.

Otherwise, Try this.

The error message you're encountering is that you're using the await keyword inside a callback function that is not marked as async. Specifically, the await keyword is used inside the .filter() method's callback function, which is not itself an async function.

To fix this issue, you'll need to refactor your code because .filter() cannot be directly used with asynchronous operations like await. Instead, you can use a loop to iterate over the records and perform the necessary asynchronous operations.

Here's a revised version of your code that addresses this issue:

 

var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); let duplicates = []; // To keep track of duplicates // Use a for loop to iterate over the records for (let i = 0; i < query.records.length; i++) { let record = query.records[i]; for (let j = 0; j < query.records.length; j++) { let potentialDupe = query.records[j]; let fName = record.getCellValue("First Names") === potentialDupe.getCellValue("First Names"); let lName = record.getCellValue("Last Name") === potentialDupe.getCellValue("Last Name"); let resi = record.getCellValue("Residence") === potentialDupe.getCellValue("Residence"); let ident = record.id !== potentialDupe.id; if (fName && lName && resi && ident) { // Initialize update variables inside the loop let upPN = null; let upEM = null; let upID = null; if (record.getCellValue("Phone Number") == null && potentialDupe.getCellValue("Phone Number") !== null) { upPN = potentialDupe.getCellValue("Phone Number"); upID = record.id; // Update this to record.id since you're updating the current record } if (record.getCellValue("Email Address") == null && potentialDupe.getCellValue("Email Address") !== null) { upEM = potentialDupe.getCellValue("Email Address"); upID = record.id; // Update this to record.id as well } // Perform updates if there are fields to update if (upPN || upEM) { await table.updateRecordAsync(upID, { "Phone Number": upPN, "Email Address": upEM, }); await table.deleteRecordAsync(potentialDupe.id); duplicates.push(record); // Add to duplicates list if any action is performed } } } } console.log(duplicates);

 

A few key changes and considerations in this revised code:

  1. Loop Structure: The code now uses nested loops to compare each record against every other record. This allows for asynchronous operations within the loop using await.

  2. Update Variables Initialization: The variables upPN, upEM, and upID are initialized inside the loop to ensure they are reset for each record comparison.

  3. Updating the Correct Record: When updating a record or deciding which ID to use for updates, it's important to target the correct record (record.id).

  4. Duplicates List: The duplicates array now tracks records for which updates or deletions have been performed.

Note:- I've used some help from GenAI here. But that ok if it solves our problems. 

👍

S
Forum|alt.badge.img+2
  • seaTurtlesRUS
  • Author
  • New Participant
  • 2 replies
  • February 20, 2024
dilipborad wrote:

Hello @seaTurtlesRUS ,

If the duplication detection and removal process is manual then First, check this extension Dedupe - Apps - Airtable Marketplace as @ScottWorld mentions. If that works it's best to use it, there are lots of flexibility in field selections.

Otherwise, Try this.

The error message you're encountering is that you're using the await keyword inside a callback function that is not marked as async. Specifically, the await keyword is used inside the .filter() method's callback function, which is not itself an async function.

To fix this issue, you'll need to refactor your code because .filter() cannot be directly used with asynchronous operations like await. Instead, you can use a loop to iterate over the records and perform the necessary asynchronous operations.

Here's a revised version of your code that addresses this issue:

 

var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); let duplicates = []; // To keep track of duplicates // Use a for loop to iterate over the records for (let i = 0; i < query.records.length; i++) { let record = query.records[i]; for (let j = 0; j < query.records.length; j++) { let potentialDupe = query.records[j]; let fName = record.getCellValue("First Names") === potentialDupe.getCellValue("First Names"); let lName = record.getCellValue("Last Name") === potentialDupe.getCellValue("Last Name"); let resi = record.getCellValue("Residence") === potentialDupe.getCellValue("Residence"); let ident = record.id !== potentialDupe.id; if (fName && lName && resi && ident) { // Initialize update variables inside the loop let upPN = null; let upEM = null; let upID = null; if (record.getCellValue("Phone Number") == null && potentialDupe.getCellValue("Phone Number") !== null) { upPN = potentialDupe.getCellValue("Phone Number"); upID = record.id; // Update this to record.id since you're updating the current record } if (record.getCellValue("Email Address") == null && potentialDupe.getCellValue("Email Address") !== null) { upEM = potentialDupe.getCellValue("Email Address"); upID = record.id; // Update this to record.id as well } // Perform updates if there are fields to update if (upPN || upEM) { await table.updateRecordAsync(upID, { "Phone Number": upPN, "Email Address": upEM, }); await table.deleteRecordAsync(potentialDupe.id); duplicates.push(record); // Add to duplicates list if any action is performed } } } } console.log(duplicates);

 

A few key changes and considerations in this revised code:

  1. Loop Structure: The code now uses nested loops to compare each record against every other record. This allows for asynchronous operations within the loop using await.

  2. Update Variables Initialization: The variables upPN, upEM, and upID are initialized inside the loop to ensure they are reset for each record comparison.

  3. Updating the Correct Record: When updating a record or deciding which ID to use for updates, it's important to target the correct record (record.id).

  4. Duplicates List: The duplicates array now tracks records for which updates or deletions have been performed.

Note:- I've used some help from GenAI here. But that ok if it solves our problems. 

👍


Hello, 

Thank you for the reply. 

I cannot use dedupe as there are hundreds of duplicates in my table. I had an initial table and then received several other tables with most of the same information but some records had additional information. So I am trying to merge records with the same three data points and delete the duplicates. 

My table around 10,000 records. Even on my test table of 500 records, nested loops time out the 30 second execution limit. 

I've been trying to work out how to use a  map or object to cut down on run time. 

But on the post code, your solution works ! I've marked as solution and will teeter on with my next problem. 

Reply


Most helpful members this week

Terms & Conditions