Skip to main content

Converting the Shoutouts template to SQL Server

, Senior Software Engineer, wtorek, 22 stycznia 2019

App templates in PowerApps are a great way to jump start the creation of an app that can be deployed in your tenant. Those templates create a polished, finished app, and in many cases it creates an Excel file so that the app can be used right away – and in most scenarios the app is ready to be used. But there are some scenarios where the app becomes popular (or you expect it to be) and the limitations of Excel may not let the app scale enough. One solution is to change the data source from Excel to one that has better capabilities, such as CDS, SharePoint or SQL Server, for example. As this issue has appeared a few times in the community, I’ll go over the steps that are required to convert the Shoutouts template from Excel to SQL Server, going through the process so that if you want to convert other templates you should be able to follow a similar path.

Short version: table definition and changes in the app

If all you want is the answer for that specific community question (and the title of this post), here are the steps that you need to take.

First, create the following table in SQL Server. Here I’m using the default schema (‘dbo’), but you can use a different schema if you want:

CREATE TABLE Shoutout (
  Id INT IDENTITY PRIMARY KEY,
  PrimaryID BIGINT,
  CreatorEmail NVARCHAR(MAX),
  CreatedOnDateTime DATETIMEOFFSET,
  RecipientEmail NVARCHAR(MAX),
  ShoutoutType NVARCHAR(MAX),
  [Image] IMAGE,
  CreatorName NVARCHAR(MAX),
  RecipientName NVARCHAR(MAX),
  [Message] NVARCHAR(MAX)
)

