How do I take an email field in one table to write a look up and based off that create a formula?

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
How do I take an email field in one table and write a formula to say: If the email field in this table matches the email field in table two, then copy the data from table two, and place it into a specific field in table 1?
Photo of Brieona Cornelius

Brieona Cornelius

  • 90 Points 75 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
The way this is done in Quick Base is via a relationship and lookup fields.  But to look up data from table 2 into table 1, you will need to be able to set the Key field of table 2 to be the email field.

The email  field in table 2 will need to be unique and non blank in order that Quick Base will allow you to change the key field to be the Email field.
Photo of Brieona Cornelius

Brieona Cornelius

  • 90 Points 75 badge 2x thumb
Okay so I currently have table one set up
so it has 
email
field 1
field 2
field 3

then table 2 
has 
email 
field 1
field 2
field 3

email, field 2 and field 3 are look ups and then field one is a text reference field.

How do I create a formula that says  if email in table one matches email in table two then write that "number" into field 1, instead of it being a text reference? 

I wan't it to automatically write this information based off the given data, so I don't have to go through each one and select the matching/related "id". 
Photo of Brieona Cornelius

Brieona Cornelius

  • 90 Points 75 badge 2x thumb
Do I still need to make the email field the key field in order to do this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Step 1 is to set the key field of the table 2 which has the source data for fields 1 2 3, to be the Key field.

Then you create an new relationship.  Quickbase will helpfully suggest to create a new field called [Related email] as the "reference" field on the right side. 

But you are too smart to fall for that.  You know that in your existing data, in fact you already have the email field you want to use to connect up with the Parent table, so you do not accept that recommendation, and instead select the [email] field.  Then you create the relationship, and instantly all your child record will be connected to their Parents as long as there is a parent with a matching email.

When creating the relationship, do not initially select the lookup fields, but rather go back into the relationship after it is setup and add the look ups field. The reason for this is that the very first field field you select as a lookup when creating a relationship becomes the "Proxy field" and in this case you do not want to have any field be designated as a Proxy field.