I posted about this a few weeks ago, but I got sidetracked for a while. I
haven't been able to figure this out yet, and I'm starting to think it may
not be possible. I am looking for any and all creative suggestions. TIA.
My Sales users need a way of finding leads that have not been contacted
since x date. For example, they want a quick way to lookup all leads with no
phone call activities between 11/1/2004 and today.
I tried c360 SearchPac and no matter how I build a query, it returns all
leads with phone calls that fall outside of the dates I have selected,
regardless of whether there is a call during the timeframe in question. In
addition, it lists a lead every time there is a phone call outside of those
dates, so if there were 20 calls, the lead is listed 20 times.
My next attempt was at building a Crystal Report with parameters. I am
basically having the same problem. If a lead has a call today it shows up --
apparently because it also has calls before 11/1/2004.
I haven't been able to get it to work right yet. I am stuck. Any ideas? Is
this a limitation of Fetch XML? Here's the query that I built in Crystal:
<fetch version="1.0" output-format="xml-platform" mapping="logical">
<entity name="lead">
<attribute name="fullname" />
<attribute name="statuscode" />
<attribute name="ownerid" />
<attribute name="leadid" />
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="description" />
<order attribute="fullname" descending="0" />
<link-entity name="phonecallactivity" alias="phonecallactivity" to="leadid"
from="objectid" link-type="inner">
<attribute name="actualend" />
<attribute name="objectname" />
<attribute name="ownerid" />
<attribute name="scheduledend" />
<attribute name="title" />
<attribute name="activitytypecode" />
<filter type="and">
<filter type="or">
<filter type="or">
<condition attribute="actualend" operator="lt"
value="2004-11-01T00:00:00" />
<condition attribute="actualend" operator="ge"
value="2004-12-22T00:00:00" />
</filter>
<filter type="or">
<condition attribute="scheduledend" operator="lt"
value="2004-11-01T00:00:00" />
<condition attribute="scheduledend" operator="ge"
value="2004-12-22T00:00:00" />
</filter>
</filter>
<condition attribute="activitytypecode" operator="eq" value="137" />
</filter>
</link-entity>
</entity>
</fetch>
--
Matt Wittemann
http://icu-mscrm.blogspot.com