The key fields which aren't present in your Excel file will pose a problem here.
You can import from your excel file 3 times--once into each table while selecting just the columns appropriate for that table. _PkA, _pkB and _pkC can auto-enter a value if they are serial number fields and you enable auto-enter options during import, but that still leaves _fkA and _fkB without the needed values to link C to the appropriate records in A and B.
There are several ways you might solve that issue, but which one will work for you will depend on how the three tables in FileMaker should link to each other in their respective relationships.
Thanks for getting back to me. I had figured out about the multiple imports for tables a and b, its the foriegn key population I am trying to work out.
For each _pkA and _pkB there will be at most one _pkC. The truth is I don't really even need a _pkC, because the relationship is unique on the two foreign keys.
Any suggestions would be appreciated.
I'd need to know more about the data in your spreadsheet. When you look at that data, how can you tell which data will relate to which in your FileMaker tables A, B via a record in Table C?
Think about it this way. Every row in the spreadsheet is a row in table c. What I want to do is take the table A specific columns and use them to populate table A and the columns specific to table B and use them to populate Table B.
Spreadsheet with Name, Adress, Conference Name, Conference Date, Registration Date, Amount Paid
Table A Table B Table C
_pkA _pkB _pkC
Name Conference Name Regsitration Date
Address Conference Date Amount Paid
Notice how moving away from A,B,C makes this easier.
You may not be able to import your data and establish the links perfectly. You are at the mercy of how accurrately and consistently the data was entered into the spreadsheet.
Define an additional text field in A with this auto-entered calculation: Name & " " & Address. Set a unique values validation rule on it with the "validate always" option specified. Now, when you import your data into A, this validation will automatically filter out duplicate name/address combinations. Use the same technique for Table B to filter out duplicate combinations of conference names and dates.
Set up these temporary relationships:
TableA::Name = TableC::Name AND
TableA::Address = TableC::Address
TableB::ConferenceName = TableC::ConferenceName AND
TableB::ConferenceDate = TableC::ConferenceDate
Note that this requires temporarily defining all 6 fields in table C.
After importing your data you can use Replace field Contents on a layout to Table C to copy the PK values from tables A and B into the matching FK fields in Table C.
For TableC::_fkA, you'd put the cursor in this field and then use TableA::_pkA as the calculation needed to copy the matching value from Table A into table C.
Once you have successfully done this for both PK values, you can modify your relationships to use the primary and foreign key fields to link your records. You'll definitely need to review your imported data for issues due to similar names and addresses and data not entered exactly the same way each time.
Example: if I registered for two conferences, I might have registered as "Phil" in one of them and as "Phillip" for another. Given the above relationships, that would produce two records in table A instead of 1. You might be tempted to match just by address, but if my son, (Just for this example) Phil Jr. registered, he might be a different person, but with nearly the same name and exactly the same address.
That makes sense. I am mad at myself for not thinking of it. I must be getting old.
Thanks as always.