Get the record of a MAX field

[Erik]

Here’s an interesting situation. I’m not sure what to search for or where to start…

In my Account stable I have created a MAX field related to a connection. I’d like to be able to report other values of the record that evaluates as the MAX.

For example, MAX is the record with the latest Date. I’d like to get the Hours value of that record.

If anyone has any thoughts on this, I’d appreciate it.

[StephenChapman]

Hey @Erik,

An interesting problem! I have three solutions, depending on your scenario.

---

Scenario 1 - You want to output hours for the current week:

1. Format your Hours table like so, with Current week hours only displaying if the Week field is in the current week.
   
   
2. You should then be able to use a Sum field on the Accounts table to fetch this value.

---

Scenario 2 - You want to output hours for the latest week, which may not always be the current week:
Use some code that turns your Accounts grid from this:

Into this:

1. Ensure your Hours table is sorted by Week from latest to earliest
2. In your Accounts grid, add a connection field of the Hours field, with comma-separated values
3. Insert the below code in your JS editor, replacing view_1 with your Accounts table key, and field_1 with your connected Hours field.
   This code will display the first value of the comma-separated Hours values in this field.
   Note that if you export the grid, all of the comma-separated values will display, so this will only work as a read-only solution

$(document).on(`knack-records-render.view_1`, async function (event, view, data) {

  const hoursKey = 'field_1';
  $trs = $(`#${view.key} tbody tr`);
  $trs.each(function() {
    const $hoursTd = $(this).find(`.${hoursKey}`);
    const hours = $hoursTd.text().trim().split(', ')[0];
    $hoursTd.text(hours);
  });

});

Here’s a video of me installing this code.

---

Scenario 3 - The above will not work and you need it to record the latest week’s Hours in the Accounts table:
You will need to use an automation (e.g. Flows, Make, Zapier, Knack JS) to run every time an Hours record is created/updated to:

1. Determine if it’s the latest week with comparison to the Account’s max week value
2. If so, update the Account’s Hours field to the Hours record’s value

---

Let me know which solution you go for, if at all!
Also, if I’ve overthought this and someone else has an easier solution, let me know!

[Erik]

Thanks Stephen.
Some interesting ideas.
I think I explained it poorly.
The MAX field is in the Account table. It finds the connected record with the latest (greatest numerical “week of” field) date.
I’d like to get the Hours from that particular connected record.

Seems like a tough one to crack…

[StephenChapman]

@Erik That’s exactly what I understood. The ‘Hours table’ I mention is from a separate table, which could be called Timesheets, etc.