Having Multiple key fields

kolaboy

Hi Experts,
I have a situation where an employee is being registered twice. The key field is employee No. but this employee has two different Nos. that is why the system accepts him twice.He has a No. from an old employer and another no. from the current employer. We want to avoid this kind of situation from happening. Instead Navision should signal an alarm that this person is already registered. Each person should register only once.
Is It Possible to make the No.,Name,Date of birth and Address as key fields? so that when the Nos. are different, the system checks for the name. if the Names are the same, It checks for the D O B. If the D O B are the same it checks for the Address and if the addresses are also the same, the system should not accept the registration and say " This person already exit."
Can anyone help? If it requires a code, I would like to see the code as well.
please any explanation.
Thanks in Advance.

Mbad

No need to make it keys. Make a check on insert or on validate(depending on the structure of your table) instead.

Waldo

Did you check the "double contacts" functionality in CRM?

kolaboy

Hi Mba,

No need to make it keys. Make a check on insert or on validate(depending on the structure of your table) instead.

Can you please explain to me how to go about your suggestion. If there is a code, Can you please saw me the code, i want to see it.
Please i don't know how to go about it.
Thanks in Advance.

themave

if you want to solve this without coding, then you need to rethink it a little bit, just changing the key to what you are saying wouldn't do it

No.,Name,Date of birth and Address as key fields

what if the name is spelled differently by a clerical error, or the address is different, because he moved, between the time he was set up in one employer and the time he was set up in another employer.

You are right in thinking the primary key is the way to go, but what you need to do is find a way to make the primary key unique, here in the unitied states, no two people should have a duplicate social security number, so that would be unique for ever person. so you could use manual numbers for new employees, and use their social security number as the employee number and you would never get duplicate employees unless someone keyed in the wrong number. but you can not protect against everything.

you could rename your existing employees to this new unique number, and code all future employees this way.

Now, I do not know if your country issues unique id numbers so this might not work for you.

themave

Along the same lines of a tax id number, would be to make the employee number a combo of the employee name and birthdate

you could make the number for John Smith, born on 03/31/1961

smithj03311961

the chances of getting two people with the same last name, first initial and birthdate is pretty slim, and if you did it would block you from using it, so you know it right away

kolaboy

Hi Themave,
What you are suggesting is exactly what is happening here. Here in the Gambia No two persons have the same social security no. This No. is unique. It is presently the primary as well in Navision.

But am saying in the even that an employee move to another institution, and those people also register him again and he has another social security No. different from the one he has with his previous employer, then he has two distinct Nos. Now the social security IT wants navision to be customized in such a way that it can detect such mistakes and situation. That is having some fields to also be primary key in case the Nos are distinct, then that alternative could be able to take care of the double registration by way of saying the record already exist.

Any explanation as to how to tackle this situation? Please your helps are urgent.
Thanks

Savatage

kolaboy wrote:

Here in the Gambia No two persons have the same social security no. This No. is unique.
......................
those people also register him again and he has another social security No. different from the one he has with his previous employer urgent.

How can this be - it is either unique or it is not.

kolaboy

Hi Savatge,

The client inadvertently give the same person another no. because he is registered as a new employee in another institution. this are mistakes they commit. they now want they system to be able to detect these mistakes
Any ideas on this.
Thanks

themave

kolaboy wrote:

Hi Savatge,

The client inadvertently give the same person another no. because he is registered as a new employee in another institution. this are mistakes they commit. they now want they system to be able to detect these mistakes
Any ideas on this.
Thanks

Is your client the government agency that issues the numbers? Otherwise, they arn't the ones making duplicates

The system can't stop clerical errors, it can help to detect them, so I would use the lastname, first initial and birthdate to make the primary key, so as long as they spelled the name corrently, and followed the format, it would at least stop duplicates from being created.

Savatage

In the US people have a Social Security Number.
That number follows us from birth to death or given to you upon becoming a citizen.
Everyone has their own number.
Do you have any such personal identifing number in your country?
IF so then that would be your "Employee No." field and the that will also be the key.
So no two people can have the same "Employee No." therefore not being entered twice.

themave

kolaboy wrote:

Hi Savatge,

The client inadvertently give the same person another no. because he is registered as a new employee in another institution. this are mistakes they commit. they now want they system to be able to detect these mistakes
Any ideas on this.
Thanks

to find existing mistakes already made, start by creating a new field, then populate it with

last name, first initial and birthdate

you can make simple processing only report to do this.

export this info into Microsoft access, and run a simple query to find duplicates. if should only take a few minutes. Once you have the duplicates, then you have to decide how to correct them. There are some good tips on how to copy info from duplicate customers, vendors, ect into one account,

http://www.mibuso.com/forum/viewtopic.php?t=15580&highlight=

Alex_Chow

The Navision CRM has the functionality to detect duplicates. However, it's limited to only the Contact table. You'll need a developer to extend this functionality to the Employee table.

The easier way is to follow what themave suggested. Less programming required.

kolaboy

Hi Experts,
Thanks for all the beautiful ideas.
I will appreciate it if someone could tell me how to extend the CRM that exit in the Contact table to the employee table
Thanks.

themave

kolaboy wrote:

Hi Experts,
Thanks for all the beautiful ideas.
I will appreciate it if someone could tell me how to extend the CRM that exit in the Contact table to the employee table
Thanks.

Can't tell you how to do it, but you can start by looking at report 5187 - Generate Dupl. search string

it uses several tables, and poplulates table 5085, which you then use form 5134 to review the duplicates it finds.