Get initial value of a ref column based on the cur...

Thilina · 08-05-2021 02:29 AM

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

Please see also:

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

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