Valid_IF and INPUT()

[gob]

Hello everyone,

I have two problems.

1.How to write a Valid_IF for a column of "Comanda" The user can only open a "comanda" if its number does not exist in the table or if its status is "Fechada" (closed)

I tried these two ways, but neither works.

A. IF(IN([_THIS], comandas[Comanda]),IF([_THISROW].[status]="Fechada", TRUE, FALSE), FALSE)

B. COUNT(FILTER("comandas",(AND(IN([_THIS], comandas[Comanda]),[_THISROW].[status]="Fechada"))))>=1)

It seems to be ignoring the rule for status column

Fechada =  close

2. I need to use the INPUT() function to modify the OBS column, but I can't save it because the validation rule prevents me. (rule above) How to make a Valid_IF that works only when inserting a line and not when I have to modify it with the INPUT() function?

[Marc_Dillon · Accepted solution]

Search for "prevent duplicates". There are many posts on this.

[gob]

I also tried this way

IN([_THIS],SELECT(comandas[Comanda],([status]=Fechada)))

and it didn't work, the formula returned me a dropdown list, I dont know why...

in one of the articles suggested by the research I saw that I should put the negative expression so I did like this:

NOT(IN([_THIS],SELECT(comandas[Comanda],([status]=Aberta))))

it worked, thank you very much!

[Joseph_Seddik]

First please read these guides carefully:

• References between tables - AppSheet Help 
• Dereference expressions - AppSheet Help