In this blog we will cover how to filter records in list based on condition using Filter function. Also we will see how to filter child records based on parent selected item.

Syntax – Filter (Table, Formula1 [, Formula2, … ] )

Filter function looks for all records in a table and returns only those records that satisfy formula.

Filter for Active Companies:

  • Select Gallery control from left navigation (BrowseGallery1 in my case for companies list). Select Items property of Gallery control. By default, for all apps created from CDS for Apps, Gallery control Items property is set as below:

SortByColumns(Search(Companies, TextSearchBox1.Text, “cr2a5_companyname”), “cr2a5_companyname”, If(SortDescending1, Descending, Ascending))

    • Companies – Data Source (with no filters)
    • Search function – Search for records in gallery containing text in search text box
    • SortByColumns function – Sort records in gallery based on SortDecending1 variable value
  • Change Items property value as below to include Filter function to filter for Active companies

SortByColumns(Search(Filter(Companies,_statuscode_label = “Active”) , TextSearchBox1.Text, “cr2a5_companyname”), “cr2a5_companyname”, If(SortDescending1, Descending, Ascending))

PP1

  • Gallery will now be filtered to display only Active Companies. Similarly, you can filter based on any other column value. You can also define multiple formulas to filter results. Refer blog to see some examples using Filter function.

Filter Products list based on selected Company:

In company detail screen, we will add a Gallery control to display list of Products. We will then filter list to show products only for selected company.

  • Navigate to DetailScreen. From Insert tab on top navigation, click on Gallery option to add Gallery control (Vertical Layout) on DetailForm. Position Gallery control to lower half of screen

PP2

  • Select Company Products as data source, Title-Sub Title-Body as layout, Expiration Date as Body, Status as Sub Title and Product Name as Title in Data Properties window on Galley control

PP3

  • Select Products gallery control, set Items property of Gallery control as below to filter product based on selected company using cr2a5_companyid column. Once filter is applied, list will display products for selected company only.

Filter(‘Company Products’, cr2a5_Company1.cr2a5_companyid = BrowseGallery1.Selected.cr2a5_companyid)

PP4

  • Select Sub Title control from within Gallery control and set Text property as below to display status as “Expired” if expiration date exists.

If(IsBlank(cr2a5_expirationdate), ThisItem._statuscode_label, “Expired”)

  • Select Body control from within Gallery control and set Text property as below to prepend “Expiration Date: “ to date value

If(IsBlank(cr2a5_expirationdate), “”, “Expiration Date: ” & ThisItem.cr2a5_expirationdate)

PP6

Part 3

Part 5