7 Replies Latest reply on Nov 3, 2016 5:08 AM by coherentkris

    How to make sure calculation field is unique

    user28358

      Good day to you all

       

      Please assist me in this small problem i have. My question is simple, i would like to make sure a calculation field is unique. how can i do this.

      I am very new to FMP, using FMP 15

       

      Thank you in advance

        • 1. Re: How to make sure calculation field is unique
          RickWhitelaw

          If you're meaning the result of a calculation I don't think this is possible. Others will know more.

          • 2. Re: How to make sure calculation field is unique
            user19752

            You can't set validation on calculation field.

            How do you want to resolve the duplication in calculation field? User would need to change value of referred fields, or remove the record silently etc.

            • 3. Re: How to make sure calculation field is unique
              user28358

              i want to make sure that a person has access to only one particular facility. to do this i want to use the persons ID and the facility name to make sure it is unique for each person.

               

              example:

              Rick of id 110 has access to swimming pool facility so the calculated field is 110swimmingpool. I dont want rick to have another swimming pool (110swimmingpool).

               

              I hope that makes sense.

               

              Thank you for your help

              • 4. Re: How to make sure calculation field is unique
                macwombat

                It sounds like you are asking the wrong question.  When you say that you want to make sure a person has access to only one particular facility are you talking about access to view records relating to one particular facility?  If that is the case then you should be using a privilege set to use a calculation to set record level privileges.

                 

                Also, if the person's ID is unique then the combination of their ID with the facility name will by definition also be unique.

                • 5. Re: How to make sure calculation field is unique
                  user28358

                  I am not refereeing to access to view records.

                   

                  Just imagine you have a many to many relationship which is modeled using a joint table. We need to create a composite primary key which is a combination of the IDs of the two tables. How do we make the two primary keys from the tables composite?

                  • 6. Re: How to make sure calculation field is unique
                    fmpdude

                    One obvious way would be to have the calculation field to have a UUID.

                     

                    Check out the documentation below:

                     

                    Get(UUID)

                     

                    But it really sounds like this is a database design problem, not a calculation field unique problem.

                     

                    Having a design like this below might help:

                     

                    USERS (main)

                     

                    FACILITY(many)

                     

                    So, for Rick he may or may not be allowed to some FACILITY by how many records he has in the "FACILITY" table. If Rick is in the FACILITY table, you can specify which resources he can access by one or more records in the FACILITY Table. (of course, you should also have a "RESOURCES" table so you don't have to hard-code "SwimmingPool", etc.)

                     

                    Then, if you do an SQL something like this:


                    SELECT * FROM USERS, FACILITY where

                    user.pk = allowed=usr,fk and

                    user.name = "Rick"

                     

                    You would get a list of all the resources Rick had access to.

                    (I didn't consider the Resources table in the above SQL.)

                     

                    HOPE THIS HELPS.

                    • 7. Re: How to make sure calculation field is unique
                      coherentkris

                      Set the field with text data type.

                      Set it to be an auto enter calculation of

                      field1 & field2

                      Validate as unique