Constraints on text and numbers

[Jonathan_Fraser]

Good day, I am creating an app that will allow the user to type in information of tenants that live an estate. I need to add some constraints on the fields like “Name” needs to be all CAPS and “Surname” is the same thing, then on fields like ID number and Cell number I need to limit the amount of numbers to “X” value and it needs to be more than “Y” value to be a valid field.

Is this possible to do? I have tried using the “UPPER” expression but that seems to only convert data to CAPS that was already in the Sheet that was not CAPS.

[Aleksi]

You can validate the ID Number like LEN(CONCATENATE([NumberField]))<10. For the CAPS issue, do you need to have them in the spreadsheet or just in the app?

[Jonathan_Fraser]

Hi Aleski, I would prefer if it could force caps in the app but if not just the saved data in the spreadsheet that was created needs to be made CAPS

[Aleksi]

If you want CAPS in your spreadsheet, you could create Event action to convert the name after the record is saved.

[Steve]

Jonathan_Fraser:

“Name” needs to be all CAPS and “Surname” is the same thing

There are at least two possible approaches:

1. Add a second, normal (not virtual) column for each to record the uppercase (“UC”) version of the name (e.g., Name_UC and Surname_UC). Give each of these new columns an app formula that sets their value to the respective user’s input value, e,g., UPPER([Name]) and UPPER([Surname]). This has the advantage of preserving the user’s original input, and is easy to implement, but the disadvantage of increasing spreadsheet size.

2. As @Aleksi suggested, create an action that converts the column’s existing value to uppercase (using a Data: set the value of a column-type action), replacing the user’s original input. Attach the action to the Row saved event in the row’s form view configuration. This has the advantage of needing fewer columns in the spreadsheet, but the disadvantage of being more difficult to implement, and the user’s original input is lost.

[Swoopy]

The approach No.2 does not work if the column is Key. AppSheet said this column cannot be changed. Any other suggestion if I need to force saving the Key column in uppercase?

[Steve]

Create another column to be the key column instead and set its Initial value expression to UPPER([original-column]), replacing original-column with the name of the column containing the original value.

Note that once saved the first time, the key column value cannot be changed.

[Swoopy]

If AppSheet has input-mask to control user input like,

All capital

9,999,999.99

ABC-D-EFGH-9999

etc.

This would definitely make AppSheet a huge more loveliness.

Regards,