Lookup with two labels?

[rorywohl]

Hi There!

I have a table of names and email addresses with the UserName column as the label and the EmailAddress column as the key.

In a different table, I have four columns:

UserName1:
EmailAddress1:

UserName2:
EmailAddress2:

In the form view for that table I want to select UserName1 from the names & email addresses table and have the EmailAddress1 field be populated with the corresponding email address.

I've defined UserName1 as Ref to the names & address table and I've defined the EmailAddress1 as Name with the formula of:

LOOKUP([UserName1],"name&address",UserName","EmailAddress")

And that works great.

Now, for User2, I want to do the opposite.

I want to select EmailAddress2 from the names & email addresses table and have the UserName2 field be populated with the corresponding user name.

Since I've already defined the UserName column as the label in the names and email addresses table, how do I make EmailAddress2 a Ref that grabs the email address instead of the user name?

Thank you!

[TeeSee1 · Accepted solution]

A ref column ALWAYS refers to the key column of the referenced table. 

A labeled column is just a convenient/human friendly way of displaying/representing a row because the key column is often a unique but meaningless value.

So Username1 defined as a ref column actually stores the key - in your case the email address - value but displays the label, Username in your case.

If you want to show a drop-down box for EmailAddress2 displaying raw email addresses, define it as text and set in the valid-if property the below

name&address[EmailAddress]

For Username2, do LOOKUP using [EmailAddress2] 

[rorywohl]

Ah!  Now it makes sense. Thank you!