Can I get a view to show a column from a parent table?

How many times have you been creating a view, and found that a column you need is on a related table two or three relations "up" the parent relationship chain? Now you can get those column into your view easily using a PowerFx formula. Start by creating a column in a table and selecting a "Formula" data type. 

For example, lets say you are designing a view of opportunities, and you want to see the territory name of the manager of the sales person that owns an opportunity. You have these tables:

A new "Formula" field will appear; type the name of a lookup column from your current table, and it will prompt you with the column names from that related table. Put a period at the end of the column and it will prompt you with all the columns (and other parental lookups) from that table. You can continue using the 'dot' notation to link up multiple levels in the relationships as necessary to get the column you want. Here is how my example looks when adding a field to the opportunity table:

The data type and format depend on the data type of the field you select. 

There are some limitations, such as with currency. For those cases, you need to use the Value() function to change the field into a Decimal data type before you can use it. At that point, you can choose  formatting under the Advanced Options:

I now prefer to use the PowerFx over the classic calculated field. I have found that when I promote a solution as managed, then the classic calculated field's "edit" icon is inactive and will not let me see the underlying formula. 

I have learned through experimentation that the PowerFx formulas don't do a good job with date/time fields when used on email templates; no matter what type of date/time formatting I have tried, it will always show the time. If you find a way to make PowerFx work on templates, I would like to hear back!

You can read more about these formulas in the MS documentation: Work with Dataverse formula columns - Power Apps | Microsoft Learn

Have you ever tried to figure out which cloud flow or workflow updated a record, but the Modified By field just says a user name (or service account)?

In my applications, I will regularly have over a hundred flows, business rules, integrations, canvas pages, and plugins that update records in Dataverse. Up until a few years ago, it was frustrating when a client would call because some odd behavior was occurring and they could not find the source. Sometimes you could go to Make.powerapps.com and open the default solution to see if the field in the table had a dependency, but that is...not dependable. 

My solution is that I created a "Fingerprint" field in every table, and when any automation  or integration creates or updates a record, it also populates the fingerprint field with its name. Now I can look at the Audit History of any record and see what process is making updates. If you have a better option, please share it!

I have been learning how to build Power Platform Canvas apps and have some things I would like to share. 

I have a client that has a need for a model-driven-like view inside a canvas page. After poking around, I found the Powercat Creator Kit contains a "DetailsList" control which is about as close to the user experience of a view (sort/filter/drill down) in model driven app as I have found to date. Releases · microsoft/powercat-creator-kit

First, thanks to Scott Durow (https://www.youtube.com/scottdurow) for making some nice videos on the topic - highly recommend. 

My use-case is a bit complex, but here is my cut-down version: I have several tables in Dataverse that I needed to do some fancy join logic to get the right values, so I decided to use Power Automate and custom FetchXML on Dataverse to get my data into a collection in my Canvas Page (story for another day). I need to present the user with the data as a view and let them click on selected cells to pass to another process. The DetailsList has the ability to format selected columns as "links" where I can customize the behavior when a user clicks on it. 

My collection has text and many columns of numbers that range from 0-15 with up to 3 decimal places. The client wants to see the numbers with all 3 decimal places and right justified. The only way to do that (that I have found so far) is to convert the displayed data using the Right("_" & Text(,"#0.000"),6) function, however, that breaks the OOTB column sorting feature in DetailsList, because it sees the column values as text, so numeric values appear to sort lexicographically (e.g., “1”, “10”, “2”, “20”) rather than numerically (and still left justified because the string appears trimmed).

My workaround is to add a 'formatted' column (using the above expression) for each numeric column. For example, if I have a numeric column name of "ecc_25", then I will do this:

AddColumns( collection, ecc_25_formatted, Right("_" & Text(ecc_25,"#0.000"),6)) 

So now there is one numeric column in the collection that can be sorted correctly, and a formatted version that I added to the fields of the DetailsList control. In my case, all my values are a links, so they appear to be underlined when rendered, and my "_" prefix is not obvious to the user.

Then in the DetailsList OnChange event I have this:

If(

    Self.EventName = "Sort",

    UpdateContext(

        {

            ctxSortCol: If(("formatted" in Self.SortEventColumn), Left(Self.SortEventColumn,6), Self.SortEventColumn),

            ctxSortAsc: If(

                Self.SortEventDirection = 'PowerCAT.FluentDetailsList.SortEventDirection'.Ascending,

                false

                ,true

            )

        }

    )

);

Note the ctxSortCol is expecting the name of the sorting column as a string, so I trim off the "_formatted" part of the column name and the DetailsList will take care of the sorting correctly.

I posted a feature request in Github for them to add a Text format property added to each column (defined in columns_Items) and a Justification property (left/right/center). If you like this idea, please add your comments DetailsList column sorts formatted numeric values lexicographically: workaround + feature request · Issue #389 · microsoft/powercat-code-components