Hi Leon!
Great question. When setting a default in a reference column, you’ll need to use a lookupOne trigger formula. In the screenshot below, I have the same two tables you have. In the Assignee column, we use a trigger formula that applies to new records. The formula used here is:
People.lookupOne(Name="Mary")
This looks up the first value in the People table where Name is “Mary”.
When setting a default value in a text column, we can simply set the formula to "Mary" because this is just a string of text. Because reference columns point to an entire record, we need to use lookupOne and specify some detail about the record we want. LookupOne finds the record where the value in the Name column is “Mary” and then points to that record. Name is just the label we chose to show to represent that record. We could change the Show Column to Row ID and we can see that it still points to the record where Name = “Mary” but now we see the Row ID instead.
Let me know if you have any questions!
Thanks,
Natalie