5 Replies Latest reply on Oct 24, 2015 4:20 AM by dyb

    Check for multiple occurences by Calculation

    dyb

      I'd like to check if a record with the same values in fields already exists.

       

      This is desirable:

      Record 1

      Field 1: "20"

      Field 2: "ABC"

      Record 2

      Field 1: "20"

      Field 2: "CBA"

       

      This is wrong:

      Record 1

      Field 1: "20"

      Field 2: "ABC"

      Record 2

      Field 1: "20"

      Field 2: "ABC"

       

      Of course each record has its unique key field.

       

       

      How do i validate this by calculation? Where to look?

        • 1. Re: Check for multiple occurences by Calculation
          wimdecorte

          Why does it need to be a calculation?  That can become a performance hog.  What's the reason for doing the check?  Is it at the time of data entry, some other point?

          • 2. Re: Check for multiple occurences by Calculation
            dyb

            These fields are in a join table. Field 1 and Field 2 are foreign keys for this table.

            I don't want to have multiple records that basically do the same thing.

             

            Am i doing some unnecessary steps?

            • 3. Re: Check for multiple occurences by Calculation
              user19752

              Make a text field with auto enter calculation as

              Field 1 & Field 2

              then set unique validation for this field.

              (use separator for concatenation if need)

              • 4. Re: Check for multiple occurences by Calculation
                wimdecorte

                dyb wrote:

                 

                These fields are in a join table. Field 1 and Field 2 are foreign keys for this table.

                I don't want to have multiple records that basically do the same thing.

                 

                Am i doing some unnecessary steps?

                 

                Understood, but you have not told us why or at what point you expect that to be checked?  Calculations will only fire when the data is already in there and the calculation will be "expensive".

                 

                So perhaps there is a better and more efficient point in time that this can be checked?

                • 5. Re: Check for multiple occurences by Calculation
                  dyb

                  I'm a beginner with FM software but i am eager to learn. It is really interesting that, what i though was a simple process, could create some potential problems you are talking about! Thank you for your interest.

                   

                  I create a record in a parent table let's call it "Table 1" and i set "Field 1" and "Field 2".

                  On the same layout i have a portal showing the matching field "Field 2" of the join table "Table2".

                  I pick a value from a list derived from previously entered records in the "Field 2" from "Table 1".

                  When i pick this value i create a corelation i'm interested with and at this point i want to know if it's already there or not.

                   

                  Saying that i also realized i don't want "Field 2" from "Table 1" repeated in a join table...