7 Replies Latest reply on Nov 12, 2016 7:25 AM by taylorsharpe

    Filtered List inside same Table

    Wicktor

      Hello everyone,

      I have TableA with some 150.000 records.

      VALUE_1 (stored) contains calculated numbers in a range between -20 up to 20 (step 0,1).

      More than one record may have the same value inside VALUE_1.

      I need to produce a calculated LIST_1 of unique values stored inside VALUE_1 (without a script).

      LIST_1 will be compared with LIST_2 inside TableB where value inside that range are unique.

      The final result would be a check if LIST_1 and LIST_2 have same values.

      Thank you for any help :-)

        • 1. Re: Filtered List inside same Table
          David Moyer

          Hi,

          for LIST_1, I think you'll need to set up a Value List based on related values in TableA.  That means you need to start from the context of the parent table of TableA.  Does a parent table exist?  Creating a value list (see the image) will provide a list of unique values from the child table (TableA).

          Capture.PNG

          • 2. Re: Filtered List inside same Table
            David Moyer

            I didn't address "inside the same table" - this would require a self-join.  This can be done in a few different ways, depending on what group of records you want to evaluate.

            If you want to evaluate all of the records in the table, you don't need a self-join.  You can do that with this setting:

            Capture.PNG

            1 of 1 people found this helpful
            • 3. Re: Filtered List inside same Table
              Wicktor

              David Moyer

              That works !

              Only problem is that LIST_1 and LIST_2 do not match: as you suggested value LIST_1 is in TableA automatically sorted, I need a way to sort values in the same way in TableB where LIST_2 comes from 4 different repeating fields.

              • 4. Re: Filtered List inside same Table
                David Moyer

                you can probably find a custom function out there to sort a list.  I used to know how to write a recursive function for sorting - it'd take a while to recreate.  (The custom functions I saw require other custom functions to work.)

                • 5. Re: Filtered List inside same Table
                  Wicktor

                  David Moyer

                  I made it without custom function!

                  Just in case you need:

                  I used the same approach you suggested for LIST_1, i.e. I made 4 LISTS of each repeating field in TABLEB and combined into LIST_2. The two LISTS match.

                  • 6. Re: Filtered List inside same Table
                    erolst

                    Wicktor wrote:

                    Only problem is that LIST_1 and LIST_2 do not match

                    If all you want to know is whether there are values common to two lists, use

                     

                    not IsEmpty ( FilterValues ( List1 ; List2 ) )

                     

                    If you want to see these values (if any), use just

                     

                    FilterValues ( List1 ; List2 )

                    1 of 1 people found this helpful
                    • 7. Re: Filtered List inside same Table
                      taylorsharpe

                      Below is the array script I use.  It first tries to use the MBS sort function, but if MBS isn't installed it does a looping FM script.  As previously mentioned, you could alternatively do it in a custom function.

                       

                       

                       

                      # ========================================================================================================

                       

                      # by Taylor Sharpe, www.TaylorMadeServices.com, ©April 2015

                      # Takes an Array and Sorts it

                       

                      # ========================================================================================================

                       

                      Set Variable [ $SP ; Value: Get ( ScriptParameter ) ]

                       

                      # If First Row doesn't scribe the Field Type, then make $SP the whole array

                      Set Variable [ $SortType ; Value: GetValue ( $SP ; 1 )   //  e.g., Text, Number, Date, Timestamp ]

                      If [ Case ( $SortType = "Text" ; 0 ; $SortType = "Number" ; 0 ; $SortType = "Date" ; 0 ; $SortType = "Timestamp" ; 0 ; 1 ) ]

                        Set Variable [ $SortType ; Value: "Text" ]

                        Set Variable [ $Array ; Value: $SP ]

                      Else

                        Set Variable [ $Array ; Value: MiddleValues ( $SP ; 2 ; ValueCount ( $SP ) ) ]

                      End If

                       

                      Set Variable [ $MBSVerify ; Value: MBS("Version") ]

                       

                      If [ $SortType = "Text" and $MBSVerify ≠ "?" ]

                        If [ PatternCount ( Get ( ApplicationVersion ) ; "Server" ) = 1 ]

                        Set Variable [ $MBS Registration ; Value: MBS("Register"; "•••••••••••"; "Complete"; "Server"; #########; -#########) ]

                        Else If [ PatternCount ( Get ( ApplicationVersion ) ; "Pro" ) = 1 ]

                        Set Variable [ $MBS Registration ; Value: MBS("Register"; "•••••••••••"; "Complete"; "20 seats"; ##########; ##########) ]

                        End If

                        Set Variable [ $ArraySorted ; Value: MBS ( "List.Sort" ; $Array ; 0 ; 0 ) ]

                        Exit Script [ Text Result: $ArraySorted ]

                      End If

                       

                      Set Variable [ $ArraySorted ; Value: "" ]

                      Set Variable [ $Loops ; Value: ValueCount ( $Array ) ]

                      Set Variable [ $Loop ; Value: 0 ]

                       

                      Loop

                       

                        Set Variable [ $Loop ; Value: $Loop + 1 ]

                        Exit Loop If [ $Loop > $Loops ]

                       

                        Set Variable [ $Record ; Value: GetValue ( $Array ; $Loop ) ]

                        Set Variable [ $LoopsC ; Value: ValueCount ( $ArraySorted ) ]

                        Set Variable [ $LoopC ; Value: 0 ]

                       

                        If [ $LoopsC = 0 ]

                        Set Variable [ $ArraySorted ; Value: $Record ]

                        Else

                       

                        Loop

                       

                        Set Variable [ $LoopC ; Value: $LoopC + 1 ]

                        Exit Loop If [ $LoopC > $LoopsC ]

                       

                        Set Variable [ $RecordC ; Value: GetValue ( $ArraySorted ; $LoopC ) ]

                       

                        If [ Let ( [  F1 = $SortType ; F2 = $Record ; F3 = $RecordC ; F4 = Case ( F1 = "Text" ; GetAsText ( F2 ) ; F1 = "Number" ; GetAsNumber ( F2 ) ; F1 = "Date" ; GetAsDate ( F2 ) ; F1 = "Timestamp" ; GetAsTimestamp ( F2 ) ; F2 ) ; F5 = Case ( F1 = "Text" ; GetAsText ( F3 ) ; F1 = "Number" ; GetAsNumber ( F3 ) ; F1 = "Date" ; GetAsDate ( F3 ) ; F1 = "Timestamp" ; GetAsTimestamp ( F3 ) ; F3 ) ;  F6 = If ( F4 < F5 ; 1 )  ] ;  F6  )      // $Record < $RecordC ]

                        Set Variable [ $Before ; Value: Let ( [  F1 = LeftValues ( $ArraySorted ; $LoopC - 1 ) ; F2 = If ( Right ( F1 ; 1 ) = ¶ ; Left ( F1 ; Length ( F1 ) - 1 ) ; F1 )  ] ;  F2  ) ]

                        Set Variable [ $Middle ; Value: $Record ]

                        Set Variable [ $After ; Value: MiddleValues ( $ArraySorted ; $LoopC ; ValueCount ( $ArraySorted ) ) ]

                       

                        Set Variable [ $ArraySorted ; Value: $Before & If ( not IsEmpty ( $Before ) and not IsEmpty ( $Middle ) ; ¶ ) & $Middle & If ( not IsEmpty ( $Middle ) and not IsEmpty ( $After ) ; ¶ ) & $After ]

                        Exit Loop If [ 1 ]

                       

                        End If

                       

                        If [ $LoopC = $LoopsC ]

                        Set Variable [ $ArraySorted ; Value: Let ( [  F1 = $ArraySorted ;  F2 = $Record ; F3 = If ( Right ( F1 ; 1 ) = ¶ ; Left ( F1 ; Length ( F1 ) - 1 ) ; F1 ) ; F4 = F3 & ¶ & F2  ] ;  F4  ) ]

                        Exit Loop If [ 1 ]

                        End If

                       

                        End Loop

                       

                        End If

                       

                      End Loop

                       

                      // Show Custom Dialog [ "Array" ; $ArraySorted ]

                       

                      Set Variable [ $ArraySorted ; Value: Let ( [  F1 = $ArraySorted ; F2 = Right ( F1 ; 1 ) ; F3 = If ( F2 = ¶ ; Left ( F1 ; Length ( F1 ) - 1 ) ; F1 )  ] ;  F3  ) ]

                       

                      Exit Script [ Text Result: $ArraySorted ]