7 Replies Latest reply on Oct 7, 2012 3:21 PM by philmodjunk

    identifying dup records in calculation not script step

    zinny

      Title

      identifying dup records in calculation not script step

      Post


           Hi, I am aware of the ! find script step that would allow me to find duplicate fields but I was wondering if there was a purely calculation based way of identifying whether records related to a master record in a one to many relationship are duplicates?

           I am a tv director who has created a program for managing my tv shows

           Each series has 10 episodes [episode table] and each episode has 3-5 stories inside it [story table] (the one to many relationship)

           Each story has a genre represented by a radio button set field - in the case of a police show the stories might be classified by a value list Traffic stop/Drug use/Disorderly behaviour etc.

           No episode should have two stories with the same genre as this would become repeditive for viewers.

           I would like to create a calculation field on the epidose table that identifies when there is a repeat value in the related stories and returns a text statement like "WARNING : Genre Conflict".

           Is this possible?

           Thanks

            

        • 1. Re: identifying dup records in calculation not script step
          bumper

               I am assuming that genre is a field in the story table. If so the following worked for me with a quickie test file.

               In the story table create a text field, DupCheck. Make it an auto-entered with the following calculation, episodeID &"-" & genre, uncheck the box do not replace value of field (if any) then go to the validation panel and check require: Unique Value. You will probably want to allow the user to override the validation, and in the Display Custom Message if validation fails, enter your error message. 

               The not so custom dialogs that are shown when the validation fails, ie, there are two identical genres for the same episode are not obvious and worse cannot be replaced with a custom dialog of your own design. 

               I put all this in a portal on the episode record so if you have only three or four stories per episode the genres would be pretty apparent at a glance, you could also set each genre to a different color which would increase the obviousness.

               HTH

          • 2. Re: identifying dup records in calculation not script step
            zinny

                 Thanks, I would never have thought of that. I put it in a test project and it works great - will impliment on monday.

                 I would still like a calculation that returns a boleen if there are duplicates in a field on related records so conflict could exist but be apparant to the user (we do a lot of shuffling).

                 also I can't really use the conditional formatting thing as I am using that to identify other issues and I don't want the end up with a rainbow effect.

                 Thanks

            • 3. Re: identifying dup records in calculation not script step
              philmodjunk

                   You can do more than change colors with conditional formats. You can put Layout text on your layout with text such as "DUPLICATE VALUE" and you can use the conditional format to make the text appear/disappear. Not only can you change the color to match your layout background, you can change the font size to 100 or larger to make the text disappear.

                   For a boolean result in a calculation, you can set up a self join match on the fields whose combined values define the duplicate condition.

                   IsEmpty ( SelfJoinTO::NeverEmptyField )

              • 4. Re: identifying dup records in calculation not script step
                zinny

                     Thanks phil, I do use the text disappear thing but need to get a calculation to trigger the conditional formatting

                     What calculation would I use, I don't understand the self join match on fields thing and need a little more detail to understand your plan

                      

                     Thanks

                      

                • 5. Re: identifying dup records in calculation not script step
                  philmodjunk

                       Say you want to know if Field A and Field B of two different records contain the same value.

                       Open Manage | Database | Relationships

                       Select your table by clicking it, then click the duplicate button. This creates a new table occurrence--a new reference to the same table.

                       Drag from Table::Field A to Table 2::Field A and then from Table::Field B to Table 2::Field B.

                       Then this calculation:

                       Count ( Table 2::Field A ) > 1

                       will return True if at least one other record has values in the same two fields. (I realize now that IsEmpty( ) won't work here as a record will match to itself.)

                  • 6. Re: identifying dup records in calculation not script step
                    zinny

                         great, thanks works a treat. Still so much to learn about relationships

                    • 7. Re: identifying dup records in calculation not script step
                      philmodjunk

                           You may find this tutorial on table occurrences helpful: Tutorial: What are Table Occurrences?