Filter multiple lists

[argwallace]

Hello,

 

I have about six variables that contain  a list of record keys. I need to filter these 6 lists so that only those keys that are in all of them remain. I am guessing I would use the FilterValues function but is there an easy way to do this?

 

Regards!

 

Stephen

[comment]

Could you explain in more detail what this is about? As it stands, your question has too many possible answers.

 

Note that "about six variables" is fundamentally different from "exactly six variables". The latter can be dealt with nesting the FilterValues() function, the former would require a custom function or a looping script.

 

Note also that if the result is going to be used as the matchfield in a relationship, then having duplicates in it is of no consequence. You cannot fetch the same record more than once - which incidentally, is another way to de-dupe such list.

 

However, I suspect your primary concern should be how did you get to a situation where you need this.

[argwallace]

Sure, thanks for the reply.

 

Depending on what the user selects, I am running up to 6 ExecuteSQL functions where the result is a list of the keys for those records into separate variables. All of the keys will be from the same table. Once I have the keys, I will be using a looping script to create new records in a different table using these keys as the foreign keys in that table.

 

Thanks.

[comment]

Once I have the keys, I will be using a looping script to create new records in a different table using these keys as the foreign keys in that table.

 

I don't think that answers my question, which was really about why are you doing all that. Technically, since you seem to be in the context of a script, you could filter the lists sequentially - either every time you get a new one through another ExecuteSQL, or at the end in a loop. Or place a concatenation of all lists in a global field, and use a relationship to get a list of distinct values through the List() function.

 

But as I said, I suspect the real answer lies elsewhere, because the real problem does too.

Edited February 24, 2014 by comment

[argwallace]

Hello again. I am trying to avoid creating a new relationship but if that is the best way, then I will go that route. This is my first effort to working with the ExecuteSQL function. The why I am doing this is this: I have a contacts database that users are able organize by the creation of groups. I am attempting to make a smart group feature that will allow the user to select certain criteria (currently this is where I have 6 options, but there may be more). As the user selects one or more of these criteria, the script would locate any matching records using the ExecuteSQL function to gather the keys. Since I need to have all the criteria met, hence the need to filter the lists sequentially to maintain only those records that meet all the criteria. Once I have a set of keys, I would then go to the groups line items table and create records there based on these keys.

 

If I have missed your question still, my apologies. Please rephrase.

 

Thanks!

[comment]

The problem I see with your approach is this: a "smart group" should be dynamic (otherwise it's not that smart). This means that the creation of a smart group should end by selecting the criteria by which a contact is considered to be a member. If a new contact is added, or an existing contact is modified or deleted, the inclusion or exclusion of any such contact should be automatic.

 

Your extra step of creating "line items" (i.e. group membership) records is in direct contradiction with the above requirement. The "real" question here, IMHO, is what is the best way to store the group's criteria and how to use them in order to locate the group's members when required (these two work together, of course).

[argwallace]

You are correct in the idea of "dynamic" smart groups as used in other programs. This is an option that I considered so perhaps my terminology may be a misnomer in this case. I was wanting to create a group with records based on a set of criteria, and at this point having the dynamic nature of the group is not required. Let's ignore the use of "smart". What would be the easiest way to accomplish this task?

[comment]

I would probably perform a find in the Contacts table, based on the supplied criteria. Then either import the found set into the GroupMemberships table, or loop over the found set and create a related record for each. I don't quite see the attraction of SQL in this situation.

[argwallace]

Thanks for the feedback. I definitely want to make it simple and performing a find might be easier than SQL statements. I've been drawn to them recently to see how they might work. So that was one of my options. Thanks again!