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 ProductId, Price, Quantity and CategoryId. To create a drop-down showing all product IDs, assign this formula to the Values property:

The drop-down now displays all product ID numbers 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 ID exactly once, even if multiple products belong to the same category. Combine SORT to organize the values in ascending order:

This formula makes your drop-down show each unique price point in ascending order. Use this formula for descending order:

Static arrays with conditional values

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

The formula above includes the number 30 only when the Priority field is valid, otherwise the drop-down shows just 10, 20 and 40.

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 codes and employee IDs, or product categories and model numbers.

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

First, create named values containing the possible employee ID numbers for each department:

• EngineeringCodes: { 1001, 1002, 1003, 1004 }{ 1001; 1002; 1003; 1004 }
• MarketingCodes: { 3501, 3502, 3503 }{ 3501; 3502; 3503 }
• SalesCodes: { 2201, 2202, 2203 }{ 2201; 2202; 2203 }

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

Now when the user selects "Engineering" from the Department drop-down, the EmployeeId drop-down automatically shows only the engineering employee ID numbers.

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 EmployeeId and Department. Then assign this formula to the EmployeeId drop-down's Values property:

Here's how this works: the FILTER function takes two parameters. The first (Employees!EmployeeIdEmployees!EmployeeId) is the array of employee ID numbers 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 IDs 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.

Numeric range filtering

Show only values within specific ranges using comparison operators. This is perfect for price ranges, score brackets or quantity levels.

Using the same Products table from the earlier data table integration example, imagine users want to see only product codes for items within a certain price range. With MinimumPrice and MaximumPrice number fields, use this formula:

Here's how this works: the comparison operators create two logical arrays. Products!Price >= MinimumPriceProducts!Price >= MinimumPrice produces TRUE for each product with a price at or above the minimum, while Products!Price <= MaximumPriceProducts!Price <= MaximumPrice produces TRUE for each product at or below the maximum. The && operator combines these arrays, returning TRUE only where both conditions are met. Only product codes where both conditions are TRUE appear in the drop-down.

Text-based filtering

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

Continuing with the Products table, to show only product codes where the product name contains 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 codes for items with "wireless" (regardless of capitalization) in their names 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 codes. They might care about the category ("Electronics") but not about the priority level, or care about the priority level ("High") but not about the category. Traditional filtering would require them to specify both the category and the priority, making it impossible to see "all high-priority items regardless of category" or "all electronics regardless of priority."

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.

To implement this, use text drop-down fields for your filters (like CategoryFilter and PriorityFilter) that can be left blank, then combine ISBLANK with the || operator.

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

Here's how this works: ISBLANK(CategoryFilter)ISBLANK(CategoryFilter) returns TRUE when no category is selected ("don't care"), while Products!CategoryId = CategoryFilterProducts!CategoryId = 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 priority filter. Only when both combined conditions are TRUE does a product code appear in the drop-down.

Date-based filtering

Filter numeric values based on associated dates, showing only items within user-specified date ranges. This technique works well for event IDs, sequential tracking numbers or any numeric identifier tied to temporal data.

Consider an event management data table with EventId 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 user roles or company affiliation. In private apps, this ensures users only see data they're authorized to access.

Using the USERHASTAG function to check user permissions against a Projects data table with ProjectCode and AccessRole columns:

The USERHASTAG function creates a logical array by checking if the signed-in user has the tag specified in each row's AccessRole column. Only project codes where the user has the required role appear in the drop-down.

For company-based filtering using email domains, consider a client projects table with ProjectCode and ClientDomain columns:

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:

• Value (lowest first)
• Value (highest first)
• 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 account numbers and product codes sorted by the same criteria, providing complementary views of their data.

Examples