Get initial value of a ref column based on the current user

[Thilina]

Hi, Sorry if the title is a bit confusing, but basically I have a Vehicles Table with [v_id] as the key, every vehicle is assigned a user (only 1 per vehicle), which is stored in Vehicles[user] (Email column type),
I have couple of other tables related to Vehicles called Maintenance, Service, Registrations all these tables have [v_id] as ref type column

Instead of a drop down to manually associate which vehicle this particular Maintenance/Service/Registrations Record is connected to can I use USEREMAIL() in some way to:

Get USEREMAIL()
Check Which Vehicles[v_id] (key) has this USEREMAIL() in the Vehicles[user] column
and Return that [v_id] as the initial value?

Basically When a user wants to for example add a new maintenance record it will automatically figure out what vehicle this user is connected to and use that [v_id] by default?

Vehicles[user] image:

Maintenance[v_id] image:

Any help would be greatly appreciated

[Aurelien]

Hi

You may want to know which user was the last one to use a vehicle, using the USEREMAIl() expression.
Similarly, you may want to retrieve some information depending on this vehicle using a LOOKUP expression.

If so, please refer to this excellent post:
you may be interested in the section “How do I get the last row of this thing only?”

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

Core concepts & functions Expressions: The Essentials FILTER() List Expressions and Aggregates LOOKUP() MAXROW() MINROW() REF_ROWS() SELECT() What is a Key? Adding & updating rows Is this a new row?/Does this row already exist? What is the previous value of this column? Excel alternatives How do I do COUNTIF() or COUNTIFS()? How do I do SUMIF() or SUMIFS()? How do I do VLOOKUP()? Last row of the spreadsheet How do I get the last row of the spreadsheet? How do I get a column value from…

Please see also:

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Thilina]

Hi, I’m actually familiar with that post I’m using it for another function in the app, but for this specifically I just want to

Get USEREMAIL()
Check Which Vehicles[v_id] (key) has this USEREMAIL() in the Vehicles[User Email]
and Return that record?

Ps. One user email can only have only one vehicle and one vehicle can only have one assigned email so it is unique

[Aurelien]

Get the v_ID attached to the last entry of the user:

LOOKUP(
  MAX(
    SELECT(
      tableEntries[_ROWNUMBER],
      (USEREMAIL()=[User Email] )
    )
  ),
  "tableEntries",
  "_ROWNUMBER",
  "V_ID"
)

[Thilina]

Thanks but I ended up going with this as the initial value

LOOKUP(USEREMAIL(),“TABLE”,“EMAIL COLUMN”,“KEY”)

ie: LOOKUP(USEREMAIL(),“Vehicles”,“user”,“v_id”)

if something jumps out at you where this approach might give me trouble in the future please tell me, I’m fairly inexperienced so sometimes I stumble onto answers that I don’t fully understand XD

[Aurelien]

Hi @Thilina

Your expression is correct.
Nonetheless, something is pretty unclear to me: how do you update the “v_id” column in this table ?
My first understanding was that you didn’t have it, and then that you were looking for a straight way to get it ?

EDIT : minor change

[Thilina]

in this case all I wanted to do was find what [v_id] is associated with the email thats the same as USEREMAIL() I didn’t need to update it, sorry if that wasn’t clear.

Now when I add a new row to my maintenance table it automatically finds the [v_id] (foreign key) of the vehicle the USEREMAIL() is assigned to

Previously when I add a new maintenance record :

Now:

Main Vehicle Table:

Maintenance Table:

[Aurelien]

Good job then