Second, go to the list of data sources in your app (in the ribbon, go to View -> Data sources), then remove the existing Shoutout data source, and add a new data source that points to the table that you created. This will generate a few errors in the app, which is expected as the data source name was changed.
Third, go to the following places in the app to fix the errors. In these items I’ll use the name of the table as '[dbo].[Shoutout]', but if you named it differently (or created it in a different schema) please update it accordingly:

  • IntroScreen, OnStart property: in the line where it says If(Not(IsBlank(LookUp('Shoutout,CreatorEmail..., replace Shoutout with '[dbo].[Shoutout]'
  • ActivityFeedScreen, GalleryActivityFeed, Items property: replace the existing expression with the following: 
    //filter results based on what filter item is selected in footer
Switch(
    _activityFeedFilter.Row,
    1, SortByColumns('[dbo].[Shoutout]', "PrimaryID", Descending),
    2, SortByColumns(Filter('[dbo].[Shoutout]', RecipientEmail = _myProfile.UserPrincipalName), "PrimaryID", Descending),
    3, SortByColumns(Filter('[dbo].[Shoutout]', CreatorEmail = _myProfile.UserPrincipalName), "PrimaryID", Descending))
  • PersonScreen, GalleryPersonFeed, Items property: replace the existing expression with the following: 
    If(
    _personTabSelection = "to",
    Filter('[dbo].[Shoutout]', RecipientEmail = _selectedUser.UserPrincipalName),
    Filter('[dbo].[Shoutout]', CreatorEmail = _selectedUser.UserPrincipalName))
  • SendConfirmationScreen, OnVisible property: in the line that says Patch(Shoutout, Defaults(Shoutout), replace it with Patch('[dbo].[Shoutout]', Defaults('[dbo].[Shoutout]') – note that there are two instances of ‘Shoutout’ that need to be replaced.
  • DeleteConfirmationScreen, ButtonConfirm, OnSelect property: replace the line Remove(Shoutout, _shoutoutToDelete); with Remove('[dbo].[Shoutout]', _shoutoutToDelete);.

That should be it. After making those changes, the app is ready to be used.

Long version – how to get to the table definition and changes in the app

Ok, the previous section is useful if you want to convert that specific template, but if you want to convert another one, it doesn’t help you too much. Over this part I’ll go over the steps that I took to find out how to create the table in SQL, and what places in the app needed to be changed.

Table schema

Before we start changing the app from pointing to an Excel table to the new data source, we need to create that data source. In the case of SQL Server, we need to find out the schema of the table that needs to be created. As of today there’s no place in PowerApps that will tell you the schema of a table, so we need to dig through the app to figure it out ourselves. There’s also no easy way to find out where the data source is being referenced, but we can find it out by removing the data source (it can be done in a new app that you create from that template) and looking at all expressions with errors:
Remove data source
Once the data source is removed, we can start looking for it. The first place to look is the OnStart property of the app, which as of the writing of this post you can find in the first screen of the app. The Shoutouts app has a very large expression for OnStart, and after scrolling about halfway through, we can find the first reference to the removed data source:
Error in the OnStart property of the app
This gives us the first hint about the schema of the Shoutout table – it contains a column called ‘CreatorEmail’, whose value should be of the same type as the value of _myProfile.UserPrincipalName. _myProfile is a variable, and you can see the value of it by going in the ribbon to View -> Variables. Its value is a text (the e-mail of the logged in user), so we have the type of the first column of our table.

Next we can go to the remaining places that reference the Shoutout data source that was removed. The App checker is a good place for that, as it will show all the formulas that now have an error due to the removal of the data source:
Errors in App checker
In this app we can see that we have errors in 4 screens, so let’s go through them. The first one is in the ActivityFeedScreen, in the GalleryActivityFeed. If we click on the gallery on the tree view on the left, we’re taken to the Items property that has the error:

//filter results based on what filter item is selected in footer 
SortByColumns(
    If(
        _activityFeedFilter.Row = 1,
        Shoutout,
        Filter(
            Shoutout,
            If(
                _activityFeedFilter.Row = 2,
                RecipientEmail = _myProfile.UserPrincipalName,
                _activityFeedFilter.Row = 3,
                CreatorEmail = _myProfile.UserPrincipalName))),
    "PrimaryID",
    Descending)

We have a little more information about the schema of the table that we need to create – another text column (‘RecipientEmail’), and a column, ‘PrimaryID’, for which we still don’t have enough information about its type.

Moving on to the next screen, PersonScreen, in the GalleryPersonFeed we see another usage of the data source:

Filter(
    Shoutout,
    If(_personTabSelection = "to", RecipientEmail, CreatorEmail) = _selectedUser.UserPrincipalName)

Nothing new here, we already know about the two columns that are referenced in this expression. But it’s good to take a note of this place, as we’ll go back to fix once we have created the table in SQL Server.

The next screen, SendConfirmationScreen, has a reference to the Shoutout data source in its OnVisible property (you may need to scroll down a little to find it):

    //Write to data source
    Patch(
        Shoutout,
        Defaults(Shoutout),
        {
            CreatorEmail: _myProfile.UserPrincipalName,
            PrimaryID: Value(Text(Now(), "[$-en-US]yyyymmddhhmmss")),
            CreatedOnDateTime: Now(),
            ShoutoutType: _selectedShoutout.Type, 
            RecipientEmail: _selectedUser.UserPrincipalName,
            Image: UploadedImage1.Image,
            CreatorName: _myProfile.DisplayName,
            RecipientName: _selectedUser.DisplayName,
            Message: MessageInputBox.Text
        })

Now that has a lot more columns that we need to add to the table. We already know about ‘CreatorEmail’ and ‘RecipientEmail’. We can now see that ‘PrimaryID’ is a number (since its value comes from the Value function). Notice that the value is an integer, but it may be fairly large, going beyond the range of the int type in SQL Server, so we need to use bigint in this case. ‘CreatedOnDateTime’ is a date/time value, so we can map it to the datetimeoffset in SQL Server (see this post for reasons why to choose it instead of datetime). The ‘Image’ column is an image, and PowerApps works natively with the Image type in SQL Server. ‘CreatorName’ and ‘RecipientName’ take the value of the DisplayName property of the users, a text field, so they’re text columns as well. Finally, ‘Message’ takes the value of a Text input control, so it’s also a text column.

The last screen that shows in the App checker is the DeleteConfirmationScreen, on the OnSelect property of the ButtonConfirm:

Set(_deletingShoutout, true);
Remove(Shoutout, _shoutoutToDelete);
Back()

That doesn’t give us any additional information about the schema of the table, so since this was the last error, we’re done, and we have all the columns of the new SQL table:

  • CreatorEmail: text – NVARCHAR(MAX)
  • RecipientEmail: text – NVARCHAR(MAX)
  • PrimaryID: integer – BIGINT
  • CreatedOnDateTime: date/time – DATETIMEOFFSET
  • Image: image – IMAGE
  • CreatorName: text – NVARCHAR(MAX)
  • RecipientName: text – NVARCHAR(MAX)
  • Message: text – NVARCHAR(MAX)

The table has a column called ‘PrimaryID’, and while it’s tempting to use it as the primary key for the new SQL table, I prefer to use a server-generated id, to avoid the problem of two users using the same app trying to send a shout out at exactly the same time (down to the second) – since both of them would have the same value of ‘PrimaryID’, one of those shout outs would fail to write to the database due to a primary key violation. With that, we end up with this table definition (which is the same as shown in the previous section):

CREATE TABLE Shoutout (
  Id INT IDENTITY PRIMARY KEY,
  PrimaryID BIGINT,
  CreatorEmail NVARCHAR(MAX),
  CreatedOnDateTime DATETIMEOFFSET,
  RecipientEmail NVARCHAR(MAX),
  ShoutoutType NVARCHAR(MAX),
  [Image] IMAGE,
  CreatorName NVARCHAR(MAX),
  RecipientName NVARCHAR(MAX),
  [Message] NVARCHAR(MAX)
)

Now that the table is created, we can add it to the app, by going to the ribbon, and selecting View -> Data sources, then “Add data source”:
Add data source
And we’re now ready to fix the app to point to the new data source.

Updating formulas

In the previous step, where we were trying to find the schema for the table to be created, we found all the places that had references to the data source. Now we have to go back and fix the references (since SQL tables are imported as ‘<schema>.<tablename>’). If we created the table with a schema that is equivalent than the previous one, then the fix is simple: just replace the original data source name (Shoutout) with the new data source name ('[dbo].[Shoutout]'). You’ll need to update in the following places:

  • App’s OnStart property (which you currently can see in the first screen of the app)
  • In ActivityFeedScreen, the Items property of GalleryActivityFeed
  • In PersonScreen, the Items property of GalleryPersonFeed
  • In SendConfirmationScreen, its OnVisible property
  • In DeleteConfirmationScreen, the OnSelect property of ButtonConfirm

And with that the app shouldn’t have any more errors.

But there’s more that we can do. If you go to the App checker again, you should see that, indeed, there are no more errors. But there are some delegation warnings in some usages of the new table, so we can try to fix those as well. The first one is on the ActivityFeedScreen, in the Items property of the GalleryActivityFeed:

//filter results based on what filter item is selected in footer 
SortByColumns(
    If(
        _activityFeedFilter.Row = 1,
        '[dbo].[Shoutout]',
        Filter(
            '[dbo].[Shoutout]',
            If(
                _activityFeedFilter.Row = 2,
                RecipientEmail = _myProfile.UserPrincipalName,
                _activityFeedFilter.Row = 3,
                CreatorEmail = _myProfile.UserPrincipalName))),
    "PrimaryID",
    Descending)

If we make the Filter expressions simpler – without an If condition nested inside of it – then we should be able to solve the issue. By hoisting the local condition (comparing against the _activityFeedFilter variable) to the outside, we can leave the expression to be evaluated by the server without any local dependencies, so that the server should have all information it needs to process it. Granted, this will cause some duplication of code, but that’s a fair price to pay to have the server process the requests so that the app doesn’t have to limit itself to 500 (or 2000) items:

//filter results based on what filter item is selected in footer
Switch(
    _activityFeedFilter.Row,
    1, SortByColumns('[dbo].[Shoutout]', "PrimaryID", Descending),
    2, SortByColumns(Filter('[dbo].[Shoutout]', RecipientEmail = _myProfile.UserPrincipalName), "PrimaryID", Descending),
    3, SortByColumns(Filter('[dbo].[Shoutout]', CreatorEmail = _myProfile.UserPrincipalName), "PrimaryID", Descending))

Moving on to the next screen with a delegation warning, we see a similar pattern: a Filter expressions that references a local variable in its condition:

Filter(
    '[dbo].[Shoutout]',
    If(_personTabSelection = "to", RecipientEmail, CreatorEmail) = _selectedUser.UserPrincipalName)

If we hoist the comparison with the local variable to outside the filter, we are left with a pair of Filter expressions that only reference a column from the table and a local value:

If(
    _personTabSelection = "to",
    Filter('[dbo].[Shoutout]', RecipientEmail = _selectedUser.UserPrincipalName),
    Filter('[dbo].[Shoutout]', CreatorEmail = _selectedUser.UserPrincipalName))

And this should fix the remaining error from the App checker.

Wrapping up

In this post I showed how to convert an existing PowerApps template (Shoutouts) from using an Excel-based data source to a SQL Server based one. By going through all the steps that I took to identify the places that need to be changed and how to define the new table, hopefully this will help if you need to perform a similar task, either from one of the other templates, or from an app that you want to improve in your company. As always, feel free to post a new topic in the community if you have questions / comments about PowerApps.

×

Zaczynajmy

Aby zacząć, wprowadź swoje konto służbowe.

Tworząc konto, wyrażasz zgodę na Warunki korzystania z usługi.

Zasady zachowania poufności informacji