6 Replies Latest reply on Jul 22, 2014 6:25 AM by techxp

    Trouble with Duplicates

    techxp

      Good Day everyone,

       

      today I have something a bit more complex i need help with.

       

      For about a week now I have been find a way to solve the following Dilema.

       

      Our Calculation Database uses a Calculation Field to produce a three letter Token.

      This Token is later used to produce a full Tour Number which is then used in various other Databases.

       

      Unfortunatley this Token uses the first three Letters of the Tour Name and it occours commonly that we have Individual Tourss that have the same three first letters.

       

      Is there a way for me to find duplicate values and asign, for example the first two and the fourth letter of the Tour Name.

      I've read something along the Lines of using a Self-Joining Relationship for this and it sounds right, unfortunatly I do not know how this should be set up to work correctly.

       

      Thanks in Advance for any help renderd.

       

      Kind Regards

      Michael Mayrhofer

       

      Using FM 13 Advance on Mac OSX 10.9.2

        • 1. Re: Trouble with Duplicates
          erolst

          techxp wrote:

          I've read something along the Lines of using a Self-Joining Relationship for this and it sounds right, unfortunatly I do not know how this should be set up to work correctly.

           

          You could solve this via ExecuteSQL(), but let's stay with the more conventional approach of a self-join.

           

          Create a new table occurrence (TO) of the Tours table and call it, say, Tours_SelfByName. Create a self-join relationship by relating this new TO with your existing TO of Tours as

           

          Tours::name = Tours_SelfByName::name

          Tours::primaryKey ≠ Tours_SelfByName::primaryKey

           

          Now your calculation could read as follows:

           

          Case (

            not IsEmpty ( Tours_SelfByName::primaryKey ) ; // there is already a tour by that name

            Left ( name ; 2 ) & Middle ( Name ; 4 ; 1 ) ;

            Left ( name ; 3 )

          )

           

          That takes care of a duplicate. But what would be your strategy for calculating the tour abbreviation for a triplicate …?

           

          Maybe …

           

          Let (

            countOthers = Count ( Tours_SelfByName::primaryKey ) ;

            Case (

              countOthers ; // there is already a tour by that name

              Left ( name ; 2 ) & Middle ( Name ; 3 + countOthers ; 1 ) ; // use the first two and the n-th after 3

              Left ( name ; 3 )

             )

          )

           

          which deals with any number of duplicate tour names, if they're long enough … otherwise, you need another method to permutate the characters.

          • 2. Re: Trouble with Duplicates
            techxp

            Thanks a million erolst,

             

            now there is only one challange left and I am truly sorry for not stateing this before,

            there are certain Series that require the same Type of Tour Name e.g. the Etosha Tour would always need a ETO Token.

             

            Is there possibly a way of excluding certain Tours in this Case?

             

            I apologize for taking up more of your time, should it not be possible to do fufill all these conditions in a single field I will use a second field with the plain Upper ( Left ( lxt_Tour_Name ; 3 ) ) for those cases that require a constant value.

            • 3. Re: Trouble with Duplicates
              erolst

              techxp wrote:

              should it not be possible to do fufill all these conditions in a single field

               

              Sure it's possible – since there is Let(), you can do all this without extra fields and keep your mental sanity …

               

              For example …

               

              Case (

                Let ( [

                  exceptionList = List ( "Etosha" ; "otherExceptionName" ; "otherExceptionName" ; … ) ; // could store this list in a Custom Function, or as records …

                  countOthers = Count ( Tours_SelfByName::primaryKey ) ;

                  isException = not IsEmpty ( FilterValues ( name ; ExceptionList ) ) ;

                  firstThree = Upper ( Left ( name ; 3 ) )

                  ] ;

                  Case (

                    isException ; firstThree ;

                    countOthers ; Upper ( Left ( name ; 2 ) & Middle ( Name ; 3 + countOthers ; 1 ) ) ;

                    firstThree

                  )

              )

               

              An altogether different strategy could be to use an ordinal number for every tour (without exceptions), like in

               

              Upper ( Left ( name ; 3 ) ) & SerialIncrement ( "0000" ; Count ( Tours_SelfByName::primaryKey ) + 1 )

               

              PS: As it stands, you cannot use these formulae in a calculation field, since any deletion or addition of a tour would change the tokens of same-named tours. (Or you would have to sort the relationship by e.g. a date-stamp, so a tour would only see and count older tours by the same name.)

               

              Maybe it's better to use an auto-enter calculation for an ordinary (no-calc) text field, so you capture the state as of the creation of the tour record..

              1 of 1 people found this helpful
              • 4. Re: Trouble with Duplicates
                techxp

                Hello erolst,

                 

                I've created the Relationship and Linked the specified fields, changed the field from a Calculation into a calculated Text Field and edited the Formula:

                 

                It looks thusly now:

                 

                Case (

                  Let ( [

                    ExceptionList = List ( PL ) ; // could store this list in a Custom Function, or as records …

                    countOthers = Count ( Calculation_Same_TourName::__ID_ALLOCATION ) ;

                    isException = not IsEmpty ( FilterValues (lxt_Tour_Name ; ExceptionList ) ) ;

                    firstThree = Upper ( Left ( lxt_Tour_Name ; 3 ) )

                    ] ;

                    Case (

                      isException ; firstThree ;

                      countOthers ; Upper ( Left ( lxt_Tour_Name ; 2 ) & Middle ( lxt_Tour_Name ; 3 + countOthers ; 1 ) ) ;

                      firstThree

                    )

                )

                 

                However it seems I'm missing a parameter or maybe I've set one wrong. Any Ideas?

                • 5. Re: Trouble with Duplicates
                  erolst

                  techxp wrote:

                  However it seems I'm missing a parameter …

                   

                  Au contraire, you've got some stuff that doesn't belong there: the entire first line ( Case ( ) must go.

                   

                  Sorry, modified this after starting from a different approach – that happens when you don't test in the system …

                  1 of 1 people found this helpful
                  • 6. Re: Trouble with Duplicates
                    techxp

                    Thank you very much,

                     

                    it works I thank you again for your time spent helping me.

                     

                    Kindest Regards

                    Michael Mayrhofer