How to filter to find the entry with the max value or max timestamp in a field?

[Dan]

I have confiforms entries tracking changes:

Key    Sequence  Other fields...
------ --------- --------------------------------------------------
value1         1 ... initial data for key=value1 ...
value1         2 ... updated data for key=value1 ...
value2         1 ... initial data for key=value2 ...
value2         2 ... updated data for key=value2 ...
value2         3 ... updated (again) data for key=value2 ...

The Sequence field could alternatively be a timestamp of when the change was made.

In a "Lookup and set value" rule, I want to pull the single most recent (i.e. "current", not historical) entry. E.g. something like:

Key:value1 AND Sequence:max(sequence)
-or-
Key:value1 AND ChangeTimestamp:max(ChangeTimestamp)

 Is there filter logic that can get me to the single entry with the max sequence or highest timestamp? Thank you!

[Alex Medved _ConfiForms_ · Accepted solution]

Hi @Dan 

In the "lookup and set" rule define this parameter

created DESC LIMIT 1

Alex

[Dan]

You sir, have the patience of a saint. Thank you for pointing out what should have been obvious :).

[Dan]

Follow-up Question:

Given a number of records with non-unique keys, what filter can I apply to an API call to return the subset of records having the max created date for each key?

Data in ConfiForm:

Key Column created    other data....
---------- ---------- ------------------------------
Key 1      2023-12-01 .............
Key 1      2023-12-13 .............
Key 2      2023-12-03 .............
Key 3      2023-12-04 .............
Key 3      2023-12-02 .............
Key 3      2023-12-10 .............

Data I want to retrieve from the API:

Key Column created    other data....
---------- ---------- ------------------------------
Key 1      2023-12-13 .............
Key 2      2023-12-03 .............
Key 3      2023-12-10 .............

I can see how to sort, and how to limit the entire dataset, but not how to limit within a "sort grouping".

Thank you :)

[Alex Medved _ConfiForms_]

ConfiForms view macro have a "grouping" parameter. Set the ordering (created DESC)  and grouping (created)

Alex