Valid If that only validates when creating a record

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.

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

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:

3 Likes

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?

1 Like

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.

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
)

AppSheet doesn’t work that way.

should instead be:

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

Thanks a lot Steve!

1 Like