Hello community I need to limit a numerical ...

expressions
(Alfredo Pou) #1

Hello community

I need to limit a numerical value, so that the user can only download it, but not increase it. I thought to put a Validif: [THIS] <[ THISROW]. [Quantity]

The expression assistant accepts it, but the comparison always gives invalid. The idea is to compare the value stored in the cell with the value that is being entered in the form. I do not understand what I am doing wrong, and what would be the correct way to do that restriction?

Thank you

(Steven Coile) #2

The user may adjust the value, but only downward?

I’m guessing the column in question is Quantity, and that your formula:

[__THIS] < [__THISROW].[Quantity]

is an attempt to compare the user-adjusted value ([__THIS]) against the initial value upon entering the form ([__THISROW].[Quantity]).

(Note that I’ve doubled the underscores in front of THIS and THISROW to avoid formatting problems with Google+. Remove the second underscore in practice.)

You’re on the right track thinking you have to refer to the initial value in some different way, but [__THISROW].[Quantity] and [__THIS] in fact refer to the same value: the copy of the column value the form is using. Your expression is exactly equivalent to [__THIS] < [__THIS]. The specific reason your inputs are always invalid is because you Valid_If insists that the Quantity input must always be less than itself, which is always false.

To get the initial value of the column, you have to tell AppSheet to look at the table by using a function that pulls values from tables, like LOOKUP() or SELECT():

[__THIS] < LOOKUP([__THISROW], “MyTable”, “KeyColumn”, “Quantity”)

or:

[__THIS] < ANY(SELECT(MyTable[Quantity], ([KeyColumn] = [__THISROW])))

(Alfredo Pou) #3

Oh, okay! I thought that [__ THISROW] referred to the row in the spreadsheet. I understood the concept and I thank you very much, but I have a curiosity, if the value was less than zero, It was not invalid. That is, it allowed me to store negative numbers, which made me think that I was not taking the correct value and interpreted it as zero. According to your explanation, it should never be valid. Things of technology …

Thank you so much for your answer!

(Praveen Seshadri (AppSheet)) #4

@Adam_Stone_AppSheet – FYI, a good place where the app could utilize [_THISROW_BEFORE] and [_THISROW_AFTER] in a form. Note for other readers — this is something supported in workflow rules and we have been discussing the possibility of supporting it in forms as well.

(Alfredo Pou) #5

@praveen

Yes, it would be excellent! In fact, the first thing I tried was that, but I did not recognize the expression. It is very useful to be able to compare the saved value with the current one.