Valid If that only validates when creating a recor...

ranorga · 05-03-2021 03:30 PM

Dear Community:
It is possible to create an expression in the “Valid If” part of a field that only acts (validates) on the creation of a record and not afterwards.
Thanks for your ideas.

Marc_Dillon

Yah, sort of. Can you expand on your exact requirements, so someone can help you out more?

Steve

Like this:

IF(
  NOT(IN([_ROWNUMBER], table[_ROWNUMBER])),
  only-on-add,
  only-on-update
)

replacing table with the name of the table for the row, and only-on-add and only-on-update with the validation expressions to apply only when adding and updating a row, respectively.

For instance:

IF(
  NOT(IN([_ROWNUMBER], Timesheets[_ROWNUMBER])),
  ([_THIS] >= (TODAY() - 2),
  ([_THIS] < TODAY())
)

Or, validate only on add:

OR(
  IN([_ROWNUMBER], table[_ROWNUMBER]),
  only-on-add
)

Like:

OR(
  IN([_ROWNUMBER], Timesheets[_ROWNUMBER]),
  ([_THIS] >= (TODAY() - 2)
)

IN([_ROWNUMBER], table[_ROWNUMBER]) answers the question, “does this row already exist in the table?”

Another common need is to present a dropdown list with available values that continues to validate when the row is a updated. The above will work, but a better approach is:

SORT(
  Appointment Slots[Time]
  - SELECT(
    Appointments[Time],
    AND(
      ([Date] = TODAY()),
      NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
    )
  )
)

which is essentially (all possible options) - (used options that aren’t this one).

See also:

ranorga

Thanks a lot @Steve for your answer!
Could you expand the concept of _ROWNUMBER in the expression a bit? I understood that _ROWNUMBER was referring to the position of a record within a table. It is right?

Steve

The system-provided _ROWNUMBER column value is the number of the row in the table. For a table based on a spreadsheet, the _ROWNUMBER value is exactly the spreadsheet row number. I don’t know how _ROWNUMBER is determined for database-based tables.

Within a table, a row’s _ROWNUMBER column value is guaranteed to be unique at least until the next sync. In that away, it’s useful as a way to differentiate rows, and is useful for all tables. We could also use the table’s key column to differentiate rows, but then my examples would require you adapt them by adding the key column name appropriately, whereas if I use _ROWNUMBER in my examples, you can use them as-is.

ranorga

Hi Steve:
I’m concerned that _ROWNUMBER might fail when dealing with a relative position. For example, if a row is inserted, modifying the position of all the rows that are below the inserted row.

So, I would like to take the alternative that expression that you mentioned. Could you confirm that it is okay ?. Thanks in advance.

IF(
NOT(IN([IdColumn],LIST([_THISROW].[IdColumn]))),
only-on-add,
only-on-update
)

Steve

AppSheet doesn’t work that way.

should instead be:

IF(
NOT(IN([IdColumn],Table[IdColumn)),
only-on-add,
only-on-update
)

ranorga

Hi @Steve:
Sorry to go back to this topic, but I am trying to ensure that the validation is done only when a record is added and that for subsequent updates it allows any data to be entered. I tried to use TRUE instead of “only-on-update”, but this must be of type list, because “only-on-add” is type list. If you have any ideas, I would appreciate it.

Austin

As far as I have found there is no way to make a field a dropdown in one condition and not a drop down in another condition. The only solution I know is to make 2 different columns, and hide/show them depending on the condition.

Steve

So you want a dropdown of a limited set of choices when adding the row, but you want the user to type a value when updating the row? Valid If can’t be used like that; the input must be either a dropdown in both cases, or manual input in both cases. An alternative might be to present the choices using Suggested values, then enforce the choice restrictions using Valid If.

Suggested values:
```
IFS(
  NOT(IN([_ROWNUMBER], table[_ROWNUMBER])),
  LIST("Red", "Blue", "Green")
)
```
If this row is not already in the table named table, present the options Red, Blue, and Green; otherwise, present no options (thereby requiring manual entry).
Valid If:
```
OR(
  IN([_ROWNUMBER], table[_ROWNUMBER]),
  IN([_THIS], LIST("Red", "Blue", "Green"))
)
```
The input is valid if this row already exists in the table named table, or if (the row doesn’t already exist in the table and) the input matches one of Red, Blue, or Green.

Austin

pretty much should be the order they are added to the table or if it is a view then it could be order by an ORDER BY statement.
(only from my casual observations)

ranorga

Thanks a lot Steve!

Valid If that only validates when creating a record