locked
View for Accounts RRS feed

  • Question

  • Hi,

    I need to build a view that returns for me all the accounts that don't have activities on them for the last 30 days.

    I've put the following filter: where "Date Created" in "Activities(Regarding)" "older than X month" and i chose 1. So this gives me 30 or 31 days. I need to check if this is the right thing i did.

    Thanks in advance

     

    Monday, November 17, 2008 8:58 AM

Answers

  • Hi,

    What i did was adding a custom field "Last Activity Date" in Account which will be updated with a workflow each time an activity is created on the Account. That way i'll be able to build a query in Reporting Services that will allow me to calculate the time for which the account had no activities.

    Regards 

     

    Thursday, November 20, 2008 7:02 AM

All replies

  •  

    I don't think that you can do this with Advanced Find. Your query will return a list of accounts that have activities that are older than 1 month. If an account has no activities at all it won't be returned in the results and if an account has old activities as well as new activities it will still be returned in the results.

     

    What you need is a list of accounts that don't have activities in the last month.

     

    As far as I can see there is no way to express a negative relationship in Advanced Find. e.g. Accounts where there are no activities that match the filter (which in your case would be created in the last 30 days).

     

    I believe that this is why the Accounts: No Campaign Activities in the Last 3 months is a view that you can't edit. It is an internally defined view that can't be expressed using the Advanced Find interface.

     

    You will probably have to build a report for your query using SQL and filtered database views.

     

     

    Monday, November 17, 2008 1:55 PM
    Moderator
  • Hi,

    What i did was adding a custom field "Last Activity Date" in Account which will be updated with a workflow each time an activity is created on the Account. That way i'll be able to build a query in Reporting Services that will allow me to calculate the time for which the account had no activities.

    Regards 

     

    Thursday, November 20, 2008 7:02 AM