How to Select oldest row [date] from a table

Hi, I hope you can help. Background - I have an excel spreadsheet that I have imported. It contains golf scores for a group of friends. I have logic/formula in the Excel spreadsheet that is not supported in Appsheet so I am looking to use Appsheet expressions to recreate them.
I have a ‘scores’ table imported from Excel with 20 rows (fixed) per person. Here are 2 of the things I would like to do, I appreciate there is always more than one approach.

I want to create a slice (single row of data) of the oldest row per person [selected by date, person] to be updated via the app. (I dont want the other 19 rows per person to be updated). I’ve looked at SELECT, MINROWS etc. For info, In Excel I used an IF/SMALL [date] function on an array.

My 2nd problem is similar - I want to create a slice to select the lowest 8 [scores] of the 20 scores per person. Again, I had this flagged in Excel using an IF/SMALL (8) [score] function on an array but it’s not supported when imported.

Any help is greatly appreciated.
Many Thanks, Ian

1 Like

Please try
[Key Column]= MINROW(“Table Name”, “Date”, [Person Name] = [_THISROW].[Person Name])

This expression will create a slice with oldest row for each person.However the slice will have one row with oldest data for each of the persons in the table.So if there are 10 persons there will be 10 rows in teh slice. Hope that is what you are looking for.

Could you please update if you are using a fixed length key such as say UNIQUEID() for this table?

1 Like

Many, Many Thanks, The 1st problem is solved.

re your question on the 2nd Question.
I have a system generated [_RowNumber] as the key.
I am thinking of adding a [PersonNumber] in the format of 001 for each person rather than use [Name] as I currently do.

Thanks again

Thank you. In general , [_ROWNumber] or even a serial number is not a good idea for a key in an AppSheet app, unelss the table is a read only stattic table. Please go through the articles below

Please evaluate your case depending on your requirement and above description.

Assuming you choose to use UNIQUEID() as a key, the AppSheet expression of UNIQUEID() generates an eight character unique ID as a key for each row.

In that case you could please try following expression in slice to select the least 8 scroes for each participant.

IN([Key Column], SPLIT(LEFT(TEXT(ORDERBY(SELECT(Table Name[Key Column], [Person Name]=[_THISROW].[Person Name]), [Score])),85)," , "))

Please note that the number 85 in above expression will vary on the length of your key. The expression assumes 8 character fixed length UNIQUEID() as key. So for first 8 keys teh list occupies a string length of 85.

2 Likes

Further reading:


2 Likes

Thanks again,
I have read the supporting doc and looked at my key structure and changed it.
I don’t have (or think I need) a UNIQUEID() in my source dataset - however, are you saying I need UNIQUEID() this to perform this expression?
I now have a _ComputedKey of [Username][Date] key in my scores table.Username is fixed at 8 characters.
Are you saying the solution above wont work on a variable length key. Have I understood correctly?

Thanks, Ian

Thanks Steve,

I’m a retired old Skool techie who’s not coded for 15+ years so I’m having to unlearn what I remember about relational data etc… I’ll get there.
Fortunately I’m building an App just for me and it’s not business critical in any way.

Many thanks

2 Likes

Yes, you are correct. This solution will work only with fixed key lengths.

Could you please mention from where you are getting USERNAME(). Again USERNAME() is generally not a good option for a key However just in case you have unique usernames for each user, you could use a key somethig like

CONCATENATE([Username], TEXT([Date] , “DDMMYYYY”))

This, i believe will give a fixed key length of 16 . So for 8 keys you may need a string of 149 characters. Thus, you could use slice expression something like below

IN([Key Column], SPLIT(LEFT(TEXT(ORDERBY(SELECT(Table Name[Key Column], [Person Name]=[_THISROW].[Person Name]), [Score])), 149 )," , "))

The latest suggestion on computed key expression and the slice expression, I have not tested. However, I believe it will work. Please test well.

2 Likes

If the key character length is not fixed, you could use following expression

IN([Key Column], TOP(ORDERBY(SELECT(Table Name[Key Column], [Person Name]=[_THISROW].[Person Name]), [Score]),8))

here we have used TOP() function with description as below to get 8 row IDs with the lowest scores. @Steve has given some beautiful examples of using the TOP() function in the article below.

1 Like

Hi

IN([Key Column], TOP(ORDERBY(SELECT(Table Name[Key Column], [Person Name]=[_THISROW].[Person Name]), [Score]),8))

Once again many thanks as this has returned the correct results.

best regards, Ian

2 Likes