Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

No Activity for last 30 days

Jump to Best Answer
  • 1.  No Activity for last 30 days

    SILVER CONTRIBUTOR
    Posted 12 days ago
    Hi All!

    I'm looking for advice on creating a solution for the following:
    We've been asked to send an email to users if they have a lead that hasn't been contacted in the last 30 days. How would we query for the absence of an activity?

    I feel like I'm missing something easy :)

    Thanks!

    ------------------------------
    Kaitlin Burns
    Application Developer
    CareSource
    Dayton OH
    ------------------------------
    Conference-CRMUG_200x200


  • 2.  RE: No Activity for last 30 days
    Best Answer

    Posted 12 days ago
    Hi Kaitlin,

    There are multiple ways to achieve this, one of my favorite way is by creating a date field that will capture the last activity date on lead entity and then using a flow check to see if the date is 30 days old, if yes then send an email to the owner.

    Create Last activity date: https://blog.crgroup.com/how-to-display-the-date-of-last-activity-in-dynamics-crm/

    Just make a note of one thing, that you also have to create another process to update the last activity date with the date lead is created so that the date field is not empty if no activity is done on that. And if any activities are performed that field will be updated by the date of activity.

    Feel free to ask, if you have any questions.

    Please let me know if you need assistance with any of the steps. I am always happy to help the community.

    Thanks
    Jay

    ------------------------------
    Jay Buddhdev
    https://jaybuddhdev123.wixsite.com/powerapps365
    Regina SK Canada
    ------------------------------

    Conference-CRMUG_200x200


  • 3.  RE: No Activity for last 30 days

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Thanks @Jay Buddhdev​ ! I like this solution!

    ------------------------------
    Kaitlin Burns
    Application Developer
    CareSource
    Dayton OH
    ------------------------------

    Conference-CRMUG_200x200


  • 4.  RE: No Activity for last 30 days

    Posted 6 days ago
    Thanks @Kaitlin Burns, feel free to let me know if you need any assistance in customizing. I am always happy to help the community at no extra cost.

    Please mark my answer as solution, if it was helpful to you.


    ------------------------------
    Jay Buddhdev
    https://jaybuddhdev123.wixsite.com/powerapps365
    Regina SK Canada
    ------------------------------

    Conference-CRMUG_200x200


  • 5.  RE: No Activity for last 30 days

    TOP CONTRIBUTOR
    Posted 12 days ago
    Kaitlin,

    Obviously you can go path Jay suggested but in involves customizations and if you don't plan to use this feature again it's overkill.
    I have alternative way:
    1. Create a static marketing list with "contact" type.
    2. Add all contacts to it.
    3. Use "Manage Members" -> Remove using Advanced Find to remove users with activities during last 30 days.

    ------------------------------
    Andrew Butenko
    ------------------------------

    Conference-CRMUG_200x200


  • 6.  RE: No Activity for last 30 days

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Thanks @Andrew Butenko! This would be good for a one time or occasional pull. Great info! ​

    ------------------------------
    Kaitlin Burns
    Application Developer
    CareSource
    Dayton OH
    ------------------------------

    Conference-CRMUG_200x200


  • 7.  RE: No Activity for last 30 days

    MICROSOFT MVP
    Posted 11 days ago
    @Kaitlin Burns - If you use the newer connector within Power Automate called "Common Data Service (current environment)", you can use a FetchXml query to filter the Accounts within the "List Records" action (see below example). However, you will need to set up the flow in Power Automate with a Schedule trigger.

    So, maybe, once a week, the flow is triggered and pulls the records impacted. You will need to get the Owner (Users entity) of the records to get the Primary Email address.

    I would suggest putting the Accounts for each Owner into an HTML table in the flow which can then be embedded into the outgoing email (using the Office 365 Outlook action).



    You can use the "FetchXml Builder" tool within the XrmToolBox to build out the FetchXml query.

    You could use the following FetchXml. This will display records without activities for the last 30 days. Additionally, if you want to sub-set test this, you can add in 
    top="10"​
    within the first line of the fetch tag.
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
      <entity name="account" >
        <attribute name="name" />
        <attribute name="address1_city" />
        <attribute name="primarycontactid" />
        <attribute name="telephone1" />
        <attribute name="accountid" />
        <order attribute="name" descending="false" />
        <filter type="and" >
          <condition attribute="statecode" operator="eq" value="0" />
        </filter>
        <link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="accountprimarycontactidcontactcontactid" >
          <attribute name="emailaddress1" />
        </link-entity>
        <link-entity name="activitypointer" from="regardingobjectid" to="accountid" link-type="outer" alias="ab" />
        <filter type="and" >
          <condition entityname="ab" attribute="createdon" operator="olderthan-x-days" value="30" />
        </filter>
      </entity>
    </fetch>


    ------------------------------
    Aaron Back
    Microsoft MVP | Sr. Microsoft Dynamics 365 Consultant
    ACE Microtechnology
    _______________________________________
    CRMUG Board Member
    CRMUG Chapter Leader - Cincinnati, Ohio
    ------------------------------

    Conference-CRMUG_200x200


  • 8.  RE: No Activity for last 30 days

    TOP CONTRIBUTOR
    Posted 11 days ago
    @Aaron Back I thought of the same approach but it's not so user friendly and obvious. One more thing - your FetchXml would not return contact if he/she was in from/to fields and not in regardingobject. There should be the link to activityparty.

    ------------------------------
    Andrew Butenko
    ------------------------------

    Conference-CRMUG_200x200


  • 9.  RE: No Activity for last 30 days

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Thanks @Aaron Back for this in depth explanation!! I realized that when we worked with PowerObjects we got the Last Activity plugin they offer, so I should be able to use this to see if the Last Activity is older than 30 days. ​

    ------------------------------
    Kaitlin Burns
    Application Developer
    CareSource
    Dayton OH
    ------------------------------

    Conference-CRMUG_200x200


  • 10.  RE: No Activity for last 30 days

    TOP CONTRIBUTOR
    Posted 7 days ago
    @Kaitlin Burns

    You can query by the createdOn or modifiedOn of the lead record. Unfortunately there is nothing that would capture that the user "viewed" the record. Although you can probably put a countdown timing mechanism that would update something in the record when the lead is loaded on screen.

    Cheers!

    ------------------------------
    Rex Kenley Tan, MCSA, MCSD
    Tallmadge OH
    https://www.youracclaim.com/users/rex-kenley-tan

    *Always be CURRENT with JavaScript & C#, NEVER be obsolete.

    DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
    ------------------------------

    Conference-CRMUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role