Rollup or Calculate this year / last year

(0) Share

Report

Posted on by Community Member

I see that the date operators in Rollup/Calculate do include this year or last year or anything other than simple inspections. Has anyone figured out a way to get a sum of invoice detail amount filtered to this year? To last year?

TIA

I have the same question (0)

All responses (7)

Answers (0)

Community Member on at

Like (0)

Report

I'm trying this as the invoice flag:

...and I am getting this denial on the account side:

Was this reply helpful? Yes No
LuHao 40,892 on at

Like (0)

Report

Hi partner,

Can you elaborate on your needs?

If OOTB Rollup or Calculate cannot meet your needs, then we can replace them with Flow.

Was this reply helpful? Yes No
LuHao 40,892 on at

Like (0)

Report

Hi partner,

Do you need more support?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

More than none? Yes.

I don't see the similarity between a calculated field and a procedure. It just seems odd that what I can do for a view/fetchXML is not implemented for the Rollups. I'm guessing that filter using NOW() on the invoice might have a problem with SQL Server's notion of an indeterminent function. If the answer is there is no such functionality, then c'est la vie.

But I need a Rollup field of this year's invoice total and another of last year's on the account form. I know I can do the math in a webApi fetch but was wondering if there was an easier way.

Was this reply helpful? Yes No
LuHao 40,892 on at

Like (0)

Report

Hi partner,

We will create 2 flows, one is to update the exisiting Accounts, the other is to update the Account in real time.

1. First flow: update exsiting Accounts

Go to Account view, click Flow > Create a flow.

Then set the flow as follow.

After saving this flow, return to the Account view of D365 and run this flow.

In this way, exisiting Accounts will get update based on their Invoices

After running successfully, this flow can be turned off.

2. Second flow: update Accounts in real time.

Create a automated flow as follow.

In this way, when the fields "Total Amount", "Customer" and "Date Delivered" are changed, this flow will be triggered to update the Account.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you. I know how to write a plugin to aggregate or calculate a field. However, Microsoft invented Rollup and Calculated fields to spare me the chore of doing that. My question is pretty clearly about the functionality of Rollup and Calculated fields.

I will proceed as if the answer is, "no, you can't do that" until someone demonstrates otherwise. I'm sorry to have taken up your time in making the above presentation and hope it might be of use to someone in the future.

Was this reply helpful? Yes No
LuHao 40,892 on at

Like (0)

Report

Hi partner,

In the logic of the Rollup field, the date field can only be compared with another date field or specific value, so we can first create two date fields, one for the start date of this year and the other for the start date of last year, then use Flow to update them every day, and finally use them as the reference date in the logic of the Rollup field.

The reason for using Flow to update them is that the rollup can only apply filters to the source entity or related entities, simple fields or non-complex calculated fields. (From this document) This is the cause of the error in your second screenshot, the "Age in Years" field is not a non-complex calculated field.

In addition, could you indicate whether last year is calendar dates (1/1/2019 - 12/31/2019) or natural days (1/6/2019 - 5/30/2020)?

Was this reply helpful? Yes No