Creating an externally synced airtable base from affinity list

CreateAirTableSyncedToAffinityList.md

This is a small gist that allows you to share an org list from affinity to an airtable

Keeping airtable synced automatically. Below is an airtable structure that pulls via airtable script automation an affinity list, populates all the data throughout the list, populates the org data, people data and interlinks all records together directly via Affinity API. This can be adapted easily to populate Opprotunity and other objects from the affinity API. Feel free to share and contribute back.

Create a base that has the following tables with fields:

Main

Fields:

1. 'Name' type of Single line text (should be default left side)
2. 'entity_id' type of #number
3. 'person_ids_json' type of Single line text
4. 'organization_json' type of Single line text
5. 'Organization' type of Link to another record to 'Organizations' table with allow multiple
6. 'People' type of Link to another record to 'People' table with allow multiple

People

Fields:

1. 'Name' type of Single line text (should be default left side)
2. 'Primary Email' type of Single line text
3. 'Emails' type of Single line text
4. 'Relationship Strength' type of Single line text
5. 'Main' Should be automatically created when you connect it from the 'Main' Table

Organizations

Fields:

1. 'Name' type of Single line text (should be default left side)
2. 'Main' Should be automatically created when you connect it from the 'Main' Table

FetchOrgObj.js

/*

Name: Fetch Organization object and create organization in the 'Organizations' table and connect the entry to the organization
Trigger: When record is created on table 'Main'
When creating this automation, create input vars on the left side:
record_entity_id = record from step1 field entity_id
record = record from step1 field recordid
apikey = 'APIKEYASKFROMAMAZINGDEV'

*/

var inputs = input.config();
let response = await fetch('https://api.affinity.co/organizations/'+inputs.record_entity_id, {
method: 'GET',
headers: {
    'Content-Type': 'application/json',
    "Authorization": "Basic "+inputs.apikey
},
});
var orgobj = await response.json();

// Change this to the name of a table in your base
let maintable = base.getTable('Main');
let orgtable = base.getTable('Organizations');

var neworg = await orgtable.createRecordAsync({"Name":orgobj.name});

await maintable.updateRecordAsync(inputs.record, {
    // Change these names to fields in your base
    'person_ids_json': JSON.stringify(orgobj.person_ids),
    'organization_json': JSON.stringify(orgobj),
    'Name':orgobj.name,
    'Organization':[{id:neworg}]
});

FetchPeople.js

/*

Name: Fetch people object and create person in the 'People' table and connect the entry to the person
Trigger: When record is updated on table 'Main' for record 'person_ids_json' on view 'Grid'
When creating this automation, create input vars on the left side:
record = record from step1 field recordid
people_ids_json = record from step1 field person_ids_json
apikey = 'APIKEYASKFROMAMAZINGDEV'

*/

var inputs = input.config();

//iterate over the persons json
var peopleobj = JSON.parse(inputs.people_ids_json);
var peopletoappend = [];

let maintable = base.getTable('Main');
let peopletable = base.getTable('People');

for (let i = 0; i < peopleobj.length; i++) {
    let person = peopleobj[i];
    let response = await fetch('https://api.affinity.co/persons/'+person, {
    method: 'GET',
    headers: {
        'Content-Type': 'application/json',
        "Authorization": "Basic "+inputs.apikey
    },
    });
    var personobj = await response.json();

    //Fetch connection strength

    let responsestrength = await fetch('https://api.affinity.co/relationships-strengths?external_id='+person, {
    method: 'GET',
    headers: {
        'Content-Type': 'application/json',
        "Authorization": "Basic "+inputs.apikey
    },
    });
    var connectionstrengtharray = await responsestrength.json();

    //calculate strength
    var strength = Number(0);
    if(connectionstrengtharray.length > 0) {
        var total = 0;
        for(var isx = 0; isx < connectionstrengtharray.length; isx++) {
            total += connectionstrengtharray[isx].strength;
        }
        strength = Number((total / connectionstrengtharray.length).toFixed(2));
    }

    // Change this to the name of a table in your base

    var newpersonid = await peopletable.createRecordAsync({"Name":personobj.first_name+' '+personobj.last_name,'Primary Email':personobj.primary_email,'Emails':JSON.stringify(personobj.emails),'Relationship Strength':strength.toString()});
    peopletoappend.push({id:newpersonid});

}

await maintable.updateRecordAsync(inputs.record, {
        // Change these names to fields in your base
        'People':peopletoappend
});

RefreshTablefromaffinity.js

/*

Name: Clearing all tables from content and reinserting all the entry_ids from affinity listid
Trigger: At Scheduled time trigger once a day
When creating this automation, create input vars on the left side:
listid = YOURLISTID from affinity
apikey = 'APIKEYASKFROMAMAZINGDEV'

*/
var inputs = input.config();

var listid = inputs.listid;
var apikey = inputs.apikey;

//Delete EVERYTHING!!!

const Tables = function(){
    // ** DEFINE ALL OF YOUR TABLE NAMES HERE **
    // ** WITH A COMMA AFTER ALL BUT THE LAST **
    this.names = [
        "Main",
        "People",
        "Organizations"
    ];

    this.numberOfTablesDefined = this.names.length;
};

// ****************************************************************
// *******   NOTHING BELOW HERE SHOULD NEED TO BE CHANGED   *******
// ****************************************************************

let tables = new Tables();

const tablesToClear = []
let totalRecordsToDelete = 0;
let tablesQueried = 0;

// Get records from each table and add to array of tables to clear
while (tablesQueried < tables.numberOfTablesDefined) {
    let tableName = tables.names[tablesQueried]
    let tableToAdd = base.getTable(tableName);
    let queryToAdd = await tableToAdd.selectRecordsAsync();
    let recordsToAdd = queryToAdd.records;
    
    if (recordsToAdd.length > 0) {
        totalRecordsToDelete += recordsToAdd.length;
        const table = {table: tableToAdd, records: recordsToAdd};
        tablesToClear.push(table);
    }

    tablesQueried++
}

let totalRecordsDeleted = 0;

for (let table of tablesToClear) {
    let recordsToDelete = table.records.map(record => record.id);
    let recordsDeleted = await batchAnd('Delete', table.table, recordsToDelete);

    if (recordsDeleted !== null) {
        totalRecordsDeleted += recordsDeleted;
    }
};

//Get Data from affinity

let response = await fetch('https://api.affinity.co/lists/'+listid+'/list-entries', {
method: 'GET',
headers: {
    'Content-Type': 'application/json',
    "Authorization": "Basic "+apikey
},
});
var entries = await response.json();
console.log(entries);
//Lets create only the entry_id in main table
const mainTable = base.getTable('Main');

for (let i = 0; i < entries.length; i++) {
    const entry = entries[i];
    var newentry = await mainTable.createRecordAsync({"entity_id":entry.entity.id});
}


// ***************************************************
// ******************   FUNCTIONS   ******************
// ***************************************************
async function batchAnd(action, table, records) {
    let recordsActedOn = records.length;

    switch (action) {
        case 'Update':
                while (records.length > 0) {
                    await table.updateRecordsAsync(records.slice(0, 50));
                    records = records.slice(50);
                };
                break;
        
        case 'Create':
                while (records.length > 0) {
                    await table.createRecordsAsync(records.slice(0, 50));
                    records = records.slice(50);
                };
                break;

        case 'Delete':
                while (records.length > 0) {
                    await table.deleteRecordsAsync(records.slice(0, 50));
                    records = records.slice(50);
                }
                break;

        default:
        recordsActedOn = null;
  }
  return recordsActedOn;
}