Duplicate entry detection

[Raju_Tadepally]

HI every one,
I have a table Named: SCAN
it contains three Columns
[Date] , [Time] , [Application ID]

the app scans the Application ID from 2d Bar code & saves the date along with Date & Time.
i want to have an expression in "valid if " to detect any duplicate entries of [Application ID] in particular day when the scan is in progress. i.e the while scanning it shall declare invalid or already Scanned message if the scan code was already in the same date.

i am not familiar with higher order Expression writing . request Help.
Regards
Raju

[Suvrutt_Gurjar]

Hi @Raju_Tadepally,

You may wish to try an expression in the valid_if of the [Application ID] column something like below

NOT(IN([_THIS], SELECT(SCAN[Application ID], AND(NOT(IN([Key Column Name of SCAN Table], LIST([_THISROW].[Key Column Name of SCAN Table]), [Date]=TODAY()))))))

However please be aware that such duplicate preventing expressions are likely to fail in multi user environment if more than one user are trying to add records to that table.

[Raju_Tadepally]

Thank you @Suvrutt_Gurjar

I have tried this formula as below
NOT(IN([_THIS], SELECT(SCAN[Application ID], AND(NOT(IN([Application ID], LIST([_THISROW].[Application ID]), [Date]=TODAY()))))))

it is giving error message that IN function is used incorrectly.

any further refinements please. [Application ID] is the Key column .
regards

[Suvrutt_Gurjar]

Hi @Raju_Tadepally,

Thank you for the update. If [Application ID] is your key column then it should not be duplicated , not only on any particular day but on any day. Also typically the app should itself give a warning of duplicate key and disallow that entry, if the key is getting duplicated while adding a new record.

Could you please update if you are getting this duplication error message, if [Application ID] which is key is getting duplicated while doing new record addition?

[Raju_Tadepally]

hi @Suvrutt_Gurjar
thank you. the Application ID will be repeated daily during scanning. Hence i have removed the application ID as key column & taken row number as key column & tried. However it says IN function is used incorrectly.

[ajay_kumar_gaut]

Sir I have sheet for voter of a village . And I want to search duplicate voter by column name [ name], [father name]
Plz make a question for a slice

[Suvrutt_Gurjar]

Just in case you have not, you may wish to take a look at the following article

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

[Raju_Tadepally]

Further i would like to know the concept of finding duplicate entries so that it would be easier for me in future.

[Suvrutt_Gurjar]

Hi @Raju_Tadepally,

Please try

NOT(IN([_THIS], SELECT(SCAN[Application ID], AND(NOT(IN([_RowNumber], LIST([_THISROW].[_RowNumber]))),[Date]=TODAY() ))))

I also request you to consider some other column as key column such as UNIQUEID() , if feasible. [_RowNumber] is not recommended as a good candidate for key column.

[Suvrutt_Gurjar]

For finding duplicate entries,please take a look at the following article

help.appsheet.com

List Expressions and Aggregates

List expressions use operators that result in a list or numeric value.

Please refer to section " Preventing Duplicate Field Values"

However as I mentioned earlier, even this approach has a limitation in multi user enviroment.

[Raju_Tadepally]

Thank you @Suvrutt_Gurjar

the expression worked well. if i want to add further constraints like what we have given now :today() with [Type of meal] any further additions can be done to the formula?

i tried with this formula:
NOT(AND(IN([_THIS], SELECT(CANTEEN SCAN[Application ID], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Date]=TODAY()))),
or(
IN([_THIS], SELECT(CANTEEN SCAN[Application ID], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Type of Meal]=“Breakfast”))),
IN([_THIS], SELECT(CANTEEN SCAN[Application ID], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Type of Meal]=“Lunch”))),
IN([_THIS], SELECT(CANTEEN SCAN[Application ID], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Type of Meal]=“Dinner”)))

)))

where as key column is :[key]
Table: canteen scan
formula to be applied to : [Application ID] column
other columns to verify : [date] , [Type of meal]

