+ Start a Discussion

User defined logic filters

Hi - I have a client request I'm struggling with, and would value any ideas.


I have members (Member__c as parent) that take awards (Award__c as child). Awards expire after time and have to be updated.


Certain high level awards require a combination of other awards before the member can qualify for awards. The client has asked to be able to add simple logic that determines which awards are required to gain the higher awards.


So I've set-up 5 look-up fields so that the user can select up to 5 awards needed to qualify for the higher level award.

And created a text field to enter a logic expressions - something like (AwardA OR AwardB) AND AwardC - a bit like the 'Add Filter Logic' when setting up a workflow.


So what I need to do is to check through all of the valid awards held by the member, and see if they have the awards required with the specified logic.


I can use a formula and SUBSTITUTE commands to create a query string using the user enter logic - 


Award_Type4__r.Qualifications_Awards__c.Name='SLSGB Assessor Update' OR Award_Type4__r.Qualifications_Awards__c.Name='SLSGB IRB Assessor'


and I can build this into a full query string. 


I have tried putting this into a query, but as I need to query from the parent, I'm struggling to get it to work..


I tried this:


Member__c[] members = [Select id, (select id from Awards__r  WHERE ((Award_Type4__r.Qualifications_Awards__r.Name='SLSGB Assessor Update' OR Award_Type4__r.Qualifications_Awards__r.Name='SLSGB IRB Assessor') AND Member__c=:memberid)) from Member__c WHERE id=:memberid];


but seem to get 1 result each time regardless of the awards held by the Member.


I'm not even sure a SOQL is the correct approach - I did consider putting all of the valid awards held by the member into a set, then uses the set.CONTAINS() to see if the required awards existed, but not sure how to apply the logic requirement with this approach.


Any ideas ?


Many thanks.






I would be tempted to pull them out into a set as you say, since the SOQL could get confusing. 


(AwardA OR AwardB) AND AwardC


could be expressed as


(theSet.contains(AwardA) || theSet.contains(AwardB)) && theSet.contains(AwardC)


Let me know how you get on or if you want more help - it's an interesting problem


Thanks - but how do I get the user-defined logic string into an apex statement?


I know that I can use database.query(string) to run a SOQL query from a defined string, but how can I take the logic string and build that into a Set.Contains logic statement?


Do I have to take the string and  parse it one element at a time? 


Or is there a way to define a condition statement from a string  like If (CONDITION_STRING is TRUE) {} etc...?




Sorry, I was not considering how complex that would be. Actaully SOQL would be easier after all! (I sent that last reply too quickly without really thinking about it!)


I can probably help you with the SOQL but I need some more information.


Can you tell me how Member and Award are related? And what are Award_Type4 and Qualifications_Awards, and how are they related to Award?






Thanks again.


Member__c is a look-up on Award__c. So a member can have many awards.


 Award_Type4 and Qualifications_Awards are fields on Award__c - easier to think of it as


Member__c[] members = [Select id, (select id from Awards__r  WHERE ((Name='AwardA' OR Name='AwardB') AND Member__c=:memberid)) from Member__c WHERE id=:memberid];


In words what I was trying to do was query [Select a member with id=XYZ with valid awards (Award A or Award B)]  where the 'valid awards' part could be a complex user-defined logic such as ((Award A or Award B ) AND Award c). If the query returns no results, then the member does not have the required awards.




If Member__c is a look-up on Award__c then what happens when many members all have the same award?


I've been thinking about this, about how to generate the SOQL and it seems pretty hard. Here's a totally different idea you might want to explore:


You could give each award a score which is a power or 2


AwardA score = 1

AwardB score = 2

AwardC score = 4




Then put a rollup summary on the member adding up the scores of all their awards.


Then you can translate your user-defined logic to arithmetic :


(AwardA OR AwardB) AND AwardC becomes


((math.mod(Total/1,2) == 1) || (math.mod(Total/2,2) == 1)) && (math.mod(Total/4,2) == 1)




in SOQL format:


where ((math.mod(Total/1,2) == 1) or (math.mod(Total/2,2) == 1)) and (math.mod(Total/4,2) == 1)