Automation trigger for matching records between two tables

[chrisnhamp]

I have a base with a record for each computer (about 4,000 total) that I am responsible for managing in my large corporation, and each record has fields about the computer including its Serial Number and Hostname, among others.

I am provided with a semi-regular report in the form of a CSV from the company that lists every computer that has checked in with their security monitoring program which is required to be installed every computer. The CSV has more than 30,000 records. I want to ensure the computers I am responsible for are compliant and checking in, so I import the CSV into Airtable and then search for my computers by SN and Hostname. I use an automation to search for each computer in the CSV and update a field in each computer record to indicate if a match is found or not.

In my current setup, the trigger for the automation is a single-select field on each computer record. When that field is set to "search for matches", the automation runs, and each computer's record is updated to indicate if it is present in the CSV file. Is there a way to trigger the search/match/update automation whenever a new CSV is imported into Airtable in this scenario, rather than setting the "search for matches" field on each record?

[Blake_D]

If you have a separate table for imported data with a linked field to your "Computers" table, you can set up an automation to link (if needed) and update the records: 

• Trigger: When a Record is Created

  • Choose the imported CSV data table.
  • Set the trigger to "When a record is created" in the upload table.

• Action: Find Records

  • Add an action to "Find records" in the Computers table.
  • Configure the search condition to match the Serial Number or Hostname from the new CSV record.

• Action: Update Record

  • Add an action to "Update record" in the Computers table.
  • Use the Record ID from the "Find records" step.
  • Update the field to indicate whether the computer is compliant or not.
  • Update the linked field to the CSV record to keep an update history if necessary.

I hope this helps if you have any questions feel free to ask. 

[chrisnhamp]

When the Find Records actions returns more than one result (a computer may appear in the CSV file multiple times) the automation fails. Is there any way to address that? 

[Blake_D]

Are there multiple records in your "computers" table with the same serial number and host name? 

[chrisnhamp]

There are computers with the same host name.

[Blake_D]

If it is just the same host name but different serial numbers you could adjust your search to just look for that unique serial number and not the host name. If there are other CSV and "Computer" fields that could be used as a unique identifier to return only one result I would try to use those as well. You can also take away the step of updating the linked field until the find is functioning as intended then you can add that function later.  

[Alexey_Gusev]

Hi,
Suppose SN is unique. in each of tables.
In first table, make it primary field. In second table, duplicate SN column and turn it to link (to the Table1)
In Table 1, you will see 3 groups of records (linked = present in 2, not linked = absent in 2, just created = present in 2 & absent in 1). 
If you make primary field in Table 1 not editable (like formula with SN), new records cannot be auto-created. So, the list of "present in 2 & absent in 1" can be found as unlinked in Table 2..
You can combine that all according to you needs (for example, use SN & "_" & Hostname formula to provide uniqueness, if SN alone is not suitable) without using any automation and script, and quite fast.

[Sachin_191]

To automate the process of matching and updating computer records in Airtable upon CSV import, we can leverage the power of Make.com. By setting up a scenario that triggers whenever a new CSV file is added, we can streamline the entire operation. This setup will ensure each computer record is automatically checked and updated without needing manual intervention.

• Use Make.com to watch for new CSV files in a specific Airtable table or a connected cloud storage.
• Parse the CSV, iterate through the records, and search for matches based on Serial Number and Hostname.
• Update the status field in the Airtable records to indicate compliance.