SERVER HOIT ASIA

Showing posts with label POWERAPPS. Show all posts

Case
I have some pieces of code in my Power App that I use for several buttons, but I don't want to create multiple copies of that code. Is there a way to create a method or function with custom code that I can call from various buttons?

Power Apps and custom functions

Solution
No, Power Apps does not support methods or functions like real programming languages. However you can use the SELECT function. This function allows you to execute code from other objects on your screen, but only objects that have an OnSelect event.

Example 1: basics
For this example add two buttons to the screen: Button1 and Button2. In the OnSelect of Button1 add a simple Notify expression:

Notify(
    "Hello",
    NotificationType.Success,
    1000
)

Notify in OnSelect of Button1

In the OnSelect of Button2 add the Select expression:

Select(Button1)

Select in OnSelect of Button2

The Result
Now hit the play button to see the preview of your app. Then click both buttons and see the Notify showing for both buttons. You can even make Button1 invisible if you don't want to execute that code by it self, but only via other buttons.

Testing simple Select solution

Example 2: parameters
By adding variables to the game you can even have some parameters for your new 'function'. Now change the OnSelect code of Button2 to:

UpdateContext({myParam: "Joost"});
Select(Button1)

And then add a third (and optional a fourth) button with the following OnSelect code:

UpdateContext({myParam: "Mark"});
Select(Button1)

Then change the OnSelect code of Button1 to:

Notify(
    "Hello " & myParam,
    NotificationType.Success,
    1000
)

And make Button1 invisible by setting the Visible property to false.

The Result
Now hit the play button again to see the preview of your changes. Click both visible buttons and see the Notify that now shows a different name for both buttons.

Testing Select with 'parameter'

Conclusion
In this post you learned how NOT to repeat yourself with the Select function in Power Apps. Perhaps not the same features as in JavaScript, C# or any other language, but very useful to keep your code more clean. By hiding the buttons and giving them a descriptive, function-like name they could act like real programming functions.

Case
I want to include a Power BI tile in my Power App. Is it possible to manipulate the Power BI tile with data from the Power App?

Power BI tile in Power Apps with filter

Solution
Power Apps has its own charts. So why would you want to use a Power BI tile in your Power App in the first place? The charts from Power Apps are pretty fast, but since Power Apps is not a reporting tool the functionality is quite basic. The two major disadvantages are:

The charts don't have options in the GUI for sorting, filtering or grouping. So either your Data Source is already prepared or you to do that with functions in Power Apps (Filter, GroupBy, SortByColumns, etc.). Not ideal, especially not for large datasets.
The charts are very basic (Column, Line or Pie chart). You could combine a column and line chart by creating two separate charts and then lay the line chart over the column chart. Again workable, but not ideal.

Let's see how you can add a Power BI tile to your Power App and what you have to do to make it more dynamic with filters.

1) Pin visual to dashboard
Power Apps can only add Power BI visuals that are pinned to a dashboard. Go to your Power BI report on PowerBI.com and click on the little pin icon above the Visual that you need in Power Apps. After that specify to which existing or new Power BI Dashboard you want to publish it.

Pin your visual to a dashboard

2) Add Visual to Power Apps
In the Insert menu of Power Apps you see the Charts submenu in which you will find the option to add a Power BI tile. Adding it is very straight forward. Just select the right Workspace, Dashboard and then the Tile.

Adding a Power BI tile to Power Apps

3) Adding dynamic filters
If you go to the properties of the new Power BI tile in Power Apps you will find the TileUri property. This is the URL of your Power BI tile which you can extend with filters. The standard URL looks something like:
"https://app.powerbi.com/embed?dashboardId=11111111-aaaa-1234-aaaa-111111111111&tileId=11111111-aaaa-1234-aaaa-111111111111&groupId=11111111-aaaa-1234-aaaa-111111111111&config=abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc1230%3d"

The DATA TileUri

You can simply add a filter to this URL by adding an extra parameter to the querystring. Let's say we have a field called Department in the table FactInternetSales and we want to filter on the value 'Amsterdam'.

