Rollup Fields & Currencies different from Default Currency

(0) Share

Report

Posted on by SErf

659

Hi,

following issue:

We have a Salesorder for USD 1000.

We send 1 Invoice for USD 1000.

On the Order entity, we have a rollup field, which rolls up the "Total Amount" of all Invoices with Status = Paid.

Paid Amount = SUM(invoices.Total Amount). So this field tells us how much of this Order has been Paid already.

On the Order we then have a calculated field, which tells us the open balance for this order:

Open Balance: Total Amount - Paid Amount (this is the rollup field)

This works when the Order and Invoices are in the default SYSTEM currency (which is EUR for us).

But it fails when we have an order in USD because of the "internal" exchange rate that Dynamics uses.

This means our Open Balance is never USD 0 but it is always some exchange rate difference...

Any ideas to make this work? Order and Invoice both have Currency USD.

I have the same question (0)

All responses (3)

Answers (0)

SErf 659 on at

Like (0)

Report
Copy link

Link copied!

Not completely thought trough yet, but maybe we can rollup the amount by dividing with the exchange rate of the order..?

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

Like (0)

Report
Copy link

Link copied!

Hi SErf,

Each currency field has a Base field, and its name ends with (Base).

The difference between it and the ordinary currency field is as follows.

Form https://neilparkhurst.com/2019/02/25/mb-210-microsoft-dynamics-365-for-sales-currencies/

As you can see, the currency Base field is automatically converted from the value of the currency field to the Base currency via the exchange rate.

Therefore, we need to use the "Total Amount (Base)" instead of "Total Amount".

However, the "Total Amount (Base)" cannot participate in the logic of the Calculated field and the Rollup field.

So we need to create a custom field for "Total Amount (Base)", and use the workflow to copy the value of "Total Amount (Base)" to the custom field. Finally, replace the "Total Amount" with this custom field in the Calculated field and the Rollup field.

Step 1. New a custom field.

Step 2. New a workflow. When "Total Amount (Base)" changes, update the order, copy "Total Amount (Base)" to the custom field.

Step 3. Replace the "Total Amount" with this custom field in the Calculated field and the Rollup field.

Was this reply helpful? Yes No
SErf 659 on at

Like (0)

Report
Copy link

Link copied!

Hi,

thanks for your answer. Unfortunately I don't think that is a solution.

- What is the gain of creating a helper field on Order that I sync with the _base value of Total Amount? This field already exists on the Order: TotalAmount_base.

- So lets assume you wanted to create the field on the invoice entity and not on the order entity.

- The invoice.totalamount_base is already the value that gets rolled up by the order.

It seems the rollup fields function the following way:

Invoice: $1000 Total Amount on the Invoice is 900€ in Total Amount_base

Order: 900€ is rolled-up to the Order. The Order now takes its own exchange rate and calculates:

$1050 as the result for the rollup field.

I think the only option is to sync the exchange rate field of the order with a custom exchange rate field on the invoice. And on the invoice then calculate a helper field using the order exchange rate. Then rolling up the helper field....

But this seems like a shitty solution for such an essential issue....

Was this reply helpful? Yes No