Cannot use Maxrow() to get the desired last value

[Light_newbie]

Hi. I am building an Appsheet to record the odometers of my company's cars and the number of km each car runs per day. 

It contains 4 columns in the Table_Record: [CarName], [Date], [Odometer], [KM Run].

What I'm trying to do is,

•  Enter the car's [Odometer], and the [KM Run] can calculate the difference between the current [Odometer] and the last value of the [Odometer]. (Goal A)
• Furthermore, it should match to the same Car to get the correct last value. (Goal B)
• The final step is to match with the same day. (Goal C)

However, I am having trouble in the very first step. I have added a new virtual column [Latest Odometer]. Using this formula: 

Maxrow("Table_Record", "Odometer", [CarName]=[_Thisrow].[CarName]

And the formula in [KM Run] is 

[Odometer]-[Latest Odometer]

But the result of the [Latest Odometer] is not something I expected: 

CAR01: 11/07/2024 : 2000

Look like it is the _ComputedKey with the following expression: 

CONCATENATE([CarName],": ",[DATE],": ",[Odometer])

Since I clicked [CarName], [DATE] and the [Odometer] as the key and it auto-computed this virtual column for me. This time I only checked the [Odometer] as the key. The return seems to be correct this time. (But perhaps it would cause an issue if CAR01 and CAR02 might have the same Odometer one day?)

And I have no clue about how to achieve the Goal B and C. Am I performing the key's setting and formula correctly? I am so new to the AppSheet, please give me some suggestions for it. Thank you so much.

[Suvrutt_Gurjar · Accepted solution]

Generally aggregating record wise values is not ideal with AppSheet. Please note and try below.

1) MAXROW() works with only on row key columns. So it will return only key column of the max record depending on max condition. 

2) You may not have odometer as the key. It does not sound like a good choice for key.

3) Instead you could add another column called say [ID] with UNIQUEID() as initial value , keep it hidden and make it a key column.

4) Please add a VC called [Latest Odometer] with an expression something like [Odometer]+ [KM Run] 

5) In the initial value of the [Odometer] column, please add an initial value expression something like 

MAX(SELECT(Table_Name[Latest_Odometer],[CarName]=[_THISROW].[CarName],[Date]=[_THISROW].[Date]))

In the valid_if of the [Odometer] column you could add an expression something like 

[_THIS]>=MAX(SELECT(Table-Name[Latest_Odometer],AND([CarName]=[_THISROW].[CarName],[Date]=[_THISROW].[Date], [_RowNumber]<[_THISROW].[_RowNumber])))

The suggestion step 5 will compute the latest odometer reading from the previous record for the same car and present it as the initial value for the new record being added. The user can then just update the [Km_run] to get the latest odometer readin for that record. The solution assumes , each increasing "Odometer" reading for the same car will be recorded in a new higher row number. Also deletion of intermediate records may cause a computation error.

Please test well.

[Suvrutt_Gurjar · Accepted solution]

Generally aggregating record wise values is not ideal with AppSheet. Please note and try below.

1) MAXROW() works with only on row key columns. So it will return only key column of the max record depending on max condition. 

2) You may not have odometer as the key. It does not sound like a good choice for key.

3) Instead you could add another column called say [ID] with UNIQUEID() as initial value , keep it hidden and make it a key column.

4) Please add a VC called [Latest Odometer] with an expression something like [Odometer]+ [KM Run] 

5) In the initial value of the [Odometer] column, please add an initial value expression something like 

MAX(SELECT(Table_Name[Latest_Odometer],[CarName]=[_THISROW].[CarName],[Date]=[_THISROW].[Date]))

In the valid_if of the [Odometer] column you could add an expression something like 

[_THIS]>=MAX(SELECT(Table-Name[Latest_Odometer],AND([CarName]=[_THISROW].[CarName],[Date]=[_THISROW].[Date], [_RowNumber]<[_THISROW].[_RowNumber])))

The suggestion step 5 will compute the latest odometer reading from the previous record for the same car and present it as the initial value for the new record being added. The user can then just update the [Km_run] to get the latest odometer readin for that record. The solution assumes , each increasing "Odometer" reading for the same car will be recorded in a new higher row number. Also deletion of intermediate records may cause a computation error.

Please test well.

[Suvrutt_Gurjar]

Another option is to have two columns in the record [Odometer_Start] and [Odometer_End] for a record to record starting and ending odometer reading. The [Km_run] is then simply [Odoemter_End] -{Odometer_Start]

[Odoemter_End] can be copied in the next higher record as initial value using the MAX() expression shared earlier.

[Light_newbie]

Thank you so much for giving me that many suggestions. Much appreciated. It gives me tons of ideas on how to use Maxrow(). 

At step 5, would it be possible that it uses AND() to link with the [CARNAME] and the [DATE]?

[AleksiAlkio · Accepted solution]

Would it be easier and without any VC to just read in the app formula the latest and biggest Odometer value with..
LOOKUP(
Maxrow("Table_Record", "Odometer",
AND([_ROWNUMBER]<[_THISROW].[_ROWNUMBER],[CarName]=[_Thisrow].[CarName]),
Table_Record,YourKeyColumn,Odometer)
and then deduct that from the added Odometer value. The rownumber is there so you can later modify that record if you find out right away something was wrongly typed.

The key could be something like CONCATENATE([CarName],": ",[DATE]) as then you are not able to add the same Odometer value for one car more than once.

[Light_newbie]

Thank you so much! This is exactly what I want!

It takes me a little bit of time to learn about LOOKUP(), AND() and the way you use MAXROW().

I have thought about some issues that might happen in the future:

• The user missed entering a record on time and must insert it later. That record's Odometer value is not the latest or highest. Then the [Km RUN] will be wrong.
• The user deletes one of his records, leading to the wrong result.
• The user has to edit the record when he finds something was wrongly typed after a new record has been added. Editing the Odometer cannot auto-update the value of [KM Run].

I will think about it later. But so far it perfectly works for me since I am the only user and understand what is the limitation of it. Many thanks for your tutorial.

[AleksiAlkio]

If you use [Date]<[_THISROW].[Date] instead of the rownumber, you can solve this challenge.

[Light_newbie]

Amazing!

I changed it to [Date] < [_THISROW].[Date] then the newly added record can update its [km RUN]. However, the records placed after the newly added record cannot be updated on their own.

Like this sample. I entered a new record Odometer 15 between two records. The new one can show the correct [km RUN] 5. But the last record cannot auto-update it [km RUN]. It still shows the 20, instead of the correct answer 15.

Thanks to this Q&A. I managed to find a solution to it. I made a new virtual column [VC_km RUN] and used the same formula. I don't understand why but the virtual column can auto-update all the values each time I edit/add/delete the record. I am so glad that everything seems to be solved now~

[AleksiAlkio]

This is because the virtual column is computed every time you sync, edit etc. The normal volume is only calculated when you somehow modify the record  

[Suvrutt_Gurjar]

---

@Light_newbie wrote:

At step 5, would it be possible that it uses AND() to link with the [CARNAME] and the [DATE]?

---

Yes , sure,  good catch. You can add it. It was a typo to miss out on the AND().