Finding Records with a matched field

[FM Newbie]

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 ]

Loop

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

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?

Thanks in Advance

[fseipel]

The logic seems correct here. When you say you monitor the value of $CN_Contact, and it contains the right value, what is that value? Is it empty for the imported record causing this error?

The reason I ask is, if you perform a find with all fields empty, you'll get an error code 400 and zero records will be found (foundcount=0). If $CN_Contact contains data, and nothing is found, error code 401 is returned and zero records are found (foundcount=0). If this is the cause, then the IF statement needs to check if $CN_Contact is empty (=""); if the imported data contains one record with $CN_Contact empty, this would explain why you are able to import once, but not a second time since the logic creating a record is based on Get(FoundCount); it creates an empty record in the target table the first import, then tries to create it again on the second import.

You can find empty fields in a DB and not generate an error, but to do so you search for = symbol in the field, rather than leaving it blank.

You can also query Get(Lasterror) to report the last error number after the find, or set error capture off.

Using a relationship as Laretta suggests, will avoid the need to capture errors in this case. If you do have to capture errors, I suggest turning error capture back off right after the script steps requiring it are complete and that the logic specifically check error number following the step potentially generating errors; this way, you can trap unanticipated errors. Otherwise, troubleshooting erratic errors elsewhere is problematic since it stays on and script execution continues through errors. I'm assuming you set error capture on somewhere else before running the first find since the script would return errors otherwise, and not import the first time. e.g.

Set $Last_DB_Error = Get(LastError)

IF $Last_DB_Error = 401 Then

{ Actions for no records found }

Elseif $Last_DB_Error <> 0 Then

Show custom dialog "Some other unanticipated error, # " & $Last_DB_Error Occurred

Endif

Set Error Capture[Off]

[FM Newbie]

Thanks to both of you. You have given this newbie a lot to study. A couple of things:

1) I fixed it, purely by stumbling around. The answer is based on a piece of information I didn't give you. The ContactID field is the email address. Apparently, the @ in the email address causes FIND to do funny things (I did not know this). I was trying various things, and tried adding the "==" operator to the Set Field before the Perform Find. Apparently that causes the @ to be performed literally, and it all started to work:

Set Field [ IM_Contacts::ContactID; "==" & $CN_ContactID ]

2) The reason I enforce uniqueness in both tables is that the first table is only an import staging table that gets cleaned out between imports. It is entirely possible for a duplicate entry to occur between imports that would not get caught until the it gets to the target application table

3) I tried using a relationship with this originally, but had a couple of problems, so I abandoned it. Perhaps I will try again.

4) I need to add error logic, so I welcome your advice and info in that area.

Thanks again.

[fseipel]

Glad to hear you resolved this. I had not considered operators in the find request! The odd behavior of "@" (match one or more characters) is explained thus: This operator matches one or more 'characters'. However, 'characters' doesn't include punctuation; it includes only letters and numbers. Thus a find on w@rd would find "word", but not "w@rd", "w#rd", etc. That's why it doesn't match a string containing @ in the same position as @ in the query string, and of course, foundcount=0 as a result, and it attempts to create the duplicate record.

What problems did you experience with the relationship method?

For error handling, you may want to print a list of the filemaker error codes in inbuilt help. Errors 508, 400, and 401 would appear to be the only applicable ones for a find request; the == prefix ought to eliminate the 400. Also I'd again recommend turning error capture off after those steps you want to trap errors for.

For error handling I would trap the expected error and display an error dialog for any other non-zero errors, or for automated solutions, log them to an event log table with a timestamp and the name of the script in which they occurred and/or e-mail them.

Unfortunately Filemaker doesn't support regex searches.

[comment]

The odd behavior of "@" (match one or more characters) is explained thus: This operator matches one or more 'characters'.

I'm afraid that's grossly incorrect. You must type one @ wildcard for EACH unknown character.

However, 'characters' doesn't include punctuation; it includes only letters and numbers.

I don't believe that's correct either. The @ wildcard stands for ANY character.

The real reason why searching for w@rd won't find "w#rd" is that "w#rd" is actually TWO words, "w" and "rd", separated by #. Therefore, it will not be found by the default find that looks for words that start with the specified characters.

However, searching for ==w@rd WILL find "w#rd", "w@rd" and even "w rd".

[FM Newbie]

Wow, such great information. This will be immensely helpful. It will be nice when FM supports RegEx searches. Does anyone know if they have a plan for that?

[mr_vodka]

Another option may be:

http://jensteich.de/regex-plugin/