Filters should support filtering by formatted value #8009
Description
Currently if you format a field then it also formats the field in the filters (for set value). However, that is just a label and it still filters on the underlying value. If you format multiple different values to the same value then they would show in the set filter multiple times which is then indistinguishable to the user which relates to which. E.g. I format 0.0001 and 0.0002 to 2dp then they would both show as 0.00 and user would not be able to understand the difference since it would appear twice but select each item individually. Or for instance if I had a formatter which grouped the data and set apple, banana, orange to Fruit and carrot, onion to Vegetable then you would have Fruit appear 3 times and Vegetable twice. Also this issue would apply to the complex filter also where user would try and find values using the formatted value and then it would not match
What I would like is for the filter to use the formatted value for matching on for both sides. So the search value and the target value would both be formatted and the unique values would be for formatted values.
The only alternative I can see if pre-enriching all the data outside the grid but it's much more convenient to be able to leave the underlying data as is and apply that formatting for the grid. Also if we format the numbers for dp then we will end up with string values and then we don't get the right handling in the grid for sorting, editing etc
Another consideration is that ideally for things like summaries it would be good to be able to carrying on getting the raw value (as well as formatted value possibly) so that if you were formatting things like numbers you could do the math on the raw data and then format that in the summary rather than using the formatted values which would give different result.