I have a column with a vendor code. This one ...

[Benoit_Gramond]

I have a column with a vendor code. This one must be 4 letters and in uppercases

I created a virtual column: vc_vendor_code

And put the following formula in my vendor_code_column:

AND([_THISROW].[vendor_code]=[_THISROW].[vc_vendor_code],

LEN([_THISROW].[vendor_code])=4 )

It does put me “invalid entry” no matter what I put in this case.

What am I doing wrong?

[Levent_KULACOGL]

AND([_THIS]=[_THISROW].[vc_vendor_code],LEN([_THIS])=4)

[Levent_KULACOGL]

However this does not validate if the entry is in uppercase or not

[pravse]

Adding @Adam_Stone_AppSheet

[GreenFlux]

FIND() is case sensitive. Do you have a fixed list of 4 letter vendor codes to compare against? If not, then maybe combine FIND() and NOT() to ensure there are no lower case letters.

FIND() help.appsheet.com

[Steven_Coile]

What are the vendor_code, vc_vendor_code, and vendor_code_columns columns each for? Just from the names, there seems to be some duplication there.

[Steven_Coile]

There is no good way to test whether the letters are upper- or lower-case, but that’s generally not a problem with AppSheet because AppSheet generally treats upper- and lower-case as equivalent when comparing textual values.

[Steven_Coile]

If your interest in uppercase letters is an aesthetic one, or because it’s significant in the back-end spreadsheet, you can have your app convert the user-entered code to uppercase automatically. There are two ways to approach this:

Create a separate column to receive the uppercase form of the code and use an app formula to do the conversion.

2. Create an action that converts the user-entered code to uppercase and attach the action as the form save action.

[Benoit_Gramond]

+Steve Coile @GreenFlux @Levent_KULACOGLU Thanks for your recommendations, they are all very usefull! I am basically connecting Apps data to my ERP system, this is why I have some restrictions in the formats.

Basically, if I understood everything:

1 - I do not need many columns, just my vendor_code 2 - For the LEN I will use LEN([_THIS])=4

3 - For the UPPER I have 2 solutions * Creating the action that will convert the column in uppercase (save event action) * Creating a BEFORE INSERT trigger in my DB

[Benoit_Gramond]

+Steve Coile I

tried to create an action but I have fiew troubles:

1. when we use a quick edit table, the Form Save action does not trigger on every row ?

2. I tried to set the data of a column with both formulas UPPER([_THISROW].[vendor_code]) and UPPER([vendor_code), it does give me an error that says that I can not trigger the action because my column can not be blank

3. I tried with UPPER(LOOKUP(MAXROW(“customers”,“last_update”),customers,id_customer,vendor_code)) Here my “last_update” is a ChangeTimestamp.

It works but only if only one user is working on the table

Is there a solution that I dis not try for this one? (I need quick edit on the table for sure) or is my only solution to put an update trigger in my Database?

[Steven_Coile]

@Benoit_Gramond I have no experience with the quick edit table feature, so I can’t provide insight until I can get to my computer to tinker.

[Steven_Coile]

@Benoit_Gramond, my own testing confirms that the on-save action does not fire when table QuickEdit is used. Note that table QuickEdit is explicitly beta, meaning it may not be fully functional, as appears to be the case.

@praveen, FYI.