1 Reply Latest reply on Jun 8, 2010 9:37 AM by philmodjunk

    Importing matching records to add to existing value field selection without overwriting

    JesB

      Title

      Importing matching records to add to existing value field selection without overwriting

      Post

      Hello - I am using Filemaker Pro 11 on a Windows Vista machine.

       

      I have created a database to track participants in our various training workshops and conferences mostly for marketing purposes, but I'm hoping to make it our main registration database in the near future.  We primarily add to this database by importing class lists from excel spreadsheets which have been exported from our registration database.  I have created a value list with our course names, and I use check boxes on each record's layout to display which of our conferences each participant has attended. This is vital for finds and sorts so we can contact only individuals from a certain class, or omit anyone who has asked not to be contacted about new courses, but still frequently registers for our trainings.

       

      The problem arises when we have a participant that registers for more than one of our conferences.  We import using "update matching records in found set" which works for everything except our value lists selections.  At the moment I cannot figure out how to import records into this database without the new value of the course name field overwriting the old value list entry instead of simply adding to the selection on the value list.  Is there any way to do this?

       

      For example John Smith might have a check box beside "class A" in an existing record, but then we import a class list for "class B" which he also attended.  In this instance the import unchecks "class A" and replaces it with a check beside "class B" even though he attended both classes.  I would like to somehow import these files so that "class A" remains checked and "class B" becomes checked as well.  Is there a way to do this?

      I'm a bit rusty with FileMaker database creation - just recently talked my current employer into switching from Access for our new databases (thus the need for importing at all!), so I haven't really worked with any of this stuff in about five years.  It's changed a lot since then, and I've forgotten a lot of the old tricks anyway, so any help would be greatly appreciated. 

       

      Thanks!

        • 1. Re: Importing matching records to add to existing value field selection without overwriting
          philmodjunk

          There are a couple of ways to do this. You might try importing this field into a different field and then running a script that updates your field after you import.

           

          Your script could

          find all records where this new field is not empty

          Loop through the records doing the following on each:

            Use IsEmpty ( filter values ( current field, import field ) ) to determine that the imported value needs to be added

            If so, use Set Field [table::valuelistfield ; table::Valuelistfield & ¶ & table::import field ) to append this value to the current list of values in the field