The field on which we want to filter

Before the last double quote of the TileUri you must add &filter=[tablename]/[columnname] eq '[value]'. The result looks like:

"https://app.powerbi.com/embed?dashboardId=11111111-aaaa-1234-aaaa-111111111111&tileId=11111111-aaaa-1234-aaaa-111111111111&groupId=11111111-aaaa-1234-aaaa-111111111111&config=abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc1230%3d&filter=FactInternetSales/Department eq 'Amsterdam'"

Note that filter is in lowercase otherwise it won't work. For more OData like filter examples see the documentation of Power BI filters. A great tip is to first test the query string filter in Power BI itself and then copy and paste it to Power Apps. After that you can replace the hard-coded value 'Amsterdam' by a value from for example a drop drown.

Adding a Drop Down to use it in the filter

With the drop down the URL looks like:

"https://app.powerbi.com/embed?dashboardId=11111111-aaaa-1234-aaaa-111111111111&tileId=11111111-aaaa-1234-aaaa-111111111111&groupId=11111111-aaaa-1234-aaaa-111111111111&config=abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc1230%3d&filter=FactInternetSales/Department eq '" & ddDepartment.Selected.Department & "'"
If your table name or field name contains spaces, dashes or non-ansi characters you need to replace those with special characters in your query string.

4) The result
Now you can hit the play button and test the dynamic filter. It works very well, but it isn't super fast. Therefore you shouldn't have more than three Power BI tiles loaded at the same time. With the property LoadPowerBIContent you can control which tile is loading.

When clicking on the Power BI tile in your Power App the user will be redirected to Power BI online to do some more in-depth analysis. If you don't want that you need to set the PowerBIInteractions property to false (default is true).

Testing the dynamic filter

Conclusion
In this post you learned how to use Power BI tiles, that are published to a dashboard, in Power Apps. Very straight forward to use and they have way more options than the built-in charts. One last point for attention. If you use these Power BI tiles then make sure that your Power Apps users have a Power BI licence and are authorized to the used tiles.

Case
I want get the product ID's of all selected items from my combobox in Power Apps, but the DisplayFields property of the combobox only shows the product name. How do I get the corresponding ID's?

Mulitselect ComboBox in Power Apps

Solution
You can loop though the collection of selected items of your combobox with the SelectedItems function. Below you will find a couple of code examples. For this example we are using a table called MyProducts with data from Adventure Works.

Table with sample data

Loop through selection
If multi select is turned off (basicly a DropDown with a search option) then you can use the Selected function, just like for the ComboBox.

ComboBox_singleselect.Selected.ProductKey

When multiselect is turned on, you must use SelectedItems. Getting a comma separated list of ProductKeys (instead of ProductNames) can be done with the following script. To test it you could add a label with this script in the Text property to show the result:

Concat(
    ComboBox_multiselect.SelectedItems.ProductKey,
    Concatenate(
        Text(ProductKey),
        ","
    )
)

This will result in a string with "PrdKey1,PrdKey3,PrdKey3," If you want to get ride of the last comma you can use a left: LEFT(mystring, LEN(mystring) - 1):

Left(
    Concat(
        ComboBox_multiselect.SelectedItems.ProductKey,
        Concatenate(
            Text(ProductKey),
            ","
        )
    ),
    Len(
        Concat(
            ComboBox_multiselect.SelectedItems.ProductKey,
            Concatenate(
                Text(ProductKey),
                ","
            )
        )
    ) - 1
)

Or if you want to keep your code a little cleaner then you could first store the result in a variable and then use the LEFT on the variable. To test this add the UpdateContext to the OnChange of the ComboBox and the Left to the Text property of your label.

// Store concat with trailing comma in variable
UpdateContext(
    {
        SelProds: Concat(
            ComboBox_multiselect.SelectedItems.ProductKey,
            Concatenate(
                Text(ProductKey),
                ","
            )
        )
    }
);

// Remove last comma
Left(SelProds, Len(SelProds) - 1)

