I have a filtering problem I haven't been able to resolve and I hope you can help.
Trying to filter fixed length (varchar(10) etc.) columns on SQL Server only shows "is empty" and "not empty" options. I have the following database schema where the Customer column is typed as Key10 which is a shortcut for varchar(10).
I can filter by the Quote column just fine, even though behind the scenes it is also varchar(30). But regardless of how I change the Table Metadata on Metabase for the customer column, it doesn't allow me to filter by Customer. Please note that this is only the tip of the iceberg as there are a lot of other columns that has fixed length that the users want to filter by.
When I try to filter, I can only get the "is empty" and "not empty" options. I tried changing the column definition on Metabase to various options like category and entity key but couldn't make it work. This issue also cascades, in the sense that if I create a filter in a dashboard, all the user can get is the same empty/not-empty options. You may think that the customer column is a foreign key and I should show the customer name to the user, but there are 20 more columns like this and also customer would like to filter by the customer id column.
The problem is likely Metabase doesn’t know how to deal with the user-defined Key type.
Metabase needs to learn how to determine the base data type for user-defined types in SQL Server so it can provide the right options. Right now those Key types are being considered opaque data and that’s why you’re not getting the ability to set filters other than NULL/not NULL.
A workaround is to create a view that casts the columns with the user-defined types to their base type, and use the view for writing questions.
Thank you @dwhitemv it looks like this was the issue.
I was able to create a custom column with the text([Customer]) formula that translates to CAST("Customer" AS varchar(256)) so it kind of works. I said kind of because if I use the exact same column name of Customer as the custom column name, despite the varchar(256) conversion I get the same problem. Please see below.
I’ll add these details to the GitHub issue tomorrow. I don’t know if it would have any priority but a lot of existing ERP and QMS applications use table schemas like this one.
There’s probably a bug in the metadata matching that needs some exploration. You should be able to overlay it, at least with a Model. In a Question, especially a SQL one, it might not know which Customer column you’re referring to. I assume it works right if you give it a unique name?
Have you tried casting the data type in the table metadata?
It works great with a unique name as a workaround.
I was just experimenting to see if I can keep the original column names, because that’s what the end-users are used to and also this is an ERP system so there are a lot of tables (100+).
The data type casting becomes availably only after changing the semantic type to something other than foreign key, I updated it as “No Semantic Type” and these are the only casting options Metabase provides.
Going back to the root cause, I think this is all due to the use of what Microsoft calls “User-Defined Data Type Alias”. The ERP vendor wanted to standardize things like Customer ID etc. so they created a custom type and called it Key10 and at the moment Metabase gets confused by these types.
