How to Avoid AppSheet Adding Decimal Places When Using TEXT() With and Expression

[Anton_Dickens]

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

[Suvrutt_Gurjar]

You may want to share the column types you have used for postcode in various tables, that may help to suggest a better solution.

In general, you may want to try the text type for the postcode columns itself.  When you need to use it as a number for any comparison etc., you could wrap it with NUMBER() such as NUMBER([Postcode]) where [Postcode]  is a Text type columns.

[Anton_Dickens]

Hello Suvrutt,

Thank you for the reply.

Originally [Postcode] was number type column, in both the Client table and the lookup table. I think I solved the functionality side of it myself by accident so it works in the meantime, I changed the column type to [Enum] then base type of text and to allow other values, then removed the valid_if and now AppSheet won't give the error when you save the record.

In table view [Postcode] looks fine (no decimals) however in form view it shows the enum (unable to be edited of course) but with the decimals. Do you know why that might be?

Thank you, Anton

[Suvrutt_Gurjar · Accepted solution]

---

@Anton_Dickens wrote:

view it shows the enum (unable to be edited of course) but with the decimals. Do you know why that might be?

---

No idea. As you can see an enum with base type as text that pulls zip code values from other table does not show any decimal places in the following example

The [Zip] column settings are as below.

It pulls [Zip] value options through valid if from another lookup table "Zip_Code". That table in turn has a [Zip_Code] column that is defined as text type.

[Anton_Dickens]

Thank you, that's what I see also however in detail it would show as decimals and clash with the valid_if. Now that I used Concatenate, combining this with your help has solved it, thank you

[AleksiAlkio]

Instead of TEXT(), try to use CONCATENATE().

[Marc_Dillon]

I second Aleksi's suggestion. I typically do this like:  "" & [column]

But I'm also wondering why you're converting to text in the first place, why not just keep it as a number?

[Anton_Dickens]

Originally, however I had I set up, it could only be filtered by range and not like Enum. But using concatenate has solved it now, thank you very much