Get specific item from alle selected items
You can also get specific items from the selection.

// First item
First(ComboBox_multiselect.SelectedItems.ProductKey).ProductKey

// Second item
First(
    LastN(
        ComboBox_multiselect.SelectedItems.ProductKey,
        CountRows(ComboBox_multiselect.SelectedItems.ProductKey) - 1
    )
).ProductKey

// Thirth item
First(
    LastN(
        ComboBox_multiselect.SelectedItems.ProductKey,
        CountRows(ComboBox_multiselect.SelectedItems.ProductKey) - 2
    )
).ProductKey

To keep your code a little cleaner you could first store the selection in a collection in the OnChange event of the ComboBox.

// Store selection in collection
ClearCollect(
    ProdSel,
    ComboBox_multiselect.SelectedItems.ProductKey
);

// First item
First(ProdSel).ProductKey

// Second item
First(
    LastN(
        ProdSel,
        CountRows(ProdSel) - 1
    )
).ProductKey

// Thirth item
First(
    LastN(
        ProdSel,
        CountRows(ProdSel) - 2
    )
).ProductKey

Maximize selection
An other need trick is that you could maximize the number of selected items by adding the following script to the OnChange of the ComboBox.

If(
    CountRows(ComboBox_multiselect.SelectedItems) > 4,
    Notify(
        "You reached the maximum of 4 items",
        NotificationType.Error,
        2000
    )
)

Or you could combine that with filling a boolean variable that you can use to disable/hide the save button

If(
    CountRows(ComboBox_multiselect.SelectedItems) > 4,
    UpdateContext({ValidationError: false});
    Notify(
        "You reached the maximum of 4 items",
        NotificationType.Error,
        2000
    ),
    UpdateContext({ValidationError: true})
);

Conclusion
In this code snippet post you learned how to retrieve the selected items from a Power Apps ComboBox. When multi select is turned on you should use SelectedItems instead if Selected. This returns a collection of all selected Items from the ComboBox.

Bug: the formulas only work with string columns. Solution is to add the integerfield to the displayfields of the ComboBox.

Add non-string fields to DisplayFields

Case
I am using a gallery on my overview page to see all records and on my details page I want a button to go to the next item from my gallery without first going back to my overview page.

Generated App with dynamic Next button

Solution
For this example we will use a generated PowerApp based on a product table that came from the AdventureWorks database.

In this generated app you know which item is selected in the product gallery, but you don't know which product is next because there is no expression for this. To solve this we will:

Create a new collection based on the gallery with only the key column (to keep it small).
Then create a copy of that collection with an extra column that contains an auto-increment (auto-number).

A preview of both collections

This second collection can then be used to query the successive product row for a specific product key. The product key of this successive product row can then be used to retrieve all data from that product to display it in the Display form.

1) Pass through product key via variable
First step is to slightly change the navigation command on the overview page. We will pass on a variable that contains the active product key to the details page. In the OnSelect action of the gallery we will change the existing code with an extra line:

// Old code
Navigate(
    DetailScreen1,
    ScreenTransition.None
)

// New code (with the variable)
Navigate(
    DetailScreen1,
    ScreenTransition.None,
    {MyProductKey: BrowseGallery1.Selected.ProductKey}
)

Change navigate in OnSelect of gallery

2) Change item code on details page
On the details page where you see all columns/attributes of the product we need to change the Item code of the Display form. The standard code retrieves the selected item from the gallery on the overview page, but we will use the variable from step 1 instead.

// Old code
BrowseGallery1.Selected

// New code
// Get first row from dataset 'MyProducts'
// that is filtered with our new variable
First(
    Filter(
        '[dbo].[MyProducts]',
        ProductKey = MyProductKey
    )
)

Change Item from Display form

3) Add Next button
The last part of this solution is to add a button and a lot of code for its OnSelect action. Adding the rownumber to the collection can be done in a couple of ways, but this flexible code is very well documented by powerappsguide.com.

