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

[shajibul]

I have an 'Employee' table, an 'Attendence_Type' table, and an 'Attendance' table. I input data in the 'Attendance' table through UX. There are 4 data fields, 'Attendance Date', 'ID', 'Name', and 'Attendance_Type' in the Attendance table. If any 'ID' and 'Attendance Date' are both found repetitive in any previous records, they will be treated as duplicate records. How do raise an error message for these and reject the input in the app?

[peterson]

Hi! yes, it is possible to do that but attention it might cause problems...

Well, you'll need to use the following formula, just change the column names to fit your needs.

Not(In([_THIS], SELECT(Table[Column_Name_for_THIS_Column], [TableID] <> [_ThisRow].[TableID])))

Go to the DATA VALIDITY in the column settings as the image below:

Open the Data Validity section and  type the formula in the 'Valid If' expression box, like below:

I tried to adapt the columns' names for you, if it doesn't work change and fix the names.

Not(In([_THIS],
SELECT(Attendance' table[ID],
AND([ID] <> [_ThisRow].[ID],[Attendance Date] <> [_ThisRow].[Attendance Date])
)))

PS: Possible problems! If two people open a form at the same time and generate the same ID in the same date the second record may update the first and erase the first one for they will have the same ID.  Do some tests, but in general it works fine.

Please let me know if that works and vote if it helped.

[shajibul]

I applied this but it still not working..... It is still saving duplicate data in the database.

[peterson]

Try this one now.

Not(In([_THIS],
SELECT(Attendance' table[ID],
AND([ID] <> [_ThisRow].[ID],[Name] <> [_ThisRow].[Name])
)))

[shajibul]

I think I am unable to make you understand. Sorry for that. This is an attendance table where an 'ID'  will be inputted every day to give attendance. But not twice a day for one employee ID. The second attempt for an ID in a day will be assumed as duplicate and rejected as well. (A 'Name' is always the same as an 'ID'. This is not the issue)