1 Reply Latest reply on Aug 2, 2009 4:01 PM by jspurr02

    Staging Table Migration Error



      Staging Table Migration Error


      I  am very new to Filemaker.  I am using FMP 10 Adv.


      I have setup a staging table to receive data from a flat file csv import. I am building a script to migrate the various fields from the import staging table into a appropriate application tables, but I am running into a problem the *next* time I run the script if there are duplicate entries in the second file (which is a valid use case) The first time I run the script with all new data, it works fine.  The second time I run it (with duplicate data [the script *should* detect and bypass duplicate data]), I get the following Error:


         "ContactID" is defined to contain unique values only. You must enter a unique value.

                 ---button choices are "Revert Record" or "OK"

      I turned on the debugger and tracked it down to a few lines of code intended to search the target table for matching data, and skip adding the data if found.  So, the code looks like this:


      Go to Layout [ “Import_Master_Table” (Import_Master) ]  //** This is the staging import table **//

      Go to Record/Request/Page [ First ]

      Set Variable [ $CN_FirstName; Value:Import_Master::First Name ]
      Set Variable [ $CN_LastName; Value:Import_Master::Last Name ]
      Set Variable [ $CN_ContactID; Value:Import_Master::ContactID ] 

      Go to Layout [ “IM_Contacts” (IM_Contacts) ]                   //** Browse Window shows all records **//
      Enter Find Mode [ ]                                                       //** Browse Window shows zero records **//

      Set Field [ IM_Contacts::ContactID; $CN_ContactID ]         //** Browse Window shows one empty record **//
      Perform Find [ ]                                                             //** Find locates nothing**//
      Set Variable [ $CN_FoundCount; Value:Get ( FoundCount ) ]  //** FoundCount = 0 ** //
      If [ $CN_FoundCount=0 ]                                                
      --New Record/Request                                                             
      --Set Field [ IM_Contacts::First Name; $CN_FirstName ]
      --Set Field [ IM_Contacts::Last Name; $CN_LastName ]
      --Set Field [ IM_Contacts::ContactID; $CN_ContactID ]       //** Error occurs here **//
      End If


      Go to Layout [ “Import_Master_Table” (Import_Master) ]
      Go to Record/Request/Page [ Next; Exit after last ]
      End Loop<!--  / message  -->

      NOTES: ContactID is unique in Import_Master_Table and IM_Contacts

      I monitor the value of $CN_ContactID ... it contains the right value.


      I think it has something to do with apparently Performing a Find on a single Empty Record.


      What am I doing wrong, and how do I fix it?

        • 1. Re: Staging Table Migration Error

          Problem Solved:  After much aggravation and desperate experimentation, it turns out (a missing bit of info) was the key.  The ContactID was an email address ... and apparently, "Find" does funny things with the @ sign.  By adding an operator "==" to the Set Field, it apparently forced a literal interpretation of the "@", and allows it to work.


          Here was the new entry:  Set Field [ IM_Contacts::ContactID; "==" & $CN_ContactID ]