// Create new collection with only the key column
// from the gallery (key=ProductKey)
ClearCollect(
    MyProductGallery,
    ShowColumns(
        BrowseGallery1.AllItems,
        "ProductKey"
    )
);


// Create new collection based on first
// collection with one extra column that
// contains an auto increment number "MyId"
Clear(MyNumberedProductGallery);
ForAll(
    MyProductGallery,
    Collect(
        MyNumberedProductGallery,
        Last(
            FirstN(
                AddColumns(
                    MyProductGallery,
                    "MyId",
                    CountRows(MyNumberedProductGallery) + 1
                ),
                CountRows(MyNumberedProductGallery) + 1
            )
        )
    )
);


// Retrieve next item from gallery and
// store its id (ProductKey) in a variable
// called 'MyNextProductKey'
// Steps:
// 1) use LookUp to retrieve the new id (MyId) from the new collection
// 2) use that number to only get records with a higher number by using a Filter
// 3) get First record from the filtered collection
// 4) get ProductKey column and store it in a variable
UpdateContext(
    {
        MyNextProductKey: First(
            Filter(
                MyNumberedProductGallery,
                MyId > LookUp(
                    MyNumberedProductGallery,
                    ProductKey = MyProductKey,
                    MyId
                )
            )
        ).ProductKey
    }
);


// Only when MyNextProductKey is not empty
// change the value of MyProductKey which
// will result in retrieving new data to
// the Display form.
// An empty value only occurs for the last
// record. The If statement could be extended
// with for example navigating back to the
// overview page when it is empty:
// Navigate(BrowseScreen1, ScreenTransition.None)
If (
    !IsBlank(MyNextProductKey),
    UpdateContext({MyProductKey: MyNextProductKey})
)

Adding a next button to the details screen

For adding a previous button you just need to change the filter to 'smaller then' and then select the last record instead of the first.

UpdateContext(
    {
        MyPrevProductKey: Last(
            Filter(
                MyNumberedProductGallery,
                MyId < LookUp(
                    MyNumberedProductGallery,
                    ProductKey = MyProductKey,
                    MyId
                )
            )
        ).ProductKey
    }
);

4) The Result
Now lets start the app add test the Next button. The nice part is that you can filter or sort the gallery anyway you like and the next button will still go to the next record from that gallery.

Test the new next button

Summary
In this post you learned how to retrieve the next (and previous) item from a gallery. You could also add this code to a save button to create a save-and-edit-next-record action which saves the user a few extra clicks by not going back to the overview page to find the next record to edit.

For an approve and go to next record button where your gallery is getting smaller and smaller each time you approve a record you could also do something very simple in your form to get next item

// Old code
BrowseGallery1.Selected

// Alternative code
First(BrowseGallery1.AllItems)

Case
In a previous post you wrote that Microsoft Flow could help to delete records with my PowerApps. How do you add a Flow to PowerApps?

PowerApps ♥ Flow

Solution
To add a new Flow to PowerApps we have two options. First could go to Microsoft Flow and create a new Flow that starts with a 'PowerApps button' trigger and then go to PowerApps and use the new Flow (see step 7). The second option is to start from PowerApps and then use the menu to create a new Flow. For this example we will start from PowerApps.

The starting position is a table called Job with a primary key called JobId for which we have generated a PowerApps app. We will replace the code of the delete button to use a Flow instead.

1) Create new Flow
Edit your PowerApps app and go to the Action menu and click on Flows. A new pane will appear where you can edit existing Flows for PowerApps or you could create a new one from scratch. Click on 'Create a new flow'. This will open a new tab with Microsoft Flow.

Create new Flow

2) Rename Flow
It's important to rename the Flow before you save it otherwise the old default name will show in PowerApps. You can do this by clicking on the text 'PowerApps button' on the top left side op the Flow editor: 'DeleteJob'. A suitable descriptive name will also make it easier to find your Flow and will make your code more readable. You could also include the name of your PowerApps app. This will make it easier to understand the purpose of your Flow in case you have a whole bunch of them.

Rename Flow

