Better Solution for Duplicate Check?

[mykrobinson]

Morning, all! 

I'm working on internal testing for my hospital rounding app, and while most is working as it needs to, I came across an interesting (at least to me) problem this morning.

I couldn't think of much of a way to perform a test for duplicated patients when entering a new patient in the app, so i created a field call "duplicate check" with a formula to create the value in that field:

CONCATENATE(LOWER([Last Name]),LOWER([First Name]),[DOB])

 
For the validity check, i have this:

NOT(
	IN(
  	[_this],
    	SELECT(
      	Patients[_duplicatecheck],
        ([_THISROW].[_RowNumber]<>[_RowNumber]))))

The issue i am having is, I needed to edit a record this morning and append a middle name for a patient, as that information was not available in the information i initially had. It failed, though, and gave me the error message
A duplicate patient exists. Please verify the information entered.

How can I adjust my validity check to make sure that I can edit an existing patient and bypass the check?

[SkrOYC · Accepted solution]

You can take a look at this post:

Different Valid_If for Adds and Edits - Google Cloud Community

Also you can vote on this idea:

Different Valid_If for each case: Add new record t... - Google Cloud Community

The workaround is an IF() so that the validity rule is applied only if the row of the form is not in the database

[mykrobinson]

This worked for me, though i welcome any improvements to the expression:

IF(
   IN(
       [_THISROW].[_duplicatecheck],
       Patients[_duplicatecheck]
   ),
   [_THISROW_BEFORE].[_THIS]=[_THIS],
   NOT(
	IN(
  	[_this],
    	SELECT(
      	Patients[_duplicatecheck],
        ([_THISROW].[_RowNumber]<>[_RowNumber])))))

[mykrobinson]

The "plain english" version of that expression:

IF this condition is true: 
....(The value of '_duplicatecheck' from the row referenced by '_patientIdentifier') is one of the values in the list (Patients[_duplicatecheck])
THEN use the value of: 
....(The value of '_duplicatecheck' from the row referenced by '_patientIdentifier') is equal to (The value of column '_duplicatecheck')
ELSE use the value of: 
....This statement is false:
........(The value of column '_duplicatecheck') is one of the values in the list (The list of values of column '_duplicatecheck'
............from rows of table 'Patients'
............where this condition is true: ((The value of '_RowNumber' from the row referenced by '_patientIdentifier') is not equal to (The value of column '_RowNumber')))

[Rafael_ANEIC-PY]

Hello @mykrobinson, isn't there some kind of identification number you can use? that's a pretty unique value at least in my country, and you could design your anti duplication system around it.

you COULD create a code for labeling each patient like what you did, but that will only stop people that type the name in exactly the same way, as the first record, which might be problematic.

To answer your question, I would try this expression:

ISBLANK(
        SELECT(
                Patients[_duplicatecheck],
                AND(
                    [_duplicatecheck]=[_THISROW].[_duplicatecheck],
                    [_RowNumber]<>[_THISROW].[_RowNumber]
                   )
              )
       )

Let me know if this works for you !

[mykrobinson]

I've considered lots of things to check against. Here, citizens have a social security number, (SSN) but some will have a stolen SSN, and immigrants, regardless of legal status, will not have one. 

Once entered into the hospital systems, they will have an MRN (medical record number), but the issue there is that each hospital has its own MRN for the patient, so it will only be unique for that particular hospital. Our clinic serves multiple hospitals while performing rounds, and the main purpose of this app is faster transmission of data internally, and independant of the hospital systems, to help speed up our own billing process and get all the moving parts moving a bit more efficiently.

So i have AppSheet generate a primary key using UNIQUIEID() but since this is random, i can;t use that as a check.

And if the doctors are seeing a patient in hospital that was not added by the team before their rounds (like someone is admitted after the listings are provided to us) they need to be able to quickly add a patient with limited information (registration folks will fill in the details later) just so they can mark that they saw a patient and collect the timestamp for the visit.

I guess i said all that to say, I can't think of a better way to do any sort of check. I realize the chance of someone having the same first name, last name, and birthdate as another is possible, but slim.

[mykrobinson]

I tried that expression (took down my current expression and replaced with this one) and the same issue persists. I'll look at some of the other posts on here and see if I can get this figured out.

Thank you.

[Suvrutt_Gurjar]

Please try in Valid_if

You need to exclude the current row from the expression so while editing , it will not give problem.

ISBLANK(
  FILTER(
    "Patients",
    ([_THIS] = [_duplicatecheck])
  )
  - LIST([_THISROW])
)

Please see the last section of the article below

https://help.appsheet.com/en/articles/961274-list-expressions 

[pangalawang]

Hi @Suvrutt_Gurjar Suvrutt,

in my case, I would like to include in the count the one in the current row.  

my aim is if i have 1 apple in the g sheet, and I input another in the current row, i  would like to automatically add that current row. this expression doesnt do that.

Count(Select(Timesheet[TM Number],And([TM Number]=[_thisrow].[TM Number],[Date]=[_Thisrow].[Date],[Shift]=[_thisrow].[Shift])))

[Steve]

Please start a new topic with your question, so that more readers see it.

[Suvrutt_Gurjar]

Since you mentioned " though i welcome any improvements to the expression:"

Did you get a chance to try this one?

ISBLANK(
  FILTER(
    "Patients",
    ([_THIS] = [_duplicatecheck])
  )
  - LIST([_THISROW])
 

[mykrobinson]

Wow, that worked and is significantly less lines. I'll need to read over this further and make sure i understand what it's doing.

Thank you!

[Suvrutt_Gurjar]

Thank you. Nice to know it works as intended. It basically removes the current row being edited from the filter through  -LIST([_THISROW]) part of the expression.  Or else the value of the current row also appears in the list and it erroneously flags off duplicate while the record is being edited.

[mykrobinson]

Seems simple but i think i may need to re-read this a few times for it to stick 🙂
Putting this here so it's easier to read:

((The list of values of column '_patientIdentifier'
....from rows of table 'Patients'
....where this condition is true: ((The value of column '_duplicatecheck') is equal to (The value of column '_duplicatecheck')) - LIST(
....The value of column '_patientIdentifier')) is empty

[Suvrutt_Gurjar]

Sure, thank you. Each one of us has different approach to memorize , understand anything. Also the approach mentioned by you is the way AppSheet also expands an expression's explanation in "no code" way. So even the explanation approach will always be uniform for any other expression and that is a better thing.