1 2 Previous Next 15 Replies Latest reply on Feb 10, 2009 10:38 AM by raybaudi

    equivalent to an "IsUnique" function

    soundtrackit

      Title

      equivalent to an "IsUnique" function

      Post

      I am trying to design a field that displays an error message as a text field on a layout rather than an actual Filemaker error message. I don't want to use Validation, but I do want to check to see if one field is "unique", and if it's not, to display a certain text. Something like the "IsValid" function to check for uniqueness instead of validity would be great.

       

      Any ideas??? 

        • 1. Re: equivalent to an "IsUnique" function
          raybaudi
             Hi soundtrack it

          1) make a value list ( name it VL ) with the values coming from that field ( name it FIELD )
          2) make a calculation field with this calc:

          Case(
          ValueCount ( FilterValues ( FIELD ; ValueListItems ( Get ( FileName ) ; "VL" ) ) ) ; "not unique"
          )




          • 2. Re: equivalent to an "IsUnique" function
            comment_1
              

            I beleive that:

             

            ValueCount ( FilterValues ( FIELD ; ValueListItems ( Get ( FileName ) ; "VL" ) ) ) 

             

            will always return 1.

            • 3. Re: equivalent to an "IsUnique" function
              raybaudi
                

              Hi comment

               

              most of the times you are right...

               

              This time no ;)

              • 4. Re: equivalent to an "IsUnique" function
                comment_1
                  

                You often think so - at the beginning...

                • 5. Re: equivalent to an "IsUnique" function
                  raybaudi
                    

                  comment wrote:

                  I beleive that:

                   

                  ValueCount ( FilterValues ( FIELD ; ValueListItems ( Get ( FileName ) ; "VL" ) ) ) 

                   

                  will always return 1.


                  ValueCount ( FilterValues ( FIELD ; ValueListItems ( Get ( FileName ) ; "VL" ) ) )

                   

                  will always return 0 if FIELD contains an unique value and the user do not re-enter the same value in that field.


                  • 6. Re: equivalent to an "IsUnique" function
                    comment_1
                       I don't think I would put it quite that way. But you are right in one thing:

                    If the field has been modified, but not yet committed, the modified value is not yet included in the value list. And if the calculation field is STORED, it will evaluate precisely in that "window of opportunity".

                    I believe this is a bug in the order of evaluation, and I would advise against taking advantage of it. Consider a few scenarios:

                    • User enters "Alpha" in record #1. It is considered unique. Later, user enters "Alpha" in record #5. Now, record #5 is "not unique" - but no similar warning appears when viewing record #1.

                    • Suppose that, given the warning, user decides to leave record #5 as is, and modify record #1 instead. The warning in record #5 remains - even though the value is now unique.

                    • If a unique record is duplicated, there will be no warning in either the original or the duplicate.

                    • If two users create a new record with the same value at approximately the same time, neither one will get warned.





                    • 7. Re: equivalent to an "IsUnique" function
                      raybaudi
                        

                      comment wrote:
                      I don't think I would put it quite that way.

                      Given the restriction of not using the validation option, I do not know other ways ( till 9 ).

                       

                      Have you something better ? Maybe with 10 ?


                      • 8. Re: equivalent to an "IsUnique" function
                        comment_1
                          

                        A self-join of:

                         

                        Table::Field = Table 2::Field

                        AND

                        Table::SerialID ≠ Table 2::SerialID

                         

                        Put Table 2::SerialID on the layout, and format it as Boolean.

                         

                         

                        I believe this will work in any version from 3 and above.

                        • 9. Re: equivalent to an "IsUnique" function
                          raybaudi
                            

                          Perfect !

                           

                          I always forget the simplest things ;)

                          • 10. Re: equivalent to an "IsUnique" function
                            soundtrackit
                              

                            OK, now I'm really confused.

                             

                            All I want to do is display a text message on the layout if the contents of a given FIELD matches the contents of that same FIELD in another record.

                             

                            What is the simplest way to do that? Thanks! 

                            • 11. Re: equivalent to an "IsUnique" function
                              ninja
                                

                              Howdy all,

                              Just my two cents (often deflated to ~1.257 cents)

                               

                              It occurs to me that the simplest way (soundtrack it, you asked for the simplest...not the slickest or most useful...the simplest, right?)

                               

                              In FMP9 or before, set the validation to "Unique" and allow the user to override.  You get your text message in a pop-up box, but you can't control what it says.  You can leave in non-unique though...

                               

                              In FMP10, use a script trigger as you leave the field to run a script that:

                              1. stores the unique ID of the record in a global holder

                              2. perform a find on the field in question (Copy contents, enter find mode, paste, perform find)

                              3. Get foundcount

                              4. If foundcount>1, Setfield [textholderfield;"Not unique dude!"]

                              5. go back to the original record (enter find mode, paste global holderID to UniqueID field,perform find).

                               

                              Anyhows...that was intermission...please continue...

                              • 12. Re: equivalent to an "IsUnique" function
                                soundtrackit
                                  

                                I am trying to avoid the filemaker error box all together.

                                I'm using Filemaker 9 (not 10) with a database hosted on a Filemaker 8 v4 server.

                                 

                                The error message should look good on a web browser via IWP.'

                                 

                                Thanks! 

                                • 13. Re: equivalent to an "IsUnique" function
                                  raybaudi
                                     Hi soundtrack

                                  I hate ;) to admitt, but the simplest and more efficient is the comment solution.
                                  • 14. Re: equivalent to an "IsUnique" function
                                    soundtrackit
                                      

                                    So, please start from the beginning so I understand...

                                     

                                    I only have one table.

                                    I have a field in that table called "myuniquefield"

                                    I placed "myuniquefield" as a modifiable text box on my layout

                                    I then place a field called "myerror" on the layout below "myuniquefield"

                                    By default, "myerror" is empty 

                                    When I type in a value into "myuniquefield" and commit those records via a script,

                                    That script checks to see if there exists the same value of "myuniquefield" in any other record.

                                    If there isn't, the record is committed. If there IS a duplicate, the records do not get committed and the "myerror" field now displays a message that says "Please Enter a different value in the above field".

                                     

                                    Can you take me through that step by step? 

                                    1 2 Previous Next