Need Advice on best approach to Compare two Found Sets

Discussion created by jlb on Apr 19, 2013
Latest reply on Apr 20, 2013 by disabled_JohnWolff


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