laguna92651 Posted January 29, 2016 Share Posted January 29, 2016 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 Link to comment Share on other sites More sharing options...
comment Posted January 29, 2016 Share Posted January 29, 2016 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"? Link to comment Share on other sites More sharing options...
laguna92651 Posted January 29, 2016 Author Share Posted January 29, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted January 29, 2016 Share Posted January 29, 2016 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. Link to comment Share on other sites More sharing options...
laguna92651 Posted January 29, 2016 Author Share Posted January 29, 2016 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. Link to comment Share on other sites More sharing options...
comment Posted January 29, 2016 Share Posted January 29, 2016 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. Link to comment Share on other sites More sharing options...
laguna92651 Posted January 29, 2016 Author Share Posted January 29, 2016 Do I use a script to pass the number to imported child notes. Link to comment Share on other sites More sharing options...
laguna92651 Posted January 30, 2016 Author Share Posted January 30, 2016 Thanks got it, made Customer_fk a calculation field in the Notes table. Link to comment Share on other sites More sharing options...
comment Posted January 30, 2016 Share Posted January 30, 2016 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. Link to comment Share on other sites More sharing options...
laguna92651 Posted January 30, 2016 Author Share Posted January 30, 2016 Oops, what should it be? Link to comment Share on other sites More sharing options...
comment Posted January 30, 2016 Share Posted January 30, 2016 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? Link to comment Share on other sites More sharing options...
laguna92651 Posted January 30, 2016 Author Share Posted January 30, 2016 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. Link to comment Share on other sites More sharing options...
comment Posted January 30, 2016 Share Posted January 30, 2016 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. Link to comment Share on other sites More sharing options...
laguna92651 Posted January 31, 2016 Author Share Posted January 31, 2016 Thanks much, your instructions were great, appreciate the help. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3136 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now