Problem with field filter

Field Filter Not Working - Parameter Type Issue

Problem Description

I have a SQL query with two filters that was working perfectly when using Text parameters, but stopped working after converting one parameter to a Field Filter.

SQL Query

SELECT DISTINCT
    mdl_user.id as parent_id,
    mdl_user_2.id as child_id,
    mdl_user_2.firstname as child_firstname,
    mdl_user_2.lastname as child_lastname,
    mdl_user_2.email as child_email,
    CONCAT(mdl_user_2.firstname, ' ', mdl_user_2.lastname) AS child_fullname
FROM mdl_user mdl_user_2
INNER JOIN mdl_context ON mdl_context.instanceid = mdl_user_2.id AND mdl_context.contextlevel = 30
INNER JOIN mdl_role_assignments ON mdl_role_assignments.contextid = mdl_context.id
INNER JOIN mdl_user ON mdl_user.id = mdl_role_assignments.userid
WHERE mdl_user_2.deleted = 0 
    AND mdl_user.deleted = 0
    [[AND mdl_user.id = {{moodle_user_id}}]]
    [[AND CONCAT(mdl_user_2.firstname, ' ', mdl_user_2.lastname) = {{child_name}}]]

What Works vs What Doesn't Work

:white_check_mark: WORKING - Text Parameter

  • Parameter type: Text
  • Test condition: AND mdl_user.id = 85 ← This works perfectly
  • Parameter usage: When I manually type "85" in the text field, I get results

