Hi everyone,
Probably an easy one for some of you seasoned professionals...
The purpose of this question is that in a table view, I want to be able to filter in the search bar by a column [Postcode] (more than one at a time though) then download the result as a CSV to use elsewhere. A postcode is a four digit number here in NZ. So to be able to select multiple postcodes, it has to be an Enum type.
I have [Postcode] as a virtual column with the expression IF(INDEX(SORT(EXTRACTNUMBERS([Client Address]),TRUE),1)>9999,INDEX(SORT(EXTRACTNUMBERS([Client Address]),TRUE),2),INDEX(SORT(EXTRACTNUMBERS([Client Address]),TRUE),1))) which is to extract the postcode for each record. Then wrap it with TEXT() to return this as Text rather than a number.
To give the ability to choose more than one postcode in the filter, I made the virtual column of type Enum, then valid_if to be a SELECT() expression to return a list of all postcodes from another table.
However... The Postcode value, when appearing as an Enum, gets formatted to have two decimal places. So of course it now won't match the postcodes in the lookup table. I can see in the TEXT() help page, normally you would use TEXT("xxxx") to do this but because it's an expression, it won't work. I can see that you can add a second argument [format] to TEXT() but I'm not sure it can influence straight text, only dates?
How might I work around this?
Thank you!
Anton
LinkedIn
Twitter
