Valid IF: not valid if two values are duplicated

AppSheet AppSheet Q&A
, , ,
1

Hello, how are you?

I have the following problem: In my app, you can add the monthly expenses manually. This table has three main columns/values:

[id_costo_mensual], [Periodo] and [Edificio].

The first is the ID of the row. The second is the month of the current selected date and the latter is the building where i’m assigning the monthly expenses to.

Basically, there can only be 1 row for each monthly expense for each building.

So, i’ve come up with this valid if formula inside the [Periodo] column:

IF(and(
IN([Edificio], SELECT(costos_mensuales[Edificio], [id_costo_mensual] <> [_ThisRow].[id_costo_mensual]));
IN([Periodo], SELECT(costos_mensuales[periodo], [id_costo_mensual] <> [_ThisRow].[id_costo_mensual])))
;false;TRUE)

Also tried this:

IF(and(
IN([Edificio], SELECT(costos_mensuales[Edificio], true));
IN([Periodo], SELECT(costos_mensuales[periodo], true)))
;false;TRUE)

They are supposed to do is this:

If simultaneously the [edificio] and the [periodo] are already inside the table, then it should not allow the form to be saved. Otherwise, it allows it.

It does NOT work, i’ve tried many combinations but i cant make it work. It’s like it just checks only for the [periodo] column and not the [edificio] one.

Can someone help me with this expression?

Regards,

Federico.

2

Please try below

Please create a VC called say [Edificio_E_Periodo] with an expression something like CONCATENATE([Edificio],“-”,[Periodo]

Then your valid_if in either [Edificio] or [Periodo] column can be something like

ISBLANK(
FILTER(
“Table Name”,
([_THISROW]. [Edificio_E_Periodo]= [Edificio_E_Periodo])
)

  • LIST([_THISROW])
    )

The approach to avoid duplicates is listed in the last section of the article below

List expressions - AppSheet Help

2 Likes
3

@Suvrutt_Gurjar

It worked, i don’t understand why it’s working to be honest, but thanks!!

I’ll figure it out later!

Thanks!!!

1 Like
4

AND() within FILTER() instead of a vc?