Import Multiple Levels

[laguna92651]

I have exported a contact list from another contact manager, In the original contact manager,I was able to create sub levels beneath each top item.

The exported file now has all of the levels as an Excel flat file, with the appropriate sub level beneath the correct item.I know FM does not implement sub levels so I was planning to import the top level into FM, and the sub levels into a notes file.How could I set this up so I can relate the correct sub level data to the main item in FM. Either during import or after it is in FM.

Thanks

[comment]

3 hours ago, laguna92651 said:

I was able to create sub levels beneath each top item.

What is a "sub level"? What fields does it have - as opposed to a "top item"?
 

3 hours ago, laguna92651 said:

The exported file now has all of the levels as an Excel flat file, with the appropriate sub level beneath the correct item.

What exactly does that mean? Is every other row a "sub level"?

[laguna92651]

1. The file is a contact list, the top level is a name with phone number, address etc.

2. The sub levels are notes, a given top level may have no sub levels or multiple sub levels.

This is what I currently have in Excel. The # column appeared when I exported the file, it seems to be unique and is only assigned to the top item, first level.

The records without the #, are the sub-levels.

 

Edited January 29, 2016 by laguna92651

[comment]

If you want to do this in Filemaker, import the sheet into a temp table (or convert it into a new file), rename the # field to PK, and add a new field named FK. Then have a script loop over all records and do something like:

Show All Records
Go to Record/Request/Page [ First ]
Loop
  If [ not IsEmpty ( Temp::PK ) ]
    Set Variable [ $pk; Value:Temp::PK ]
  Else
    Set Field [ Temp::FK; $pk ]
  End If
  Go to Record/Request/Page[ Next; Exit after last ]
End Loop
Commit Records/Requests

Now you can find the records that have anything in the PK field, and import them into the Contacts table. Then Show Omitted and import them into the Notes table.

Once you have done this, you can add a ContactID field to both tables, populate it with serial numbers in the Contacts table, and use a relationship based on matching Contacts::PK to Notes::FK to populate the Notes::ContactID with the parent contact's ID. Then you can switch the relationship to match on ContactID.

 

 

[laguna92651]

So far so good.

3 hours ago, comment said:

Once you have done this, you can add a ContactID field to both tables, populate it with serial numbers in the Contacts table,

I'm a little confused here.
I have "imported" the top level Names and the PK number into my FM Customer table.
And I have "imported" the second level Notes and the FK number (which have the related PK number in it) into the FM Notes table.
Is the ContactID a new field, different than the CustomerID, PK and FK fields? If this ContactID is the same as CustomerID, I created new CustomerID's when I ran my import script. Is that the intent of this section or something else?

3 hours ago, comment said:

and use a relationship based on matching Contacts::PK to Notes::FK to populate the Notes::ContactID with the parent contact's ID.

Is this ContactID different than the CustomerID serial number or is it the same thing? Is this just creating the relationship between the two tables?

3 hours ago, comment said:

Then you can switch the relationship to match on ContactID.

Not sure what you mean?

Question, I imported the Temp table data into the appropriate Customer and Notes table with a script, is there a simpler way to do this?

Thanks for your help.

[comment]

The thing is, if you want to continue creating records in the Customers table, you need to have each new record auto-enter a unique value into the CustomerID. And they need to be unique with regard to the imported records, too. So the idea is to serially number the imported Customer records (either during the import or after, it doesn't matter), and then pass that number to the imported child Notes - so that from now on the relationship can be based solely on the new numbers.

[laguna92651]

Do I use a script to pass the number to imported child notes.

[laguna92651]

Thanks got it, made Customer_fk a calculation field in the Notes table.

[comment]

25 minutes ago, laguna92651 said:

Thanks got it, made Customer_fk a calculation field in the Notes table.

No, I don't think you got it. It should not be a calculation field.

[laguna92651]

Oops, what should it be?

[comment]

Before going on, could you clarify:

1. Do you have any existing records in the target Customers and Notes tables?

2. If not, are you creating new tables as you import, or did you already create the target tables and their fields?

[laguna92651]

1. No, I'm deleting all of the customer and notes records as I'm trying different things.

2. I am not creating new tables as I import. I am importing the Excel file into a Temp table, then running a script to assign the appropriate PK number to the associated sub-item.

I run a two scripts to 1)create a new customer record with auto-entered CustomerID_pk for the top level items, and 2)create a new note with a new NoteID for the imported "sub-items". The imported file is now separated into customers and notes.

I only need to assign the newly created CustomerID_pk to a notes field, Customer_fk, so I can link the two tables together.

[comment]

Okay, let me try describe this step by step.

First, create these fields in the Customers and Notes tables (in addition to the data fields);

• Customers::CustomerID_pk - Number, Auto-enter serial number;
• Customers:PK - Number;
• Notes::Customer_fk - Number
• Notes::FK - Number

Now, after having imported the Excel into a Temp table, and running the script to assign the appropriate FK (not PK, as your post says) value to all the sub-items, do this (you can script this if you want, but since you only need to do this once, I'll assume you're doing it manually):

1. In the Temp table, enter Find mode, type * into the PK field, and perform find. Now you should have a found set of "items" (i.e. customers) only.

2. Go to the Customers table. If necessary, show all records and delete them. Select File > Import Records. Import records from the Temp table. Map the Temp::PK field to import into Customers::PK (do not import the FK field). When asked, select to perform auto-enter options during the import.

3. Go to the Temp table, and select Records > Show Omitted Only. Now you should have a found set of "sub-items" (i.e. notes) only.

4. Go to the Notes table. If necessary, show all records and delete them. Select File > Import Records. Import records from the Temp table. Map the Temp::FK field to import into Notes::FK (do not import the PK field).

5. Define this relationship:
    Customers::PK = Notes::FK

6. Return to the Notes table. Click into the Notes::Customer_fk field and select Records > Replace Field Contents… > Replace with calculated result. Enter Customers::CustomerID_pk as the formula.

7. Change the relationship to:
    Customers::CustomerID_pk = Notes::Customer_fk
    
8. You should now be able to delete the Customers:PK and Notes::FK fields.
    

 

[laguna92651]

Thanks much, your instructions were great, appreciate the help.