Check if item is already referenced in the table

[Tomas_Gonzalez]

Hi everyone!

I’m trying to build an assistance app, i already have my structure but i’m having problems finding the correct expression. I attach a diagram below for better understanding.
Basically i have a list of persons, and i have to create an inscription to a program.
Each person CAN HAVE multiple inscriptions to different programs (i.e: John Doe => preschool, Lunch, Chess, Taekwondo).
Right now i can make a inscription to a program, but the app let me create duplicates (i.e John Doe => preschool, preschool, preschool, Lunch, Chess, Chess, Taekwondo).
I want to add an IF statement to Inscription’s table that prevents adding a person if that person already have a reference to that program, i feel that i have to use Count // select // Contains, but i’m getting lost in how to check if a referenced person already has a referenced program.

Thank you in advance, any help is appreciated!!!

Tomi

[Marc_Dillon]

Tomas_Gonzalez:

I want to add an IF statement to Inscription’s table that prevents adding a person if that person already have a reference to that program

Try to set the following as a valid_if expression on one of the columns in the Inscription table. I’d suggest putting it on either [Person] or [Program], typically makes more sense for whichever one is selected last.

ISBLANK(
  FILTER(
    Inscription ,
    AND(
      [Person] = [_THISROW].[Person] ,
      [Program] = [_THISROW].[Program] ,
      [ID] <> [_THISROW]
    )
  )
)

Alternatively, you could use a similar expression to show a selectable list of only Programs that haven’t yet been selected by the Person.

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

[Tomas_Gonzalez]

Marc_Dillon:

ISBLANK(
  FILTER(
    Inscription ,
    AND(
      [Person] = [_THISROW].[Person] ,
      [Program] = [_THISROW].[Program] ,
      [ID] <> [_THISROW]
    )
  )
)

Thank you for your response, but it isn’t working , it’s still letting me repeat programs for the same person.

Maybe i’m wrong, but shouldnt the VALID action be executed at the moment of creating an inscription record? I feel that the VALID_IF constraint should be in the “Add” options under behavior of inscriptions. Something like “Add this record ONLY IF this record’s person doesn’t have this record’s program associated in this data”.
FILTER() could be an option, i’m going to try building an expression with this.

[Marc_Dillon]

It is possible I made a small error in the expression, or my interpretation of your needs, but I’m certain the general idea works as I’ve used it many times before. I suggest putting some effort into understanding it and hopefully finding where it went wrong.

Tomas_Gonzalez:

Maybe i’m wrong, but shouldnt the VALID action be executed at the moment of creating an inscription record? I feel that the VALID_IF constraint should be in the “Add” options under behavior of inscriptions.

You are wrong. There is no mechanism for this sort of thing within Appsheet. Validation is done per column. But if even one column does not meet a validation rule, then the record cannot be saved.

[Tomas_Gonzalez]

Marc_Dillon:

<>

It worked!!!

ISBLANK(
FILTER(“Inscriptions”,
AND(
[Person] = [_THISROW].[Person],
[Program] = [_THISROW].[Program]
)
)
)

I removed the [ID] <> [_THISROW] part in your example and it worked perfectly!

The selectable list, is a great thing to have, that should be in the programs column i think .

Thank you x2!

[Marc_Dillon]

Great!

I’m not sure why the [ID] <> [_THISROW] portion was causing an issue. Without that portion of the expression, you won’t be able to edit existing Inscription records, because they won’t pass validation.

[thotaravi]

I’m trying to build an assistance app, i already have my structure but i’m having problems finding the correct expression. I attach a diagram below for better understanding.
Basically i have a list of persons, and i have to create an inscription to a program.
Each person CAN HAVE multiple inscriptions to different programs (i.e: John Doe => preschool, Lunch, Chess, Taekwondo).
Right now i can make a inscription to a program, but the app let me create duplicates (i.e John Doe => preschool, preschool, preschool, Lunch, Chess, Chess, Taekwondo).
I want to add an IF statement to Inscription’s table that prevents adding a person if that person already have a reference to that program, i feel that i have to use Count // select // Contains, but i’m getting lost in how to check if a referenced person already has a referenced program.