How to count sum based on actual year

How to
#1

Hey,

I have a problem and I don’t know how to find a solution.

Let’s assume that we have two tables:

  1. Invoices
  2. Clients

These two tables are linked - one client has many invoices

Now in the Clients table I would like to have calucalted field, which counts the sum of all invoices from actual year.

How to achieve this?

#2

Hi,

please try it this:

  • In Clients, create a normal numeric field, for example <CLIENTS_YEAR_SUM_FIELD>.
  • Recalculate that field by script whenever an invoice is created/changed.
  • In the script:
  • load invoices with doo.table.getData(...)
  • filter by the client link and by invoice date in the current year
  • sum the invoice amount
  • write the result back to the client with doo.table.updateFields(...) 
const invoicesTable = "<INVOICES_TABLE>";
const clientsTable = "<CLIENTS_TABLE>";

const clientId = doo.model.<CLIENT_LINK_FIELD>.value?.id; // Requires your schema to have this link field
if (!clientId) return;

const now = new Date();
const year = now.getFullYear();
const yearStart = `${year}-01-01`;
const yearEnd = `${year}-12-31`;

const invoices = await doo.table.getData(invoicesTable, {
  filter: [
    { field: "<CLIENT_LINK_FIELD>.id", operator: "eq", value: clientId },
    { field: "<INVOICE_DATE_FIELD>", operator: "gte", value: yearStart },
    { field: "<INVOICE_DATE_FIELD>", operator: "lte", value: yearEnd }
  ],
  filterOperator: "and",
  loadFields: ["<AMOUNT_FIELD>"]
});

const sum = (invoices.data || []).reduce((acc, row) => {
  return acc + (Number(row.fields.<AMOUNT_FIELD>) || 0);
}, 0);

await doo.table.updateFields(clientsTable, clientId, {
  "<CLIENTS_YEAR_SUM_FIELD>": sum
});