How to Display Nested Array Parameters in Table Rows Using Coda Pack?

[Juan_Carlos_Vega]

Hello everyone,

We have developed a pack that performs a GET request to a billing program to retrieve orders. The issue we’re encountering is related to extracting information that’s nested within an array. We attempted to address this by defining a constant with the parameters of the array we wanted to retrieve, along with another constant for the information outside the array that includes the array itself:

const InvoicesSchema = coda.makeObjectSchema({
  properties: {
    ref: { type: coda.ValueType.String },
    id: {
      type: coda.ValueType.Number,
      fromKey: "id",
    },
    lines: {
      type: coda.ValueType.Array,
      items: InvoiceLinesSchema,  // Usar el nuevo esquema para las líneas de factura
    },
    date: { 
      type: coda.ValueType.String,
      codaType: coda.ValueHintType.Date,
    },
    totalTTC: {
      type: coda.ValueType.Number,
      fromKey: "total_ttc",
    },
    cond_reglement_doc: { type: coda.ValueType.String },
  },
  displayProperty: "ref",
  idProperty: "id",
});

We then use pack.addSyncTable to create a table with all this information. The problem is, the table looks like this:

As can be seen, there is no option to “unhide.” Therefore, I would like to ask if there’s a way to display the parameters in table rows? Thanks in advance.

[Eric_Koleda]

Hi @Juan_Carlos_Vega - The “unhide” option only works on top-level properties (AKA columns) of the sync table. The Lines column is an array of sub-objects, and the properties of those objects don’t behave the same way. You can manually create a new column with a formula that extracts a value from the lines, for example Lines.ProductType.

An alternative design would be to have a separate sync table for lines, which has a reference column that referenced the parent invoice in the invoices tables. It’s a bit more complicated to build, but might have a nicer UX.

[Juan_Carlos_Vega]

Hi, and thank you for your advice on separating a sync table for lines. We implemented a new sync table following your suggestion, but we’ve run into several issues that we hope you can help us with. Here’s a brief overview of our setup and the code we used:

const BATCH_SIZE = 600;
const BATCH_SIZE2 = 120;

const InvoiceLinesSchema = coda.makeObjectSchema({
  properties: {
    id: {
      type: coda.ValueType.Number,
      fromKey: "rowid", 
    },
    quantity: {
      type: coda.ValueType.Number,
      fromKey: "qty", 
    },
    totalHT: {
      type: coda.ValueType.Number,
      fromKey: "total_ht",  
    },
    VATRate: {
      type: coda.ValueType.Number,
      fromKey: "tva_tx",  
    },
    totalTTC: {
      type: coda.ValueType.Number,
      fromKey: "total_ttc", 
    },
    fkfacture: {
      type: coda.ValueType.String,
      fromKey: "fk_facture",
    },
  },
  displayProperty: "quantity",  
  idProperty: "id"  
});
pack.addSyncTable({
  name: "InvoiceLines",
  description: "",
  identityName: "InvoiceLine",
  schema: InvoiceLinesSchema,  /aquí
  formula: {
    name: "SyncInvoiceLines",
    description: ".",
    parameters: [
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "sortField",
        description: "",
        autocomplete: ["t.ref", "t.date"],
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "sortOrder",
        description: "",
        autocomplete: ["ASC", "DESC"],
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.Number,
        name: "limit",
        description: "Número a mostrarse como límite",
        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 fetchedData = [ ];
  let url = `${context.endpoint}api/index.php/invoices`;  

  while (true) {
    let queryParams = {
      sortfield: sortField,
      sortorder: sortOrder,
      limit: BATCH_SIZE2, 
      page: page,
      sqlfilters: sqlfilters,
    };

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

    let invoices = response.body;
    if (!invoices.length) break;

    for (let invoice of invoices) {
      let lines = invoice.lines || [];
      for (let line of lines) {
        if (!line.rowid) continue;  
        fetchedData.push({
          ...line,
          id: line.rowid,  
        });
      }
    }
    page++;
    if (invoices.length < BATCH_SIZE) break; 
  }
  return {
    result: fetchedData,
    continuation: fetchedData.length ? { page: page } : undefined,
   };
  },
  },
});

We’re facing three specific problems:
1.-The table functions correctly up to 1,000 rows. However, increasing the limit to 10,000 rows causes issues beyond page 8 of the GET requests, despite the URL being valid.

Error:
Overview: A continued refresh of sync table InvoiceLines failed: Error at line 714: 404 - {"error":{"code":404,"message":"Not Found: No invoice found"}}
User ID -1
Connection ID 76b169ab-6e38-47c7-8706-47d74ca54b8d
Pack Version 125
Pack ID 30791
Request ID 39539fd1-a4cf-454a-9ff9-67de34fd60f6
Created at May 14, 2024 @ 10:54:15
Request type invokeSyncFormulaRequest
Duration 1398ms
Error Error at line 714: 404 - {"error":{"code":404,"message":"Not Found: No invoice found"}}
Stack trace at async execute (code.ts:714:20) at async Object.exec [as execute] (coda:@codahq/packs-sdk:4:40497

2.- When we switch the batch size check in the loop from BATCH_SIZE to BATCH_SIZE2 like this:

 page++;
    if (invoices.length < BATCH_SIZE2) break; 
  }

we get this error:

3.- Reducing the batch size to enhance the granularity of data fetching (e.g., setting it to intervals of 10) drastically increases the load time. And get the past 60 seconds of loading error.

Could you suggest how we might resolve these issues, particularly regarding pagination limits and batch size adjustments? Any insights or alternative strategies would be greatly appreciated. Thank you!

[Eric_Koleda]

Hi @Juan_Carlos_Vega - Regarding problem #1, that response is coming from the API itself, indicating that you are trying to fetch pages beyond the last page. That means that there is an error in your logic somewhere causing you to try and fetch pages you shouldn’t.

For problem #2, we go have a max of 4mb on all fetch responses. That’s usually enough for most JSON APIs, but perhaps this one is returning a lot of data for each item? Reducing the page size to 100 or 200 would likely keep you under the limit while still being performant.

For problem #3, very small batch sizes can make the sync quite slow, but as per the above a think you could likely do a few hundred and still be fast and under the limit.

Looking at your code, you should remove the while loop. When using continuations, each call to the execute function should only fetch a single page of results, and then return those results along with a continuation object if the sync should continue. The while loop here is trying to fetch all the pages of data in a single execution, which isn’t correct.

[Juan_Carlos_Vega]

Hi Eric,

Thanks for your helpful advice. Following your suggestions, we corrected our pagination logic to avoid requesting pages that don’t exist, resolving the 404 errors. We also adjusted the batch size to 120 items per request, which kept our data responses within manageable limits without causing timeout issues. Additionally, we removed the while loop to ensure each execution only fetches a single page of results, improving overall efficiency and preventing excessive load times. With these changes, the sync table now works correctly, allowing us to display and manage invoice lines effectively.

Thanks again for your support!

[Eric_Koleda]

Glad to hear it all worked out!