PowerApps: Concurrently returning variables and multiple arrays from Azure SQL Database

Let’s say that you have tens of thousands of rows of sales and want to display for any given day:

  • the year-to-date total sales amount
  • the individual sales of the day
  • the details for all of the day’s customers

Normally this would require three separate operations in PowerApps, and the first may not even work since Sum() is not yet delegated.  With the approach I outline in this post, this can be done in one operation and without any delegation issues.  For this we leverage Flow. Continue reading “PowerApps: Concurrently returning variables and multiple arrays from Azure SQL Database”

PowerApps: Repeating section with editing

In this post I will build a repeating section in a simple PowerApp.  You will be able to select an order and view/edit its details in a repeating section.

As a data source I will be using the AdventureWorksLT database on Azure SQL Database, but this will work with any data source such as SharePoint or CDS as long as you have two tables: OrderHeader and OrderDetail.  OrderDetail should have a column pointing to a row of OrderHeader. Continue reading “PowerApps: Repeating section with editing”

PowerApps: Caching lookup tables to improve performance

Lookup tables can slow down app performance significantly.  This is because the lookup is performed by going back to the server every time the data is needed.

The way to improve performance is to cache the lookup tables locally once, and then refer to these lookup tables elsewhere in the app.  This works well when the lookup table is unlikely to change during a user session. Continue reading “PowerApps: Caching lookup tables to improve performance”

PowerApps: Replace the Defaults() function to improve performance

The Deafults() function is often used inside a Patch() function to create new records, with this sort of syntax:

Patch(
    MyTable,
    Defaults(MyTable),
    {TextColumn: "Hello world", NumberColumn: 123.45})

Performance of this expression is slow because every time the Defaults() function is invoked, there is a call to the server where MyTable is stored.  The above expression would then make at least two calls, one for the Defaults() and one for the Patch(). Continue reading “PowerApps: Replace the Defaults() function to improve performance”

PowerApps: Cryptocurrency exchange rate API custom connector

As an example of what can be done with custom APIs in PowerApps, I created a connector to fetch the exchange rates of various cryptocurrencies from an open source.  You will be able to use the rates in formulas in PowerApps.  This approach is very flexible and can be used with almost any other API.  All you need is to edit the Swagger file below to match the output of your chosen API. Continue reading “PowerApps: Cryptocurrency exchange rate API custom connector”

Microsoft Flow: Automatically uploading foreign exchange rates daily from the European Central Bank

Many applications require reference foreign exchange rates to be updated daily and/or a table of historical forex rates to be maintained.  These are typically accounting, expense reporting, portfolio tracking, e-commerce applications.  Microsoft Flow is perfect for this, and the data can be stored almost anywhere.

Continue reading “Microsoft Flow: Automatically uploading foreign exchange rates daily from the European Central Bank”