2 Replies Latest reply on Apr 20, 2013 6:04 AM by disabled_JohnWolff

    Need Advice on best approach to Compare two Found Sets

    jlb

      Background:

      Developing a Marketing Campaign Manager tool for our executive team. A subset of this project allows users to build a list of the Campaign Recipients that will be targeted for each specific campaign.

       

      I already have built the portion of the tool that gathers the user inputs and executes the complex/multiple search criteria. The issue I am now running into this:

       

      Scenario Example:

      Search 1 - User inputs their seach criteria which in this case is based on our Contacts table (segmented by "x", "y", "z" ). Result of Found Set is 35,000

      Search 2 - User inputs additional seach criteria which in this case is based on our Invoice Lines table (anyone who bought product "x" between date range ). Result of Unique Contacts in this Found Set is 5,000.

       

      Followup Action -

      Sometimes the user wants to include only the Contacts that appear in both Found Sets.

      Other times, they want only the Contacts that are in Search 1, but not 2.

      And of course, the opposite - Only Contacts that are in Search 2, but not in 1.

       

      I can script this followup action with my user interface/selection tool, but where I am at a loss is what is the best approach for peforming this action.

       

      Speed is the number one issue here. I cannot have the management team waiting for that elusive beachball to stop spinning. Please keep in mind that the found set size of each search will vary depending on the user selections made.

      I have tried several methods, all of which will end up producing the result I desire - but at a price (speed). Some methods work well, but I run into recursion issues when data sets get too large.

      I need suggestions that work for large found sets. It is critical that the user doesn't inadvertently freeze up the system/their cptr when trying to execute this final piece of the puzzle.

       

      Any approaches are welcome (ExecuteSQL, export/import, repeating fields, multikey lists, applescript, custom functions.)

       

      Thanks in advance. JLB

        • 1. Re: Need Advice on best approach to Compare two Found Sets
          gdurniak

          you could just "Add New Record / Request" in Find Mode to add ( or omit ) a second requirement ( and this can be scripted )

           

          if this is too slow, look at the fields used in the "find"

           

          I suspect many are not "stored" or indexed

           

          if not, you can try finding first on any indexed fields, then "constrain found set" based on the unstored calcs

           

          but with 35,000 found records, most other "tricks" may be just as slow

           

          greg

           

          > Sometimes the user wants to include only the Contacts that appear in both Found Sets.

          Other times, they want only the Contacts that are in Search 1, but not 2

          • 2. Re: Need Advice on best approach to Compare two Found Sets

            Hi JLB,

             

            Have you looked at building return separated lists of the record ID's from each found set, then use Bruce Robertson's AntiFilter Custom Function for the comparisons?

             

            http://www.briandunning.com/cf/926

             

            Bruce claims it is fast over thousands of records but you'd need to test that for yourself. It also avoids the recursion limit.

             

            I HTH,

             

            John