Dependent dropdowns in Canvas Apps : Sorting, Lookup and Filter

Introduction:
Drop down is one of the commonly used input types in canvas app development.
This article illustrates the use of sorting, lookup and filter in case of using cascading dropdowns.

Scenario:
Consider two entities in the CDS, which is country and state with a 1:N relationship between them. The state dropdown should display relevant states based on the selected country from the country dropdown.

Data:
Countries :
India, Turkey and UK
States:
Karnataka, Delhi and Kerala (country:India)
Antalya, Istanbul and Cappadocia (country:Turkey)
Bristol, Manchester and Liverpool (country:UK)

Canvas Apps:
Let us add 3 dropdown inputs in the canvas app and see how to achieve this scenario.

Country dropdown will be used to display country information and data source will be country entity in CDS.
Set the formula for the items property as : Sort(Distinct(Countries,Name),Result)
This formula will fetch the distinct countries based on the name and sort the result alphabetically

State dropdown with filters will be used to display the state information and data source will be state entity in CDS.
Set the formula for the items property as:
SortByColumns(Filter(States,Country.Name=dd_Country.SelectedText.Value),”new_name”)
This formula will filter the states based on the country dropdown selected value and sorts the result by the state name alphabetically. 

State dropdown 2 uses the lookup function to lookup the country that is selected in the country drop down and then displays the state results for that country, here there results are not filtered and displayed in the order the are returned.
Set the formula for the items property as:
LookUp(Countries,Name=dd_Country.Selected.Result).States.Name
This formula will lookup to countries in the cds based on the country drop down selected result and then displays the states for the selected country

 

Thanks,
Shayan

 

 

 

Using IsBlank with Gallery Items to hide empty records

This post details about the use of Isblank function in power apps.

Scenario : A power apps user enters details such as name, phone and comments and adds them to a collection say “ContactInfo”.
The items from the collection is then displayed in the gallery.

Lets take a look at the below screen displaying records in a gallery where title is set to name and subtitle is set to phone.

Problem : The user can leave the name field as empty resulting the collection to not have any name, hence the gallery displays records with out name. This is because the items property of the gallery is simply set to the collection “ContactInfo”

 

Requirement : The gallery should be filtered such that it displays only items that has name

Solution : Filter + IsBlank

The IsBlank function tests for a blank value or an empty string, whose result can be used to filter the items property of the gallery, in our case we need to check if there is a name in the collection”ContactInfo”, if yes then display the collection items in the gallery, otherwise do not display.

Notice after the Items property is set with the below function  the gallery will only display records which has a value for the name.
Filter(ContactInfo, !IsBlank(Name));

 

 

 

Thanks,
Shayan