How to Create a Combobox-Filtered Data Table

Overview

In this tutorial, we have two separate Avonni Comboboxes: one shows a list of states, and the other shows a list of industries.

We use a formula text variable as the query filter to filter our Data Table dynamically. This formula updates based on what's selected in each combo box. For example, if you choose "Investor" and "Banking," the Data Table (which is linked to the Account object) will only show accounts with an investor type that belongs to the banking industry.

Final Result

Guided Steps

1. Create Your Screen Flow

• Begin by setting up a new screen flow within your Salesforce environment.

2. Build the Layout

• Add a Section element to your screen flow.

• Configure the section to have two columns side-by-side.

• In each of these columns, you'll place an Avonni Combobox component. These comboboxes will serve as our filters.

3. Insert the Avonni Data Table

• Drag and drop the Avonni Data Table component into your screen flow. This is where your filtered data will be displayed.

4. Configure the Data Table's Data Source

• Query Data Source: Choose this option to fetch data dynamically.

• Select Object: Specify the Salesforce object you want to query (e.g., Accounts, Contacts, Opportunities).

• Add Fields as Columns: Select the fields from your object that you want to display as columns in your Data Table.

5. The Magic of Dynamic Filtering – Create a Formula

• Formula Text Variable: Create a new formula variable of the "Text" type.

• WHERE Clause: This formula will act as the WHERE clause in your SOQL query. It will combine the selections from your Avonni Comboboxes to create a dynamic filter condition.

• Example: Let's say your comboboxes filter by Industry and Rating. Your formula might look like

"Type= '"&{!TypeCombobox.value}&"' AND Industry = '"&{!IndustryCombobox.value}&"'"

Understanding the Formula

What It Does

This formula is designed to dynamically create a filter for your Salesforce data based on selections made in two comboboxes:

• TypeCombobox: This is the API name of the Avonni Combobox component configured to display account type picklist values (like "Account" or "Contact").

• IndustryCombobox: This combobox lets users select an industry (e.g., "Technology," "Finance," etc.).

When a user selects these comboboxes, the formula combines their choices into a single filter condition enclosed within quotation marks. This filter is then used to retrieve only the records that match both the selected type and industry.

How It Works

1. Type= ': In our example, we filter by the "Type" field, which is a picklist field on the Account object (as shown in the screenshot). The single quotation mark (') is added because we're looking for a specific text value, and the equal sign (=) is used to compare this picklist field to the value selected in the combobox.
2. &{!TypeCombobox.value}&': This is where the dynamic filtering occurs. It fetches the value the user selected from the TypeCombobox (using its API name) and inserts it into the formula. The & symbols are used to concatenate (join) the different parts of the formula together. The second single quotation mark closes the text string we started earlier.

3. AND Industry = ': This part expands the filter to include the "Industry" picklist field from the Account object. The AND operator means that a record must match the chosen Type and the chosen Industry to be displayed in the Data Table..

4. &{!IndustryCombobox.value}&"': Similar to step 2, this pulls the user's selection from the IndustryCombobox and inserts it into the formula.

6. Make the Data Table Reactive

• Your formula variable should be referenced within the "Query Data Source" configuration of your Avonni Data Table. This will automatically refresh the Data Table whenever the user changes the selections in the combo boxes.

How to assign the created formula as a dynamic filter

Troubleshooting

Have trouble getting your reactive query formula to work? Here are some tips and tricks to help you troubleshoot and get the results you expect.

Validate information returned using the Display Text

To ensure your formula works as intended, you can use a Display Text element to see the actual value your formula returns. This helps you confirm if the result makes sense and can be used effectively in a SOQL query.

Formula rendering in action using the Display Text

Test Your Formula in a SOQL Query

You can also validate your formula in a SOQL query but adapt it slightly. Remove the extra symbols and variable references that are specific to Flow Builder.

For example, if your formula is

"Type= '"&{!TypeCombobox.value}&"' AND Industry = '"&{!IndustryCombobox.value}&"'"

you would modify it for a SOQL query like this: Type = 'Investor' AND Industry = 'Banking'

Be sure to replace "Investor" and "Banking" with actual values from your Salesforce data to ensure the query returns results. If you get an error, it indicates an issue with your formula syntax.