when i scan the [Application ID] , during breakfast / lunch / dinner time as getting entered based on time in [Type of meal] column ,the app shall check for duplicate entries of the [Application ID] in today’s date & now type of meal.
i tried to construct the expression. But the app is not detecting the duplicate entry for the day for the given meal.
please do check & advise.

[Suvrutt_Gurjar · Accepted solution]

Hi @Raju_Tadepally,

Can you please try

NOT(IN([_THIS], SELECT(CANTEEN SCAN[Application ID], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Date]=TODAY(),OR([Type of Meal]= “Breakfast”, [Type of Meal]= “Lunch” , [Type of Meal] =“Dinner” )))))

[Raju_Tadepally]

dear @Suvrutt_Gurjar
Perfectly worked. Thanks a lot.
Regards
Raju

[Suvrutt_Gurjar]

Thank you for the update @Raju_Tadepally. Good to know the expressions works as per your requirement. All the best !

[Raju_Tadepally]

dear @Suvrutt_Gurjar

checked in the evening during Dinner time. it is giving error message to all scans who’s so ever scans are present on the same day during breakfast & lunch.

any thoughts please.
advanced thank you for your time.

[Suvrutt_Gurjar]

Hi @Raju_Tadepally,

Got it. I believe, you will need three different but unique combinations of date and each meal time in conparison for each [Application ID]. So I presume, for each [Application ID] , there will be three records on each day, one each during each meal time.

Please explore, if following helps

NOT(IN([_THIS], SELECT(CANTEEN SCAN[Application ID], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),OR(

AND([Type of Meal]= “Breakfast”, [Date]=TODAY()),

AND([Type of Meal]= “Lunch” , [Date]=TODAY()),

AND([Type of Meal] =“Dinner”, [Date]=TODAY() ) )))))

[Raju_Tadepally]

dear @Suvrutt_Gurjar
our understanding is correct. Same person would take meals 3 times in a day. the app shouldn’t stop the scan at different times of the day, but shall stop if there is a duplicacy in the same meal period.
i just tried the expression.
it is giving the error message that the entry is already there.
it may be searching the entire day & giving the error.
please advise.

[Suvrutt_Gurjar]

@Raju_Tadepally,

Got it. I believe , you will need to qualify it with meal time. Say if the current time is between 8AM-12 PM for Breakfast, 12PM-4 PM for say lunch and 7PM-10 PM forsay Dinner. I believe only date component will not work in this case, as it does not qualify three time periods and the app needs to permit three entries on a day. I believe you may need a [DateTime} type column instead of just [Date] and some assumptions to qualify the three types by time as mentioned.

Still, I will revert , if I believe you may still be able to achieve it with with [Date] and [Meal Type]

Hope this helps.

[Raju_Tadepally]

i have created meal types based on Timings in [Type of Meal] column as below:

IFS([Time]<=“10:00:00”,“Breakfast”,[Time]<=“16:00:00”,“Lunch”,[Time]<=“21:30:00”,“Dinner”,[Time]>“21:31:00”,“Late Dinner”)

so based on time the column would auto pick the type of meal.

now comes is the finding duplicates based on this type of meal that gets generated automatically for every record.

may be the valid if is searching all records of the day. if we can limit the duplicate entry check to the meal timing only then we can make this happen.
please advise.
regards

[Suvrutt_Gurjar]

Hi @Raju_Tadepally,

Please explore if following helps in the valid_if of the [Application ID]

NOT(IN([Type of Meal], SELECT(CANTEEN SCAN[Type of Meal], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Date]=TODAY() ))))

The valid if error message may be something like “For this type of meal, the application ID is already scanned”

Edit: : Please ignore above expression. Please try with the expression below

NOT(IN([Type of Meal], SELECT(CANTEEN SCAN[Type of Meal], AND(NOT(IN([KEY], LIST([_THISROW].[KEY]))),[Date]=TODAY(), [Application ID]=[_THISROW].[Application ID]))))

[shajibul]

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Reject-duplicate-data-input-when-data-of-two-fi...

[shajibul]

Subject: Reject duplicate data input when data of two fields both found a duplicate in another record