Power Apps Snack: Replace Textbox by Drop down

Case
I generated an app with Microsoft PowerApps, but I want to replace the generated textbox on a foreign key field (integer) with a Drop Down with values from the related table. This would make the form much more usable. How do I accomplish this in PowerApps?

Replace standard textbox

Solution
For this example we have two tables: Job and JobStep. JobStep has a Foreign Key to Job. This is a one to many (1-n) relationship. By default this (foreign key/integer column) will become a textbox when you generate an app on data. We will be replacing it with a Drop Down that stores the JobId, but shows the Job name instead. Below you see the model.

1-n relation with Foreign Key on JobStep.JobId = Job.JobId

1) Add Data source
The app is generated on the JobStep table only. If we want to get a list of Jobs to show, we need to add an extra data source for the Job table. Via the View menu click on Data Sources and then on Add data source. Now you can select the server and then the table.

Add Data source

2) Replace Textbox by Drop Down
The default field type for this integer column (JobId) is a textbox. We will replace this by a Drop Down by taking the following actions.

• Click on the datacard of JobId (not only the textbox) and then click on Edit in the property pane.
• Click on the 123 option behind the JobId field and change it from Edit Number to Allowed Values.

Now the Textbox has been changed to a Drow Down box. Next step is to show the names from the Jobs table.

Change textbox to drop down

3) Show name in Drop Down
Now that we have a Drop Down we need to fill it with the Name column from the Job table.

• Click on the Drop Down box itself
• Go to the Advanced tab in the property pane on the right and click on 'Unlock to change properties' to override the default settings.
• Go to the ITEMS property and replace it by the Job table: '[dbo].[Job]'
• In the VALUE property select the field you want to show: Name in this case.
• Then enter this expression in the DEFAULT property to retrieve the Name column via the JobId column.: LookUp('[dbo].[Job]', JobId = ThisItem.JobId, Name)

Change Drop Box properties to show the Name column

Explanation Lookup expression:

• FROM: First part is the table where you want to do the lookup. Where does your data come from? In our case this is the Job table.
• WHERE: Second part is the WHERE statement, where JobId is the column in lookup table (Job). The part after the equals sign is the lookup value that comes from your app.
• SELECT: The last part is the column that you want to retrieve. In our example we want to show the Name column from the Job table.

4) Saving the ID
The Drop Down is now showing the Name column, but we want to save the id column JobId.

• Click on the card again. The easiest is via the Screens pane on the left.
• Go to the UPDATE property and change it with the following expression to save the JobId field instead of the name field: DataCardValue17.Selected.JobId Warming: the name 'DataCardValue17' of the data card value (the drop down) is probably different in your app.

change Default property

5) Testing
Now press on the play button and edit the form to see the result. If you take a good look, you see the ID changing from 72 to 73 on the details screen.

Testing the form changes

Summary
In this post you learned how to change the standard textbox in a generated app to a very useful Drop Down. This is especially handy when you have a 1-n relationship with not too many records. In a next post we will try to find a solution for an n-n (many to many) relationship.

Now that you are ready with the edit form, you probably also want to change the JobId in the Details screen. You could for example solve this with a lookup on the text property of that label. Change Parent.Default to LookUp('[dbo].[Job]', JobId = Value(Parent.Default), Name).


Perhaps not part of the BI stack itself, but though a very useful supporting tool for BI projects. Thanks to coworker Siebe for helping figuring this out.

Power Apps Snack: Add confirmation to delete button

Case
I generated an app with Microsoft PowerApps, but the delete button is missing a confirmation and deletes records a bit too easy. Is there an option to ask something like "are you sure?".

Need some delete confirmation

Solution
There is no out of the box option, but you could change the delete button action a little bit. In this example we will add two more hidden buttons (Confirm and Cancel). The Delete button will unhide these buttons. The Confirm will then do the actual delete and the Cancel will hide the buttons again.

1) Create variable
The first step is to create a Boolean variable for this screen that will be used to show or hide buttons. In the screens pane on the left side click on the screen with the Delete button. Then add the following expression in the OnVisible property: UpdateContext({isVisible: false}). Now you have a variable called isVisible.

Add screen variable with value 'false'

2) Add Confirmation and Cancel icons
Add two icons to your screen via the Insert ribbon. One for the Confirmation action and one for the delete. Also add a label above it with a text like "Are you sure?".

Add icons and label

3) Move delete code
Now Cut and Paste the OnSelect code from the Delete button to the Confirm button and change the Delete button code to UpdateContext({isVisible: true}). This will change the variable value from false to true.

Switch delete code

Add the following 'reverse' code to the OnSelect of the Cancel button: UpdateContext({isVisible: false}). This will change the variable value back from true to false.

4) Hide buttons and label
Since we don't want to show the icons when you haven't clicked on the delete button, we need to change the Visibily property of the two icons and the label. We will replace the default value 'true' to the isVisible variable.

Change Visibility

The intermediate result is a delete button with a confirmation.

Delete with confirmation

5) Disable other buttons
To finish it off we can disable the other buttons so that the user has to confirm or cancel the delete. To accomplish this we need to adjust an expression in the DisplayMode property of the Delete and Edit button. You need to add: && !isVisible to the if contruction. To make it a little more visable that the buttons are disabled you could change their disabled font color to grey.

Final adjustments

The result

Summary
In this post you learned how to add a confirmation visual to a delete button and learned how to add and use variables to change properties of items on your screen (because you cannot use code like: btnCancel.Visible = true).

The (value of the) variable is only usable on this screen. Other screens cannot use it. In a next post we will show you how to pass values from one screen to another screen.

Note that Microsoft PowerApps isn't part of Azure, but part of Office 365. However this tool could be very useful in Business Intelligence / Data Warehouse projects to replace manually created Excel / CSV source files with for example forecast data or simple lists that don't come from a source system. Users often 'accidentally' damage such files for example by adding or deleting columns. With PowerApps you can prevent that. An other great way to use Power Apps is within Power BI as an input form, but more about that in a next blog.