Data Import/ Foreign Key Calculated
So I'm nearing the completation of our database and lo and behold, the data coming out of our accounting system is messing me up. To make things short, I'm bringing in 2 tables worth of data (field names in bullets):
- PK Customer ID (serialized)
- Customer ID (Non serialized uppercase name)
- Customer's Full proper name
- Descriptions, addresses, etc.
Table 2: Jobs
- PK Job ID (serialized)
- FK (Blank - no data)
- Customer ID (Non serialized uppercase name) like Table 1
- Job name, desc, etc.
As of now, the customer table is small (600 records) so it was easy to just apply a 1-600 serialization in excel before I imported those. No problem.
However the jobs are a challenge. There are over 6,000 records. Unfortunately, the accounting system doesn't let you see a numerical Customer ID to use as a FK, instead it has an all uppcase text ID instead.
My question is this: Is there a way to (in Filemaker, Numbers or Excel) have the app look to see if the Customer ID (Non serialized uppercase name) from Jobs Table matches on Customers table then if they do, enter in the appropirate Customer ID (serialized) into my Jobs FK field?
Just trying to save time instead of going through 6,000+ records to serialize each job manually.