Better way to compare two tables and vice-versa?

[Gustavo_VD]

Hi there,

I’m writing a script to compare two tables (views actually) and mark the records that match.

I wrote a script that compares View 1 to View 2. And then needed a way to compare the other way around, View 2 to View 1. The problem is I got a big chunk of duplicated code and surely there’s a better and more elegant way to accomplish this. Right?

:thumbs_up: Here’s the setup section of my script, nothing fancy:

//get table
let table = base.getTable("Table 1");

//get records in the App View
let AppView = table.getView("App");
let AppRecords = await AppView.selectRecordsAsync({fields: ["DateApp", "Payee", "Amount"]});
let AppExpenses = AppRecords.records

//get records in the Bank View
let BankView = table.getView("Bank");
let BankRecords = await BankView.selectRecordsAsync({fields: ["DateTransaction", "DateProcessed", "Payee", "Amount"]});
let BankExpenses = BankRecords.records

:thumbs_up: Here’s where I compare compare View 1 (App) to View 2 (Bank). And update the records:

// Find matches on App
let AppMatches = AppExpenses.filter((record)=> {
    return BankExpenses.find((potentialMatch)=> {
        // compare record to potential duplicate
        return (record.getCellValue("Payee") === potentialMatch.getCellValue("Payee") &&
        record.getCellValue("Amount") === potentialMatch.getCellValue("Amount") &&
        (record.getCellValue("DateApp") === potentialMatch.getCellValue("BelegdatumConverted") ||
        record.getCellValue("DateApp") === potentialMatch.getCellValue("WertstellungConverted")))
    });
});

let updatesApp = AppMatches.map(record => {
   return {
      id: record.id, 
      fields: {
         "Match": true
      }
   }
})

while (updatesApp.length > 0) {
    await table.updateRecordsAsync(updatesApp.slice(0, 50));
    updatesApp = updatesApp.slice(50);
}

:-1: My issue is that I need to perform the same comparison and update the other way around. So I have a big chunk of duplicated code that goes like this:

// Find matches on Bank
let BankMatches = BankExpenses.filter((record)=> {
    return AppExpenses.find((potentialMatch)=> {
        // compare record to potential duplicate
        return (record.getCellValue("Payee") === potentialMatch.getCellValue("Payee") &&
        record.getCellValue("Amount") === potentialMatch.getCellValue("Amount") &&
        (record.getCellValue("BelegdatumConverted") === potentialMatch.getCellValue("DateApp") ||
        record.getCellValue("WertstellungConverted") === potentialMatch.getCellValue("DateApp")))
    });
});

let updatesBank = BankMatches.map(record => {
   return {
      id: record.id, 
      fields: {
         "Match": true
      }
   }
})

while (updatesBank.length > 0) {
    await table.updateRecordsAsync(updatesBank.slice(0, 50));
    updatesBank = updatesBank.slice(50);
}

:thinking: I’m wondering if there’s a more efficient way to perform this two-way comparison?

[Justin_Barrett]

I think that you could write a function that can do both comparisons. It’s a matter of noticing the similarities between the two comparison processes, then defining a function that accepts parameters for passing the source and destination views and fields to plug into that generic process.

[Gustavo_VD]

Yes, a function would help keep the code shorter and more legible.

I was thinking if there’s a way to use the loop that’s going on in that AppExpenses.find((potentialMatch)=> { function to test for matches.