1 2 Previous Next 18 Replies Latest reply on Jul 14, 2016 6:50 AM by CICT

    Conditional format a duplicate value

    russk68

      Hi all,

      I want a field to fill in red when the value is duplicated in another record in the same table. I found some answers about creating a self-relationship. Can this be done without creating a relationship?

       

      Thank you!

       

      Russ

        • 1. Re: Conditional format a duplicate value
          user19752

          First answer I think of is using ExecuteSQL() in "without relationship"

           

          ExecuteSQL ("

          SELECT COUNT(*) FROM yourTable WHERE yourField=?

          " ; "" ; "" ; yourTable::yourField

          ) > 1

          • 2. Re: Conditional format a duplicate value
            russk68

            Thank you for your reply!

            This is what I have entered and every record has a red fill. I'm guessing the 2nd line is the issue.

            ExecuteSQL ("

            SELECT COUNT(*) FROM instruments WHERE Fixture Ch. #=?

            " ; "" ; "" ; Instruments::Fixture Ch. #

            ) > 1

             

            Thanks!

            • 3. Re: Conditional format a duplicate value
              user19752

              I mistake that the function result is not number. And you need quotation for specific field name.

               

              ExecuteSQL ("

              SELECT COUNT(*) FROM instruments WHERE \"Fixture Ch. #\"=?

              " ; "" ; "" ; Instruments::Fixture Ch. #

              ) + 0 > 1

              1 of 1 people found this helpful
              • 4. Re: Conditional format a duplicate value
                russk68

                Works perfect!

                 

                Thank you!!

                • 5. Re: Conditional format a duplicate value
                  CICT

                  Be a little careful with this, particularly  as your number of records grow. ExecuteSQL can cause performance issues, particularly when you first open your database and access the layout in question.

                   

                  You may be better off using a self link rather than SQL if this starts to happen.

                   

                  Regards

                  Andy

                  1 of 1 people found this helpful
                  • 6. Re: Conditional format a duplicate value
                    keywords

                    Please mark user19752's answer as correct, not your own.

                    • 7. Re: Conditional format a duplicate value
                      CICT

                      Sorry, this is a fault in the system, not ours.

                       

                      I replied to your comment of 'works perfectly' and was concerned that performance issues will catch you out in the future. I haven't marked my reply as anything.

                       

                      As far as I can see currently it is your answer that is marked as the correct answer, not my reply to it.

                       

                      Regards

                      Andy

                      • 8. Re: Conditional format a duplicate value
                        keywords

                        CICT, I was not replying to your post, I was replying to the OP (see: (in response to russk68))—i.e. the same response you replied to with your cautionary word. OP (russ68) has marked his own "works perfect!" acknowledgement as correct, rather than the thing which he says works, which was suggested by user19752.

                        • 9. Re: Conditional format a duplicate value
                          CICT

                          No problem. The system shouldn't allow anyone to mark their own posting as the correct answer and the emails received often appear out of context.

                           

                          Apologies for the confusion.

                           

                          Andy

                          • 10. Re: Conditional format a duplicate value
                            russk68

                            Oh,

                            Thanks for the advice!

                            • 11. Re: Conditional format a duplicate value
                              russk68

                              Hi CICT,

                              I took your advice and created a self link and I'm having an issue with conditional formatting with using 2 fields.

                               

                              I am using this condition format formula for duplicates for the 2 fields: Count ( Instruments 2::Fixture Ch. # ) > 1 and ( Instruments 2::Fixture Name ) > 1

                              and it works fine with 1 field. The table is: "Instruments" and the 2 fields I'm formatting to find duplicate values are: "Fixture Ch. #" and "Fixture Name"

                              The issue is when I have a duplicate # in the "Fixture ch. #" field, it formats (Red background) as expected but the "Fixture name" field formats in red as well but does not contain duplicate names.

                               

                              The relationship between the TO copies is: Fixture Ch. # = Fixture Ch. #

                              I don't understand why the "Fixture Name" field is formatting based on information from the "Fixture Ch. #" field when the formula in the field is different.

                               

                              Any help would be greatly appreciated!

                              • 12. Re: Conditional format a duplicate value
                                CICT

                                I think you've slightly misinterpreted the results of the SQL. We've already established that the SQL works, therefore the following should provide the same result:

                                 

                                Count ( Instruments 2::SerialNoField ) > 1

                                 

                                This assumes Instruments 2 is the self link TO and you need a field in Count () that always has a value such as a serial no. field, or maybe one of your Fixture fields

                                 

                                Breaking down the SQL:

                                 

                                Count (*) just provides a count of all records

                                From instruments is your local table

                                WHERE is providing the match between the 2 fields, which is what your self link is doing

                                 

                                You just need to count the number of matches on the far end of the relationship

                                 

                                I hope that makes sense

                                 

                                Kind regards

                                 

                                Andy

                                • 13. Re: Conditional format a duplicate value
                                  russk68

                                  Hi Andy,

                                  Thanks for the reply.

                                  The "Fixture Name" field still shows a true conditional format even though the field names are different in the 2 records. They are resulting true from the duplicate "Fixture Ch. #" fields from the 2 records.

                                  My goal is to have the "Fixture Name" field format true if it's duplicated in other records and separate from the "Fixture Ch. #" field that formats true in other records in the same table.

                                   

                                  I'm a novice at this so forgive me if you already explained this above because I only partially understand what you are saying.

                                   

                                  Thanks again!

                                   

                                  Russ

                                  • 14. Re: Conditional format a duplicate value
                                    CICT

                                    Hi Russ

                                     

                                    I based the advice around the results of the SQL. Would you like to take screen shots of the self link relationship, your conditional formatting formula and a couple of examples of the data working or not and private message these to me have a look?

                                     

                                    Regards

                                    Andy

                                    1 of 1 people found this helpful
                                    1 2 Previous Next