6 Replies Latest reply on Jan 24, 2012 11:21 AM by philmodjunk

    importing into the "notes" table in the Contact Management Template

    WorkStudy

      Title

      importing into the "notes" table in the Contact Management Template

      Post

       Hello, I am trying to import a .cvs file into the "contact management" template database.  I can get all the data needed into the "contact management" table but I cannot figure out how to import data into the "notes" table.  When I am in the "Import Field Mapping" pop up window the "notes" table is grey in the "tagged" drop down menu. I have tried opening the notes section in the "record detail" view as well as showing the notes field in the table view. Can I not import text into the notes table? I have already made a unique serial number for each record and have solved the problem for not being able to add a note after my first import.  Adding a note works, but I cannot import into the "notes::text" field. Any help is appreciated! Thanks

        • 1. Re: importing into the "notes" table in the Contact Management Template
          philmodjunk

          You have a few issues to work through.

          First you have one table for contacts and another for notes. They are linked by an ID field that will not receive a value when you import your contacts from the CSV file unless you also enable auto-enter options during import. See this thread for more on the subject and how to fix the issue after the fact:  Help importing Excel data into a Filemaker Starter solution (It's a different starter solution, but the issue and how to fix it are the same.)

          Second, to import records into the note table, you must select a layout based on Notes not contacts. Then you can successfully import data into this table.  The notes utility layout is the only layout in the starter solution that is based on this table. It's not accessible from the drop down list of layouts when in browse mode, you'll need to open it either from layout mode or from Manage | Layouts.

          Finally, just importing data into notes won't link the notes records to your imported contacts records. This requires giving the notes records the correct ContactID to match to a contact management record--but the contact ID wasn't added to those records until during or after they were imported.

          A fix for that last issue will depend on your data to be imported. When you open the file with the notes data, how do you tell which contact is to be matched to a given note?

          • 3. Re: importing into the "notes" table in the Contact Management Template
            WorkStudy
             Ah Ha! Yes, I see what you mean.  I am now able to see the "notes" table in the "import field mapping" pop up window but there is no way for me to match the note with the record. The .cvs document I am importing from only has basic contact info, name, phone, email, etc all in one table. Each record is unique based on the email address. Is there a way for me to distinguish somehow so that the notes import into the correct record? Thanks for the quick response.
            • 4. Re: importing into the "notes" table in the Contact Management Template
              philmodjunk

              That's issue number three. I'm not clear on the structure of your data. Are you importing data from one file that contains both contact and notes data or do you have two files, one for each table?

              If it's all in one file, you can temporarily add a notes field to contact management and import the note text into there, then use Replace Field Contents or a script to copy the data into the related notes table with the appropriate ID value to link it to the correct contact.

              If there are two separate files and both have the email address field, you can temporarily add an email address field to the Notes table and define a relationship between contact management and a new table occurrence of Notes that links records by email address fields. Once you've imported the data you can use Replace field contents to copy the ContactID number into the new notes records via the email based relationship. You can then remove the extra relationship and email address fieild once you have imported the notes and linked them by contact ID.

              • 5. Re: importing into the "notes" table in the Contact Management Template
                WorkStudy
                 I tried adding an "email" field to the notes table.  I did this by creating a new field in that table and then in the relationships window added an "email = email" relationship.  In other words, I made the email field in the "contact management" table equal to the email field that I created in the notes table.  The problem is that the email field in the notes table has not automatically filled in any emails. My excel file that I am importing from has all the info in one table in rows and the notes are included in this table. WHen you say, "a new table occurrence of Notes" does that mean that I have to make a new "notes" table in the relationships window and then make the "email" in my contact management equal the new "email" in my newly created notes window? I think I understand what I need to do I just don't know specifically how to do it.  Also, if I am going to be importing data like this more than once should I delete this email relationship for future imports? Thanks for your help.
                • 6. Re: importing into the "notes" table in the Contact Management Template
                  philmodjunk

                  You'd need to delete your current notes and import again, this time mapping the email field in notes to the appropriate column in your spread sheet.

                  A new "table occurrence" is a new "box" in manage | database | relationships. In Manage | Database | relationships, make a new table occurrence of Notes by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                  This keeps the original relationship to notes intact so that you can still use it once you have correctly linked notes to contacts by contactID.

                  To update your imported notes records so that they have the correct contact ID, add a new layout and specify your new occurrence of notes in "Show Records From". You can make this a blank layout and just add the ContactID and email fields from this new Notes table occurrence to the layout. Do a Show All Records (Records menu), click into the ContactID field and select Replace Field Contents from the Records menu. Select the calculation option and use this calculation:

                  Contact Management::K_ID_Contact

                  to copy the ID value from Contact management into the kf_ID_CONTACT field in notes.

                  Now you can go to the original layouts where you have access to notes and you should see the imported notes appearing on these layouts.