How to use Upper()

David_Jones1 · 06-08-2021 11:14 PM

Hi all. A very basic question. I have a vehicle database and the registration number format for the UK is typically AB12ABC. This is a key field in my data. On the input form, how do I make sure that this is stored as upper case as shown rather than ab12abc?
I’ve tried using the upper() function in an app formula on the Registration field i.e. upper(Registration) but this generates an error when saving the app changes -

Key column ‘Registration’ in Schema ‘Vehicles_Schema’ cannot use an app formula. The app formula will be removed.

Suvrutt_Gurjar

Please explore either of the the below workarounds. It sounds that the [Registration] field is a user-entered field. If so

Make the user enter values only in upper case by using a valid if expression in the [Registration] field such as

FIND([_THIS] , UPPER([_THIS]))=1

The above uses the property that the FIND() function is case sensitive

Or

Let the user enter value in mixed case but you may display the value in upper case by creating another column with an expression UPPER([Registration])

This other column can be shown in non-form views such as Detail and Table views.

Suvrutt_Gurjar

Nice one @Steve

@David_Jones ,

As usual, @Steve’ solution is more user friendly ( user is not compelled to enter upper case) and more efficient because there is no need of additional column and some CONTEXT() expressions.

David_Jones1

Thanks for the suggestion @Suvrutt_Gurjar I definitely don’t want to generate an error if the user enters lower case or having additional columns to handle it. So Steve’s solution looks like it might be the best option.

Cheers

Steve

Curious why it matters whether it’s upper- or lowercase?

My advice would be to create an action of type Data: set the values of some columns in this row that converts the existing value to uppercase using UPPER(), the attach that action as the Form Saved event action of the form view.

David_Jones1

Hi Steve. Thanks for the response and suggestion, which I’ll certainly try.

Does it matter? Well, I guess the true answer is no! If it’s entered in lower case then no-one’s going to die. I do have a degree of OCD with some things, and capitalisation is one of them. I could never conceive of a time when I might sign myself off as david So to me, the letters on a vehicle registration are always upper case so that’s the way I want it to be.

Steve

I actually completely understand. I’ve had to adjust my tendencies to accommodate AppSheet’s limitations.

David_Jones1

Hmm, any idea what I’m doing wrong?

Steve

Ooooh… Is the Registration column the table’s key column? If so, no approach will allow you to change the value once the row is saved the first time: row keys may not change once initially saved. In this case, the best alternative is to allow the user to enter the value in a different column, then use the Initial value expression for the Registration column to populate that column with the uppercase value from the input column.

See also:

rdfjohnstone

Hi @David_Jones1 I'm not sure if you solved this, but this may be of interest to anyone looking for a solution to your problem.

I had a similar issue and used the following expression to convert the key into upper case.

This did not convert existing rows but it does apply to future rows of data added.

If you want to convert previous key values to upper case you can do so by creating a new column next to your key value column directly in your Google Sheet. Then run the formula =UPPER("A2") and then copy and paste the values over into your original key column.

You can then delete the column you created that is now a duplicate of your key column.

(Be very careful when copying the values from the one column and pasting into the other, that you keep the same order )

Ccuadros

Maybe this suits you better: Add a Format Rule to the column you want.

NorthstateAutoB

I was having the same issue with VINs and this format rule is perfect! Thanks