The question of the day comes from Toby in the UK:
Does anyone know how to get it so I can have a Lookup field that uses a calculated field in another table? My requirements is to create a concatenated name from first name and last name in Contacts (not the Full name that it comes with) and use that as a lookup in another table.
The problem manifests itself like this: lets say you have a table, like Contacts. If you add your own field to the table, say ‘newfield’ and make that a calculated value, in this example I use “=CONCATENATE([first name], “ “, [last name])”. Having done all of that I then go to a new table and want to include “newfield” and have it be a LOOKUP on “newfield” from my Contacts table. Trouble is, whenever I try to do this, although ‘newfield’ definitely exists it refuses to show as something that can be used in the LOOKUP.
Toby - after looking at your calculation value, I think that might be the problem. My value for the calculation is =[FirstName]&" "&[LastName]
and that seems to work fine. Let me know how it works out for you. I have provided a short demo screencast.