Unable to create dropdown filter

Get Help
1

I know I'm missing something in the process of create a dropdown filter for a dashboard, but I can't find what is wrong. Here are the things I've done so far:

  • Configure Metadata for the table, especifying a column (atm_id, a company identifier) as a foreign key, selecting values list as a filter and getting values from the appropiate column on the foreign table.

    02
    02353×537 17.8 KB

  • Next I created the query, very simple one with Field Filter on the column with foreign key, mapping the field to the correct column in the table, filter type ID and mandatory.

Here is the query:
select card_id, operationdate, ticket_id, tfxmz, HCXIM
from coga.LOGRECHARGE
where regtype in (1,2,11)
and operationdate >= to_date('01/01/2020','dd/mm/yyyy')
and {{atm}}

01
011898×658 73 KB

  • Finally I create a dashboard and put the question in. Then I select "another cathegory" filter, but the interface says there is no valid field. Selecting ID as type of filter just spawns a text box for writing manually the value.

What I'm doing wrong?

"system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_252-8u252-b09-1~16.04-b09",
    "java.vendor": "Private Build",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_252",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.252-b09",
    "os.name": "Linux",
    "os.version": "4.15.0-101-generic",
    "user.language": "es",
    "user.timezone": "Europe/Madrid"
  },

"application-database-details": {
      "database": {
        "name": "MySQL",
        "version": "5.7.30-0ubuntu0.16.04.1"
      },

"version": {
      "tag": "v1.35.4",
      "date": "2020-05-29",
      "branch": "enterprise-release-1.35.x",
      "hash": "b8ff861"
    },
2

Hi @RafaJ
Please use support email when using Enterprise Edition.

There are some limitations to how many items can be displayed in the dropdown, so how many unique values do you have in ATM_ID ?

If you have want to be able to show a dropdown of the names, then do this:

select card_id, operationdate, ticket_id, tfxmz, HCXIM
from coga.LOGRECHARGE
left join coga.NODE on coga.NODE.NODE_ID=coga.LOGRECHARGE.ATM_ID
where regtype in (1,2,11)
and operationdate >= to_date('01/01/2020','dd/mm/yyyy')
and {{atm}}

And then you set coga.NODE.NAME to List in Data Model, and refer to coga.NODE.NAME in the variable sidebar, which will have the “Filter widget type”=Category.

3

Thanks @flamber for your answer. There are only 3 unique values for ATM_ID.
I’ve tried your suggestion but with no success.

4

@RafaJ Okay, please use support mail and provide more screenshots and full “Diagnostic Info”