How to use Upper()

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.

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

  1. 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

  1. 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.

1 Like

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.

2 Likes

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.

1 Like

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 :face_with_raised_eyebrow: So to me, the letters on a vehicle registration are always upper case so that’s the way I want it to be.

1 Like

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

2 Likes

Hmm, any idea what I’m doing wrong?

2 Likes

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

1 Like

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:

1 Like