Get Latest Date Summary based on all records in another table

[stenkate]

I've used hours to search for a solution via Airtable support pages, this community, Youtube (interesting video) and even ChatGPT, but I can't seem to find an out-of-the-box solution. Hopefully someone like you can help 🙂

In an Airtable dashboard interface my use case is just a 5 seconds configuration, and I can select it in a view's summary bar. But unfortunately not in the data tables as part of a formula so it seems.

The situation:

• I'm tracking expenses by importing transactions in a table {expenses} as separate records
• Each expense record has a {Date} field
• I'm categorizing each expense by selecting one category which is a linked field {Category} from the table {budget} with 73 category records.

What I'd like to do is create a formula in the {budget} table which uses - among other data - the latest date of all records in the {expenses} table. Why? To calculate the difference of my spending per category, which is now based on today instead of the last transaction date of all transactions via this formula for the field {Expenses Over(-) Under(+) €} in the {budget} table.

(
  {Expenses Yearly (budget 2023)} 
* 
(DATETIME_DIFF({Latest Import Date},DATETIME_PARSE(DATETIME_FORMAT({Latest Import Date},'YYYY')&'-01-01'), 'days')/365)
) 
- 
{Spend to Date}

The MAX(values) Rollup function is not helpful here: in that case I get the latest date for each category based on the records linked with that specific category. But I want to use a function in a formula that takes into account all records in the table.

In the Dashboard interface it's easy to retrieve this latest date: see in screenshot right bottom:
Number > Field Summary > Field: Date > Summary Type: Latest Date

In the summary bar of a table view it's also easy to retrieve: see in screenschot:
Latest Date

Is there a way to accomplish this in the Data Table via an out-of-the-box solution?

Thanks in advance!

[TheTimeSavingCo]

Hacky, but I'd probably just end up adding an "Overall" category type and linking every record to it and using the MAX(values) rollup you mentioned honestly.  Seems like the fastest way to get you the data you want?

[stenkate]

Thanks for that proposed solution Adam, I appreciate thinking along.

I was also thinking of it as possible last resort. But that involves an additional linking step for each expense record (maybe doable via an automation, I have to dive into that) and I still need to find a solution to use this data in a formula field instead of a rollup field (which accepts formulas but not as good as a formula field to my knowledge). So I was hoping/looking for a more automated solution.

[TheTimeSavingCo]

> that involves an additional linking step for each expense record (maybe doable via an automation

Ah it'd definitely be doable via an automation; triggered on record creation, and it would update the triggered record to add the link to the "Overall" category.

> find a solution to use this data in a formula field instead of a rollup field (which accepts formulas but not as good as a formula field to my knowledge)

Ha yeah fair enough.  If it helps it's usually fairly easily surmountable in my experience, so might be worth a shot.  Best of luck!

[stenkate · Accepted solution]

Update: I've managed to get this latest date via the following script (thanks chatGPT and my trials&errors):

// Select the tables
let tablePersonalExpenses = base.getTable("personal expenses");
let tablePersonalBudget = base.getTable("personal budget");

// Query the records in personal expenses table for the dates and sort them descending
let query = await tablePersonalExpenses.selectRecordsAsync({
    fields: ["Date"],
    sorts: [{field: "Date", direction: "desc"}],
    maxRecords: 1
});

// Get the latest date value (= first record in descending sorted order)
let latestDate = query.records[0].getCellValue("Date");

// Query the records in personal budget table
let tablePersonalBudgetQuery = await tablePersonalBudget.selectRecordsAsync();

// Update the value of field "Expenses Over(-) Under(+) €"
let updateRecords = tablePersonalBudgetQuery.records.map(function(record) {
    let spendToDate = record.getCellValue("Spend to Date");
    let budget2023 = record.getCellValue("Expenses Yearly (budget 2023)");

 // Calculate the number of days between latestDate and first day of that year as "diffDays"
    let jsDate = new Date(latestDate);
    let firstDayOfYear = new Date(jsDate.getFullYear(), 0, 1); // January 1st of the same year as 'latestDate'
    let timeDiff = Math.abs(jsDate.getTime() - firstDayOfYear.getTime()); // Difference in milliseconds
    let diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24)); // Convert to days and round up

// Return an update object for the current record
    return {
        id: record.id,
        fields: {

            "Expenses Over(-) Under(+) €": budget2023 * (diffDays/365) - spendToDate
        }
    };
});

// Update records - Only up to 50 updates are allowed at one time, so do it in batches
    while (updateRecords.length > 0) {
        await tablePersonalBudget.updateRecordsAsync(updateRecords.slice(0, 50));
        updateRecords = updateRecords.slice(50);
    }

[NikRib]

Is there a formula to find the Latest Date in another field in a table?
I have a field listing several dates, I need to pull the latest date from that list and show it in another field.
I cannot find the correct Function Name to accomplish this.
I tried these. {Dec 23 Check-ins} is the field name I am searching for the latest date:

LATEST_DATE({Dec 23 Check-ins})
and LATESTDATE({Dec 23 Check-ins})

[stenkate]

Do you mean you have a table with various records, each record has a date value and you want to find the latest date of all these dates of all these records?

In that case the MAX(values) Rollup function could work for you I assume. In my situation that was not useful, so I've used the following script to calculate the value for a latestDate field. Although using a script extension is not part of Airtable's free plan after the new pricing plans this year.

// Select the tables
let tablePersonalExpenses = base.getTable("personal expenses");

// Query the records in personal expenses table for the dates and sort them descending
let query = await tablePersonalExpenses.selectRecordsAsync({
    fields: ["Date"],
    sorts: [{field: "Date", direction: "desc"}],
    maxRecords: 1
});

// Get the latest date value (= first record in descending sorted order)
let latestDate = query.records[0].getCellValue("Date");