Sure! Here’s the example I’m currently working on.
Context
We have an applicant tracking system with the following ERD (simplified):
- An
Application
is a row that…
- has a
Source
value (e.g. Indeed)
- hasOne
Opening
lookup (e.g. “House Cleaner” position)
- hasOne
Stage
lookup (e.g. “Phone Screen”)
- hasMany
Resources
it maps to (e.g. bookingUrl
, assessmentUrl
, etc.)
- hasOne
Candidate
lookup
- has a
firstName
, lastName
- hasMany
Contact Addresses
with types (e.g. phone number, email address, etc.)
And it goes on and on. We’re really leveraging lookups nested a few layers here in our data model.
Message Personalization
There are several places in our ATS where we want to send messages to our candidates. In some cases it’s manual, but most often it’s automated and based on a template like this:
{{ candidate.firstName }}, we’d like to interview you for the {{ opening.label }} role at Fist Bump.
Please schedule a time here: {{ position.phoneScreenUrl }}
Then fill out this form: {{ position.screeningFormUrl }}
Currently, we’ve implemented token substitution in various places like this:
thisRow.Application.Candidate.WithName(Recipient, thisRow.Template.Message
.RegexReplace("{{ applicant.firstName }}",
Recipient.[First Name].IfBlank(""))
.RegexReplace("{{ applicant.lastName }}",
Recipient.[Last Name].IfBlank(""))
.RegexReplace("{{ applicant.fullName }}",
Recipient.Label.IfBlank(""))
.RegexReplace("{{ position.label }}",
Application.Opening.Label.IfBlank(""))
.RegexReplace("{{ position.phoneScreenUrl }}",
Application.Opening.[Phone Call Booking URL].IfBlank("fistbump.co"))
.RegexReplace("{{ position.screeningFormUrl }}",
Application.Opening.[Screening Form].[Public URL].IfBlank("fistbump.co"))
.RegexReplace("{{ position.applicationFormUrl }}",
Application.Opening.[Application Form].[Public URL].IfBlank("fistbump.co"))
)
But we’ve run into a few issues:
- This code ends up replicated in various different places where we send messages (i.e. not DRY) so any time I want to introduce a new token I need to track them all down and update them.
- Similarly, one of Coda’s strengths is that I can rapidly iterate. Every time our data model (not just a column name) changes I have to track down all of the different impacted formulas and update them. We’ve already gone through dozens of iterations to get to our current model and will likely go through many more.
- And finally, we’re unable to build new features we want because of Coda’s limited regex capabilities so we’re pulling token substitution out into a Pack. Pack’s can’t operate on rows of data, so I need to transport data to it somehow. And ideally, I want a mechanism that’s not as brittle as what I’m currently doing (because of the challenges described in the first two steps).
So the solution we’ve landed on is to use the Object()
method to build a transport object that conforms to a fixed schema that all of my respective Coda tables and the Pack expect:
- Each table has an
Object
column that maps its data to the schema.
- Each row’s object inherits any dependent row’s objects giving me access to all the data I need easily. In other words, if RowA looks up RowB from another table, RowA’s Object will include RowB’s and so on.
This is great because:
- Each table is responsible for producing its own schema-conforming object
- If the data model changes, I know which table/column to update to keep it confirming to the schema.
- If the schema changes, I only have to update a single column to have the change reflected everywhere it’s imported.
- And I have a JSON object I can pass to my packs to make existing data models and pack functions compatible.
Ultimately, I have an object that looks like this that I can pass around:
# from Applications: table
{
"source": "Indeed",
"stage": "3. Phone Screen",
# from Openings: table
"opening": {
"label": "Cleaner",
# from Jobs: table
"job": {
"label": "Cleaner"
}
},
# from Candidates : table
"candidate": {
"firstName": "John",
"lastName": "Jacobs",
# from Contact Addresses: table
"contactAddresses": [
{
"type": "Email",
"addressRaw": "<redacted>",
"address": "<redacted>"
},
{
"type": "Phone",
"addressRaw": "<redacted>",
"address": "<redacted>"
}
]
}
}
This is just one example. We also have a property management system with a ton of linked / nested tables that’s taking advantage of this same pattern to create consistency in how we interface with packs.