Hi all
Our current database has an enum column where values are stored as numbers which correspond to certain states. The problem is that several states only exist for short periods of time, and therefore at any given time there might be no records in the database having these states.
Anyway, when they do happen, it is important to show them, and also users should be able to search for them and create dashboards and alerts using these states.
The problema is that currently in the UI we can only map custom display strings to values that currently exist in the database. We have tried modifying the fields directly in the database, but the mapping is reset as soon as the database synchronizes.
Is there any way to create custom mappings for ENUM values that don’t currently exist, but which we know that will exist in the future?
v0.31.2
Docker
Chrome
Hi @diego0020
Interesting challenge. What if you manually set the fields directly in the database (like you already tried) and then in “Databases” you would disable the sync of “Scanning for Filter Values” via enabling “This is a large database …”, clicking “Save” and then go to the “Scheduling” tab ?
Then you might want to sync some other tables, but that could be done via the API, where you would loop through all the tables and then ignore the one(s) with the manually modified enum field values.
Haven’t tested this, but it seems like you have an uncommon problem, so expect uncommon solutions 
Until one of these issues have been implemented, I guess you have to do things the hacky way:
https://github.com/metabase/metabase/issues/5245
https://github.com/metabase/metabase/issues/6820
Alternatively, create a lookup table that contains all the possible enum values. 2nd column needs to be either a duplicate or description of the enum.
Then use foreign key.
If you can’t create a new table, you could use a view with lots of unions. Just long winded.
Thanks all for the ideas.
Unfortunately we can’t turn off the “scanning of filter values” for the whole table because we need it for some other columns.
Is there any way to turn it off by column?
Where can we find more information about the API you mention?
We also tried generating another table with the mapping, but we found two problems.
Our model has several tables in a star pattern, and the “state” field is in the middle table. Therefore when we start a question with one of the external tables, there is no way to make a join all the way to the new labels map table.
When we start a question in the middle table, we can access the mapping, but it will only show options in filters for the values that currently exist.
We are also thinking of running an update in the metabase database after every filter values sync. For the moment the only idea we have is monitoring the logs looking for the message, and afterwards run our update. Would there be a more elegant way of doing something like this?
Thanks again for your attention
@diego0020
I don’t think you can sync specific columns only. I’m not sure what I would do in your case. If you cannot make it work with a lookup table, like @AndrewMBaines suggested, then maybe just having a trigger, which resets the field data to whatever you define every time it’s sync’ed to the wrong data.
API info:
https://github.com/metabase/metabase/wiki/Using-the-REST-API
https://github.com/metabase/metabase/blob/master/docs/api-documentation.md
