Help with lookup and change value please

[Martina_Hawkins]

Hi,

I have a table called Equipment List

Item Id - key
Location
ect…

I have a table Requests
Request No - Key
Item Id
Current Location
Requested by site
etc…

I want to add an inline action to each row of the requests table.
The Action will look up the Requests - Item Id in Equipment List and change Equipment List - Location to Requests - Requested by Site.

Not sure of what to choose in actions or where to put the lookup expression

Lookup([_thisrow].[Item Id],“Requests”,“Item Id”,“Requested by Site”) ??

[Steve]

help.appsheet.com

Actions: The Essentials

Learn how to use actions to modify data and navigation in your app.

[Martina_Hawkins]

Thanks Steve, I’ve added lots of actions before but i’m not sure on which “do this” action to choose as its updating in a different table.

[Steve]

There’s no good, straight-forward way to set values in columns of a row of another table, so any solution is going to have some complexity.

Martina:

The Action will look up the Requests - Item Id in Equipment List and change Equipment List - Location to Requests - Requested by Site.

Based on this, I imagine the following:

1. An action for the Requests table of type Data: execute an action on a set of rows that targets the Equipment List row corresponding to the Item Id in the request, to perform the action described in (2), below.

2. An action for the Equipment List table of type Data: set the values of some columns of this row to set the Location column to the Requested by site column of the row fro the Requests table. Your LOOKUP() expression would be the value expression for the Location column in this action.

[Martina_Hawkins]

thanks Steve, yes working on that idea, haven’t got it working yet but will keep trying

[Martina_Hawkins]

Steve, I can’t figure out what to put in Referenced Row in action 1

[Steve]

It appears you already know the equipment ID from the request: [Item Id]. The action requires a list of rows, so you just need to present that equipment ID (which is a key column value for a row in the Equipment List table) as a list:

LIST([Item Id])

[Martina_Hawkins]

Got it Thanks Steve, have a great weekend.

[Martina_Hawkins]

Hi @Steve, I was wondering if you help me again with the above. It was working but now its not working on all occasions. I got this error this morning after selecting the action to change location but not sure this is related. It only happened in the Editor and not the browser. I made a change in the editor to get the Save and verify to appear and then it synced again ok.

I can’t actually find where this expression is.

The problem with the above set of actions I think happens if the location has to be changed more than once as items will move between different locations

Example this item moved twice

So it moved from GMV6 - London Warehouse - Rome House (in Equipment Requests table)
but action didn’t change the location in Equipment table

and I think this is because this expression is picking up the first row in Equipment requests (1f726dc1) and not the 2nd move (78cdb4c0)

Is there a way for it to pick up the most recent row? I thought it would pick up the row the action is on.

[Steve]

Martina:

Is there a way for it to pick up the most recent row?

The most recent row is likely to be the row closest to the bottom of the spreadsheet, which will be the one with the greatest _ROWNUMBER column value, so maybe this:

LOOKUP(
  MAX(Equipment Requests[_ROWNUMBER]),
  "Equipment Requests",
  "_ROWNUMBER",
  "Requested By Site"
)

See also:

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()

[Martina_Hawkins]

Thanks @Steve. Definitely won’t be the last row as several requests could be sitting there all waiting for action to be taken on them. I will try to work out something with MAX

[Martina_Hawkins]

This is an old post but revisiting it again as I never got it figured out. Any help very much appreciated.

I have a table called Equipment List

Item Id - key
Location
ect…

I have a table Requests
Request No - Key
Item Id
Current Location
Requested by site
etc…

I want to add an inline action to each row of the requests table.
The Action will look up the Requests - Item Id of that row in Equipment List and change Equipment List.[Location] to Requests.[Requested by Site].

Example of data, looking at Item Id ACC-0001

Equipment List Data:

Requests Data

So the item started in Location SS and was moved to Aberfeldy.
This was manually updated in the Equipment List table so current location is Aberfeldy.

A request was submitted to move this item from Aberfeldy to Location DD.

I want an inline action on each row of the Requests table to automatically update the Equipment List Current Location with the value of Requested by Site from the Requests table.

what I have tried so far.

Action Update Location:

Action Update Delivery: