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
CrmThings 