Power Platform Community Forum Thread Details

My apps that I built in 2018-2019 rely upon an Azure SQL DB. I track the time of events in that DB but then want to display / sort based on normal way to view time. Way back then I found More Regular Expressions for Canvas apps - Microsoft Power Platform Blog that said a better way is coming. Was there ever "a better way"? This is how the time is stored in the DB.

First(ForAll(MatchAll(Text(ThisItem.MorningSigninTime),"PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"),Time(Value(hours),Value(minutes),Value(seconds)))).Value

That is a sample of the code. If I attempt to sort by it then it isn't delegatable.

Categories:

Building Power Apps

Hi, I tested this approach and, in practice, the cleanest solution is still to handle it in SQL instead of parsing the ISO duration string in Power Apps.

I created a SQL view on top of the table and exposed additional text columns formatted as HH:mm. Example:

The select query of SQL view looks exactly as expected.

After that, in Power Apps I connected the view as a separate SQL data source.

Then we can use connected SQL data source (in our case view we created) to the gallery items.

In Gallery Label we set Text property: ThisItem.MorningSigninTimeText

That displays the value directly as 08:00 / 08:15 / 07:50 without using MatchAll / ForAll / Time parsing.

So from my testing:
- there still does not seem to be a better native Canvas handling for SQL time fields
- the regex parsing approach works, but it is not a good option when you want cleaner formulas
- exposing display-ready columns from SQL view is much simpler and easier to maintain

If sorting is also important, I would additionally recommend exposing a numeric sort column from SQL as well, for example seconds or minutes since midnight, and then sort by that column in Power Apps.