Hi, I am trying to create a virtual field wit...

references
(Camille P) #1

Hi, I am trying to create a virtual field with a dereference, and I don’t think I have the setup right. I would like to create in Table 1 the field [Name].[Phone]. Is the issue that the Name field in table 1 has to be a Ref field? Thanks

TABLE 1: This is the table I am trying to set-up the dereference in

–NAME: Name Field / Key / Label

–ADDITIONAL FIELDS

TABLE 2:

The fields in this table are:

–NAME: Ref / Label

–PHONE NUMBER

–PHONE TYPE:

(Tony Fader) #2

@Camille_P Take a look at this example, which shows a reference from Appointments to People. appsheet.com - Table References - A basic demo of table references

The Appointments table has a reference to a Person. From there, it can access the person’s nickname. That should be completely analogous to your question. Table References - A basic demo of table references appsheet.com

(Tony Fader) #3

(But let me know if it isn’t, if I misunderstood)

(Praveen Seshadri (AppSheet)) #4

Camille, you’ve got the dereference on the “wrong” side of the Ref column. In Table2, you could have a virtual column with app formula [Name].[Table1Column]

But the reverse isn’t possible because there could be many Table2 rows that all have the same value of [Name].

In general though, if you think there is only a single value in another table that you want to use to lookup a row and fetch a value from it, you can use LOOKUP()

LOOKUP([_THISROW].[Name], Table2, [Name], [Phone])

— this gets the Phone from the row in Table2 where [Name] = [_THISROW].[Name]

— [THISROW] refers to the current row of Table1.

(Camille P) #5

@praveen Thank you for those instructions. I thought I had it backwards. I don’t want to reorganize how my tables are setup though because I will have to reconfigure so much. I think the LOOKUP function will work. Thank you!