Field script to extract earliest date from table field

[Peter Riley]

My freezer inventory db (template attached) has a table field in which I record each time I make a batch of a particular recipe. The table field’s fields include things like the quantity of meals frozen, and the date frozen. I have a field script that correctly populates a Total field with the total remaining meals from each record’s table, and I have tried to do the same with a date field – ‘Earliest freeze date’ – to extract the oldest frozen meal from that recipe, using the following script so I can then include the field in a search:

record.getMinOfLinkedFieldForField('fld-7b64bb627a8a4beaadacd625f3c4e46a', 'fld-557ef5d4175949a8a0ac0b7b3cf5f932');

This is the total script – ie there is no function – but for a batch of meals frozen on 21 April 2025 and 14 September 2025 I get ’21 Jan 1970 at 05:46:30′ in the script field. The console shows the result of running the script as 1745190000. The Result Type is set to ‘Date’.

Is getMinOfLinkedField not appropriate for a date calculation?

I’m assuming it’s not possible to script a search through the freeze dates of the table field?

Thanks

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

According to the API documentation getMinofLinkedFieledForField() expects a number as argument.

Following works for me:

function earliestDate() {

    const portions_id = 'fld-7b64bb627a8a4beaadacd625f3c4e46a';
    const portions__date_frozen_id = 'fld-557ef5d4175949a8a0ac0b7b3cf5f932';

    // get all table records
    let tableRecs = record.getFieldValue(portions_id);

    // get dates as an array and filter out invalid dates
    let dates = tableRecs.map(el => {
      let raw = el.values[portions__date_frozen_id];
      let obj = typeof raw === "string" ? JSON.parse(raw) : raw;
      return obj?.date ? new Date(obj.date) : null;
    })  
    .filter(d => d instanceof Date && !isNaN(d));

    console.log("Dates: " + dates)

    // get earliest date
    let earliest = new Date(
      Math.min(...dates.map(d => d.getTime()))
    );
    
    console.log("Earliest date: " + earliest);

    return earliest;
}

earliestDate();

Return type is Date.

There most likely more elegant ways do to this.

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Peter Riley]

Wonderful! Thanks Daniel.

I did some research into JS methods but have to admit the code was way above my level so, elegant or not(!), your solution is just what I need.

I get a console error if the table is blank in a record (because that particular meal has been eaten and the row deleted) but that doesn’t seem to affect execution of the script.

Cheers

Peter

[Daniel Leu]

Great! You can add a if (tableRecs.length == 0) return; just after the let tableRecs =... assignment. This should fix the error.

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Author]

Posts