Workflow Nightmares

Well, I have been waking up at night with this workflow problem bouncing around in my head. Usually when I get a tough one like this I file it away in my subconscious processor and I'll wake up at 3am with some new idea about how to approach it. So far, I haven't had too much luck with this one:

My client needs a way (a quick and easy way, it goes without saying) to look up leads in CRM that haven't been contacted in certain time frames. The salespeople were accustomed to using ACT and doing lookups for leads that were old, but not that old, or in some cases, they might look for leads that were last reached more than a year ago -- the time frame varies.

There's no doubt, though, that this is a very useful kind of search for a salesperson. They have their own leads to work, but the database is also set up so that a lead is free unless it has been called on, in which case it becomes a protected lead and gets assigned to the salesperson who called the lead. After 30 days if they haven't called the lead, they get a task in CRM that tells them to call the lead again or it will go back in the shark tank in 5 days.

The whole lead life cycle I have pretty well hammered out with workflow. It's not perfect, and won't be until there are some major upgrades in CRM's Workflow Manager.

Now, you may be wondering what this lookup dilemma has to do with workflow: Well, I basically have two options for doing complex queries on the database: Use the SDK to create something from scratch or use an add-on like c360's SearchPac. Well, I have SearchPac on another CRM deployment and I tried experimenting with this kind of lookup. Joined the leads with Activities of the Phone Call type, set dates, etc. The problem is that CRM uses Fetch to query the db and Fetch currently has no mechanism for searching null values, so the queries never quite can be built the way I would need them to be.

As far as the SDK goes, I am not enough of a wizard to build my own search utility for CRM. And the problem is that the client needs this done quickly. We are at the point where salespeople are doing searches in their old ACT database in order to find leads to call. Ugly!

So here's where the workflow comes in. I have an inkling that if I create a custom picklist for the lead record and incorporate updates to this field in my workflow, I could add a column to the Leads Advanced Find view that would show the salespeople what they want. The picklist would have values like:
-Less than 1 month ago
-1 to 2 months ago
-2 to 3 months ago
-3 to 6 months ago
-6 to 12 months ago
-More than a year ago

At the point in the workflow where a lead stays protected, the field would be updated to "Less than 1 month ago" (because for the lead to stay protected it has to have been contacted within the last month).

Then, at the point in the workflow where the lead reverts to the sharktank I could put in Wait commands so it would wait 30 days and change the field to "1 to 2 months ago" then wait another 30 days and change to "2 to 3 months ago" etc.

I think this is going to work. I really need to build a test environment though so I can play with this. Then I need to change the time values so I can test it speeded up to see it work. Oh, what joy! Well, I'm going to get started. I have my work cut out for me.

I'll keep you posted!