Use Info on submitted form as value to change on another row

[AviSyst_Admin]

I have an action set to “Data: execute an action on a set of rows”. I can reference the row I want to edit, that’s fine and working. I have then made another action that is called by this one in the “referenced Action” drop down.

In this action I would like to use a value in the form that was just submitted (so the last row in the table) and replace a value in the row that was referenced in the initial action. They are in 2 different columns but I don’t think that makes a difference. This is all on the same table.

I can easily set the value in the column (in the row to be edited) to be changed to just a random number so I know its all working fine upto that point.

The problem comes when I try to get the value from the last row and use that. If I use [_THISROW].[Column_To_Get_Value_From] or just [Column_To_Get_Value_From], it will use the value from the row that I want to edit, not the last row (the one that trigged the action).

I have used various attempts as MAXROW(), LOOKUP(), SELECT() but can’t get anything to work. I’m sure this should be easy, I just need to to get the value from the last (bottom) row and use that. The closest I got was to make it find the MAX value from the [Column_To_Get_Value_From] and insert that, but as the values wont be in any sort of order, it needs to be the very last row, simple as that!

I’m sure this isn’t as hard as I’m finding it, spent 6 hours trying to get it to work!

Any help would be great.

Rob.

[Lynn]

Hi @AviSyst_Admin
Did you try some thing like this.

How can I select the most recent record from ... Questions

To get the row at the bottom of the spreadsheet, the row with the highest row number: MAXROW(“TableName”, “_ROWNUMBER”) replacing TableName with the name of your table. If your table has a timestamp column (Timestamp), and you want the row with the most-recent timestamp: MAXROW(“TableName”, “Timestamp”)

[AviSyst_Admin]

How can I select the most recent record from ...

MAXROW(“TableName”, “_ROWNUMBER”)

Yep, It gives the following error:

The expression is valid but its result type ‘Ref’ is not one of the expected types: Number

I also tried

MAXROW(“APP Fuel Truck Form ”, “_ROWNUMBER”).[Column_To_Get_Value_From]

and I get the same error.

So I can find the row as a reference, but can’t seem to “convert” that to a row number or a reference I can use.

[Suvrutt_Gurjar]

Have not properly understood all the details.

AviSyst_Admin:

I also tried

MAXROW(“APP Fuel Truck Form ”, “_ROWNUMBER”).[Column_To_Get_Value_From]

However the above could be possibly achieved by

SELECT(Table Name[Column_To_Get_Value_From], [Key Column of the table]=MAXROW(“APP Fuel Truck Form ”, “_ROWNUMBER”))

But please note that this will be an inherently sync time expensive expression, especially for a table with large number of rows.
Depending on app design, you may try a dual step approach. Acolumn that calculates MAXROW() first and then using that column to get the value of [Column_To_Get_Value_From] using dereference expression.

[AviSyst_Admin]

That gave me the error:

“The expression is valid but its result type ‘List’ is not one of the expected types: Number”

I’ve tried variations of what you’ve suggested and always got this error or the previous one (“The expression is valid but its result type ‘Ref’ is not one of the expected types: Number”.

Your second suggestion is what I’m trying to achieve but by using actions. Not sure I know how to do what your suggesting using actions?

Rob.

[Suvrutt_Gurjar · Accepted solution]

Sorry, please try

ANY(SELECT(Table Name[Column_To_Get_Value_From], [Key Column of the table]=MAXROW(“APP Fuel Truck Form ”, “_ROWNUMBER”)))

[AviSyst_Admin]

Nailed it,

THANK YOU so much. Now I’ll go about to figure out why that works and move onwards and upwards.

Cheers,

Rob.

[Suvrutt_Gurjar]

You are welcome. Sure,. The way you have posted earlier updates, I believe you are familiar with how all the relevant AppSheet functions work in the shared expression.

Please post back in case of any specific query.

[Steve]

For reference:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get a column value from the last row of this thing only? LOOKUP( MAX( SELECT( My Table[_ROWNUMBER], ([_THISROW].[Thing] = [Thing]) ) ), "My Table", "_ROWNUMBER", "Wanted Column" ) Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want. See also: MAX()

[AviSyst_Admin]

Thanks Steve,

That’s very handy to have as a reference.

Rob.