Loosing my mind over validation...

[pittelman]

If anyone could help me with this validation problem, I'd be hugely grateful. Basically, my validate calculation isn't kicking in when it should...

Here's the setup:

1. A field "FullName", a calculation that just combines fields First Name and Last Name, set to turn on indexing when necessary

2. A Self join relationship based on the field FullName

3. cFullNameDups: an unstored calculation: Count(Selfjoin Name::Full Name)

4. The script: When cFullNameDups > 1, it should give an error message. This script is part of the process when a user is entering a new person's info. This same exact procedure works fine in another context, for a maintenance script which checks the db for duplicates.

Since the calculation check is being called by a script, so it isn't anything to do with recognizing whether data was entered.

I think it is an indexing problem because if I turn the indexing off of Last Name then the thing works. But if it's a NEW record, why would indexing cause a problem? Also, unindexing Last Name will make finds very slow, so this would be a bad solution.

Also by the way, if I use the same calculation in the Last Name fields auto-validation, I have the same problem (and same correction if I turn off Last Name's indexing)

Help!

Yeah it's 11pm on a Saturday night... Is there a point where you've worked on a database so long that they just have to come and cart you away to the filemaker farm?

[Ugo DI LUCA]

Hi,

Well,

Make sure you exit the record before the script is triggered, so that this newly created record is comitted.

This can be handled by adding a "ExitRecord" script step at the very start of your script.

Let me add a few comments though :

1) Checking duplicates on a compound calculation of FirstName and LastName is far from being bullet proof, because this FullName calculation would not guarantee uniqueness.

It can be acceptable according to the type of solution you're developing, and provided the length of this concanation doesn't exceed FileMaker's indexing capabilities, which is for the version outlined of 20 words in a row.

To limit this risk, your calculation should be FirstName&" "&LastName.

2) If you're not going to use this cFullNameDups later on, it would be a good idea to use its formula in the script without having this field created at all.

If [Count(SelfJoinByFullName:id)>1] (in your case) can be used without any reference to your calculation.

3) When using Validation of this kind, it is generally a good idea to use an "Entry Layout" with global fields, rather than creating a new record.

If really the user entered a duplicate, you won't need to delete the record, as it is the case with the solution you're using.

- gFirstName (global text)

- gLastName (global Text)

- cFullNameUnstored = gFirstName & " " &gLastName

--> checkDupeRelationship -- cFullNameUnstored::cFullName

In this case, your script should check for the presence of a previous similar cFullName, rather than the presence of "more than 1".

If [Count(checkDupeRelationship:ID]--Boolean construction using an ID rather than cFullName

HTH

[pittelman]

Hi Ugo. As always, you're my hero! The exit script step does fix the issue.

Thank you also for all your great advice. I do have FullName set with a space in between the fields, I forgot to say.

This also isn't the only way I check for duplicates, so it's okay that it's not bullet proof. It's more an important reminder to the user: "Are you sure you didn't enter this person already?!"

I don't think I do need the calculated field cDups itself, that's a good point--I will check and see if it makes a difference in the Maintenance Script. The reason I thought I needed it is because it's being used by a script called in a different database...(yup part of the reason is because of having to erase all those new related records when canceling out of the Entry Layouts...)

In regards to that, I completely agree that the globals in an Entry Layout would have been a much better way to enter a new record. Unfortunately this database was made for us by a developer who, well, to tell you my sob story, didn't do a very good job. It was like he was a programmer but he just didn't know the ins and outs of filemaker at all! I have been learning on my feet as I go along the last three months trying to convert what he made into what we actually needed. It turned out to be a mammoth task and I have learned a great deal during the process. But unfortunately learning more and more has also made me see that if I could go back and start all over again so many problems could have been solved by better original design. But now that the whole seriously elaborate solution is done and I am just adding finishing touches, it'd be too hard to go back and change the structure and redo all the months of work. Though I do fantasize about it because it does totally pain me all the workarounds I have had to do because of what I now understand to be bad design. Alas...

Thanks again for all your help, Ugo!

[CobaltSky]

...provided the length of this concanation doesn't exceed FileMaker's indexing capabilities, which is for the version outlined of 20 words in a row

I believe Ugo meant to say 20 *characters* in a row - by which he was referring to the maximun indexable word length in FMPv4-v6. That is one way that including a space may help - the word-length limit will then be applied separately to each of the component names.

Including a space is important for an entirely different reason though, because without it, unintended matches can occur. For instance, a record for Jacki Eaves would erroneously throw a duplicate error if you happened to already have someone else in the database by the name of Jackie Aves.

But that aside, it is worth thinking about the fact that names are not, by definition, unique. I imagine you want to be able to have more than one Mary Smith in your database.

For this reason, it may pay to consider the options and perhaps include a telephone number, (or date of birth if available etc) in the duplicate check (you may *not* typically want to have more than one Mary Smith from the *same residence* - or born on the same date etc - in your database).

[pittelman]

Thanks for your help, Ray. That is an important point. In this case I am allowing them to override the validation caution message since you are right, it could be one of those Mary Smith situations. The goal of the validaiton is to make sure they go back and check first, because more often they just forgot to look and see if that person is in the db--and also very often they may be entering different contact information for the same Mary Smith so checking other fields probably wouldn't make that much of a difference...