Skip to content

Instantly share code, notes, and snippets.

@janbaykara
Last active March 29, 2022 16:42
Show Gist options
  • Save janbaykara/8548c36ee1544a77565239fa35f94385 to your computer and use it in GitHub Desktop.
Save janbaykara/8548c36ee1544a77565239fa35f94385 to your computer and use it in GitHub Desktop.
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.
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment