Preventing duplicate records

(0) Share

Report

Posted on by Community Member

A table has two fields. one is "empcode" and other one is enum "Gender" which has 2 values say "Male" & "Female".

If there is one record with empcode "0003" of gender "Male" in the system. When user tries to create one more record in system with same empcode "0003" and same Gender "Male" then, system should not accept it.

How to write the code so that it throws error message "Record already exists".

But if empcode is same and Gender is not same then it should accept.

I have the same question (0)

All responses (28)

Answers (14)

Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

Your table should have unique index (AllowDuplicates:No) that has fields empcode and Gender.

However I would like to encourage you to double check and challenge this requirement one more time. Usually you would declare the employee id as unique, and not allow two people (regardless of gender) to have sampe employee id.

Let's imagine that your employee now does something in the system and you want to store information about the user who did it. You always need to store empcode and gender in all tables where you want to refer to the employee. Because the ID alone would not be sufficient information to identify the employee.

So instead of having something like "employee 0003 created this invoice" you would need to store "Male employee 0003 created this invoice". Does your customer really want it like that? Or is there some misunderstanding?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

yes , the condition is like that . I need help in coding it :-)

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

Hi Aniket,

ok, so then you can refer to my previous reply that contains instructions.

If you don't have any unanswered questions, please mark the helpful answer(s) as verified. Thanks!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes I referred. But empCode cannot be unique index because it can be repeated when gender is "Female". (This is the requirement)

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

You are correct. But I didn't say that you should have unique index that has just empcode.

Could you please read the very first sentence of my first reply one more time. Thanks!

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,978 Most Valuable Professional on at

Like (0)

Report

Title changed from "How to give" to "Preventing duplicate records".

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

yes . How to change it?

Was this reply helpful? Yes No
nmaenpaa 101,162 Moderator on at

Like (0)

Report

What do you mean?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi,

So I tried the way you suggested . But it's not working .

The requirement is : For suppose, a table has two fields. One is "Code" and other one is Enum "Type" which has 2 values say "ABC" & "XYZ". Following are the two conditions :

1) If there is one record with Code "0003" of Type "ABC" in the system. When user tries to create one

more record in system with same Code "0003" and same Type "ABC" then, system should not accept

it.

2) But if Code is "0003"and Type is "XYZ" then it should accept.

How to write the code so that it throws error message for respective conditions.

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

No code needed, just that index, as mentioned. This is exactly what unique indexes are meant for.

"it's not working" could mean 100 things, we can only help you if you tell us what exactly that means.

Was this reply helpful? Yes No