Now that you’ve prepared one spreadsheet to import into your main table and a second spreadsheet to import into your secondary table, it’s time to think about how you’re going to connect this data once it's imported. Before you import the data, you need to make sure this will be possible and then prepare your data appropriately.
In this section, you'll identify columns in your main table and secondary table spreadsheets that can be matched in FileMaker Pro to create a relationship, and then you'll doublecheck that the data in these columns will work consistently.
If you’re wondering why you can’t use the relationship you already created in your solution, here’s the reason. In your solution, your main table and secondary table are linked by a special auto-enter ID field that you created as part of Goal 1. However, your spreadsheets don’t contain that ID field.
This means that when you import your main table data and your secondary table data, FileMaker Pro has no way of connecting the data from these tables automatically. After the import is done, you will need to perform a manual process (described in Part 4) that copies the appropriate primary key (i.e. Main Table ID) into your foreign key field for each record in your secondary table. This will associate the records in your secondary table with the appropriate records in your primary table.
This manual process wasn’t necessary when you imported Work Orders in the Landscaping file, because the Customer No fields in both tables were pre-populated. This meant that your two tables were linked by the data exactly as imported. You might be lucky enough you have your own ID fields, similar to the Customer No fields in the Landscaping file. That's great — see below for what to do with them.
This section will help you choose the fields you will use for your temporary relationship so that you can perform the manual process in Part 4.
Look for possible connections
The important thing is that you find a column (or columns) in your main and secondary spreadsheets that you will be able to use to match the data. Whatever field or fields you choose must have these specific attributes:
- The column must be unique for each row in your main table, and cannot be empty.
- The column in both tables cannot be empty.
- The values in the two columns must match exactly: no trailing spaces, no spelling variations, etc. (Capitalization is not an issue, however.)
The best fields to use for this purpose are identifiers like a Social Security Number. If you have matching primary key and foreign key ID fields from other systems, those are ideal as long as they meet the criteria above.
The next best fields are ones you expect to be unique and spelled reasonably consistently, such as email address or cell phone number.
The worst (but better than nothing) fields are those most likely to be duplicated or have inconsistent spellings, such as Full Name, Company Name, or a combination of Street + City + Zip. You would only use these fields as a last resort, because they could result in incorrect matches. It’s up to you to determine the best option available in your data, and whether it matches well enough to be worthwhile.
IMPORTANT: The possibilities we recommend here for matching fields only apply to temporary relationships to be used during the import process. For best results, the ongoing relationships in your solution should use unique IDs such as auto-enter serial numbers, never fields such as email addresses, phone numbers, or the person’s full name. Otherwise, you risk creating a solution with data integrity problems.
Choose columns in your primary and secondary spreadsheets that can be used for the temporary relationship needed in Part 4.
Main table spreadsheet
- Find a column in your main table spreadsheet that you believe is unique for each row and that has a value for each row.
- Double-check that a column with this same data also appears in your secondary table spreadsheet.
- Sort the main table spreadsheet by that column.
- Scan every row of the column to see if any values repeat or are missing.
- If so, decide whether you can fix the data manually or whether you need to choose another column.
Secondary table spreadsheet
- Find the matching column in your secondary table spreadsheet.
- Sort the spreadsheet by that column.
- Scan every row of the column to see if any values are missing. Note that in this case, they can repeat.
- Spot-check groups of rows with the same value by going back to the main table spreadsheet and searching for this value.
Does a row exist for this value? Does the group of rows in the secondary table spreadsheet belong with the record you found in the main table spreadsheet?
- If you find any problems, decide whether you can fix the data manually, can tolerate the problems, or need to choose another column.