Help Needed with Syncing Data from External API in Coda Pack

Making Packs
1

Hi everyone,

I’m encountering issues while syncing proposal lines in a Coda pack. I have created a sync table for the proposal lines (ProposalLines) that fetches data from an external API. However, during synchronization, only the last line of each proposal is displayed in the table, even though there are multiple lines for each proposal in the data source. Here’s the current code I am using to define the schema and the sync function:

const BATCH_SIZE3 = 70;

const ProposalLineSchema = coda.makeObjectSchema({
  properties: {
    fk_propal: { type: coda.ValueType.Number },
    desc: { type: coda.ValueType.String },
    fk_product: { type: coda.ValueType.Number },
    product_type: { type: coda.ValueType.Number },
    qty: { type: coda.ValueType.Number },
    tva_tx: { type: coda.ValueType.Number },
    subprice: { type: coda.ValueType.Number },
    total_ht: { type: coda.ValueType.Number },
    total_tva: { type: coda.ValueType.Number },
    total_ttc: { type: coda.ValueType.Number },
    product_ref: { type: coda.ValueType.String },
    product_label: { type: coda.ValueType.String },
    product_desc: { type: coda.ValueType.String },
  },
  displayProperty: "product_label",
  idProperty: "fk_propal",
});

pack.addSyncTable({
  name: "ProposalLines",
  description: "Table for proposal lines",
  identityName: "ProposalLine",
  schema: ProposalLineSchema,
  formula: {
    name: "SyncProposalLines",
    description: "Syncs the proposal lines from the proposals.",
    parameters: [
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "sortField",
        description: "Field to sort by",
        autocomplete: ["t.ref", "t.date"],
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "sortOrder",
        description: "Ascending or descending order",
        autocomplete: ["ASC", "DESC"],
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.Number,
        name: "limit",
        description: "Number of items to limit",
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "sqlfilters",
        description: "",
        optional: true,
      }),
    ],
    execute: async function ([sortField, sortOrder, limit, sqlfilters], context) {
      let page = Number(context.sync.continuation?.page) || 0;
      let url = `${context.endpoint}api/index.php/proposals`;

      let queryParams = {
        sortfield: sortField,
        sortorder: sortOrder,
        limit: BATCH_SIZE3,
        page: page,
        sqlfilters: sqlfilters,
      };

      let response = await context.fetcher.fetch({
        method: "GET",
        url: coda.withQueryParams(url, queryParams),
        headers: { "Content-Type": "application/json" },
      });

      let proposals = response.body;

      if (!proposals.length) {
        return {
          result: [],
          continuation: undefined,
        };
      }

      let fetchedData = [];
      for (let proposal of proposals) {
        let lines = proposal.lines || [];
        for (let line of lines) {
          if (!line.rowid) continue;
          fetchedData.push({
            ...line,
            id: line.rowid,
          });
        }
      }

      let continuation = proposals.length === BATCH_SIZE3 ? { page: page + 1 } : undefined;

      return {
        result: fetchedData,
        continuation: continuation,
      };
    },
  },
});

I would appreciate any guidance or advice on how to resolve this issue and ensure that all lines for each proposal are synced and displayed correctly in the Coda table.

Thanks in advance!

2

Odd. I wasn’t able to look too deeply at your code, but usually if an api is returning multiple objects and a Coda pack is returning only 1 or 1 per group its related to your id column in your schema.

Coda only returns one row per unique entry in your defined id column

So if you have the following values in fk_prop:
1
1
2
2
3
3

It will only return three rows: 1, 2, 3

Does that sound like it might be your issue? Hard to diangnose without actually seeing the http response

1 Like
3

Hi Scott,

Thank you so much for your help! Your suggestion to change the idProperty to a unique identifier for each line did the trick. I switched it to use rowid, and now all the proposal lines are syncing correctly. I can see all results as expected.

I appreciate your quick and effective assistance!

Best regards

4

You got it! Reach back if you need any more assistance!

1 Like