3) New Step - SQL Server - Delete Row
Next step is the actual deleting part. Since our source is an Azure SQL Server Database we first search for 'SQL Server' and then choose 'Delete row'. If you already have database connections (like the ones in PowerApps) then you can reuse them. Click on the three dotes menu to change the connection.

Delete Row

Note: If you want to delete multiple records then you should use 'Execute a SQL query' or 'Execute stored procedure'. For this example we will only delete one row at a time.

4) Rename Step
Now first rename the SQL action. This will give the parameters in the next step better names. Again, click on the three dotes menu to change the name of the action: SQL Delete Job.

Rename SQL Action

5) Step Parameters
In the Table name property select the name of the table in which you want to delete records. In the Row id property we will add a parameter that will ask PowerApps for the value of the primary key column. If you click on the textbox you can add an expression. Go to the Dynamic content tab and click on 'See more' in the PowerApps row. This will show the option 'Ask in PowerApps'. Select it and you will see the new expression in the textbox.

Parameters

6) Save and Close
Now we are done with the Flow. Click on the save button and then close the tab to return to PowerApps editor. The list of existing Flows will now automatically show the newly created Flow.

Save and close

7) Use new Flow in PowerApps
Select the icon or button where you want to execute the Flow and then add the code: [NameOfYourFlow].Run([PrimaryKeyValue])

Is should look something like this. The Back() returns to the previous screen.
DeleteJob.Run(BrowseGallery1.Selected.JobId)

Add flow to icon

You could also add Back() to return to the previous screen:
DeleteJob.Run(BrowseGallery1.Selected.JobId);Back()
8) The result
Now it is time to test the newly added delete icon/button.

The generated app with the new delete icon

Conclusion
This post showed you how to add a Microsoft Flow to delete records within you PowerApps. This is especially useful in case you have more than 500 records in your table. The steps are fairly simple, but please keep in mind to use good descriptive names for the Flow and the steps within the Flow. This will make the code in the PowerApps more readable.

Case
I want to navigate to a new screen and get a value from the calling screen to filter a specific record on that new screen. Most examples (including 'Start from data') filter a record with the selected item from a specific gallery (on a specific screen) with:

BrowseGallery1.Selected

However I want to call this screen from various calling screens (not just one). Is there a push meganism instead of a pull meganism to filter the record on the new screen?

Navigating to a new screen from various screens

Solution
The solution is to pass a value from the calling screen to the new screen. The Navigate function has an optional argument called 'UpdateContextRecord' which allows you to pass a record to the new screen which on its turn updates the context variable on the new screen. Then you can use that variable to filter a specific record.

1) Calling screen(s)
In PowerApps a record with one column (a variable) looks like:

{myIntColumn:123}

If you put this in the 'UpdateContextRecord' argument from the Navigate function it looks like:

Navigate(myNewScreen, 
         ScreenTransition.None, 
         {myIntValue:123})

The hardcoded '123' should of course be replaced with some code to get the value from a gallery column on the calling screen. The Value function converts the string to a number:

Navigate(myNewScreen,
         ScreenTransition.None,
         {myIntValue:Value(ThisItem.myIntColumn)})

You need to repeat this on all calling screens that want to navigate to this new screen.

2) New screen
On the new screen this record will be pushed to a new context variable which can be used in for example a LookUp function in the Item property of a form:

LookUp('[dbo].[myTable]', myIntColumn=myIntValue)

Changed from pull to push

Alternative form item formula:

First(Filter('[dbo].[myTable]',myIntColumn= myIntValue))

Conclusion
In this post you learned how to pass through variables from one screen to an other. This can be used to pass through values for filters from several screens instead of just reading values from one screen.

Case
With PowerApps, Microsoft brought a new element to the existing world of reporting and dashboarding inside the Microsoft BI stack. For example, you can connect and customize your data using PowerApps. How does this work and how can you use PowerApps inside Power BI?

Power BI Marketplace - PowerApps custom visual

