14 Replies Latest reply on Feb 12, 2010 2:51 PM by agreer

    No Duplicates Allowed! (FileMaker 5

    agreer

      Title

      No Duplicates Allowed! (FileMaker 5 & 5.5)

      Post

      I have a table with a list of items, each of which has a unique product number that is a calculation (=Product Number + "-" + Customer Number). I'd like for a message to pop up if someone tries to enter a duplicate unique product number. However, I don't have any validation options when using a calculation field. Any ideas?

       

      Thanks!!!

        • 1. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
          philmodjunk
            

          You seem to have two product numbers here? Product Number is a data field and then you have a Product Number calculation field?

          (which should be written as: Product Number & "-" & Customer Number )

           

          Why do you have a human (who can make mistakes) entering a value that must be unique?

           

          If you can set things up with an auto-entered value such as a serial number you would avoid the duplicate value issue all together.

          • 2. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
            agreer
              

            PhilModJunk: You are right, and I have it written as: Product Number & "-" & Customer Number. Sorry about the typo. My other programming languages are bleeding into my FileMaker-ese.

             

            This table has a Unique Product Number as well as a Product Number. The Unique Product Number, obviously, I'd like to be unique. The Product Number, as well as the Customer Number, can and do have many duplicate entries.

             

            The background: The Product Number refers to a specific vitamin. The Customer Number refers to the customer who has ordered this vitamin. The Unique Product Number refers to the printing plate required to print the specific vitamin information on the specific customer's labels. Therefore, multiple customers can order the same vitamin, or a single customer can order multiple vitamins, but if an order is already in for a specific vitamin/customer plate, I'd like to warn the orderer that it has already been ordered.

            • 3. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
              philmodjunk
                

              You'd have more options with Filemaker 10.

               

              Using 5.5, I'd make a self join relationship.

               

              define a relationship linking your file to itself by Unique Product Number.

               

              Let's name the relationship "UniqueProdNumb".

               

              Define a calculation field set to return text: If ( count ( UniqueProdNumb::Unique Product Number ) > 1 ; "Duplicate Value" ; "" )

               

              Place this field on your layout and you'll have a message that only apears when a duplicate number is calculated.

              • 4. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                agreer
                  

                One thing I would like to further explain:

                 

                The Unique Product Number is a calculation that is auto-entered once the orderer has filled in the Product Number and the Customer Number. I'm not sure if I clarified this before.

                • 5. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                  agreer
                    

                  Thanks, PhilModJunk...I'll try that and let you know how it goes.

                   

                  I wish I had a newer version. My company bought a very expensive database program called BatchMaster but they haven't incorporated our department into it yet. Since we're "on the verge" of joining the system (NOTE: and have been for 5 years! *sigh*), they won't upgrade FileMaker.

                   

                  April

                  • 6. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                    agreer
                      

                    This solution will have to do. We'll see if it works as I am not the person(s) who put in the orders. I'm sure I'll hear about it when it pops up for them the first time!

                     

                    Thanks!

                     

                    April

                    • 7. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                      agreer
                        

                      Eh, it kinda works. The problem is that you have to click out of all fields, even fields not related to the duplicate - tabbing to the next field doesn't work, to have the "Duplicate" message appear.

                       

                      I don't know that the orderers do that, and training them to do that is out of the question.

                       

                      Thanks anyways.

                      • 8. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                        ninja
                          

                        Howdy,

                         

                        Not proposing it as ideal or anything, but you could launch a script onobjectexit that simply performs "Commit Record".

                        This would avoid the retraining.

                        • 9. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                          agreer
                            

                          Ninja,

                           

                          Love the idea and would use it a thousand times over if I could. I don't believe FileMaker 5.5 supports that kind of functionality, though. I can't find anything in the help section or in the field format menus about the ability to perform scripts dynamically like that.

                           

                          If you know something I don't, please please help. I could add all sorts of functionality if I could have "listener" scripts.

                           

                          Thanks,

                           

                          April

                          • 10. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                            ninja
                               Sorry,  Absolute brain freeze...it's even in your thread title....Geeez.
                            • 11. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                              philmodjunk
                                

                              agreer wrote:

                              Eh, it kinda works. The problem is that you have to click out of all fields, even fields not related to the duplicate - tabbing to the next field doesn't work, to have the "Duplicate" message appear.

                               

                              I don't know that the orderers do that, and training them to do that is out of the question.

                               

                              Thanks anyways.


                               

                              We keep banging up against the limitations of your version. :smileysad:

                               

                              It's not much of an improvement, but if your interface is setup so that your orderers do everything (new order, next order.. etc.) with scripts, you can include a test of this field and pop up a message box warning them of the duplicate before they can do anything else. You can even set this check to run in a script that runs when the file is closed.


                              • 12. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                                agreer
                                  

                                Phil,

                                 

                                Thanks for the thought...but still no dice. :( Unless you click out of all fields, it doesn't register the duplicate, even on a button click. *sigh*

                                 

                                I think I'm just two steps beyond the capabilities of this version. It's where I seem to hover when I get the old "Hey, it would be cool if..." from one of the other users.

                                 

                                I really appreciate your help, though!

                                 

                                April

                                • 13. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                                  philmodjunk
                                    

                                  On your buttons' script add code such as

                                   

                                  Exit record/Request

                                  IF [not Isempty (WarningField)]

                                     Show Message ["Warning! duplicate value...."]

                                   

                                   

                                  • 14. Re: No Duplicates Allowed! (FileMaker 5 & 5.5)
                                    agreer
                                      

                                    Phil,

                                     

                                    Thanks! Working great!

                                     

                                    April