Lookup and Ref Questions

[CPansewicz]

Hi,

I am having two different (but similar) issues with Lookups and Derefs.

Issue 1: Yellow warning triangle. I have a parent table of Requests, and a Child table of Users. I am trying to pull into the parent table the user name of the requester based on their user id.  In the Requests table:

• [request_id] = key column
• [user_id] = REF - Referencing the Users table. Initial value =  USEREMAIL()
• [user_name] = REF - LOOKUP([_THISROW].[user_id], "User", "user_id", "user_name")

In the Users table, the [user_id] is the key column. I'm sure I have something set-up weird with the references. What I am trying to do is to pull into the table the name of the user based on the [user_id]


Issue 2: Virtual column data shows in the Preview view, but not live view. This is the same sort concept as above. I would like to create a virtual column that pulls into the view user id.

• Purchasing Table - [po_id] is the key column, and there is a [PO Number] field
• Requests Table - [request_id] is the key column, and there is a [PO Number field]

The expression I wrote is for the virtual column in my Purchasing table is
LOOKUP([_THISROW].[PO Number], "Requests", "PO Number", "user_id")

Thank you so much!

Camille

[lynchk21]

If I understand correctly:

Issue 1: I don't know what the warning says (sometimes they don't mean anything is wrong, just something to be aware of), but I would suggest if the [user_id] is a reference column, you should be able to use [user_id].[user_name] to get the data from the Users Table. This seems better than using a lookup function

Issue 2: Again, if these tables already have a reference between them, it seems like using something like [request_id].[PO Number] - Not sure if this will solve the problem you are having with the preview data not showing up 

If I misunderstood, sorry! Happy to help with more information. Good luck!

[Landan_QREW]

Regarding Issue 1, Is your [user_id] column in the Users table an email for each row? 

The yellow triangles appear when there is a value in the foreign key column of a child table that does not have an associated primary key value in the table that column references. (ie. if emails are being recorded in the request table for the user, then you need to have emails as the primary key in the users table). Once this is set up correctly, you can just use the following expression to derive the user's name: [user_id].[user_name]

  
Regarding issue 2:
It looks like your [PO Number] column in your Requests table is likely not a true reference to the Purchasing table unless the [po_id] and the [PO Number] in the Purchasing table hold the same values. I would recommend having a [po_id] column in your Requests table that directly references your Purchasing table. Then you can derive the [PO Number] column with the following expression: [po_id].[PO Number]. To take it a step further, you can link multiple dereferences together in the request table like this: [po_id].[user_id].[user_name] to get the user's name that created the associated PO.