ReadyXRM

Form and list components in Power Pages are based on model-driven Power App forms and views, which of course are connected natively to Microsoft Dataverse.

While it’s very easy to create tables, columns (fields), forms, and views in the Power Pages data workspace, for new (and even established) makers, understanding how the overall data model works takes a bit of practice, and even with all the relational database power behind Dataverse, there are still a few shortcomings.

More of video person? Here is the companion video to this post:

Data modelling 101

I can create a simple table to hold a list of all my “widgets”, and from there I can create a view and a form to show and edit the data in a Power App or create a list or form component on a webpage. This is very straight forward to create either in Power Apps or the Power Pages data workspace.

Taking it a step further, suppose I want to also keep track of the various suppliers, while I could easily create a new column in my widget table called “supplier”, I might have the same supplier provide multiple widgets, and I also might want to store some additional details about my supplier. Keeping it all in the widgets table would lead to duplication of a lot of data, making it time consuming to re-enter this data, as well as maintaining. For example, what if my supplier address changes? I would need to update all of those records.

In this case, its better to create a new table to hold all my supplier information (or use an existing table like “Accounts”).

From the widget’s table, I can create a lookup column to the supplier table, so I can associate the widgets to their suppliers. Having a dedicated table means that I only need to update information in my supplier table once.

In Dataverse forms and views, the lookup will show the primary name column of the lookup, so in a Power App or on a Power Pages webpage I can see the corresponding supplier name for my widgets. In the backend, the supplier column will contain a key value (a GUID), which will associate the link to the tables.

Creating views

When I create a view in Power Apps or in the Data workspace, I can extend the view to show related columns from a lookup relationship. I have a requirement where I want to see what city the supplier is located. In a view (which is the basis for a Power Pages list component) I can show related columns, by adding a column and choosing Related, followed by select the related table and then choosing the column.

Effectively, your view will display related columns.

This will appear on the list component on a Power Pages webpage.

Problem #1: Can’t sort on related field

Using a list component on a Power Pages site, when a user tries to sort on a related column header, nothing seems to happen!

This is easily resolved by adding a relatively obscure site setting in the Portal Management app.

Once the site setting is added, you will be able to sort on related columns on your lists on webpages.

Problem #2: Search comes up blank!

If I enabled the search on my list (and why wouldn’t you?) I can search by all the columns, however, if I enter a city, the search comes up blank! Yet, we know that we have records that match!

This is because the Power Pages list search is unable to search on the related columns, only the columns available on the primary table associated with the list. Fear not, we have a solution below, which will fix this, but also the next problem…

Problem #3: Adding related field to a form

If I also want to show the city column on a form on a webpage, while you can show the lookup column (which shows the primary name) there is no ability to add the related column to the form.

While the ability exists in views to add related columns, there is no ability in the form designer.

The prescribed solution to this is create and embed a quick view form that will show the related information.

While this is a perfectly viable solution it does have some limitations. You will need to create an configure a Quick view form, and then you have some limitations in terms of placement on the form.

It seems like a lot of work to add one related column to a form.

Solution: Calculated columns to the rescue

Pretty much everyone who has worked with Dataverse knows about calculated columns. Generally, its used for some math problems (quantity x price) and for calculating dates.

Another one of the obscure features of calculated columns is that it can reference related columns based on a lookup relationship.

In our situation, we can create a new calculated column in our widgets table that pulls the city column value from the related supplier table.

Here’s how:

Create a new column, of type text, but choose Calculated as the behavior.

In the calculation panel, find the lookup column (supplier), select it. In the edit window, press “.” (period/dot) and you should see a list of related columns. Select the address1_city column (or whichever column you want).

Once configured, the calculated column will be updated every time its accessed (viewed) and if the related info in the lookup table is updated (e.g. city changes) then it will show up in the related widgets table.

You can even “grandfather” this calculated relationships, so you can relate to a calculated column in a parent table, that refers to a calculated column form another related lookup.

Searching fixed!

If you swap out the related city field with the new calculated column, you can now search on that column and get results.

Related field on a form – fixed!

The calculated column can now be placed on the form, note that it will be read-only, but in most use cases this should be OK. If you do need to update it, then the quick view might be the better solution (as always, “it depends”).

The column display is being pulled from the supplier table, and will always be updated.

Summary

Microsoft Dataverse is a very powerful data service, but even it has its quirks. Hopefully this tip helps you with either your Power Pages project or even potentially your Power Apps projects as well.

Cover Photo by Toa Heftiba on Unsplash

Want to learn more?

If you want to learn more about Power Pages and get ramped up quickly, here are some FULL DAY workshop options coming to a conference near you!

Iberian Summit – Build Power Pages in a day workshop

European Power Platform Conference – Power Platform Developer’s workshop

Nordic Summit – Build Powerful websites using Power Pages workshop

Nick Doelman is a Senior Content Developer at Microsoft, a Microsoft Certified Trainer, a former Most Valuable Professional (2017-2021), one of the cohosts of the Power Platform BOOST podcast, a community presenter and also competes in international Powerlifting competitions. Follow Nick on twitter at @ReadyXRM