Tables contain Personnel number in AX 2012 R3

(0) Share

Report

Posted on by Community Member

Hi,

Today I got an issue while creating a new worker in AX. When I enter a personnel number (determined by HR dept., not by system), I got error message "This personnel number is already in use. Enter a new personnel number". So I search data in table HcmWorker by PersonnelNumber & I can't find any records that have that personnel number. I also tried to search all records that I entered today by either CreatedBy or ModifiedBy & I can't find any records.

So beside table HcmWorker, I wonder if there are any table that has personnel number value.

Thanks

Thai

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

André Arnaud de Cal... 299,782 Super User 2025 Season 2 on at

Like (1)

Report

RE: Tables contain Personnel number in AX 2012 R3

Hi Thai,

As far as I have seen the HCMWorker table should be the only table having this number. However if you have enabled dimensions based on a worker, the value is also used in some Dimension framework tables.

Have you also checked the table contents directly in the SQL server? I have seen an issue lately where a record was present in the SQL server, but probably due to wrong caching it was not visible in AX.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (1)

Report
RE: Tables contain Personnel number in AX 2012 R3

That error message is definitely in response to HcmWorker records, as found in form HcmWorkerNewWorker method validate for field PersonnelNumber.

public boolean validate() { boolean ret; ret = super(); if (ret && HcmWorker::findByPersonnelNumber(this.text()).RecId != 0) { ret = ret && checkFailed("@SYS327962"); // This personnel number is already in use. Enter a new personnel number } if (!ret) { this.text(personnelNumberIdOldValue); } return ret; }

Go into table HcmWorker method findBypersonnelNumber, and place a breakpoint on the final line, then try again. That should reveal which record is causing the validation error.

public static HcmWorker findByPersonnelNumber( HcmPersonnelNumberId _personnelNumber, boolean _forUpdate = false, ConcurrencyModel _concurrencyModel = ConcurrencyModel::Auto) { HcmWorker hcmWorker; hcmWorker.selectForUpdate(_forUpdate ); if (_forUpdate && _concurrencyModel != ConcurrencyModel::Auto) { hcmWorker.concurrencyModel(_concurrencyModel); } select firstonly hcmWorker where hcmWorker.PersonnelNumber == _personnelNumber; return hcmWorker; // breakpoint here }

Was this reply helpful? Yes No
Sohaib Cheema 49,024 User Group Leader on at

Like (1)

Report

RE: Tables contain Personnel number in AX 2012 R3

I do agree with André

May be you are checking from Form, that may be showing you active works only.

May be you have multiple partitions.

better would be if you can write a select query on SQL Server to know if its really exists in system.

Regarding number sequence, as you are entering number manually, so we cannot doubt that area

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Tables contain Personnel number in AX 2012 R3

Hi Andre, Brandon & Sohaib,

I'd really appreciate all your help. I have to say that I've learned a lot since the day I joined to our AX forum. It's not only where I can learn & get help from all members, but also a place to verify or test what I've learned everyday. Again thanks a lot, all of you.

Back to my issue, first I was really confused about "personnel number" last night. Spending hours & I could go to anywhere. Just a loop in loop etc. This morning, it looks better after reviewing all your advise. In SQL Management Studio, I repeat my search in all tables that I suspect: HcmWorker, HcmEmployment, HcmPersonPrivateDetails, DirPerson, DirPartyTable, LogisticsElectronicAddress & a few dimension tables including DimensionAttribute, DimensionAttributeValue, etc.

Finally searching data with PersonnelNumber, CreatedBy, RecID, Name, Alias, etc. (i.e. field names), I found unwanted records, for some reason, cannot be shown in Worker form in AX. These records exist in HcmWorker, HcmEmployment, DirPerson, DirPartyTable, HcmPersonPrivateDetails & LogisticsElectronicAddress .

One thing that I cannot explain myself is that after remove record in HcmWorker & other tables (except DirtyPartyTable), I also got error message "This personnel number is already in use. Enter a new personnel number". Then I made a test: Instead of using the first name & last name I should use for my new employee, I entered different first name or last name like Thai123 (so I can recognize them later & remove out of AX database). Guess what: AX accepted even though I used the same personnel number. So I had to remove my test record manually in the above table & then try to enter a new worker again with correct first name & last name with the correct personnel number. This time I got the same error message again. I tried to search in dimension tables, but couldn't found. In fact, I didn't reach to step to enter financial dimensions at all & therefore no records exist & relate to the new worker above.

Again I had to repeat task to remove. This time I did faster because no record at all in HcmWorker & other tables (except DirtyPartyTable). When searching in DirtyPartyTable, I found unwanted record (even though there's no personnel number value in it). After deleting it, I can enter the new worker successfully (with the same personnel number from HR).

I feel better now.

Thanks

Thai

Was this reply helpful? Yes No
Sohaib Cheema 49,024 User Group Leader on at

Like (0)

Report

RE: Tables contain Personnel number in AX 2012 R3

Usually HR data is not supposed to be deleted.

An employee is hired or employee goes away either by resign or by expulsion.

That means employee has a Hiring start date and Hiring End Date. An employee/ worker may has a contract. Also it’s not logical to delete an employee. Yours past historical data or HR data must be staying with your HR Department.

Proceeding little further let’s take example of Employee Position; an employee 101, let’s suppose was working as sales associate during period of 2014. Now he/she may be prompted to Sales Manager (a higher designation) for period of 2015. Now at this point you should not thin to delete employment/ position record of this employee.

So, as a conclusion, I can say deleting HR data is not something better (especially if it’s a live system)

Despite of that, if you are facing issue of Duplicate Personal Number, that can be traceable. Do let us know, if you are still facing the issue.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Tables contain Personnel number in AX 2012 R3

Hi Brandon,

I'll try to place a break point this weekend to learn more about my issue. In fact I'd like to do now, but I'm very new to AOT & so I need to learn more about it.

Thanks

Thai

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Tables contain Personnel number in AX 2012 R3

Hi Sohaib,

Thanks for your advise Sohaib.

The records that I removed from above tables are not historical data or from existing employees in the company. The situation of my issue is that while entering data if a new worker in Employment details, first I got error message regarding employment start date. After closing the Worker form accidentally, I intend to back to that worker to edit Employment start date, but I couldn't. It didn't show the worker that I've just entered even though I tried to filter by Name, Search name & Personnel number (I didn't filter by using other fields like Telephone, Extension because I didn't enter data into these fields). Guessing that the worker that I entered before was not recorded in table, I tried to re-enter that worker & since then, I got error message "This personnel number is already in use. Enter a new personnel number" ...

At present, my issue was solved after I found & removed unwanted records from HcmWorker, HcmEmployment, HcmPersonPrivateDetails, DirPerson, DirPartyTable, LogisticsElectronicAddress based on their relationships. In fact, using PersonnelNumber, Partition & other fields (ModifiedBy, ModifiedDateTime, CreatedBy, CreatedDateTime to make sure I worked on my records on a specific date time), I could locate a worker record that I entered & existed in table HcmWorker (for some reason, this record was not shown in Worker form in AX). Then based on their relationship between HcmWorker and other table including HcmEmployment, HcmPersonPrivateDetails, DirPerson, DirPartyTable & LogisticsElectronicAddress via the following fields such Person, RecID, Partion, PriavteOrPartyWorker, NameAlias, etc. I could find out more children records that related to the record-not-shown in HcmWorker.

Now my worker database is neat & clean & I already added the new worker that I need into AX successfully. Nothing wrong with my existing worker data at all.

Thanks

Thai

Was this reply helpful? Yes No
Sharafat 5 on at

Like (0)

Report

RE: Tables contain Personnel number in AX 2012 R3

Hi Thai,

I am facing the same issue in Dynamics365. Could you please explain how you were able to do that? How did you manage to find and remove unwanted records from HcmWorker, HcmEmployment, etc.

Thanks

Sharafat

Was this reply helpful? Yes No