Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Rollup Field for Rolling Year - Issue

  • 1.  Rollup Field for Rolling Year - Issue

    SILVER CONTRIBUTOR
    Posted 7 days ago
    I'm trying to create a rollup field on the Account entity that will count all Opportunities with Status of Won AND the Actual Close Date within the last year.

    The issue I'm having, though, is that the only options for rollup field conditions are to compare against a static value or another field.

    So I created a calculated field on the Opportunity, One Year Ago, defined as SUBTRACTYEARS(1,NOW()) which works great for that field. The only thing is I cannot reference this calculated field in my rollup field filtering. I discovered this is because it treats the NOW() in the same manner as another calculated field.
    "A rollup can't reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity."
    (https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-calculated-fields)

    Does anyone have any tips for accomplishing this? I can create a workflow, flow, or business rule that sets a field on each opportunity, but I'd rather not force an update on the hundreds of thousands of historic records if I can accomplish this another way.

    Thanks for any thoughts in advance!
    #CustomerEngagement
    ​​​​

    ------------------------------
    Chad Althaus
    Bray International, Inc.
    Houston TX
    ------------------------------
    Conference-CRMUG_200x200


  • 2.  RE: Rollup Field for Rolling Year - Issue

    Posted 6 days ago
    Hi Chad,

    One way to do this would be by using plugin. You can also use JavaScript (if you prefer low level coding), but you need to keep in mind that JavaScript is for client side. So if you are using JavaScript then you have to make sure that it will only trigger based on client actions.

    My suggestion to you would be to use a Plugin. Following are some of the documentations that would help you to develop the plugin.

    Tutorial on Write and Register a Plugin: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/tutorial-write-plug-in
    Retrive Multiple sample code: https://docs.microsoft.com/en-us/dotnet/api/microsoft.xrm.sdk.iorganizationservice.retrievemultiple?view=dynamics-general-ce-9

    Please let me know if you have any questions/concerns.

    Thanks
    Jay

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

    Conference-CRMUG_200x200


  • 3.  RE: Rollup Field for Rolling Year - Issue

    TOP CONTRIBUTOR
    Posted 5 days ago
    I solved a similar issue in the past by creating a hidden field that would contain a copy of the value I wanted to use for further calculation and then filling that field using workflow. I had the restriction of being in a strict no-code environment, therefore this is the solution I came up with, avoiding JS.

    ------------------------------
    Sissy Bottcher
    AlphaBOLD
    San Diego CA
    ------------------------------

    Conference-CRMUG_200x200


  • 4.  RE: Rollup Field for Rolling Year - Issue

    Posted 5 days ago
    Hi Sissy,

    I thought of the similar idea but this would not be useful in this case. It is mentioned that Chad wants to create a field to store exactly one year back dated, using an hidden field and workflow would not be useful to update so many opportunity and that too everyday. Also the workflow would not be able to run everyday and update the field with today's date.

    Please let me know if you have any questions/concerns.

    Thanks
    Jay

    ------------------------------
    JayBuddhdev
    https://www.powerappsd365.com
    Regina SK Canada
    ------------------------------

    Conference-CRMUG_200x200


  • 5.  RE: Rollup Field for Rolling Year - Issue

    TOP CONTRIBUTOR
    Posted 5 days ago
    True, I overlooked that. However it should be possible to do using PowerAutomate without a rollup and extra field altogether.

    It'll have to be a daily (nightly) job and you can just have the flow count the number of respective opportunities on the account and fill in the field.

    ------------------------------
    Sissy Bottcher
    AlphaBOLD
    San Diego CA
    ------------------------------

    Conference-CRMUG_200x200


  • 6.  RE: Rollup Field for Rolling Year - Issue

    SILVER CONTRIBUTOR
    Posted 2 days ago

    Thanks for the thoughts and suggestions! I was hoping for something simple using a rollup field, but this kind of date comparison is not so simple.

    I think in any case I will need to run a mass job for all opportunities closed within the last year. I've come up with solutions using business rules, calculated fields, and workflows/flows, but they all require running a regular process to update each of those opportunities. The scale of that job can be minimized by running the process for only the records which need updating.

    For example, I can use a boolean field "Closed in Past Year". Then, I can run a daily flow through Power Automate that lists all opportunities with a close date between 364 and 365 days in the past. For each record in the list, I will update the boolean field from Yes to No. For our records, this comes out to around 300-500 records. With one mass job to update the records, I then limit the scale of the flow to a manageable amount on a recurrin basis.

    I am of the mindset to refrain from external customizations if I can find a way to do it without. :) 



    ------------------------------
    Chad Althaus
    Bray International, Inc.
    Houston TX
    ------------------------------

    Conference-CRMUG_200x200


  • 7.  RE: Rollup Field for Rolling Year - Issue

    Posted 2 days ago
    Hi Chad,

    If you don't want to make any external customizations, then your example sounds good to me. As an example, create the flow as shown below and add update yes or no to opportunity for each records.

    The formula that I have used is: addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),-364).



    I hope this helps you to achieve your goal.

    Please let me know if you need any more assistance.

    Thanks
    Jay


    ------------------------------
    JayBuddhdev
    https://www.powerappsd365.com
    ReginaSKCanada
    ------------------------------

    Conference-CRMUG_200x200


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