Adding Quickbooks Customer ID to An Airtable Record

Help
, ,
1

Hello all, I believe this is a general Javascript question. I haven’t run into this before, and I’d like some advice on the best way to solve this.

What I’m Doing

I have airtable data of the shape:

{
  "records": [
    {
      "id": "airtable_record_id",
      "fields": {
        "Email": "test@email.com",
        "Name": "Stetson Test 1"
      },
      "createdTime": "2021-07-03T17:01:29.000Z"
    },
    {
      "id": "airtable_record_id",
      "fields": {
        "Email": "test2@email.com",
        "Name": "Stetson Test 2"
      },
      "createdTime": "2021-07-03T17:01:29.000Z"
    },
    {
      "id": "airtable_record_id",
      "fields": {
        "Email": "test3@email.com",
        "Name": "Stetson Test 3"
      },
      "createdTime": "2021-07-03T17:01:29.000Z"
    }
  ]
}

And I have quickbooks data of the shape:

"Customer": [
            {
                "Id": "1345135123",
                "GivenName": "Stetson",
                "FamilyName": "Test 1",
                "FullyQualifiedName": "Stetson Test 1",
                "DisplayName": "Stetson Test 1",
                "PrimaryEmailAddr": {
                    "Address": "test@email.com"
                }
            },
            {
                "Id": "0987098600",
                "GivenName": "Stetson",
                "FamilyName": "Test 2",
                "FullyQualifiedName": "Stetson Test 2",
                "DisplayName": "Stetson Test 2",
                "PrimaryEmailAddr": {
                    "Address": "test@email.com"
                }
            },
            {
                "Id": "2394798706",
                "GivenName": "Stetson",
                "FamilyName": "Test 3",
                "FullyQualifiedName": "Stetson Test 3",
                "DisplayName": "Stetson Test 3",
                "PrimaryEmailAddr": {
                    "Address": "test@email.com"
                }
            },

-----Desired Outcome----

I want to add the Customer[0].Id from the quickbooks data to the corresponding airtable data (email address should be the best way to correlate the two). Then the airtable object would look like:


  "records": [
    {
      "id": "airtable_record_id",
      "fields": {
        "Email": "test@email.com",
        "Name": "Stetson Test 1"
        "Quickbooks ID": "1345135123"
      },
      "createdTime": "2021-07-03T17:01:29.000Z"
    },...........

How I’m thinking about doing this:

  1. Loop over airtable data, get email address for each record
  2. Send API call to quickbooks to get customer using email address
  3. grab ID from response object
  4. add quickbooks ID to airtable
  5. update airtable data via airtable API

This will probably work, but I make a quickbooks and airtable API call each round.

Could I:

  1. make one API request and get an object of all airtable records
  2. make one API request and get all quickbooks customers
  3. run some sort of compare-find-mutate operation on the two arrays of objects to accomplish the same thing?
  4. once I’ve mutated my airtable data, post update to my airtable base

Is the first way fine and the second way unnecessary?

I get stuck on #3. Not sure how to add the quickbooks ID to my airtable objects once I have both the records array of objects from airtable and the Customers array of objects from quickbooks.

I’m working with about 550 records.

Thanks for the advice if you have some!

2

I would use the map function on your array of Airtable records and write something like this:

const airtable_records = steps.airtable.$return_value //put the source of your array of airtable records here
const quickbooks_records = steps.quickbooks.$return_value //put the source of your array of quickbooks records here

const airtable_records_with_qb_ids = airtable_records.map(record => {
	const matching_quickbooks_record = quickbooks_records.find(qb_record => qb_record.PrimaryEmailAddr.Address === record.fields.Email)
	record.fields['Quickbooks ID'] = matching_quickbooks_record.Id
	return record
})

For every record in your array of Airtable records, it searches the array of Quickbooks records until it finds one with a matching email address. Then it adds the id of the Quickbooks record it found to the current Airtable record object. It returns this record, which means the result of your map function will be an array of the updated Airtable record objects.

In the code above, I’m assuming every record has an email address filled in, and that every airtable record has a match in the QB array. If that’s not the case you’ll have to add some details to account for the possibility of undefined properties and for matching_quickbooks_record to come back with a null if no match was found. I also haven’t tested the code so you might find mistakes! But that’s the general idea of what I’d do.

Here’s the documentation for the array methods I used:

3

That looks like it will work. Your assumptions should be correct, but I’ll add a little error handling just in case the two objects don’t contain the same data.

Thanks Celeste!

1 Like