5 Replies Latest reply on Jun 11, 2013 12:10 PM by mikefrey

    Conditional formatting for duplicate values

    mikefrey

      I am trying to use conditional formatting to flag records that have duplicate values in a given text field. I'm not easily finding the right function to compare the just entered text to the rest of the index for that field. Am I approaching this the wrong way? Should I be thinking script rather than using the conditional formatting formula line? Is there really any difference?

       

      Thanks,

       

      Mike

        • 1. Re: Conditional formatting for duplicate values
          Malcolm

          I am trying to use conditional formatting to flag records that have duplicate values in a given text field. I'm not easily finding the right function to compare the just entered text to the rest of the index for that field.

           

           

          if you need the flag to work at all times you can use a relationship. Create a duplicate TO of the table and match the field to itself. Add an extra element to the relationship to ensure that each record ignores itself, something like this:

           

          text field = text field

          id ≠ id

           

           

          if you only need to do this job once or infrequently, you might just search for duplicates using the exclamation mark, then work through the resulting list.

           

          Malcolm

          1 of 1 people found this helpful
          • 2. Re: Conditional formatting for duplicate values
            LyndsayHowarth

            Hi Mike,

             

            I would create a self-relationship on that field (or on the text values of that field)... then you have related values which are duplicates... so your conditional formating can check for related values...

             

            - Lyndsay

            • 3. Re: Conditional formatting for duplicate values
              mikefrey

              I have created the self-relationship with the separate table occurrence, but I'm still having a mental block on just what function to use to compare the two. It seems like it should use Count () and/or List () but I'm not sure. What I want the conditional formatting formula to ask is whether there are 2 or more instances of each value in the given text field and then react accordingly.

              • 4. Re: Conditional formatting for duplicate values
                mikebeargie

                assuming you've follwed the relation that malcolm pointed out above, the calculation could be:

                 

                if ( count(relatedTable::someField) > 0 ; 1 ; 0 )

                 

                then conditional formatting formula would be:

                 

                mainTable::calcField = 1 - change background color.

                1 of 1 people found this helpful
                • 5. Re: Conditional formatting for duplicate values
                  mikefrey

                  Everyone's answers above were very helpful, thank you all! Per Lyndsay's suggestion, I created a duplicate table occurrence and established the self relationship between the two tables. Then, it was simply a matter of putting this formula in the condional formatting formula dialog Count ( duplicate table::related field ) > 1

                   

                  Then I clicked a couple of check boxes and changed the colors and SHAZAAM it worked!!

                   

                  Good Stuff, thanks again everyone!!!