Creating a Finance App with Model-Driven Apps from Scratch – Part 6

<- Part 1 <- Part 4

<- Part 2 <- Part 5

<- Part 3

In this post, I’m going to explain how to use a rollup field to aggregate values. But first, we need an entity to which we can add these aggregates. Each portfolio should have a list of assets, along with its total quantity and monetary amount.

Let’s start by creating the table. Personally, I prefer the classic approach: “Table Advanced Properties.”

We’re going to set the primary column, “Name,” as a number sequence.

And we’re going to add two lookups, one to the Asset table and another to the Portfolio table. Then, we’ll set these fields as a unique key, meaning there cannot be two records with the same Portfolio and Asset.

For the rollup, we’ll also need the movements to be linked to a portfolio asset, so let’s add a lookup field to the Movements table.

Now we can create the rollup field:

Due to some technical limitations with Power FX formulas (which cannot work with related currency fields), we’ll create a new field in the Assets table for the price (in the base currency) but as a decimal value.

Finally, we can calculate the total amount for the portfolio asset using a new calculated field.

While the user won’t need to manually add this data, since we are going to automate the entire process with Power Automate, we still need to create a form to test if it’s working and to update the aggregated fields. So, let’s edit the main form.

Now, let’s edit the Active View to add it to the Asset form as a related grid.

Let’s manually check if it’s working by adding a new Portfolio Asset.

Now, let’s create a movement for Microsoft and link it to this Portfolio Asset in the backend. There’s no need to add the field to the form since we’ll automate this process.

Then, we need to return to the Portfolio Asset we created and update the rollup.

Finally, we can create a sell order. After manually updating the lookup for the Portfolio Asset and refreshing the Microsoft Portfolio Asset, we should check if the update reflects in the Portfolio.

Rollups update every 12 hours by default, but you can adjust the frequency. It’s also possible to trigger a rollup update when a field is created.

In the next post, we’ll create a Power Automate flow so the user doesn’t need to manually add records to the Portfolio Asset table or link movements to it.