Importing Data to Many-to-Many Relationships (populating related fields in join tables)

1. every record on the join table will need to be a separate record on the excel upload.
2. You will need to prove the values for the fields [Related Individual] and the field [Related Organization]  on every excel record.

So typically you will need to dump the data from your app into excel to get at the Record ID#  ( and name) of the organizations and the individuals, and then use VLOOKUP (  ..., false) - to be sure of an exact match) in excel to get the record id of the Individuals and the Organizations coaxed into your import file.

Ah, I think I see - thank you. So, to clarify, I should upload the records into their separate tables so they get their record IDs, export again, and then map each record in the join table spreadsheet to its record ID by using VLOOKUP?

I am new to all this. Do you happen to know how I can use excel to automatically format my data into separate records.
i.e. instead of:
Individual 1: Org 1, Org 2

Change to
Individual 1: Org 1
Individual 1: Org 2

OK, so this is an empty app you are trying to populate.  

I suggest that you temporarily make the Key field of the Two Parent Tables to be the text field for the Individual name and the Organization name.  That will make the process easier. once you coax your data in, you can then change the Key field back to the more traditional Record ID field unless you plan to need to do more uploads.

Yes, you will need to make a pivot table of the unique Individuals and the unique Organizations and then import those to populate the two parent tables.

But then wow, you need to rework your data to have one row per join record.

Can you tell me what is the typical maximum number of organizations per record in your current excel file?

My thinking is to import that data just the way it is into a temporary table in quick base.  Then use a formula to parse out into separate fields the Org 1, org 2 say up to org 10. (I can help with that formula) 

I assume in this example a max of 10 different orgs per individual.

Then make 10 reports  and  each will have the individual name and say the data in the field Organization #1.

Then next report will have the individual name and the data in organization #2, but filtered where org 2 <> blank.

then for each of the 10 reports, use the native "More ..."  option for copy these records to another table and copy them into your join table.

Hmm, thank you. I don't think there will be more than 10 organizations per individual. There will, however, sometimes be more than 10 individuals per organization - possibly as many as 30-40 in a few cases.

But do you need to load is both ways? 

In your screen shot is that the same data just presented differently or are those actually two unrelated data sets that you need to import.

Ah, the two datasets are related. Organization 1 in the Individuals set is the same as Organization 1 in the Organizations set.

I'm trying to understand if the 2nd data set is the same information as the first data set but only presented differently, or is in in fact a completely separate set of data, even though it is data for the same Individuals and the same Organizations.

I was hoping that you did not, in fact, have to load the second data set at all if it was just a duplicate of the 1st data set just presented differently.

That middle Join tale just needs the info loaded once, not twice.

I see. They are separate sets of data, in that the Organizations table will contain organizations not mentioned in the Individuals table, and vice versa.

Are there any disadvantages to changing the key field in the parent tables to the Individual and Organization names, rather than  keeping it as the record ID? 

How can you have an Organization not mentioned in the Individuals table?  That would only happen if the Organization had no Individuals, right?  

I was trying to respond to this statement here.

sometimes be more than 10 individuals per organization - possibly as many as 30-40 in a few cases.

and my response is that I think you do not have to load the table based on the second set of data. That data can be ignored as the data elements will all have been loaded when you used my suggested method.

As for the Key field, as long as these Organization names and individual names do not change - like correcting a spelling error, then you can use the text values as the Key fields.  I think you will find it easier to work with the app that way.

How can you have an Organization not mentioned in the Individuals table?  That would only happen if the Organization had no Individuals, right?"

Yes, sort of. Our dataset is not complete. So there are some organizations for which we have simply not recorded the individuals involved. Similarly, in the individuals table, some of the individuals do not yet have organizations (or, in fact, do not represent an organization at all).

Thank you for the advice about key fields.