Values property

The values that can be selected in the drop-down field.

This property can be set using a formula to dynamically determine which values are available. This enables powerful features such as populating drop-down values from data tables, creating multi-level drop-downs where the values of one drop-down depends on another drop-down and filtering values based on other fields, dates or user roles.

Data table integration

A simple and powerful way to use formula-driven drop-downs is by linking them directly to data table columns. This eliminates manual copying and keeps everything synchronized automatically.

Consider a product inventory table stored in a form screen named Products with columns (in the form of named values) that include ProductName, Category, Brand and Status. To create a drop-down showing all product names, assign this formula to the Values property:

The drop-down now displays all product names from the table, and any changes to the data table automatically appear in the drop-down.

When working with data tables, you often want to remove duplicate entries and sort the values. Use the UNIQUE function to show each value exactly once:

This shows each category exactly once, even if multiple products belong to the same category. Combine SORT to organize the values alphabetically:

Static arrays with conditional values

Create arrays with specific text strings using curly braces. Elements are separated by commas, and you can include values conditionally using the IF function.

The formula above includes the text "Pending" only when the Priority field is valid, otherwise the drop-down shows just "Active," "Inactive" and "Complete."

Multi-level drop-downs

Create drop-downs that adapt their values based on selections in other drop-downs. This is perfect for hierarchical relationships like department names and employee names, or product categories and brand names.

Consider a scenario where you have a text drop-down field called Department (with values like "Engineering," "Marketing" and "Sales") and you want a text drop-down field called EmployeeName to show different employee names depending on which department the user selects.

First, create named values containing the employee names for each department:

• EngineeringDepartments: { "Alice Johnson", "Bob Smith" }{ "Alice Johnson"; "Bob Smith" }
• MarketingDepartments: { "David Wilson", "Emma Brown" }{ "David Wilson"; "Emma Brown" }
• SalesDepartments: { "Grace Taylor", "Henry Moore" }{ "Grace Taylor"; "Henry Moore" }

Then assign this formula to the EmployeeName drop-down's Values property:

Now when the user selects "Engineering" from the Department drop-down, the EmployeeName drop-down automatically shows only the engineering employee names.

Filtering

Use the FILTER function to narrow down values based on various criteria, making large data sets manageable and relevant.

Basic data table filtering

The simplest use of FILTER creates dependencies between drop-downs by filtering one data table column based on another drop-down's selection.

Consider the multi-level drop-down scenario from the previous section, but using data table filtering instead of named values. You need an employee data table stored in a form screen called Employees with columns EmployeeName and Department. Then assign this formula to the EmployeeName drop-down's Values property:

Here's how this works: the FILTER function takes two parameters. The first (Employees!EmployeeNameEmployees!EmployeeName) is the array of employee names you want to filter. The second (Employees!Department = DepartmentEmployees!Department = Department) creates a logical array by comparing each department name in the data table to the value selected in the Department drop-down field.

When the user selects "Engineering" from the Department drop-down, the comparison Employees!Department = DepartmentEmployees!Department = Department produces an array like { TRUE, FALSE, TRUE, FALSE, TRUE }{ TRUE; FALSE; TRUE; FALSE; TRUE }, where TRUE appears for each employee in the Engineering department. The FILTER function then returns only the employee names where the corresponding logical value is TRUE.

This approach automatically maintains relationships without requiring manual updates when new employees are added to the data table.

Text length filtering

Show only values that meet specific text length criteria using the LEN function. This is perfect for filtering by name length, code formats or description brevity.

Here's how this works: LEN(Products!ProductName)LEN(Products!ProductName) returns the character count for each product name, creating numeric arrays. LEN(Products!ProductName) >= MinimumLengthLEN(Products!ProductName) >= MinimumLength produces TRUE for each product with a name at or above the minimum length, while LEN(Products!ProductName) <= MaximumLengthLEN(Products!ProductName) <= MaximumLength produces TRUE for each product at or below the maximum length. The && operator combines these arrays, returning TRUE only where both conditions are met. Only product names where both conditions are TRUE appear in the drop-down.

Text-based filtering

You can easily filter text values based on the value of another text field.

Continuing with the Products table, to show only product names that contain text entered in a Search field, use this formula:

The CONTAINS function creates a logical array by checking if each product name contains the search text. LOWER is applied to both the product names and the search text for case-insensitive matching. When users type "wireless" in the Search field, only product names containing "wireless" (regardless of capitalization) appear in the drop-down.

Optional criteria filtering

When filtering data with multiple criteria, users often want to filter by some conditions while ignoring others. Without optional criteria, filtering becomes too restrictive and forces users to specify values for every filter, even when they don't care about certain attributes.

Consider an inventory scenario where users want to find product names. They might care about the category ("Electronics") but not about the status, or care about the status ("Active") but not about the category. Traditional filtering would require them to specify both the category and the status, making it impossible to see "all active items regardless of category" or "all electronics regardless of status."

The solution is giving users three options for each filter criterion: select a specific value, select a different value, or leave it blank (meaning "I don't care"). This transforms restrictive "must match everything" filtering into flexible "match what I specify, ignore what I leave blank" filtering.

Returning to the Products table from earlier examples (which now includes Category and Status columns), here's a formula that lets users filter by category, status, both, or neither:

Here's how this works: ISBLANK(CategoryFilter)ISBLANK(CategoryFilter) returns TRUE when no category is selected ("don't care"), while Products!Category = CategoryFilterProducts!Category = CategoryFilter returns TRUE when the product's category matches the selected filter.

The || operator means "or," so the first condition returns TRUE when either the filter is blank or the category matches. The same logic applies to the status filter. Only when both combined conditions are TRUE does a product name appear in the drop-down.

Date-based filtering

Filter text values based on associated dates, showing only items within user-specified date ranges. This technique works well for event names, project titles or any text identifier tied to temporal data.

Consider an event management data table with EventName and EventDate columns. With StartDate and EndDate date and time fields controlling the time window:

This creates two logical arrays: one for dates on or after the start date, and another for dates on or before the end date. The && operator combines them, returning TRUE only for events within the specified date range. Users can set narrow ranges (like a single week) or broader ones (like a full quarter) to focus on relevant events.

Role-based filtering

Show different values based on the signed-in user's role or permissions. This is perfect for multi-tenant applications where different users should see different data.

Using the USERHASTAG function to check user roles:

This returns only project names where the signed-in user has the required access role. Each row in the ClientProjects table specifies which role is needed to see that project name.

You can also filter based on email domain matching:

The ENDSWITH function checks if the user's email address ends with the domain from each row, automatically determining company affiliation without manually assigning tags to users.

Dynamic sorting

Let users control how drop-down values are organized by sorting the same data in different ways.

Creating sort controls

First, create a text drop-down field named SortBy with meaningful choices:

• Name (A-Z)
• Name (Z-A)
• Date added (newest first)
• Date added (oldest first)

Implementing dynamic sorting

Use SORTBY with SWITCH to sort based on user selection:

Multiple coordinated drop-downs

Create multiple drop-downs that all respond to the same sort control:

Now users can see both employee names and product names sorted by the same criteria, providing complementary views of their data.

Examples