Solution
Important to know is that Microsoft PowerApps is part of Office 365. In case your organization does not have an Office 365 licence, PowerApps will cost 7 or 40 dollar per user per month. With a license, you can start building your own apps. There are more features available for an additional cost. More details about pricing here.

In this post we want to show you how you can connect to your data and store new data in an Azure SQL Database using PowerApps. For this example, you can give as Sales Employee approval (or not) on the report. This all happens in a Power BI dashboard using PowerApps.

1) Create new PowerApp
We will build the PowerApp using the Power BI Desktop and service. First, you have to create a new report in Power BI Desktop and add the PowerApps custom visual to the report. Choose PowerApps from the marketplace. Notice that this visual is still in Preview.

Power BI Desktop - Add PowerApps custom visual

For our example, we will use data from WideWorldImportersDW (Azure SQL Database) and import the "Employee" dimension and "Sales" fact table. We have implemented a number of transformations to keep only sales employees and created a 'Employee Full Name' column with values like 'Schuurman, Ricardo'. We choose the columns 'Employee Full Name' and 'Profit' in the PowerApps custom visual.

After you have published your report and opened it in the Power BI service, you will see the option Create new. You will be redirected to the development portal of PowerApps.

Power BI Service - Create new PowerApp using custom visual

Note:
When you create a new PowerApp using the Power BI service, a new dataset for the Power BI data will automatically be created with the name "'PowerBIIntegration'.Data".

2) Build PowerApp
Once you are in the development portal, you can start building your app. In a next post we will fine tune the app and explain and show you several elements of the PowerApp.

PowerApps Studio - Developing the PowerApp

Important to know is that we have created a new table in WideWorldImportersDW, called "SalesApproval".

USE [WideWorldImportersDW]

CREATE TABLE [PowerBI].[SalesApproval](
 [SalesApprovalKey] [int] IDENTITY(1,1) NOT NULL,
 [EmployeeFullName] [nvarchar](100) NULL,
 [Profit] [numeric](20, 8) NULL,
 [Approved] [nvarchar](50) NULL,
 [Comment] [nvarchar](max) NULL,
 CONSTRAINT [PK_Dimension_Employee] PRIMARY KEY CLUSTERED 
(
 [SalesApprovalKey] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

In this table we will store the data we have filled with the PowerApp. Later on we will show you how to do this and which data will be stored.

When you are done developing, you have to save and publish the PowerApp. After this, the app is live and available in several applications, such as Power BI and Microsoft Teams.

PowerApps Studio - Publishing the PowerApp

Note:
After saving and publishing your PowerApp it can happen that the PowerApp is missing the Power BI data the next time you open it. To fix this, go to the Power BI service and add a new Power Apps custom visual in the report. Select the right columns (in PowerApps Data) and click on Choose app. Add your existing app and go to the PowerApps Studio. Now your PowerApp is working again, including the Power BI data.

3) Result
Let's see if we can use this PowerApp in Power BI and store the result in the table "SalesApproval" we created earlier. Go to the Power BI service and open the report. Search for the right sales person (this is you as sales person if this dashboard is live) and fill in the form:
Yes or No for approval and associated comment (Text input). Press Submit when you are done.

Now if we look at the table in SQL Server Management, we see a new record containing the data we have filled in. Cool!

Power BI Service - Submit data in PowerApp (custom visual) and result

You can download the Power BI report here and the first version of the corresponding PowerApp we used for this post here. Use the Export (preview) and Import (preview) feature to add the app in your environment.

Note:
The Export and Import feature is still in preview. After preview, you must have PowerApps Plan 2 trial or PowerApps Plan 2 paid license for this feature.

Summary
In this post you saw how to use the PowerApps custom visual in Power BI. For this example we used PowerApps to approve (or not) a sales report and store this data in an Azure SQL Database.

In a next post we will describe and explain how this PowerApp is build. We will also customize the app to make it more user friendly.

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.

Latest News

e-book: Windows Virtual Desktop Migration Guide for Remote Desktop Services

Main Menu

You May Like

Follow Us!

Popular Posts

Trend

Bài mói nhất

Copyright Text