Escaping Single Quote at end of data entry

[jackinva]

Hi,

I'm working on migrating data from one DB to another DB. In playing around with FileMaker I found an interesting gotcha:

If I do a find with ==spectators' it returns false and then I want to create the record with spectators' as the value.

However when I enter spectators' as the value for the (indexed, strict, unique) primary key field, I get an error indicating that the value isn't unique. The value spectators is in another record. I'm speculating the FileMaker thinks the ' at the end of spectators' is extraneous whitespace when comparing it to the existing values in the table.

So it appears searching for the ' at the end works but allowing it as data entry does not. I'm wondering if there is a way to escape single quotes during data entry? I tried ' and ''; neither of which worked.

If I'm way off and someone could enlighten me as to why

when searching ==spectators' does not equal ==spectators

but when entering data spectators' does equal spectators

I'd appreciate it.

Thanks,

Jack

[HOnza]

Jack,

Uniqueness validation in FileMaker uses the same rules as relationship key matching. Its behavior depends on the language you choose for indexing and sorting (field options - Storage tab).

For example, if you choose English, the single quote character will be understood as word separator, and thus will not be taken into account when validating uniqueness. However, if you change the fields language to ASCII, single quote will be understood by FileMaker as a significant character.

In general, ASCII will take all characters as unique, and only few of them will be understood as word separators, while most of the real languages will make punctuation equivalent to spaces, and also several accented characters will be equivalent to their non-accented counterparts.

HOnza

[jackinva]

HOnza,

Thanks for this information. I've passed this along to management. Unfortunately, the database I'm working with is a purchased application so I can't just modify the underlying structure. We've decided to drop the insertion of terms with ending apostrophes to resolve this. I do appreciate the explanation.

Thanks again,

Jack