locked
Can I get a view of Accounts that don't have an Activity scheduled in future RRS feed

  • Question

  • Hello All,

    I have a request to see a list of all accounts that don't have an activity schedule against them. We do a lot of telemarketind as such almost all of our active accounts should have an activity (Phone Call) scheduled at some point in the future. We'd like to see a list of accounts that don't have an activity due in the future so that so that we can go create one.

    I've had a look at advanced find but I don't see a way to filter on "Does not exist" or similar.

    Any suggestions are appreciated.

    Thanks,
    Jason
    Monday, January 18, 2010 2:01 AM

Answers

  • Hi, Jason.

    It seems that it easier to use report for your task. SQL Query for your report will be like:

    Select * From FilteredAccount a
    Where Not Exists (Select * From FilteredActivityPointer Where RegardingObjectId = a.accountid and DueDate > GetDate())

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page

    Better yet:

    Select *
      from FilteredAccount acct
      left join (Select * From FilteredActivityPointer Where scheduledstart> GetDate()) apt
       On apt.RegardingObjectId = acct.accountid
     Where apt.RegardingObjectId is null

    The other looks like a correlated subquery which will hammer your performance.
    Monday, January 18, 2010 10:40 PM

All replies

  • Hi Jason,

    As you alreay knew, MS CRM doesn't provide NOT IN or Does NOT Exist Condition Operator in Advance Find.
    I think you have to write a custom ASPX pages to achieve the functionality.
    Thanks, Ranjitsingh R | http://mscrm-developer.blogspot.com/ | MS CRM Consultant
    Monday, January 18, 2010 4:28 AM
  • Hi, Jason.

    It seems that it easier to use report for your task. SQL Query for your report will be like:

    Select * From FilteredAccount a
    Where Not Exists (Select * From FilteredActivityPointer Where RegardingObjectId = a.accountid and DueDate > GetDate())

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Monday, January 18, 2010 4:30 AM
    Moderator
  • Hi Andriy,

    I think this is what I need. I tried your SQL query but it says that DueDate doesn't exist. I don't see that field in the FilteredActivityPointer view and I'm not sure which field to use. Can you specify which field in FilteredActivityPointer represents the due date of the activity?

    Thanks for your help,
    Jason
    Monday, January 18, 2010 12:33 PM
  • Hi Jason,
    Here is an idea for you that wouldn't require work outside of CRM.
    Add 2 date attributes to your account entity - 'phone call planned', and 'last phoned'.
    Create a workflow against the phonecall activity that fires on create that updates the 'phone call planned' attribute with the 'start date' (use update Regarding (Account) related entity). You might also want this to fire on change of the start/end date attributes incase someone re-schedules a call.

    Create a second workflow against the phonecall activity that fires on status change (complete) which updates the 'last phoned' date with the 'actual end' date.

    You will now have two date fields on the account that you can use advanced find to easily search on all accounts where 'phonecall planned' is either null or on or before a certain date. Same with the actual 'last phoned' date to monitorif an account has been neglected.

    Hope that helps,

    Rob

    P.S. you might need to run the first workflow manually to begin with to populate the accounts' 'phone call planned' field. Just run an advanced find on all open phone calls and run the workflow.

    CRM4 MBSS
    Monday, January 18, 2010 10:13 PM
    Answerer
  • Hi, Jason.

    It seems that it easier to use report for your task. SQL Query for your report will be like:

    Select * From FilteredAccount a
    Where Not Exists (Select * From FilteredActivityPointer Where RegardingObjectId = a.accountid and DueDate > GetDate())

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page

    Better yet:

    Select *
      from FilteredAccount acct
      left join (Select * From FilteredActivityPointer Where scheduledstart> GetDate()) apt
       On apt.RegardingObjectId = acct.accountid
     Where apt.RegardingObjectId is null

    The other looks like a correlated subquery which will hammer your performance.
    Monday, January 18, 2010 10:40 PM
  • Hi, Jason.

    It seems that it easier to use report for your task. SQL Query for your report will be like:

    Select * From FilteredAccount a
    Where Not Exists (Select * From FilteredActivityPointer Where RegardingObjectId = a.accountid and DueDate > GetDate())

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный) free counters Locations of visitors to this page

    Better yet:

    Select *
      from FilteredAccount acct
      left join (Select * From FilteredActivityPointer Where scheduledstart> GetDate()) apt
       On apt.RegardingObjectId = acct.accountid
     Where apt.RegardingObjectId is null

    The other looks like a correlated subquery which will hammer your performance.
    Hi mardukes,

    That is pretty much what I'm looking for, this gives me a list of accounts that don't have an activity scheduled. I tried to filter the results based on the customertypecode field so that I can exclude records with a value of 3 (Customer) but adding NOT=3 to the filter column in the query builder made the query show the inverse of what I want - I got a list of customers that don't have an activity scheduled.

    Can you show me where I would add this WHERE clause to your SQL query? My SQL isn't that strong.

    Thanks,
    Jason
    Tuesday, January 19, 2010 2:06 AM
  • to the end

    and customertypecode<>3
    Tuesday, January 19, 2010 5:45 PM
  • That's awsome, thanks for that. I've marked your first post as an answer.
    Kind regards,
    Jason
    Tuesday, January 19, 2010 10:51 PM
  • The 'quick and dirty' solution is to use a marketing list. Add in all accounts and remove those with activities. The list is static so you'll need recrate to update it.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/

    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Friday, January 22, 2010 8:38 PM
    Friday, January 22, 2010 8:38 PM