Prevent Duplicate Records in a Master-Detail relation

AppSheet AppSheet Q&A
1

Hi community!!!
I’ve been trying to validate if a record already exists in a detail table to avoid adding the same service more than once.
There is no problem when is in a master table (1 single key), but I have not been able to validate when there is more than one key field.

This is the expression I’m using in Valid if,

COUNT(SELECT(Servicios[Id_Servicio],
AND([Id_Servicio]=[_THISROW].[Id_Servicio],
[Id_Cita_Servicio]=[_THISROW].[Id_Cita_Servicio])))=0

any ideas???

Thanks

2

In what way doesn’t that expression work?

3

3X_d_7_d747978e04c7384459d59886ac26ac0a5cc61a8f.png
3X_d_7_d747978e04c7384459d59886ac26ac0a5cc61a8f.png1497×838 127 KB

I’m not sure if i’m explaining well …

Need to validadte if the “Servicio” in a “Cita” doesn’t exists before…

4

Your expression, reformatted for my clarity:

COUNT(
  SELECT(
    Servicios[Id_Servicio],
    AND(
      ([Id_Servicio] = [_THISROW].[Id_Servicio]),
      ([Id_Cita_Servicio] = [_THISROW].[Id_Cita_Servicio])
    )
  )
) = 0

What I suggest instead:

ISBLANK(
  FILTER(
    "detail-table",
    AND(
      ([Id_Cita] = [_THISROW].[Id_Cita]),
      ([Id_Servicio] = [_THISROW].[Id_Servicio]),
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
    )
  )
)

Replacing detail-table with the name of the detail table.

2 Likes
5

Steve:

auto> ISBLANK(> FILTER(> "detail-table",> AND(> ([Id_Cita] = [_THISROW].[Id_Cita]),> ([Id_Servicio] = [_THISROW].[Id_Servicio]),> ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])> )> )> )> >

That was exactly what I was looking for. !!! Thank you so much Steve.

1 Like