Finding duplicate data in a column

[Raju_Tadepally]

Hi All,

started creating an App which captures In & out date , time & ID of persons.

Have created [Date] , [Time] , [in/out] & [ID] columns for capturing movement of persons.

when ever a person goes out he punches out & when ever he enters he punches IN.

i need to findout , after 8pm , is there any one who had not punched in ? so that we can call that person & find the reasons.

i would like to slice the records to findout any person that had not punched in after 8 PM.
request for any suggestion on building the expression for the slice.

Thanks in advance.

[WillowMobileSys]

Welcome @Raju_Tadepally to Appsheet!!!

Give this a try:

AND([Date] = TODAY(),
         [In/Out] = "Out",
         NOT(IN([ID], SELECT(this-tablename[ID], [In/Out] = "In")))
        )

Rows where the Date is Today and stamped “Out” and have an ID that is NOT in the list of ID’s for rows that were stamped as “In”.

The [In/Out] = "Out" portion may not be needed but I think it will help reduce the number of rows that are compared so it is more efficient.

[Raju_Tadepally]

Dear Friend,

Thank you for the quick suggestion. i am very happy to be part of this dynamic Helping group.

will give a try & post the feedback.

[Raju_Tadepally]

Hi @John

i have tried the above expression. the appsheet says the expression is true , but it is not filtering the data properly.
it is not showing all the data where only "OUT "is there & there is no IN.
it is omitting many rows.
can you help on this issue.
Advanced Thanks to you.

[WillowMobileSys]

Hi! I didn’t get the notification of your message. It doesn’t look like the @ tag is correct. When you type “@”, wait a moment and it will suggest names associated with the post. Make sure to choose from there.

Can you post the expression you are using? I’ll take a look at it.

[Raju_Tadepally]

Thanks @WillowMobileSystems.

i have used the following expression :

AND([Date] = TODAY(),
NOT(IN([Application ID], SELECT(Trainee Scan[Application ID], [In/Out] = “In”)))
)
when three are 50 out scans on a particular day & only 20 In’s , it should show the details of remaining 30 entries for which there is NO IN.
but it is showing only 10 entries which are yet to come instead of 30 entries.

the scenario is :
workers go out every day in the evening.
security gate scan the ID card which has got 2D QR code fetching “Application ID” , IN / Out input & Time of exit.
when workers keep coming in, they scan their cards again for IN scan.
the slice i made is for showing all the workers who are still outside for whom the IN scan is not yet done.
this is to enable the security person to find how many people are yet to come back & take suitable action.

Hope i could explain the scenario properly. looking forward for your insights.
Regards
Raju

[WillowMobileSys]

Are the 10 entries correct entries you would expect to see?

[Raju_Tadepally]

yes @WillowMobileSystems , they are. Other 20 entries are also scanned properly but not appearing in the list.

[WillowMobileSys]

I just created a little tester app and the expression seems to be working fine. Maybe I don’t have a complete understanding of your data.

First, I want to make sure that your [Application ID] is the unique ID of the Person?

If not, please replace with the ID column of the person.

If it is, then could you provide a snapshot of the table you are recording the In/Out rows into? I need to make sure I understand your data structure correctly.

[Raju_Tadepally]

Application ID is unique to the person.But it gets repeated daily.But in a day there would be only One OUT & one IN.

the snapshot of the table is below :

[WillowMobileSys]

This is what I imagined. I am using the same table structure and same expression and I am not yet seeing an issue.

First things first, in AppSheet editor and looking at the Slice, tap the “View Data” button. Do you see the correct list of rows that way?

If so, then your view is filtering them out in some way.

If still not the correct list of rows…

There have been reports of unexpected behavior with dates when not in US format. It usually stems from a mis-match in settings across the components - device, datasource and AppSheet.

Try wrapping your dates in a DATE() function. so the expression looks like this:

AND(
DATE([Date]) = DATE(TODAY()),
NOT(IN([Application ID], SELECT(Trainee Scan[Application ID], [In/Out] = “In”)))
)

Any difference?