Issue using nested object schemas and mapping data against them

Making Packs
1

Hi Codans!

I’m working on a pack that syncs one table from OnePageCRM into Coda. At this point, I can get the pack to build successfully with some fields populating, but the nested objects (i.e. contacts) return blank.

I’d appreciate if someone could take a look and help steer me in the right direction. Thanks!

Background

The API call response from OnePage contains several nested objects/arrays, which is more complicated that the tutorial examples on the Coda site. I suspect that I either set up my nested schemas incorrectly or the mapping function I’m using doesn’t carryover to the nested objects.

The Companies Table from OnePage lists each company entity, which contains multiple contacts (array of 'contact objects). My issue is converting those into useable object chips in Coda. My focus right now is extracting the “contacts” array of “contact” objects and ‘address’ which uses the CompanyAddressSchema.

Here’s an example of the [API response / structure] from the Companies Table in One Page (https://app.onepagecrm.com/api/v3/companies):

{
  "status": 0,
  "message": "OK",
  "timestamp": 1528373119,
  "data": {
    "companies": [
      {
        "company": {
          "id": "5aba31e99007ba0f570c92ac",
          "name": "Crockett & Tubbs Security",
          "description": "For all your security needs",
          "phone": "(912) 644-1771",
          "url": "https://{foo.bar}",
          "address": {
            "address": "Unit 5, Business Innovation Centre",
            "city": "Upper Newcastle",
            "state": "Galway",
            "zip_code": "H91 Y0T0",
            "country_code": "IE"
          },
         
//...........
          "contacts_count": 3,
          "won_deals_count": 5,
          "total_won_amount": 999.99,
          "pending_deals_count": 1,
          "total_pending_amount": 99.99,
          "contacts": [
            {
              "contact": {
                "id": "5aba31ea9007ba0f570c92d4",
                "title": "Mr",
                "first_name": "Joe",
                "last_name": "Bloggs",
                "job_title": "Engineer",
                "starred": true,
                "photo_url": "https://{foo.bar}/joe-bloggs.jpg",
                "company_id": "5aba31ea9007ba0f570c92d5",
                "company_name": "Morgan's Forensic Lab",
                "url": [
                  {
                    "type": "linkedin",
                    "value": "https://www.linkedin.com/{joe.bloggs}"
                  }
                ],
                "phones": [
                  {
                    "type": "work",
                    "value": "(912) 644-1770"
                  }
                ],
                "emails": [
                  {
                    "type": "work",
                    "value": "joe.bloggs@{foo.bar}"
                  }
                ],
                "address_list": [
                  {
                    "address": "Unit 5, Business Innovation Centre",
                    "city": "Upper Newcastle",
                    "state": "Galway",
                    "zip_code": "H91 Y0T0",
                    "country_code": "IE",
                    "type": "delivery"
                  }
                ],
                "status": "Prospect",
                "status_id": "5e31e030849d781e837b6ba1",
                "tags": [
                  "Java",
                  "Android",
                  "iOS"
                ],
               
                "letter": "b",
                "pending_deal": true,
                "total_pendings": 999.99,
                "total_deals_count": 3,
                "company_size": 1,
                "sales_closed_for": [
                  "5aba31e99007ba0f570c12f7"
                ],
                "closed_sales": [
                  {
                    "user_id": "5aba31e99007ba0f570c12f7",
                    "closed_at": 1524818902,
                    "comment": {}
                  }
                ],
                "google_contacts_data": {
                  "account_email": "jane.doe@{googles-mail-service}",
                  "id": "342c5b5c08d4b0b2",
                  "saved_at": 1522840153
                },
                "created_at": "2018-05-16T11:52:09Z",
                "modified_at": "2018-05-16T11:52:09Z"
              },
              
          "pending_deals": [
            {
              "deal": {
                "id": "5aaa9b059007ba08c9ebaf58",
                "contact_id": "5aaa9b059007ba08c9ebaf58",
                "owner_id": "5aba31e99007ba0f570c12f7",
                "pipeline_id": "5cfa87a4849d7873db082f98",
                "name": "Solar panels",
                "text": "Fingers crossed!",
                "stage": 50,
                "status": "pending",
                "expected_close_date": "2018-04-15",
                "close_date": "2018-04-15",
                "date": "2018-04-15",
                "amount": 99.99,
                "months": 1,
                "cost": 0,
                "margin": 99.99,
                "total_amount": 99.99,
                "total_cost": 0,
                "commission_base": "none",
                "commission_type": "none",
                "commission": 0,
                "commission_percentage": 0,
                "reason_lost_id": "5aaa9b059007ba08c9ebaf58",
                "deal_fields": [
                  {
                    "deal_field": {
                      "id": "5aad9b039007ba28c9ebad56",
                      "name": "Deal size",
                      "type": "select_box",
                      "position": 2,
                      "choices": [
                        "Small",
                        "Medium",
                        "Large"
                      ]
                    },
                    "value": "Large"
                  }
                ],
                "has_deal_items": true,
                "deal_items": [
                  {
                    "id": "5c7973019007ba3f7519e86a",
                    "name": "Solar panels",
                    "description": "Photovoltaic solar panels (2019 model)",
                    "cost": 95,
                    "price": 149.99,
                    "amount": 299.98,
                    "qty": 2,
                    "deal_id": "5aaa9b059007ba08c9ebaf58",
                    "predefined_item_id": "5c6abf565481dd28ff1bc6a8",
                    "position": 1,
                    "created_at": "2019-03-01T17:59:29Z",
                    "modified_at": "2019-03-01T17:59:29Z"
                  }
                ],
                "author": "Jane D.",
                "has_related_notes": true,
                "attachments": [
                  {
                    "id": "5afc1ea9d556730b780096db",
                    "filename": "mario.jpg",
                    "size": 3841,
                    "storage_provider": "amazon",
                    "url": "https://{foo.bar}/mario.jpg",
                    "url_expires_at": "2018-05-16T13:06:53Z",
                    "thumbnail": {
                      "url": "https://{foo.bar}/mario-thumbnail.jpg"
                    }
                  }
                ],
                "contact_info": {
                  "contact_name": "Joe Bloggs",
                  "company": "Big Company Inc."
                },
                "owner": {
                  "id": "5aba31e99007ba0f570c12f7",
                  "name": "Joe Bloggs",
                  "email": "joe.bloggs@{foo.bar}"
                },
                "previous_pipeline_stages": {
                  "5da7b67a849d78737812437c": 40
                },
                "created_at": "2018-03-15T16:10:45Z",
                "modified_at": "2018-03-15T16:10:45Z"
              }
            }
          ],
          "created_at": "2018-05-16T11:52:09Z",
          "modified_at": "2018-05-16T11:52:09Z"
        }
      }
    ],
    "total_count": 1,
    "page": 1,
    "per_page": 10,
    "max_page": 1
  }
}

My code so far:

const CompanySchema = coda.makeObjectSchema({
  properties: {
    company_id: {
      type: coda.ValueType.String,
      fromKey: 'id',
    },
    name: { type: coda.ValueType.String },
    description: { type: coda.ValueType.String },
    phone: { type: coda.ValueType.String },
    url: { type: coda.ValueType.String },

  // --------------

    address: CompanyAddressSchema,

    contacts: { 
     type: coda.ValueType.Array,
      items: ContactSchema,
      },
//---------------------
     
  },
  idProperty: "company_id",
  displayProperty: "company_id",
  featuredProperties: [
    "name",
    "address",
    "contacts"
  ],
});


const constructCompanyRow = function (item) {

  const company = item.company;

  return {
    id: company.id,
    name: company.name,
    description: company.description,
    phone: company.phone,
    address: company.address,
    contacts_count: company.contacts_count,
    won_deals_count: company.won_deals_count,
    total_won_amount: company.total_won_amount,
    pending_deals_count: company.pending_deals_count,
    total_pending_amount: company.total_pending_amount,
    contacts: company.contacts
  }
}


pack.addSyncTable({
  name: "Company",
  description: "Sync companies from OnePage CRM",
  identityName: "Company",
  schema: CompanySchema,
  formula: {
    name: "getOnePageCompanies",
    description: "Sync companies from OnePage CRM",
    parameters: [],
    execute: async function ([], context) {
      // Get the page to start from.
      let page = (context.sync.continuation?.page as number) || 1;

      let url = coda.withQueryParams("https://app.onepagecrm.com/api/v3/companies", {
        page: page,
        per_page: PageSize,
      });

      let response = await context.fetcher.fetch({
        method: "GET",
        url: url,
      });

      let companies = response.body.data.companies;
      console.log(companies)

      // If there were some results, re-run this formula for the next page.
      let continuation: { page: number; };
      if (companies.length > 0) {
        continuation = { page: page + 1 };
      }

      // Return the results and the continuation (if any).
      return {
        result: companies.map((item: any) => constructCompanyRow(item)),
        continuation: continuation,
      };
    },
  },

});
2

I believe it’s because the contact information in the response from the API is actually nested inside of another key called contact.

So the contacts information is actually accessible via

  • item.company.contacts[0].contact.first_name

Notice the extra non-plural contact key?

So you’d have to adjust your schema for contacts to account for that extra top level key

3

:exploding_head: :exploding_head: :exploding_head: I see what you’re saying - thanks @Scott_Collier-Weir !

I made updates to the “constructCompanyRow” function → contacts: company.contacts[0].contact.

const constructCompanyRow = function (item) {

  const company = item.company;

  return {
    id: company.id,
    name: company.name,
    description: company.description,
    phone: company.phone,
    url: company.url,
    address: company.address,
    company_fields: company.company_fields,
    syncing_status: company.syncing_status,
    synced_status_id: company.synced_status_id,
    syncing_tags: company.syncing_tags,
    synced_tags: company.synced_tags,
    contacts_count: company.contacts_count,
    won_deals_count: company.won_deals_count,
    total_won_amount: company.total_won_amount,
    pending_deals_count: company.pending_deals_count,
    total_pending_amount: company.total_pending_amount,
    contacts: company.contacts[0].contact
  }
}

// item.company.contacts[0].contact.first_name

After the updates, the pack returned some data, but it appears to be incomplete. The output isn’t an object and only shows data for one contact, even for companies with multiple contacts.

Here’s an example of what I mean → the company in row 1 has 49 contacts, but the result shows one contact id. However, it does look like Coda interprets this as a rich object (for one contact at least).

image
image846×244 25.7 KB

image
image946×472 43.1 KB

Perhaps I’m missing something. Did you mean update the ‘CompanyContactSchema’ (property schema) to reflect the nested object? If so, I’m not certain how to go about that given the examples I’ve reviewed so far.

4

paging @Eric_Koleda . . . .

He can likely give you a lot more thorough help than I!

1 Like
5

Hi @Chris_Williams - This API returns data in a somewhat strange way, which is probably why it doesn’t fit exactly with the examples in the documentation. @Scott_Collier-Weir has the right insight, in that the issue is that the API wraps the data in an additional layer that is causing the problems. Instead of just pulling out the 0’th item and unwrapping it, instead use the map() function to unwrap all of the items.

contacts: company.contacts?.map(item => item.contact)

You can apply the same basic pattern to any other sub-items that have this additional wrapping.

1 Like
6

@Eric_Koleda It worked! Thank you for helping out here. The nested schema from that API was throwing me off a bit.

Well, this is good experience for future packs.

@Scott_Collier-Weir I appreciate you taking the time to help me out as well.

I’ll write back if I encounter any other issues, but things are looking good so far.

2 Likes