Dynamic Duplicate Check Issue

[mykrobinson]

I am trying to add a duplicate check into a new app i’m building, and it “kind of” works. I have people_db to store people’s basic information, and i have a [Person Type] column that is an enum with options for Employee, Patient, Vendor, Provider, and so on.

I have a [Duplicate Check] column that is a concatenation of last name, first name, and date of birth. The valid_if statement on [Duplicate Check] is supposed to check the value of that column against the people_db to see if it already exists, and also check the value of [People Type], then post an error message and not allow the new person to be added if the value of [Duplicate Check] already exists AND the value of [Person Type] is the same. So, i could, for example, have an employee record for myself, but i can also be a patient. There cannot, however, be two copies of me as a patient.

As i said, it kinda works, but if i switch the person type after a validation error, the valid_if does not reset and recheck.

Is there a way around this?

[Aurelien]

Hi @mykrobinson

I would suggest not using a “duplicate check” column, but rather the same valid_if expression directly in each field involved, that is to say in the columns [PersonType] and [personal_id] for example.

If my understanding is correct, then the valid_if expression would be:

ISBLANK(
  FILTER("people_db",
    AND(
      [personal_id]=[_THISROW].[personal_id],
      [Person Type]=[_THISROW].[Person Type],
      [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
    )
  )
)

Note: this assumes [personal_id] is NOT the key-column of your table. From your screenshot, I deducted [personal_id] is the concatenation of last name and first name.

EDIT:

For reference:

List expressions - AppSheet Help

Note2: this expression could also be written:

ISBLANK(
  FILTER("people_db",
    AND(
      [personal_id]=[_THISROW].[personal_id],
      [Person Type]=[_THISROW].[Person Type],
      [key-column]<>[_THISROW]
    )
  )
)

or

ISBLANK(
  FILTER("people_db",
    AND(
      [personal_id]=[_THISROW].[personal_id],
      [Person Type]=[_THISROW].[Person Type]
    )
  )
  - LIST([_THISROW])
)

[mykrobinson]

thanks, i modified it slightly to this and it worked:

ISBLANK(
  FILTER("people_db",
    AND(
      [duplicate check]=[_THISROW].[duplicate check],
      [Person Type]=[_THISROW].[Person Type]
    )
  )
  - LIST([_THISROW])
)

the [personal_id] column is the key for each row and is set to UNIQUEID()

the method above works as i was hoping for, even when changing person type on the fly

[mykrobinson]

totally didn’t realize i posted this in Tips & Tricks instead of Q&A. My apologie.

[dbaum]

FYI: You can “report” a post to suggest it be moved.