Help with relations #2

Ask for Help Formulas
#1

Hi Folks,

A need a help with something.

I have 2 tables with the following attributes:

People

  • Name
  • University of Master (referenced to Universities.Name)
  • University of PhD (referenced to Universities.Name)

Universities

  • Name
  • Sector

I would like to show the “Sector” of the “University of Master” at the table “People”.

I’ve tried the “lookupOne” unsuccessfully.

Any thoughts?

Thanks in advance!

Eduardo

#2

This can be done with a reference lookup. I made an example here: Reference Lookup

Because the PEOPLE table has a reference to the UNIVERSITIES table, you can use that reference to look up additional fields in the referenced record.

The formula is $[Reference Column in this Table].[Field to Look Up in Referenced Table]

In this case, it would be $University_of_Master.Sector

image

By the way, if you had tried in the PEOPLE table the formula Universities.lookupOne(Name=$University_of_Master), the reason it doesn’t work is because $University_of_Master is a reference column and its value is the record that it is pointing to, not the displayed text. The formula is trying to match the text in Name to the entire record’s string in '$University_of_Master` and can’t find a match. Hope that helps clear it up!

#3

It works perfectly, thanks!

1 Like