Why dealing with Rollup and calculated Currency is impossible??

[Yahya]

I have an OrderEntity and a Chila InvoiceEntity, Type of Invoice is Payable and Receivable. I want to Rollup for each Order its invoice and it does not seam possible to subtract (payable). Eh 

[dpoggemann]

Hi @Yahya,

If I understand this correctly you have an Order Entity and you want to total up the related invoices to that order and the invoice entity has both payable and receivable invoices. 

You should be able to sum up the invoices related to the order that are receivable by looking at whatever field is the determining factor (i.e. an optionset or whatever it is) and filter to only bring in the receivable to the rollup total.  

Is there a reason you can't qualify like this?  Now of course you are not filtering on this field but hopefully this is what you were looking for.  Please let me know if I am missing what you are asking.

Hope this helps.  If answers your question, please accept.  Please like if helps in any way.

Thanks,

Drew

[EricRegnier]

Hi @Yahya,

If you want to rollup both Invoice of type Payable and Receivable into one total field which automatically subtracts or sums based payable or receivable, then no unfortunately it's not possible out of the box. Also, roll-up fields are not calculated in real-time (every 12h) so this might not be the right approach for you especially when dealing with money.

The other common option is calculating the total on the Order with Power Automate flow whenever a new invoice is created/deleted/updated, using the Dataverse connector.

Hope this helps!

[Yahya · Accepted solution]

Hi @EricRegnier , You got it right understanding the problem. Your proposal could work but I have to say I trust Realtime Process and Business Rules more than Power Automate..

I did reach a solution to my struggle after creating 2 new Currency columns in InvoiceEntity, 

AmountPayable

AmountReceivable 

With Business Rule in InvoiceEntity that

sets AmountPayable to Amount when Invoice Type is Payable and Clears AmountReceivable

sets AmountReceivable to Amount when Invoice Type is Receivable and Clears AmountPayable

and both AmountPayable and AmountReceivable are hidden As i dont need to see them in Forms/Views.

Now I could Roll up OrderEntity to add the AmountReceivable and deduct the AmountPayable and it works perfectly..

Thanks a lot.