4 Replies Latest reply on Sep 19, 2014 10:25 AM by glorifindal

    Limitation on Field Validation Unique Value


      Hi there,


      I'm hpoing someone can provide some clarity on the subject of Field Validation with Unique Value selected...


      I have a string which is comprised of 2 object names and a UUID ...

      It turns out that only when I cut down the number of characters to 120 does it work ...


      Does this make sense ? Is the Unique value being checked against the index?


      At the moment I am using the following calc to ensure Uniquness (I know that it is nowhere near perfect)


      $$_set.id & "|" & Left( Object1_Name ; 35 ) & "|" & Left( Object2_Name ; 35 )


      The Object_Names can easily be over 60 characters - therefore the problem.


      I use them in an import function - where the import string ( as given above above) has to be unique within the import set ( defined by the $$_set.id )


      Does anyone have a suggestion?


      I could do the Unique value check per script AFTER the import, but with an import set of 8000 + records, this takes too long.


      Any ideas greatly appreciated...

        • 1. Re: Limitation on Field Validation Unique Value

          Yes, unique value validation uses indexing. Try turning indexing and auto-create indexes off, then turning unique value validation on for a field, and see what happens.


          The indexing on text fields is limited to the first several characters — apparently 120 according to your results. If you have FileMaker 13, there's a way around this. FileMaker 13 introduced the GetContainerAttribute function, and one of the attributes you can get is anMD5 hash. The function was apparently created with container data in mind, but the MD5 attribute can be used with arbitrary text instead. You can use this to create a reasonably unique 32-character string short enough to be indexed based on your data which are sometimes too long to index. Create a separate stored calculation field based on GetContainerAttribute ( Table::sourceTextField ; "MD5" ), then validate the uniqueness of this calculation field.

          • 2. Re: Limitation on Field Validation Unique Value

            I believe the value index is used for unique validation. According to the FTS Advanced, page 87, the first 100 characters of each value (defined as a return-separated item) in the field comprise the index entry for the field.


            Jeremy's suggestion for MD5 hashing is an excellent solution for the problem if there's only one value in the field (i.e., only zero or one carriage return) and the "uniqueness" of the field is in question because its length goes beyond that. You can also use a calculation that breaks the field into 100-character chunks and inserts carriage returns to validate against, should you choose.





            • 3. Re: Limitation on Field Validation Unique Value

              Many thanks for your input - the solution is unfortunately in FM11 - the rebuild in FM13 is planned after completion in FM11 - so I'll need to do a CF for the hashing ...

              • 4. Re: Limitation on Field Validation Unique Value

                Again, many thanks indeed for the clarity you chaps have provided ...


                The idea of splitting it into two lines - that's very interesting. But after some thinking, I think the hashing is the correct way to go...


                Still, both you input is greatly appreciated


                Kindest regards