Customer Engagement & Dynamics CRM Forum

 View Only
  • 1.  Roll-up and calculated field functionality

    Posted Apr 21, 2020 05:07 PM
    We are currently integrating Dynamics 365 for Sales with a financial system. The financial application creates Invoice transactions for customers. We are retrieving the Invoice transaction from the financial system and using the standard Invoice entity in Dynamics 365 to give visibility of the invoice to the sales team, which uses Dynamics 365 for Sales. The sales team doesn't have access to the financial application.

    These invoices are related to a project. The project entity is a custom entity.  There can be one or more invoices related to a project.  The sales team would like to see MTD, YTD and PTD (project to date) invoice totals. The roll-up functionality works well for the PTD amount. However, the roll-up functionality doesn't have the date feature like Advanced Find where a date field can be evaluated for "current month" or "current year". I created a calculated field for a "Yes" or "No" value for the condition of "Current month" because calculated fields have the capability to evaluate a date field, but a calculated field value can't be used as a qualification in the roll-up filter. I am trying to avoid a monthly and yearly routine that interrogates the date and sets a simple field to "Yes" or "No" to determine Current month or Current year. And I'm not in favor of having tens of thousands of workflows with timeout clauses in a "waiting" state to change the value.  

    Am I overlooking something in existing functionality for roll-ups or calculated fields that would solve this? Being able to evaluate a date field in a roll-up filter like in advanced find would be very useful.

    I'm looking forward to any suggestions. Thank you!

    ------------------------------
    Jim Lorrig
    Senior Business Solutions Consultant
    Heartland Business Systems
    Pewaukee WI
    ------------------------------


  • 2.  RE: Roll-up and calculated field functionality

    TOP CONTRIBUTOR
    Posted Apr 22, 2020 03:28 AM
    Have you checked out TCS tools?
    That has the ability to do instant rollups and also to use Query XML to define things.

    https://www.itaintboring.com/dynamics-crm/lets-roll-up/

    I also believe that what you are asking should be possible with Power Automate but I haven't gone there yet.


    ------------------------------
    Donal McCarthy
    Digital Marketing Administrator
    BrightWork
    Galway
    ------------------------------



  • 3.  RE: Roll-up and calculated field functionality

    TOP CONTRIBUTOR
    Posted Apr 22, 2020 03:41 AM
    It is possible with using Power Automate. I'm using it for something similar. ​

    It has become much easier using the new(ish) Common Data Service (Current Environment) List Records action, where you can use FetchXML to define the query. You can just build a view using Advanced Find and then export the FetchXML. Then dynamically update the view using dynamic content in Flow (e.g. using variables).

    Here's an example, where I have replaced a hardcoded GUID from the FetchXML with a variable in Flow to run in the context of the flow:


    ------------------------------
    BR,
    Niels
    ------------------------------



  • 4.  RE: Roll-up and calculated field functionality

    D365UG/CRMUG ALL STAR
    Posted Apr 22, 2020 07:47 AM
    Hi Jim

    As you discovered, roll-up fields cannot calculate based on the current date (month, year, etc)

    A different 100% out of the box, always accurate, solution would be to embed 3 simple chart views on the project form.

    here is how;

    • Create 3 Invoice views (MTD, YTD, PTD) based the date values (current month, current year, etc)
    • Create a simple bar chart for invoice entity, just totaling the invoice amount.
    • On the project form, create 3 subgrid sections, showing the related invoice records, specify the specific invoice view for each (MTD, YTD, etc).
    • In the subgrid properties (you need to use classic form designer) set the subgrid to "Show Chart only"
    • Publish.
    The corresponding project form will show 3 bar charts with the specific invoice totals for each (MTD, YTD, etc)  These would show up-to-the-minute values since you are not waiting for the rollup to run every hour.

    Caveats
    You won't see just a number, but a number and a chart.
    You won't be able to see the totals in a view of projects

    I figured this might be an alternative that could address the need.

    Cheers
    Nick




    ------------------------------
    Nick Doelman
    Microsoft MVP
    Power Platform/Dynamics 365 Specialist
    Nick Doelman Co.
    Ottawa ON
    ------------------------------



  • 5.  RE: Roll-up and calculated field functionality

    Posted Apr 22, 2020 09:02 AM

    Nick:

    This is a very valid approach that just may fit the need here.  I understand your caveat on not seeing the totals in a view but any reporting on these totals across multiple records will likely be through Power BI.  Other than the screen real estate the three sub-grids will use, the method is simple and, as you state, out-of-the-box.  

    I was thinking through how to describe the need in an enhancement request to Microsoft and my thoughts had, unfortunately, jumped to defining the solution and not the requirement.  I had grossly oversimplified the solution to "all that is needed is the ability to reference a view (or FetchXML statements) to qualify the records in the set-up of the roll-up field".  Unless I'm not understanding this, the roll-up query for Goals is the model for the solution.  And enhancement for this would get the screen real estate back to displaying three fields and not three sub-grids since the MTD total will be zero most of the time.  

    Thank you, and thanks to Tonal and Niels for their suggestions!  



    ------------------------------
    Jim Lorrig
    Senior Business Solutions Consultant
    Heartland Business Systems
    Pewaukee WI
    ------------------------------



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