Best practices for writing queries for user-defined business rules

Robin Richardson

Where I worked before I had user-defined business rules set up for a number of reasons: special handling (contact solicitor if changing record, etc.), does not want thank-you letters. I had these rules run based on a query that was based on "one of" a set of constituent ID #'s. I also put notes in the records about the special circumstances. Now at my new org, I wanted to add a constituent to the "special handling" rule and was surprised to find out how my predecessor set up the query. It's based on "Notes", "Specific Notes" and

"Special Handling Import ID." It seems more complicated than it needs to be. I'm now wondering how others set up their queries for business rules?

Nicole S

It really depend on what the business rule is for.  If it's more appropriate to set up the query to look at specific records, then I will do that.  But I often use dynamic queries to automatically catch certain records.  As long as the query is pulling the correct records, then use what works.

John Heizer

You could also try to Annotate specific records rather than have them in a query.

JoAnn Strommen

Our business rules are based on data in a specific field on the record.  Personally my first choice would not be to do one by constituent ID as the query would require manual updating / extra work to add those IDs.  The info for special handling is going to be recorded somewhere on the record so why not just query for records with that data in that place.  Examples of our business rules: look for check/yes in deceased field / received ABC appeal / gift amount for fund XYZ greater than $X.  None require manual entry of a record to a query - info is just part of record.  Sounds like your predecessor may have entered data in more than one field of the records if he/she included several in the query used for the business rule.  



As Nicole said, as long as query is pulling correct records you should be good.

Christine Cooke 2

I agree with others that using dynamic queries that look for information in particular field(s) is the best way to go, rather than having to manually updating queries with constit IDs.  Less error in my book.  I use business rules to show folks that are in a particular group:

-deceased but spouse is not or both are deceased

-current Board Member

-address is invalid but phone and/or email is current (for those not as familiar that do not remember to look and see)



Then I annotate things at a higher level that are very donor specific:

-special handling, like how to approach or not

-never ever ever contact this person

-the only person who can contact this person is...

-serious illness in the household, do not disturb for X amount of time or until the annotation is removed



If the special handling has to do with appeals and who gets what when I use Solicit Codes.

-requests no mail

-requests no solications

-requests no invitations

-appeal once a year in fall

-appeal once a year in spring

-requests no phone calls