14 Replies Latest reply on Feb 6, 2009 6:59 AM by LightningAd

    Checking calculation result is a unique number...how?

    LightningAd

      Title

      Checking calculation result is a unique number...how?

      Post

      I have a calculation that creates an alphanumeric code number from various elements. I need to include something into the calculation to ensure it checks the result is a unique number, and forces the user to re-enter using new elements....

      problem is i don't know how to do it.

       

      The calculation currently in use is "agency code &  "/" & product code & Right( "000" & serial; 3) & "/" & duration".

       

      Please can anyone tell me how i can add something to check its uniqueness?

       

      thanks

      Adam 

        • 1. Re: Checking calculation result is a unique number...how?
          ninja
            

          howdy LightningAd,

           

          you say "calculation" but also say "force user to re-enter".

          I can see how both may be true being that user can enter one component but calc assembles multiple components.  In this case, I THINK that defining the field to contain only unique values may work...have you tried that yet?

           

          File>Define>Database

           

          There are certainly more involved ways to go about it too if needed.

          • 2. Re: Checking calculation result is a unique number...how?
            mwc_1
              

            In the option for a field, you can set field validation to require:unique entry.

             

             

            • 3. Re: Checking calculation result is a unique number...how?
              LightningAd
                

              thanks for the quick replies,

               

              unfortunately those options are not valid in this case.  The only time the code becomes unique is once all the various elements are combined. Basically, the first 3 characters define the agency, the next 4 are the product descriptors, then a 3 digit serial, and then a duration (3 digits representing seconds).

               There is a serial number in the calculation, but as i only have the option to use a 3 digit serial that goes back to 000 after 999 (trust me - that is set in stone and cannot be changed), then i need to find a way to look at the combined result and decide if that is unique. 

               

              If the number is not unique, then the user will have to alter at least one of the elements and try again. 

               

              The option field for a calculation does not have the validation setting for a Unique Entry.....thats why i posted the question! 

              • 4. Re: Checking calculation result is a unique number...how?
                ninja
                  

                LightningAd wrote:

                The option field for a calculation does not have the validation setting for a Unique Entry.....thats why i posted the question! 


                 

                Quite right...sorry...wan't thinking.

                 

                1. consider a triggered script using SetField[].  This allows you to use a script-based calculation outside of having a calculation field...thus the field can have the "unique" validation on it.

                 

                2. How many records are you looking at...is it too many to do a find and Get(Foundcount)?  Would this work or take to long?

                 

                Thinkin' out loud...stop me when something rings true...

                • 5. Re: Checking calculation result is a unique number...how?
                  LightningAd
                    

                  Hi Ninja,

                  thanks for the help.... 

                   

                  1 - thats the kind of thing i need...although i am still very much a newbie and that description is a little too brief for my level of knowledge...can you elaborate a bit?

                   

                  2 - currently just over a thousand entries...These are actually id numbers for tv commercials i edit, and i need a system that will generate new codes for me, based on the contents of the advert, and as there are often many versions of one ad i need to be sure that the numbers i get are unique, rather than having to double check the output everytime i create a new record. 

                   

                  regards

                  Adam 

                  • 6. Re: Checking calculation result is a unique number...how?
                    ninja
                      

                    Sure thing...

                     

                    If you need to run a series of steps for a field that can't fit into the "calculation field" boundaries, you can run a script instead...In this case a simple one-step script of:

                     

                    SetField[Assembled_ID;account::Id & Version::ID & "ABCInc" & Version_SerialNo]

                     

                    or whatever you are using for your assembled ID#.

                     

                    If your field Assembled_ID is NOT a calculation field, but a text field instead, you can validate it by requiring both "Not Empty" and "Unique Value"

                     +++++++++++++++

                    the remaining choice then is how to launch the script.

                    - In FMP10 there are script triggers that you can latch to filling in another field so that it runs automatically and you won't have to remember to do it.

                    - You could put a "Check ID" button in earlier versions with the script attached to the button.

                    - You could manually run the script (don't do this...use the button).

                    - Or you could lay a transparant "looks like a field" button over the field itself that pops open a window as part of the same script using the "Show Custom Dialog" command and defining input fields.  The script could then do your assembling of ID#.

                     

                    What do you think?

                    • 7. Re: Checking calculation result is a unique number...how?
                      LightningAd
                        

                      thanks Ninja - just leaving the office, so i'll give it a shot on monday. Certainly looks like the kind of thing that i need.

                       

                      thanks for your help.

                      Adam 

                      • 8. Re: Checking calculation result is a unique number...how?
                        Sorbsbuster
                          

                        Here's another idea to throw into the arena:

                         

                        - Suppose your fields to be concatenated are FieldA and FieldB, concatenated into calculation FieldC.

                        - Set up a self-join relationship to the table, using two criteria in the relationship:

                        RelationshipSelfJoin::

                        FieldA = FieldA

                        and

                        FieldB = FieldB

                        - Set the Validation criterion for FieldB to be 'Count (RelationshipSelfJoin::FieldA) = 0'

                        - If that combination of FieldA and FieldB already exists, you will not be allowed to complete the concatenation, and the data-entry warning will pop up.

                         

                        HTH,

                        Alan.

                         

                        • 9. Re: Checking calculation result is a unique number...how?
                          LightningAd
                            

                          Hi Alan,

                           

                          i think you are on the path i thought i had taken...but obviously i have not done it right!!

                           

                          In trying to assimilate your description into my situation, i'm getting confused about which bit goes where...

                           

                          my setup is thus:-

                          clock (final concatenated data) is made up of 4 elements - Agency, Product, serial Number, Duration.

                           

                          I have self-joining tables that connect both agency and product codes, but i don't where the validation criteria actually goes...because in the table and field views, they all only show up once - theres no a and b versions.

                           

                          Also - because the final Clock field is made up from 4 elements (all of which have a bearing on the uniqueness of the end result)  would i need to self-join all four elements?

                           

                          thanks for your help

                          Adam 

                           

                           

                          • 10. Re: Checking calculation result is a unique number...how?
                            Sorbsbuster
                              

                            Sorry, to be more clear using your field names (I think!):

                             

                            - Set up a self-join relationship to the table, using the four criteria in the relationship:

                            RelationshipSelfJoin:: defined as:

                            Agency = Agency

                            and

                            Product = Product

                            and

                            Serial Number = Serial Number

                            and

                            Duration = Duration

                            - Set the Validation criterion for Duration to be 'Count (RelationshipSelfJoin::Agency) = 0'

                            - If that combination of Agency, Product, Serial Number, and Duration already exists, you will not be allowed to complete the concatenation, and the data-entry warning will pop up.

                             

                            Hope that's a bit clearer,

                            Alan.

                             

                            Actually, I've just done some more testing:

                             

                            - you can create the relationship as 'clock = clock' (which is only the end result of all 4 fields, anyway.

                            - you can set the same validation on all of the fields.

                             

                             

                            • 11. Re: Checking calculation result is a unique number...how?
                              LightningAd
                                

                              Thanks for clarifying that, Alan.

                               

                              I had understood after all, but i think i must have some other problem thats preventing me from getting the results i hoped for.

                               

                              I have tried your suggestions but i think it  is not working because all four fields already have specific validations attached to them (because each of those fields require the data in specific formats), and i don't know if there is a clash going on. Am i correct in trying to put the Count command in the Validation Calculation field?

                               

                              I also tried to validate the Clock field but i don't know how to do that on a Calculation field, as there is no obvious validation setting.  The calculation is :-

                               

                              agency code &  "/" & product code & Right( "000" & serial; 3) & "/" & duration 

                               

                              and i tried to add the validation on the next line (using a semicolon to end the first line) but that just tells me its expecting an operator. 

                               

                              This one has really got me puzzled!

                               

                              Adam 

                               

                              • 12. Re: Checking calculation result is a unique number...how?
                                LightningAd
                                  

                                one further point - when i say not working...what actually happens (when i use the validation on the duration field) is i do get the standard warning that "Duration" is defined to contain only specific values. Allow this value?  Revert No Yes .

                                 

                                That is confusing because the duration is correct, its the combined calculation that is not a unique one. I want to prevent the number from being used again, yet if i hit the Yes button, it uses the number that i want to prevent from being used.

                                 

                                I if set the validation to display a custom message, i don't get the option to decide which repsonse buttons are shown.

                                 

                                 

                                 

                                Hence, i think the validation needs to be done on the calculation rather than on the individual fields that feed the calculation...I'm just not sure how to do it.

                                 

                                 

                                adam 

                                • 13. Re: Checking calculation result is a unique number...how?
                                  Sorbsbuster
                                    

                                  Sorry, hadn't realised you already had validations against all of those fields.  Can you add the Validation criterion with an AND statement in your validation calculation?

                                   

                                  You don't need to validate the 'Clock' calculation - validating the final component, for example, is enough.  If the addition of that particular final piece of data makes the whole 'Clock' non-unique then it will trigger the Validation warning.

                                   

                                  I agree that the default validation in your case is misleading, implying there is a problem with and only with the final field.  Not very helpful, but you could maybe phrase a Custom Dialogue around it.  Eg: "This combination of blah blah and Duration is not unique?  Allow this combination?"

                                   

                                  If you positively want to ban some users from accepting (or even seeing the option of 'Yes', to accept the invalid value) then you can set that in their Access Privileges ('Allow warning override = No').

                                   

                                  I think we're getting there.

                                   

                                  Alan.

                                  • 14. Re: Checking calculation result is a unique number...how?
                                    LightningAd
                                      

                                    You're a star...thanks Alan.

                                     

                                     

                                    I used the validation with the alteration to the message as you suggested. I like the sound of the Access Privileges...but thats more advanced than i am at the moment!!  

                                     

                                    Its enough for me to know when the clock is not unique.

                                     

                                    Right , now to think about the next obstacle.....

                                     

                                     

                                    thanks again

                                     

                                    Adam