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:
@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
(But let me know if it isn’t, if I misunderstood)
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.
@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!
User | Count |
---|---|
41 | |
28 | |
28 | |
23 | |
13 |