I'm trying to do something that seems relatively commonplace in a data visualisation tool, to filter by an ID but use a different value for the display value.
- I have a table "Payments" that contains a field "Account ID"
- I have a table "Accounts" that contains fields "ID" and "Name"
- I created a query "Account List" that returns the account values
I now want to add a dropdown filter to a Dashboard that shows a list of Account names. When selecting an account it should filter the dashboard by the selected Account ID.
I don't see any option for choosing a different display field in the filter options.
Other things I've tried/read:
- One suggestion I've seen uses a model to denormalise make the account name part of the same dataset. However, we need to cater for accounts with the same name so this won't work
- I've seen some posts that refer to setting up the foreign key relationship between the two tables (I'm not sure if this then enables some additional capabilities on filters?). However, I seem to have an issue that when configuring the field as a Foreign Key on my table, it doesn't actually allow me to select a table (the dropdown list is empty) - see screenshot
