Find a Record using SYS_USER and MAX

Hi,

I am new to Honeycode and LOVING it! I am building an app and have the following table;

MEMBER | DATE | SCORE

What I would like to do is display (in the Builder screen) the DATE associated with the row that meets this criteria;

MEMBER = SYS_USER
SCORE = MAX SCORE

A member can have many SCOREs over many DATEs. If by chance they have 2 scores meeting the MAX condition then I would like to return the most recent one, using DATE.

How can I use Honeycode Functions to achieve this (I have tried everything I can think of).

Thanks.

Hi @justin.stirling, welcome to the community :smiley: :honeybee:

Happy to help! For this, here's an example solution that uses FILTER and FINDROW to display the top scores of a user. I put the formulas in the tables to make it easy to call the result in the app. This is how it looks:

Here's a table of Scores, with an associated date and name:

And I have a table of Participants, which I've added columns to pull all scores, the top score, and the top score's associated date.

For the All Scores column, the formula to pull all of a participant's associated scores is:
=FILTER(Scores,"Scores[Name]=[Name]")

The next column uses a combo of FINDROW to call the first row of the [All Scores] FILTER and ORDER BY to sort the score list in ascending order by both score and date. This will return the most recent top score:

=FINDROW((FILTER([All scores],"ORDER BY Scores[Score] DESC, Scores[Date] DESC")))

And the last column calls the associated date of that row we just called of the top score:

=[Most recent top score][Date]

Now in the app, you can call for the top score and date columns for the SYS_USER using formulas like:

=FINDROW(Participants, "Participants[Name]=$[SYS_USER]")[Most recent top score]

=FINDROW(Participants, "Participants[Name]=$[SYS_USER]")[Recent top score date]

You could also add a list object of all of the top scores for all users like this:

Here's how it looks in action:

Hope this helps! :slight_smile: Let us know if we can assist any further. :honey_pot: :honeybee:

This is Brilliant! Thanks so much @Alyssa.

1 Like

@justin.stirling, you're welcome :smiley: Always happy to help :honeybee:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.