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
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
NOT WORKING - Field Filter
- Parameter type: Field Filter
- Field mapping:
moodle_user_id→mdl_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
- Type casting:
CAST({{moodle_user_id}} AS UNSIGNED) - String extraction:
SUBSTRING_INDEX({{moodle_user_id}}, ' - ', -1) - Regex extraction:
REGEXP_SUBSTR({{moodle_user_id}}, '[0-9]+ - 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:
- No dropdown with all available options
- No auto-suggestions while typing
- 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:
- Extract the ID value (85) from the display format
- Use this ID value in the WHERE clause:
mdl_user.id = 85 - 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
- How should Field Filter parameters work when the display format is "Name - ID" but I need to filter on just the ID?
- Is there a way to configure the Field Filter to pass only the ID value instead of the display string?
- Why doesn't Metabase show a dropdown or suggestions for ID field filters? Is this expected behavior?
- What's the correct input format for Field Filters mapped to ID fields?
- Should I use a different parameter type for this use case?
Environment
- Metabase version: [Your version here]
- Database: MySQL/MariaDB
- Table structure:
mdl_user.idis 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:
- No dropdown with all available options
- No auto-suggestions while typing
- 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:
- Extract the ID value (85) from the display format
- Use this ID value in the WHERE clause:
DISCOURSE_PLACEHOLDER_10 - 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
- How should Field Filter parameters work when the display format is "Name - ID" but I need to filter on just the ID?
- Is there a way to configure the Field Filter to pass only the ID value instead of the display string?
- Why doesn't Metabase show a dropdown or suggestions for ID field filters? Is this expected behavior?
- What's the correct input format for Field Filters mapped to ID fields?
- 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_11is 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!
