Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Workflow to do something based on count of records by user and date range?

  • 1.  Workflow to do something based on count of records by user and date range?

    TOP CONTRIBUTOR
    Posted Apr 29, 2019 10:43 AM
    ​​

    Good afternoon everyone

    We need to be able to have a notification sent out when any user reaches a count of x number of records created in last y days.  I've got a workflow that creates records in a custom entity, which is simply Createdon and User, but each time need to say CHECK if count of records for this user where date is in last 30 days, send email to someone. 

    I just can't work it out.

    If anyone out there can point me in the right direction it would be much appreciated.  Even if the direction is 'give up now'.

    Many thanks

    Jules



    ------------------------------
    Julia Streatfield
    CRM Business Intelligence Manager
    CBI
    London
    ------------------------------


  • 2.  RE: Workflow to do something based on count of records by user and date range?

    GOLD CONTRIBUTOR
    Posted Apr 29, 2019 12:04 PM
    Can you create a new Rollup field on the user entity to count the number of records? It might not 100% solve the problem though as I don't know if you can do 'only count last 30 days,' but if you only care about records created in the last 30 days and don't need anything historical you can always add a bulk delete job to delete anything older than 30 days. You can then reference this field in a workflow for emailing.

    And of course there's the plugin route, where you can do whatever you need but it'll take development effort. I'm assuming you're looking for a no/low-code option though.

    ------------------------------
    Aaron Hirshfield
    Sr. CRM Developer
    Foundation Medicine
    Needham MA
    ------------------------------



  • 3.  RE: Workflow to do something based on count of records by user and date range?

    TOP CONTRIBUTOR
    Posted Apr 29, 2019 12:29 PM
    Oh I like that Aaron, sneaky way around it :).  Many thanks, I'm trying that now.​

    ------------------------------
    Julia Streatfield
    CRM Business Intelligence Manager
    CBI
    London
    ------------------------------



  • 4.  RE: Workflow to do something based on count of records by user and date range?

    GOLD CONTRIBUTOR
    Posted Apr 29, 2019 12:07 PM
    I would suggest creating a Rollup field on the User entity that counts the number of records created by the user but then the problem is you can not define Last X days for the Created On filter. The Rollup Filter doesn't have Date Range operators, there's an Idea you could upvote,

     https://powerusers.microsoft.com/t5/PowerApps-Ideas/Rollup-fields-with-a-date-field-in-condition-not-all-filters-are/idi-p/220093

    We had a similar business scenario and we ended up accomplishing it using a tool called North 52, we created a custom rollup field using their FindSum and DateDiff Functions

    https://support.north52.com/knowledgebase/article/KA-01022-dynamics-crm-365-xRM-Formula-018-Rollups-Total-Open-Opportunities/en-us

    https://support.north52.com/knowledgebase/article/KA-01606-dynamics-crm-365-DateDiff/en-us

    ------------------------------
    Edrei Mpanduki
    CRM Technical Analyst
    Scottsdale AZ
    ------------------------------



  • 5.  RE: Workflow to do something based on count of records by user and date range?

    TOP CONTRIBUTOR
    Posted Apr 29, 2019 12:30 PM
    Thank you Edrei, if I don't get anywhere with Aaron's suggestion I'll try North52.​

    ------------------------------
    Julia Streatfield
    CRM Business Intelligence Manager
    CBI
    London
    ------------------------------



  • 6.  RE: Workflow to do something based on count of records by user and date range?

    SILVER CONTRIBUTOR
    Posted Apr 29, 2019 01:46 PM

    Hi Julie

    There are a couple of issues here

    • I don't believe workflows can be triggered on the update of a rollup or calculated field, so you will not be able to trigger your workflow that way
    • You could trigger the workflow on create of the child record, which then checks the value of the roll up field. However, as the rollup field is only calculated periodically, it will not have the correct number, unless you use a custom workflow step which forces the recalculation

    SO how do you do it?

    Workflow elements by Kaskela are a free tool which provide extra workflow steps to extend the options available to you in a workflow. This will help with this issue.

    1. Install Workflow Elements
    2. Create a view for the entity in question, using one of the options below(you will need to test which works best). Remove all but one columns from the view.
    3. Create a workflow which is triggered on create of custom entity you are interested in
    4. First step, Kaskela Workflow element>Query -Get results.
    5. Pass in the query you created in step 2 (either as a query or fetxh xml)
    6. Add check condition, is GetResults #records > 30?
      • If true create your email
      • Else stop

    Hope this helps



    ------------------------------
    Jamie Hirst
    Dynamics Specialist
    Hirst Dynamics
    Stockport
    ------------------------------



  • 7.  RE: Workflow to do something based on count of records by user and date range?

    TOP CONTRIBUTOR
    Posted Apr 30, 2019 05:22 PM
    I second @Jamie Hirst's suggestion.

    Kaskela's element's query is a must have functionality.
    Building on that, you can have the workflow include a delay to then wait 24 hrs and restart itself, so it runs more or less every day.  It is worth noting this won't work for workflows that restart themselves frequently as there are system limits to prevent infinite loops, but for anything that needs to run less than once an hour, and isn't problematic if it shfts by a minute or two each day, this works.

    If you need something more precise (IE run at a certain set time every day), you'd need to include a scheduling entity that contains a list of workflows to run, when to run them, and then uses Kaskelas workflow to start them.  This was the 2nd iteration we eventually went to, b​ut the above may meet your needs.

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    ------------------------------



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