9 Replies Latest reply on Jan 29, 2014 10:47 AM by pthomas

    How do I eliminate duplicate records for labels?

    synergy46

      I am using FM 13 Adv. This is a 'skills' application. The 'skills' are on a portal

      (Based on Category table) and they work via a conditional value list. It all works.

      LabelsRelations.png

      I have a report that allows the user to select a 'CATEGORY' (Accounting, Construction etc) and then the report filters accordingly.

      labels1.png

      LOSkills.png

      The problem is I need to have each person who is within a Category (In the case above it is 'Construction')

      print their label only ONCE. As you can see above, Fred Flintstone prints 2 labels because he is represented

      in two 'Skill" areas: Electrical and Painting.


      I need him to print on only 1 label.

       

      I have written a script in which I use a FIND to try and eliminate duplicates; but it does not seem to do so.

       

      LabelsScript.png

      I tried using Constrain ! to eliminate duplicates but that did not seem to work.


      So, how would you solve this problem?

       

      Thanks for thinking about it and your ideas.

       

      Ron

        • 1. Re: How do I eliminate duplicate records for labels?
          LabsRock

          Hi Ron,

          Could you use a Go To Related Records from the found set of Category labels to pull up the list in your People table?  Assuming that the People table is an unduplicated list of people that should give you one record for each individual in the original list.  Create your label layout in the People table and your off and running.

           

          Craig

          • 2. Re: How do I eliminate duplicate records for labels?
            synergy46

            Craig,

             

            I can't imagine how to use GTRR fromt the found set in Category.   I understand GTRR is used to go to a specific, single related record in another table.  What GTRR (perhaps in a loop?) look like?

             

            Yes, People is a unique primary table.  If I base the labels layout on people, it brings up ALL people.

             

            I need to just see a single person ino matter how many times they appear in the found set.

             

            Thank you for your thoughts.

             

            Ron

            • 3. Re: How do I eliminate duplicate records for labels?
              synergy46

              I have tried using ExecuteSQL via SeedCode's SQLEXPLORER in order to use the Distinct function.

               

              I think the problem is that the global variable $$MenuSelection (which holds the Category ; ie, Accountant, Construction etc) isn't visible when I run the SQL statemtn in the script.

               

              I don't know much about SQL can anyone see where I am screwing up?

               

              SQLDistinct.png

              • 4. Re: How do I eliminate duplicate records for labels?
                LabsRock

                Hi Ron,

                If you currently have a found set in Categories which you are printing labels from (context for the layout is Category) you can add a GTRR for People, sending it to a labels layout based on the People table.  Set it to Show only related records and Match all records in found set.  It should take you to the people associated with the category list.  Your Get related record from = People and Show record using layout is the People-based label layout.

                 

                GTRR_Settings.jpg.

                I hope that helps.

                 

                Craig

                • 5. Re: How do I eliminate duplicate records for labels?
                  synergy46

                  I implemented what I think is the GTRR idea and this is what I get:

                  gtrroutcome.png

                  As you can see, Schlosser is, for some reason, included... He does not have

                  any "Construction" Category data... huh?

                   

                  This is the script that produces that outcome. 

                  scriptgtrr1.png

                   

                  I have tried moving the GTRR after the LabelsSkills layout is loaded but it made no difference...

                   

                  What am I missing ?  (pounding head on desk) 

                   

                  R

                  • 6. Re: How do I eliminate duplicate records for labels?
                    LabsRock

                    I think you're making this way more complicated than it has to be.  Below is a sample script that, if I understand everything correctly, you could run from a button on your report layout.  My assumption is that the context for the report layout in the background of you screen shot is from the Category table.  A more detailed shot of the GTRR setup is below that.

                    SampleScript.jpg

                    GTRR_Setup.jpg

                     

                    Craig

                    • 7. Re: How do I eliminate duplicate records for labels?
                      pthomas

                      I think if you remove the following lines of the script:

                       

                      If [$$MnuSelection="Construction"]

                      ...

                      End If

                       

                      And then change the line that currently says:

                       

                      Go to Layout [ "LabelsSkills" (People) ]

                       

                      To instead be:

                       

                      Go to Related Record [ From table: "People"; Using layout: "LabelsSkills (People) ]

                       

                      It should work for you.

                       

                      Basically you want to open up the new window first, and then go to the related records from there. Note that you need to define the "Using Layout" part which you had not done.

                       

                      Cheers,

                       

                      Paul.

                      • 8. Re: How do I eliminate duplicate records for labels?
                        synergy46

                        I don't want to get all related records in found set. 

                         

                        The problem is I have multiple people on the Skills Roster report.  But, I only want one label for each unique person.

                         

                        I solved the problem by looping through the found set and setting a DUP field to 1 if there was a previously existing duplicate.  At the end of the process I just 'found' DUP =0.

                         

                        I am going to try and get your GTRR system to work; since it would be simpler than using the looping script...

                         

                        Thanks for the ideas and the reply.

                         

                        Ron

                        • 9. Re: How do I eliminate duplicate records for labels?
                          pthomas

                          I am pretty sure you do actually want to get all related records in the found set! You have stated that you want to print one label for each unique person.

                           

                          Expanding on your earlier example, if your found set looks like this:

                           

                          Fred - Electrical

                          John - Electrical

                          Bill - Painting

                          Fred - Painting

                          Bob - Painting

                           

                          If you GTRR back to your People table using the entire found set, you will end up with the following found set in your people table:

                           

                          Fred

                          John

                          Bill

                          Bob

                           

                          If you GTRR using matching only the current record you will end up with either Fred OR Bill OR John OR Bob only (depending which is the record in focus when you execute the GTRR command).

                           

                          Just make sure you set up your GTRR correctly to go to the LabelsSkills layout as I outlined in my other post!

                           

                          Cheers,

                           

                          Paul.