9 Replies Latest reply on Nov 7, 2016 11:31 AM by realgrouchy

    Compare list A to List B

    steveoh

      Hey FM Devs

       

      I've been working on a script that looks for any common links between two lists and so far my only method is quite cumbersome and in need of rethinking.  My database consists of 16000 customers and there are times when i need to associate a particular customer to a separate list we will call List B.

       

      If Customer list = List A and the associated list is List B, what is the best method to find if a customer on List A exists on List B?

       

      MY CURRENT SETUP

      I tried using Patterncount ( [Record from list A] ; List B ) and going through the shorter list one by one, but this clearly did not work as I had expected it to due to my fundamental misunderstanding of pattern counting with lists.

       

      This failure then evolved into my current long and tedious loop driven method of having each entry of List A compared with every entry of List B.  Again, highly tedious and time consuming, but it works, which is what I needed.

       

      Is there a method to cut down the comparison rate that I am unaware of?  Perhaps Patterncount has a cousin that is more useful with lists.

       

      Any advice would be appreciated.

        • 1. Re: Compare list A to List B
          erolst

          steveoh wrote:

          Perhaps Patterncount has a cousin that is more useful with lists.

           

          Indeed; use

           

          FilterValues ( ListB ; ListA )

           

          The resulting list are the elements of A that are also in B.

          1 of 2 people found this helpful
          • 2. Re: Compare list A to List B
            monkeybreadsoftware

            A quick look on our List functions   shows we have List.FindEquals and List.FindUnequals.

            So you can find which entries are in both lists or find the ones not in the second list.

            1 of 1 people found this helpful
            • 3. Re: Compare list A to List B
              steveoh

              Thank you for your comment, I was aware of filtervalues, but didn't know it could be used like that.

              • 4. Re: Compare list A to List B
                ucharles

                How would I use FilterValue to get the result of A that are not in B or the vice versa ?

                • 5. Re: Compare list A to List B
                  philmodjunk

                  ucharles wrote:

                   

                  How would I use FilterValue to get the result of A that are not in B or the vice versa ?

                  You wouldn't really. You'd need to create your own custom function, download one from a CF sharing site or use a plug in that offers that capability.

                   

                  A script could also loop through the values and append them to a list if the following expression is true:

                   

                  IsEmpty ( FilterValues ( ListBeingSearched ; GetValue (ListOfValuesUsedInSearch ; $K ) ) )

                   

                  Your loop would increment $K with each iteration and exit when $K > ValueCount ( ListOfValuesUsedInSearch )

                  • 6. Re: Compare list A to List B
                    ucharles

                    Thanks, but I seem to have been confused more. I have two tables, one in my FileMaker solution and another is external. Both tables have the same attributes.: Id and name. i want to compare and find out which records are in both and which are noly in one or the other.

                    • 7. Re: Compare list A to List B
                      steveoh

                      ucharles wrote:

                       

                      Thanks, but I seem to have been confused more. I have two tables, one in my FileMaker solution and another is external. Both tables have the same attributes.: Id and name. i want to compare and find out which records are in both and which are noly in one or the other.

                      Actually, I found another way.  You create a text dump field that is basically all of the values from the list, just minus the carriage returns.  For example,

                       

                      listItem1

                      listItem3

                      listItem3

                       

                      Becomes:

                      listItem1 listItem2 listItem3

                       

                      Then it's a simple matter of using PatternCount ( ) to find the matches.  It's light weight and doesn't involve heavy loop scripts.  It can also be compiled with script triggers looking for modifications.

                       

                      For example, lets say we want to remove listItem2, you would simply use:

                      Substitute ( DumpTextFieldName ; ["listItem2" ; ""]; ["  " ; " "] ) to clear it from the list and remove any possible double spaces left over.

                      • 8. Re: Compare list A to List B
                        philmodjunk

                        ucharles wrote:

                         

                        Thanks, but I seem to have been confused more. I have two tables, one in my FileMaker solution and another is external. Both tables have the same attributes.: Id and name. i want to compare and find out which records are in both and which are noly in one or the other.

                        Is it possible that you replied to the wrong thread? I see posts from you about this subject in two different threads. Both discuss lists of data but in two different contexts. Here, we aren't discussing a "list" of data where each item in the list is a row in the table, but rather a set of values all in one field or variable separated by return characters. That's where FilterValues and these custom functions become useful to the discussion in this thread.

                        • 9. Re: Compare list A to List B
                          realgrouchy

                          ucharles wrote:

                           

                          How would I use FilterValue to get the result of A that are not in B or the vice versa ?

                          I had the same problem. I wanted to compare two checkbox sets to find out where the differences were.

                           

                          See the "ExcludeValues" custom function I posted at FileMaker Custom Function:RemoveValue ( Value ; ValueList )  in the comment dated November 7, 2016.

                           

                          I'm posting the link to the custom function page instead of copying the function here in case my comment is followed up by more helpful examples.

                           

                          - RG>