Help with lookup and change value please

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”) ??

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.

1 Like

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.

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.

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

1 Like

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

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

Got it :grin: Thanks Steve, have a great weekend.

1 Like

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.

Equipment error

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)

Expression1

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.

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:

1 Like

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

1 Like