London Renters Union airtable setup. Airtable automation that will receive a webhook, create an event for it, and link it to an existing person in another table.

1-table-contacts.png

2-table-events.png

3-automation-page.png

4-automation-script.png

5-iparl-to-airtable-contact-matcher.js

const inputConfig = input.config()
const petitionsTable = base.getTable("Organic Campaign Events")
const newRecord = await petitionsTable.selectRecordAsync(inputConfig.id)
const membersTable = base.getTable('Members & Contacts')
const members = await membersTable.selectRecordsAsync({
    fields: [ "Email address", "Phone number", "Postcode", "Name", "First name", "Second name" ]
})

if (newRecord) {
    output.set("petitionRecordID", `id: ${inputConfig.id}`)
    const borough = newRecord.getCellValueAsString("borough")
    const ward = newRecord.getCellValueAsString("ward")

    // Try matching to Members & Contacts by...
    let matchedContact

    // email
    let email = newRecord.getCellValueAsString("email")
    if (email && email.length) {
        email = formatEmailAddress(email)
        output.set("Last search", `email: ${email}`)
        matchedContact = members.records.find(r => {
            let thisValue = r.getCellValueAsString('Email address')
            if (!thisValue) return false
            thisValue = formatEmailAddress(thisValue)
            return (
                thisValue === email
            )
        })
        if (matchedContact) {
            output.set("Match method", "email")
        }
    }
    
    // phone
    let phone = formatPhoneNumber(newRecord.getCellValueAsString("phone"))
    if (!matchedContact) {
        if (phone && phone.length) {
            output.set("Last search", `phone: ${phone}`)
            matchedContact = members.records.find(r => {
                let thisValue = r.getCellValueAsString('Phone number')
                if (!thisValue) return false
                thisValue = formatPhoneNumber(thisValue)
                return (
                    thisValue === phone
                )
            })
            if (matchedContact) {
                output.set("Match method", "phone")
            }
        }
    }

    // name + postcode
    const postcode = newRecord.getCellValueAsString("postcode")
    const surname = newRecord.getCellValueAsString("surname")
    const name = newRecord.getCellValueAsString("name")
    const newRecordName = !!surname && !!name ? `${name.trim().toLowerCase()} ${surname.trim().toLowerCase()}` : !!name ? name.trim().toLowerCase() : false
    if (!matchedContact) {
        if (!!newRecordName && !!postcode && postcode.length) {
            let newRecordPostcode = formatPostcode(postcode)
            output.set("Last search", `name: ${newRecordName} / postcode: ${postcode}`)
            matchedContact = members.records.find(r => {
                const _name = r.getCellValueAsString('Name')
                const fname = r.getCellValueAsString('First name')
                const sname = r.getCellValueAsString('Second name')
                const thisName = !!_name ? _name.trim().toLowerCase() : !!fname && !!sname ? `${fname.trim().toLowerCase()} ${sname.trim().toLowerCase()}` : false
                let thisPostcode = r.getCellValueAsString("Postcode")
                if (!thisName || !thisPostcode) return false
                thisPostcode = formatPostcode(thisPostcode)
                return (
                    thisPostcode === newRecordPostcode
                ) && (
                    thisName === newRecordName
                )
            })
            if (matchedContact) {
                output.set("Match method", "name + postcode")
            }
        }
    }

    let matchedContactId
    let newMemberData = {
        "First name": name || newRecordName || "",
        "Second name": surname,
        "Email address": email,
        "Phone number": phone,
        "Postcode": postcode,
        "Borough": borough,
        "Ward": ward
    }

    if (!matchedContact) {
        matchedContactId = await membersTable.createRecordAsync({
            ...newMemberData,
            "Contact type": { "name": "Interested - eaction signup" }
        })
        output.set("Created new contact name", newRecordName)
        output.set("Created new contact id", matchedContactId)
    } else {
        output.set("Matched contact name", matchedContact.name)
        output.set("Matched contact id", matchedContact.id)
        matchedContactId = matchedContact.id
        // Patch members with new data, as it's the latest
        const overrideData = {}
        for (const property in newMemberData) {
            // If there's no data in the new thing, don't blank out old data
            if (!!newMemberData[property]) {
                overrideData[property] = newMemberData[property]
            }
        }
        membersTable.updateRecordAsync(
            matchedContactId,
            overrideData
        )
    }
    petitionsTable.updateRecordAsync(newRecord.id, {
        'matched contact': [{ id: matchedContactId }]
    })
}

function formatPostcode(n) {
    if (!n) return n
    let out = n.toString().trim().toLowerCase().replace(/ /g, '+')
    return out
}

function formatEmailAddress(n) {
    if (!n) return n
    let out = n.toString().trim().toLowerCase().replace(/ /g, '+')
    return out
}

function formatPhoneNumber(n) {
    if (!n) return n
    let out = n.toString().trim().toLowerCase().replace(/^\+?44\(?0?\)?/gim, "0").replace(/[^+0-9]/gim, '').replace(/\-/g, '').replace(/ /g, '+')
    return out
}