:cross_mark: NOT WORKING - Field Filter

  • Parameter type: Field Filter
  • Field mapping: moodle_user_idmdl_user.id
  • Widget type: Input box (surprisingly, there's no dropdown for ID fields)
  • Display format: No suggestions appear while typing - I have to guess and type values like "Florent - 85" (Name - ID format)
  • Issue: When I type "Florent - 85" and press Enter, I get zero results

What I've Tried

  1. Type casting: CAST({{moodle_user_id}} AS UNSIGNED)
  2. String extraction: SUBSTRING_INDEX({{moodle_user_id}}, ' - ', -1)
  3. Regex extraction: REGEXP_SUBSTR({{moodle_user_id}}, '[0-9]+
  4. String comparison: CAST(mdl_user.id AS CHAR) = CAST({{moodle_user_id}} AS CHAR)

None of these solutions work.

Unexpected Behavior with Field Filter

I find it surprising that when using a Field Filter mapped to an ID field (mdl_user.id), Metabase doesn't provide:

  1. No dropdown with all available options
  2. No auto-suggestions while typing
  3. No indication of what format I should use to input values

This seems counterintuitive for ID-based filtering where users typically want to see all available options or at least get hints about the correct format.

Expected Behavior

When I type "Florent - 85" in the Field Filter input and press Enter, it should:

  1. Extract the ID value (85) from the display format
  2. Use this ID value in the WHERE clause: mdl_user.id = 85
  3. Return the same results as the working text parameter

Current Behavior

When I type "Florent - 85" in the Field Filter input and press Enter:

  • The query returns zero results
  • It seems like Metabase is passing the entire string "Florent - 85" instead of just the ID "85"
  • There's no feedback on whether the format I'm using is correct or not

Questions

  1. How should Field Filter parameters work when the display format is "Name - ID" but I need to filter on just the ID?
  2. Is there a way to configure the Field Filter to pass only the ID value instead of the display string?
  3. Why doesn't Metabase show a dropdown or suggestions for ID field filters? Is this expected behavior?
  4. What's the correct input format for Field Filters mapped to ID fields?
  5. Should I use a different parameter type for this use case?

Environment

  • Metabase version: [Your version here]
  • Database: MySQL/MariaDB
  • Table structure: mdl_user.id is an INTEGER field

Additional Context

This is a Moodle database where I'm creating a parent-child relationship report. The moodle_user_id parameter is meant to filter for a specific parent user, and it works perfectly as a text parameter but fails as a field filter.

Any help would be greatly appreciated!)4. **String comparison**:DISCOURSE_PLACEHOLDER_8`

None of these solutions work.

Unexpected Behavior with Field Filter

I find it surprising that when using a Field Filter mapped to an ID field (DISCOURSE_PLACEHOLDER_9), Metabase doesn't provide:

  1. No dropdown with all available options
  2. No auto-suggestions while typing
  3. No indication of what format I should use to input values

This seems counterintuitive for ID-based filtering where users typically want to see all available options or at least get hints about the correct format.

Expected Behavior

When I type "Florent - 85" in the Field Filter input and press Enter, it should:

  1. Extract the ID value (85) from the display format
  2. Use this ID value in the WHERE clause: DISCOURSE_PLACEHOLDER_10
  3. Return the same results as the working text parameter

Current Behavior

When I type "Florent - 85" in the Field Filter input and press Enter:

  • The query returns zero results
  • It seems like Metabase is passing the entire string "Florent - 85" instead of just the ID "85"
  • There's no feedback on whether the format I'm using is correct or not

Questions

  1. How should Field Filter parameters work when the display format is "Name - ID" but I need to filter on just the ID?
  2. Is there a way to configure the Field Filter to pass only the ID value instead of the display string?
  3. Why doesn't Metabase show a dropdown or suggestions for ID field filters? Is this expected behavior?
  4. What's the correct input format for Field Filters mapped to ID fields?
  5. Should I use a different parameter type for this use case?

Environment

  • Metabase version: v0.54.5.4
  • Database: MySQL/MariaDB
  • Table structure: DISCOURSE_PLACEHOLDER_11 is an INTEGER field

Additional Context

This is a Moodle database where I'm creating a parent-child relationship report. The DISCOURSE_PLACEHOLDER_12 parameter is meant to filter for a specific parent user.

Any help would be greatly appreciated!

Hi there,

Thanks for providing such detailed context :folded_hands:
Can you try the following, let me know if it improves things:

  • Go to Admin > Table Metadata > User table > Id field
  • Its type should be Entity Key
  • Set "Filtering on this field" to "Search box" or "A list of all values" (you can try both and see which one works better for your use case)
  • You can also set the name column of the user to type "Entity Name" so that it's associated with the search.

With "A list of all values" on the ID it would look like this (it shows the Entity Name before each ID):

1 Like

Nice thanks !
I have an other question.
I see a limit of 1000 results in the field filter? Can this limit be changed ?

This limit can't be changed currently. If there's more than 1000 values, then it would be more suitable to use a Search Box instead

Additional Context and Feature Requests

Use Case: Dashboard with Linked Field Filters

I want to use these field filters to create linked filters on a dashboard. I'm working with a large database containing many users who are parents, and consequently, a large number of children linked to these parents. My goal is to use linked filters to display only the children of selected parents.

Current Limitations

1000-Record Limit Issue

The 1000-record limit is very restrictive for my use case. With hundreds of parents and potentially thousands of children, this limitation significantly impacts the functionality.

Lack of Customizable Linked Fields

It would be extremely valuable to have more customizable linked field options. For example, being able to limit the data retrieved from a table field would be highly beneficial.

Current Workaround and Its Problems

In my current situation, I need to either:

  • Create new tables in my database, or
  • Create views in my database with only the data I need

Scalability Challenge: I potentially have hundreds of platforms to integrate, meaning hundreds of databases to connect to Metabase. All these platforms share the same database schema, which makes this even more frustrating. With the current limitations, I would need to add a table/view to each database, making the solution very difficult to maintain.

Workflow Inefficiency: Since all platforms have identical schemas, my ideal workflow would be to create questions once and then simply copy-paste them while changing only the data source. However, current limitations force me to modify the database layer for each platform instead of handling customization at the question level.

Metabase Limitations for My Use Case

Through this implementation, I've identified several limitations in Metabase for large-scale, multi-database environments with identical schemas. These limitations significantly impact productivity and maintainability when working with enterprise-level deployments.

I would be happy to share detailed feedback about these limitations and potential solutions based on my real-world experience.

The ideal approach would be to handle everything within Metabase's question/dashboard parameters, requiring minimal database modifications. Even when linking foreign keys in the Data Model, it would be great to have an option to configure this directly within the question that contains the linked filters.

Ideal Solution

  1. Increase or remove the 1000-record limit for field filters
  2. Enhanced customizable linked fields with data filtering capabilities
  3. Question-level customization that works seamlessly with identical database schemas
  4. Copy-paste workflow optimization for questions across different data sources
  5. In-app foreign key linking without requiring database schema modifications
  6. More flexible field filter configuration to reduce database maintenance overhead

Feature Requests Summary

Conclusion

These enhancements would significantly improve Metabase's usability for large-scale, multi-database environments and reduce the maintenance burden on database administrators.


P.S. I'm currently looking for an apprenticeship for my final master's year - if you want to assign me this feature development project, I'd be delighted to contribute! I have extensive real-world experience with Metabase's limitations in enterprise environments and would love to help improve the platform. :grinning_face_with_smiling_eyes: