Microsoft Dynamics CRM (Archived)

Rollup of records in current month

(0) Share

Report

Posted on by ChrisJC

1,540

I'm trying to create some rollup fields of a child record (Revenue) where they are in certain criteria.

The Revenue has a date and value field.

In each case the value field rolls up as a sum.

The 3 roll up fields need to query the date field in either the:

Current month

Current Quarter

Current Financial Year

I can't see a way of handling this using either a combination of calculated fields on the child entity and roll ups to the parent. Can someone point me in the right direction?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

RE: Rollup of records in current month

Hello,

Seems that there is no way to implement your scenario using Calculated/Rollup fields. You will have to implement your requirement using Plugins/Custom Workflow Activities/.Net application that will calculate required amounts.

Also you can leave your suggestion on Connect portal to include DateTime fields specific operations to Calculated/Rollup fields - connect.microsoft.com/dynamicssuggestions

Was this reply helpful? Yes No
ChrisJC 1,540 on at

Like (0)

Report

RE: Rollup of records in current month

Hi,

I thought that was the case. The trouble with using a plugin or other is it needs regularly recalculating in time, rather than triggering the calculation by the child record being created.

Do you have a thought on what the trigger could be?

Thanks,

Chris

Was this reply helpful? Yes No
Suggested answer

Lyfe 25 on at

Like (0)

Report

RE: Rollup of records in current month

Its probably a bit late to answer this, but here goes. This can be done without customisation.

You have (in your case), a drop down on your child record with the 4 values (Current Month, Current Quarter, Current FY and Older than 1 year). Current Month is the default value.

On create, you trigger a workflow. This waits until the record Created On is older than 1 month, and updated the field to Current Quarter and waits until its older than current quarter and updates to Current FY and you can guess the next part.

You then filter the roll ups based on this field - for example, for the Current FY rollup filters on all children with Current Month, Current Quarter or Current FY.

To quote a dodgy advertising campaign - "Simples!"

Was this reply helpful? Yes No
Martin Donnelly 1,030 on at

Like (0)

Report

RE: Rollup of records in current month

Can someone confirm that the Windows Workflow Processing is efficient enough to handle this kind of collection of waits? Should I build my own task tickler or jobs that query for records needing attention? And how does one create new system jobs when Online? Am I limited to hanging "this-month", "this-quarter" and "this-year" associated records on then and running scheduled deletes as they expire?

Was this reply helpful? Yes No
Lyfe 25 on at

Like (1)

Report

RE: Rollup of records in current month

Hi Martin,

I am not a personal lover of very large numbers of waiting System Jobs, but assuming we aren't talking 10s of millions, a waiting System Job should have neglectable impact.

By "Task Tickler", I am assuming you mean a scheduling engine? These can be a bit fiddly. MSCRM Objects have an interesting Workflow Scheduler tool. I tend to use the Bulk Delete Scheduling method to trigger, however, these can sometimes need code to get the context of the record to be run on. Depends on your problem, and if you have code capability. If you are able to develop, there's likely to be far more elegant solutions you could apply.

To create a new system job, in the context of this discussion, you create a CRM Workflow and each instance of it will be a System Job. That's if I'm understanding your question correctly. This is the case with OnPrem and Online.

I don't understand your final question, my suggested solution is to have a field on the child entity which helps age the record. If you wanted to automatically delete records older than your filter criteria, there is not reason that wouldn't be instead of updating the field to "Old than 1 year". It just comes down to your data retention requirement.

Hope this helps

Was this reply helpful? Yes No
Martin Donnelly 1,030 on at

Like (0)

Report

RE: Rollup of records in current month

By task tickler I think of something like a custom entity record that stores a due date and, say, a serialized OrganizationRequestCollection member to self immolate and every wee hours of the day ExecuteMultiple those that are overdue. But again, what's the trigger?

By System Job I don't mean the individual executions. I mean like a bulk delete, a scheduled, repeating job. Seems like I should be able to create my own that are not limited to bulk deleting -- of course it also seems like product families should be more useful than just making pretty tree-structure graphics, but c'est la vie.

I think you understand the bulk delete trigger; again, say I created a custom activity and hang an instance of it on each submitted order -- it's presence indicates, for instance, this month. Then every first-of-the-month I go delete all of them that are not of this month -- absence indicates not of this month. Use that delete to unset the flag. AGAIN, if I had a scheduling mechanism I wouldn't need to use the scheduled bulk delete (how do I make my own scheduled system jobs.)

Of course, I only need the flag if I am implementing a rollup field since they are provided only some impoverished subset of fetchXML functionality -- where're all the last week, last month, last year operators?!

Was this reply helpful? Yes No
James Reinhardt 222 on at

Like (0)

Report

RE: Rollup of records in current month

You could easily use flow for this scenario, but I'm not sure of the impact of running the number of transactions it would require every night to do the calculation.

I've created a nightly flow that iterates through all accounts with opportunities, and the queries CRM for a total of those Opps within a given timeframe. The returned total then get put in the correct spot on the Account.

Was this reply helpful? Yes No
Lyfe 25 on at

Like (0)

Report

RE: Rollup of records in current month

Why would you use Flow for that sort of roll-up, when the OotB Rollup field type is designed to solve exactly that need, but uses the native SQL capability internal to the product and doesnt rely on API calls...?

Was this reply helpful? Yes No
Suggested answer

Byron Dittberner 235 on at

Like (0)

Report

RE: Rollup of records in current month

Here is a suggestion, this is the way I do it with no code.

On the child record, you will need a date field (Date and Time), then create a new field called Age that is calculated. Set the calculation action as follows : DIFFINDAYS(<date_field>,NOW())

This will calculate how old or the difference in the number of days from the record date to todays date. Then on the parent record, you will be able to filter your rollup records using the age field, for example,

Filter

Age is greater than 0 AND less than 30 - Last 30 days

Age is less than 365

This is the simple approach, in some cases, you would need to create a few fields to achieve the desired outcome and of course, the method you choose should support the business case.

Was this reply helpful? Yes No