Answered

Update Invoice Parent with sum of all child InvoiceDetails values (via Dataverse Accelerator App)

(0) Share

Report

Posted on by funkymusic

I would like to update fields in the "Invoices" table every time an child InvoicesDetail (aka Invoice Products) record is created, updated or deleted (e.g. the Sum of all Invoice Products privates or tax).

It's about the default Invoices + InvoiceProducts table (one Invoice can have n InvoiceProducts).

The goal is to solve this via Dataverse Accelerator App.

I am not ending up with the correct values, however and I can't figure out how to properly use the "Filter" function in this case:

Patch(
Invoices,
ThisRecord.'Invoice ID',
{
'Total Net Amount': Sum(
Filter(
'Invoice Products',
'Invoice ID'.Invoice = GUID(ThisRecord.invoiceid.'Invoice ID')
),
'Net net'
)
}
)

How to reference all children of the Invoice (referenced by the Invoice property of the currently updates/modified/deleted/created InvoiceDetails item)?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @funkymusic,

A couple of notes/questions to consider:

Reference the tables (or other tables) inside plugins with the @[] E.g. @['Invoice Products']

There shouldn't be a new to cast to GUID, you're referencing ThisRecord.'Invoice ID' in the Patch so can't you use directly this inside the Filter?

Hope this helps...

Was this reply helpful? Yes No
funkymusic 6 on at

Like (0)

Report
Copy link

Link copied!

hi @EricRegnier, thank you for your reply.

- Using the @ Notation does not work:

- Using the ThisRecord.'Invoice ID' does not work:

This is the database structure (default invoices table):
"Invoice ID" / "invoiceid" is a field in the invoicedetails table referencing the parent invoice via lookup field.

As a cross-check, manually filtering for invoicesdetail columns with a static GUI works (parent record will be updated correctly):
I tried to set a static variable with the value of the parent invoice ID first (as I think the ThisRecord will match the current record in the loop and not the original record firing the event?), but it looks like setting a new variable is not allowed - https://learn.microsoft.com/en-us/power-apps/maker/data-platform/low-code-plug-ins-powerfx ("Requires the variable to exist (as bound table row property) and the variable type to match what you’re trying to set it to.")

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @funkymusic, not sure if you resolved yet but I finally had the chance to test from my side and get what you're saying 🙂 To resolve the main issue with ThisRecord because it's getting referenced within the Filter and so is another context/scope, use the With function. Here's an example. Also noticed how I used the [@] notation that is still required, so not sure why is wasn't working for you:

With({ invoiceRecord: ThisRecord }, Patch(Invoices, invoiceRecord, { 'New Amount': Sum(Filter([@'Invoice Products'], 'Invoice ID'.Invoice = invoiceRecord.Invoice), Amount) }) )

Hope this help!

Was this reply helpful? Yes No
funkymusic 6 on at

Like (0)

Report
Copy link

Link copied!
@EricRegnier thank you, works like a charm 🙂 Not sure why but I had to add 'Invoice ID' to the With statement but otherwise it works now as expected 🙂
With({ invoiceRecord: ThisRecord.'Invoice ID' }, Patch(Invoices, invoiceRecord, { 'Total Net Amount': Sum(Filter([@'Invoice Products'], 'Invoice ID'.Invoice = invoiceRecord.Invoice), 'Net net') }); );

Was this reply helpful? Yes No