You no longer need such a calculation field in order to match records in Filemaker. You can set up a relationship by dragging from the first name field in db1 to the first name field in db2 in Manage | Database | Relationships. Then drag from the Last Name field in one table to the last name field in the other so that you are using both match fields.
But matching records by name is very problematic. Names are not unique. People change their names. Names take more than one form (some people call me Phil, others call me Phillip) and are vulnerable to data entry errors.
Thanks for the quick reply. I did create the relationships. I completely agree about the challenges with name but i have very few fields to work from. I have the zip code in both databases so I can use that to double check varriations and I deduped before I imported the data.
My only experience with Lookup is with spreadsheets. So after the relationships are established do I just need to add a "Lookup" function for the fields I'd like to pull from db1 into db2?
First you need to decide if you even need to do the look up. With the tables related and with matching values in the match fields, you can add fields from one table to the layout based on the other and the data is automatically displayed. You only need to copy over the data with a looked up value field option if you need to capture data from the first table so that it is preserved if the original data should change.
And example of this is when an invoice is set to look up (copy) contact data so that the data that was current at the time the invoice was created is preserved. (Sorry you didn't get your order Mr. Smith, let me call up that invoice and see what shipping address was used to send it to you...)
Ok, so since db1 has the full address information I'm trying to get into db2 (does not have full address, just the zip), I'm guessing there is a way to layout the info from both tables once the relationship is esablished????
Again, sorry for being dense.
for context db1 has over 13 million records and I'm trying to match up with 13k in db2
Assuming this relationship:
db1::FirstName = db2::FirstName and
db1::LastName = db2::LastName
You can create a layout where layout setup | Show Records From lists db2.
WHen you use the field tool to add fields to the layout or click Modify to add fields to a table view, you can then select the fields you want from both db1 and db2.