AnsweredAssumed Answered

Staging Table Migration Error

Question asked by jspurr02 on Aug 2, 2009
Latest reply on Aug 2, 2009 by jspurr02


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?