I have a database with 15,000 names. When I want to add data to one client I run a find request entering a few letters in the last name field and a few letters in the first name field. With so many names, I have several clients that have the same first and last name. What I would like, is a script calculation that would run when I do the find to make visible a container field with a graphic that says something to the effect "Caution - Client with same name exists".

You dont really need a script for this per se.

You can just have a stored calculation field or text calculated value) used with a self join and then a calculation field with container result.

gCautionImage - global container that stores your image.

cNameFull (NameLast & "_" NameFirst)

Self Join Relationship Based off cNameFull

Then your container calc could just be:

cDisplayCaution ( Case ( count ( SJ::cNameFull) > 1; gCautionImage)

Hmmm, I see what you are saying, I just can't seem to get it to work. I must need some clarification.

gCautionImage - global container that stores your image. <===Done, No problem here

cNameFull (NameLast & "_" NameFirst)<=== OK, I have this field

Self Join Relationship Based off cNameFull <===New table occurrence, done

Then I made a calc field (result is container)called cDisplayCaution in my main table and inserted the calculation

Case ( count ( SJ::cNameFull) > 1; gCautionImage)

This doesn't work.

EDITED POST:

OK, I just changed the calc field cDisplayCaution to this:

If ( Get ( FoundCount )> 1; gCautionContainer)

and that works without having to use a self-join. Your solution may be more elegant and offer me more but I could not get it to work.

Edited February 8, 200719 yr by Guest

This example file may help you figure what's going wrong. There are several examples for duplicate checking and one of them uses the same basic technique outlined by mr_vodka.

http://www.filemakerpros.com/Duplicatesv7.zip

OK, I just changed the calc field cDisplayCaution to this:

If ( Get ( FoundCount )> 1; gCautionContainer)

and that works without having to use a self-join. Your solution may be more elegant and offer me more but I could not get it to work.

That wont work. All that does is checks to see if the found count is greater than 1. So anytime that the found set it more than 1, it will display. Take a look at the sample.

Frink.zip

OK, thanks for the example. That should make it much easier for me to see what I was doing wrong. I'll report back here after I get a chance to put it to work into my database. Glad to see you caution flagged the right record in your example .

:yay: Yep, I see what I was doing wrong. This is just what I need. Have a Grey Goose on me!