IDs list with the latest record and unique values

[G0ldsoul]

Hi, sorry I know the tittle can seem repetitive, and probably wrongly spelled.

I hope someone could help me with this conundrum.

I have this 3 tables one where i register tires, another with trucks and lastlty another where people register tyre movements and it references the other 2.

Tyre table

[Tyre ID]

Truck table

[Truck ID]

Activity table

[Act ID]	[Date]	[Truck ref]	[Position]	[Tyre ref

report table

[Report ID]	[Report date]	[Act Ids list]

So, I want to pull a list of [Acti ID] without repeating [Tyre ref] values (unique values), and those records should be the latest by [Date] in that specific [Tyre ref], and the date should be lower than [Report date] specifyed in reports table.

Truck can be repeated since each truck has 4 tires, and [Position] is a numeric value from 1-4.

Thanks in advance for your help.

[Suvrutt_Gurjar]

if understanding of your requirement is correct, please create a slice called say “Latest_Tyre_Activity” with a slice filter expression something like

[Act ID] = MAXROW(“Activity”, “Date”, [Tyre Ref]=[_THISROW].[Tyre ref])

where “Activity” is the “Activity table” name

This slice will give records with latest date for each unique [Tyre Ref]

If you wish only list of unique [Act Id] s then you could have those by the expression

Latest_Tyre_Activity[Act ID]

[G0ldsoul]

Sorry I wrote this on midnight, forgot to add the part of a fourth table reports. That [Act Id] list sould be also lower than a date specified in Reports table, so that list ahold be a value in column [Act ids list].

Although your answer does work in case I don’t have the date in reports.

[Suvrutt_Gurjar]

If you mention how the Report table is related to other tables, we could try to include that condition also.

[G0ldsoul]

Of course.

The reports table is used to trigger an automation bot who produces a report (pdf or xlsx) based on what kind of report they chose.
In this case the tires report filters the list of activities that were made before the date introduced and by the use and unique [tyre id], this [Act ids list] would be used in the report in a <<START: >><> expression as keys.

[Suvrutt_Gurjar]

Thank you. But I am sorry that I have not understood how exactly how Report table is used. For example not sure what the following statement means

@G0ldsoul

activities that were made before the date introduced

This is also not clear to me

[quote=“G0ldsoul”]
and unique [tyre id], this [Act ids list] would be used in the report in a <

[G0ldsoul]

Ok, so in this case the Reports table is not directly related to any table in the app, is used for making reports of any kind, it just generates them and the user can introduce in a date column an specific date to filter the information that the report will contain.

Whenever a record is created, a bot triggers, to create a .xlsx file with a report of the activities, that were made prior to the date inputed by the user in a [Report_date].

For example i got this result whitout using this formula

< < Start:FILTER
(
"Activity table",
[Tyre ref_act].[Tyre stat_tyre] = "Operating"
) > > 
< < [Truck_ref].[Truck name_truck] > >

Truck	Position	Tyre	Size	Tyre size	Date
ST-301	4	F3N181852	Operating	18.00R25	18/2/2025
ST-301	4	F4E182865	Operating	18.00R25	20/2/2025
ST-301	1	BVX1621T1A	Operating	18.00R25	18/2/2025
ST-301	2	CVC0014G4B	Operating	18.00R25	18/2/2025
ST-301	3	F3N581023	Operating	18.00R25	18/2/2025
ST-303	1	AVX0012T0A	Operating	18.00R25	19/2/2025
ST-303	2	XVX1779T3A	Operating	18.00R25	19/2/2025
ST-303	3	F3M184656	Operating	18.00R25	19/2/2025
ST-303	4	F4E182865	Operating	18.00R25	19/2/2025
ST-303	4	F3N181852	Operating	18.00R25	20/2/2025

As you can see, the trucks, position and tyre are repeated.

I want to pull something like this in case teh user inputed 21/02/2025 in [Report_date}.

Truck	Position	Tyre	Status	Tyre size	Date
ST-301	1	BVX1621T1A	Operating	18.00R25	18/2/2025
ST-301	2	CVC0014G4B	Operating	18.00R25	18/2/2025
ST-301	3	F3N581023	Operating	18.00R25	18/2/2025
ST-301	4	F4E182865	Operating	26.5R25	20/2/2025
ST-303	1	AVX0012T0A	Operating	18.00R25	19/2/2025
ST-303	2	XVX1779T3A	Operating	18.00R25	19/2/2025
ST-303	3	F3M184656	Operating	18.00R25	19/2/2025
ST-303	4	F3N181852	Operating	18.00R25	20/2/2025

Now, no tyre is repeated, it only shows its last record, in the last position it was and last truck it was.

So the problem is that i dont know how to get the right expression to put in <START:> to get a table like the last.

Hope this explains better my problem. Thank you @Suvrutt_Gurjar

[Suvrutt_Gurjar]

Thank you for more details However the following is not yet clear

@G0ldsoul

Whenever a record is created, a bot triggers,

Which table you are referring to please? Is it Reports table or Activity table or some other table?

Also does report table have multiple rows, say one per user to store the selection of that user or something else?

Based on the understanding so far and some assumptions, please try below

Please modify the suggested expression for the slice called “Latest_Tyre_Activity” with a filter expression something like

[Act ID] = MAXROW(“Activity”, “Date”, AND( [Date] <= [_THISROW].[ [Report_date],[Tyre Ref]=[_THISROW].[Tyre ref]))

I believe you will need modification in the expression based on which record of the Report table is used. If you respond to new queries, we could help you with the expression.

Then the <> expression can be simply

<<START: Latest_Tyre_Activity[Act Id] >>

[G0ldsoul]

@G0ldsoul

wrote:> > Whenever a record is created, a bot triggers,

Which table you are referring to please? Is it Reports table or Activity table or some other table?

Also does report table have multiple rows, say one per user to store the selection of that user or something else?

Yes im refering to the reports table.

Yes, the reports table can have multiple rows, its just a table to store the reports. take for example the next screenshot, its a table report from another app.:

It just stores the file, and data of whom solicited, the report, what kind of report, and a date to filter the data. And as you can see any user can come and generate a report with diferent parameters.

@Suvrutt_Gurjar

[Act ID] = MAXROW(“Activity”, “Date”, AND( [Date] <= [_THISROW].[ [Report_date],[Tyre Ref]=[_THISROW].[Tyre ref]))

This expresion could work, but how does [_THISROW].[ [Report_date],[Tyre Ref]=[_THISROW].[Tyre ref] helps to get a unique value? I don’t undesrtand how that works.

[Suvrutt_Gurjar]

@G0ldsoul

This expresion could work, but how does [_THISROW].[ [Report_date],[Tyre Ref]=[_THISROW].[Tyre ref] helps to get a unique value? I don’t undesrtand how that works.

If you look into the MAXROW() function, the expression basically

1. lists the latest rows by date with the following part of the expression

MAXROW(“Activity”, “Date”

2. The following part of the expression

[Tyre Ref]=[_THISROW].[Tyre ref] lists latset row for each unique [Tyre ref]

3. The following part of the expression

[Date] <= [_THISROW].[ [Report_date] selects only records that have date less than or equal to the date in the report table.

4. Since the expression is in the slice filter, it needs to eveluate to TRUE or FALSE. So we equate it with [Act ID]

[Act ID] = MAXROW(“Activity”, “Date”, AND( [Date] <= [_THISROW].[ [Report_date],[Tyre Ref]=[_THISROW].[Tyre ref]))

So the expression basically checks if the [Act ID] matches with the [Act ID] computed by right side of the equality operator and selects that record in the slice.

Hope this helps.

MAXROW() - AppSheet Help

[G0ldsoul]

Thank you @Suvrutt_Gurjar , I used this just specified the position of the tyre and it worked

[Suvrutt_Gurjar]

You are welcome. Great. Nice to know it worked the way you want. Thank you for the update.