Background:
I have a large-ish data set (~5k rows) that I want to pull into Coda for reference and analysis. These are invoices so there is a need to bring in new ones as they are created but I do NOT need to sync old ones since they are fairly static once created.
Sync Table:
I could create a sync table for this, but it seems really stupid since this will query all ~5k rows and I really only need the most recent 200. The issue is if I filter to only the most recent ones the sync table will remove the other 4,800 rows.
Custom Function:
I’ve created a custom function that grabs the rows I want and outputs (I think) an array of objects. I then combine this with FormulaMap to input these objects into a table. This is where things get weird.
Issues:
- The table sees them as objects, but not really. They don’t have the icon or rounded edges, but when I mouse over them I can see other attributes
Despite seeing some other attributes, most of the attributes defined in the schema are missing.This was because I was passing the reference schema and not the actual schema.- Any formulas in other columns that attempt to reference the attributes on the object return a blank string
- I see the following error in the Pack Log:
Formula Invoice failed: Unexpected token R in JSON at position 0
I’ve also tried using formatColumn to tell Coda that these are objects that follow a schema. The issue there is that it seems this was designed to only work for a function that alters the value and I can’t really tell what that value is. Any function I try to run on it (in the pack or in Coda itself) returns very unexpected values. (JSON.parse errors out, referencing elements directly fails).
Am I doing something entirely wrong? This seems like it should be incredibly simple but somehow is actually impossible.
Some Relevant Code Snippets:
Schema and Reference Schema
const InvoiceSchema = coda.makeObjectSchema({
properties: {
uuid: {
type: coda.ValueType.String,
required: true
},
id: {
type: coda.ValueType.String,
required: true
},
data: { type: coda.ValueType.String },
serviceFee: { type: coda.ValueType.Number },
year: { type: coda.ValueType.String },
startDate: { type: coda.ValueType.String },
endDate: { type: coda.ValueType.String },
totalSales: { type: coda.ValueType.Number },
company: CompanyReferenceSchema,
location: LocationReferenceSchema
},
displayProperty: "id",
idProperty: "uuid",
featuredProperties: ["uuid", "id", "company", "location", "serviceFee"]
});
const InvoiceReferenceSchema = coda.makeReferenceSchemaFromObjectSchema(
InvoiceSchema, "Invoice");
Fetcher Function:
async function fetchInvoices(nextToken = null, context) {
let invoices = [];
let payload = {
nextToken: nextToken,
perPage: 500,
...
};
let response = await context.fetcher.fetch({
method: "POST",
url: "<url>",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify(payload),
});
let data = response.body;
if (data.nextToken) {
let additional_responses = await fetchInvoices(data.nextToken, context);
//invoices = additional_responses;
}
for (let item of data.items) {
invoices.push(formatInvoice(item))
}
return invoices;
}
function formatInvoice(item) {
return {
uuid: item.id,
data: item.data,
id: item.id,
company: { uuid: item.data.company?.id },
location: { uuid: item.data.location?.id },
year: item.data.year,
startDate: item.data.startDate,
endDate: item.data.endDate,
totalSales: item.data.sales.totalSales,
}
}
pack.addFormula({
name: "FetchRecentInvoices",
description: "Fetches any invoices in the last 30 days.",
parameters: [],
resultType: coda.ValueType.Array,
items: InvoiceSchema,
execute: async (args, context) => {
const response = await fetchInvoices(null, context);
return response;
},
});
Attempt at Column Format:
pack.addColumnFormat({
name: "Invoice",
instructions: "Invoice Objects",
formulaName: "Invoice",
})
pack.addFormula({
name: "Invoice",
description: "Shows an invoice",
resultType: coda.ValueType.Object,
schema: InvoiceSchema,
parameters: [
coda.makeParameter({
type: coda.ParameterType.String,
name: "input",
description: "f",
}),
],
execute: async function ([input], context) {
return JSON.parse(input)
}
})
