I love love love the Object() function

To anyone at Coda looking to rationalize the existence of the Object() method, I’m happy to sing its praises and share my use-cases. It simplifies bridging existing tables and custom packs so very much.

I’d love to see methods for manipulating the objects (add, remove, replace, etc.) but even as it stands it’s a fantastic load off of having to reinvent the turn-to-JSON wheel.

Nice! Can you peel back the curtain and give us an idea of how you used it?

(Note: It’s currently an unreleased formula and is subject to breaking changes without warning.)

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 Job it maps to
    • 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:

  1. 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.
  2. 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.
  3. 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.

5 Likes

@Eric_Koleda one more thing worth mentioning: this is particularly useful with the (deprecated?) _Merge function.

I get to create each row’s object representation in the row, then merge those objects together any time I need context from multiple places to be sent to a pack.

Thanks for all of this context Christian! As we build out the features of the Packs platform it really helpful to understand how people use it and the speed bumps and walls they run in to.

1 Like

Hi I’m sorry, but how do I use Object( ) in the table? is it text with formula, and what value to choose?

Here is a quick example for you :
Tell me if it’s not self-explanatory enough :slight_smile:

@Eric_Koleda By the way, is there any plan for this formula to be deprecated in the future or is it safe to use ?

2 Likes

@Martin_Portevin - No plans that I know of. Use at your own risk.

2 Likes

Will do ! Thank you.

Hello @Eric_Koleda
Thank you for the warning.
==> Is there any “safe” way to construct a JSON object (without using Object() function) ?

Best regards :blush:

Eric’s apps script pack has a json constructor that is the functional equivalent of object()!

Oh great ! Thank you for this information :pray:
I will check that.

Edit : Actually this pack JSONBody() formula works for strings values only. I would love a way to be able to insert Numbers and Objects also :thinking:… like the Object() formula :stuck_out_tongue_winking_eye:

It seems like you solved that problem!

Partially but it works for what I want :+1:
Thank YOU really much for all what you did on that topic :pray: