Data Validation when entering data

Hello,

I have a table that has a column named “Status”. The value for that column may only be “Open” or “Closed”.

There should be no more that one record in an “Open” state.

How do I make it so that a user can only add a new record when all other records are in a “Closed” state?

Thanks in advance
Stel

It sounds that you are basically looking for preventing duplicate occurrences in the [Status] column for one of the enum options Please take a look at the following article’s last section on avoiding duplicates.

In your case I believe since you wish one of the enum options only to be non duplicate, you could revise the expression something like below

IF([Order Status]=“Open”,
ISBLANK(
FILTER(
"Table Name",
“Open”= [Status]
) - LIST([_THISROW])
),
TRUE
)

1 Like

Thanks. That worked well.

2 Likes