PowerApps: A simple app using Azure SQL Database

In my post about PowerApps and Azure SQL Database, we created three tables and a blank app connected to those tables.  In this post we will develop the app and hopefully you will find some design hints.

So starting exactly from where the previous post left off, let’s get back to app design.

In your blank app, create a drop down control on Screen1, rename the control DropdownProject and set its Items property to:

'[dbo].[Projects]'

The drop down should now allow you to select a project:

Create a blank vertical gallery, rename it GalleryAllocations and set its Items property to:

Filter('[dbo].[Allocations]', ProjectId=DropdownProject.Selected.ProjectId)

Create a label in the gallery’s template area and set its Text property to:

LookUp('[dbo].[Resources]', ResourceId=ThisItem.ResourceId, Name)

Create another label in the gallery’s template area and set its Text property to:

ThisItem.ResourceAmount

Insert a trash bin icon also in the gallery’s template area and set its OnSelect property to:

Remove('[dbo].[Allocations]', ThisItem)

Now create another dropdown outside the gallery and near DropdownProject, rename it DropdownResource, and set its Items property to:

Filter('[dbo].[Resources]', Not(ResourceId in GalleryAllocations.AllItems.ResourceId))

You will see that there may be a blue circle or underlining warning as shown below, but please ignore this.  It should be resolved in future updates of PowerApps.

Now create a text input below DropdownResource, rename it TextInputAmount and set its Default property to:

""

Also set its Format property to:

TextFormat.Number

Finally, create a button and set its Text property to:

"Save"

Also set its OnSelect property to:

Collect('[dbo].[Allocations]', {ProjectId: DropdownProject.Selected.ProjectId, ResourceId: DropdownResource.Selected.ResourceId, ResourceAmount: Value(TextInputAmount.Text)})

Now you can run the app and see how it performs. Type any positive integer in the text input and save.  The values should appear in the gallery.

Your app should now look something like this:

 

 

7 Replies to “PowerApps: A simple app using Azure SQL Database”

  1. I can’t seem to get the Remove function to work as you have shown it above. When I try it, Powerapps gives me the error “The first argument of ‘Remove’ should be a collection.” I created the Azure SQL database from an Excel Table I used to prototype they app. It works when linked to the Excel Table but not from the SQL table. Any thoughts? Hopefully this is something obvious I’m missing. Thank you in advance.

    1. Again, Chris, the table should have a primary key defined in Azure SQL DB. Is that your issue?

  2. I’m getting the following ERROR message: The first argument of ‘Collect’ should be a collection. How are you avoiding this same ERROR message? Please advise.

    1. Please note that an Azure SQL DB table should have a primary key in order for the Patch() function to work. Was that your issue, JRV?

  3. This is fantastic, especially with all the specific code examples, visuals, and even reference to blue circle warnings to calm my self-critical soul when I think I’ve made a mess following your steps seeing that warning.

    Now trying to figure out the syntax of the code, specifically what is telling DropdownResource only to display empty resource data fields in the code
    “Filter(‘[dbo].[Resources]’, Not(ResourceId in GalleryAllocations.AllItems.ResourceId))”

    Specifically understanding the following formula pieces and how to apply/modify correctly:
    1. What does “in” do with respect to the “not” logical operator?
    2. What does “AllItems.ResourceId” mean within the GalleryAllocation? How does the “AllItems” segment relate in comparison to other similar code like “ProjectId=DropdownProject.Selected.ProjectId”
    3. After thinking again, is the DropdownResource displaying only what resources that are NOT being displayed in GalleryAllocations?

    Thanks again Giacomo!

  4. Thanks Giacomo, together with the other post on how to connect to Azure SQL DBs, this completes the picture, really helpful. Great way to get started exploring use of Azure SQL with PowerApps.

Leave a Reply

Your email address will not be published. Required fields are marked *