Hello everybody,
I've also been struggeling with this employee import problem in AX2009 and in the end given up on an Exel template import and created a class method which does the job. It also creates the communication items like phone, email etc. (No garantees though!)
static void My_ImportEmployeeAndAddresses(str 200 path = ""){
SysOperationProgress p = SysOperationProgress::newGeneral(#aviUpdate,"Importing employees",1);
int z,w,i,j,w1;
FileIoPermission _permEmpl;
CommaIo file,file2;
container c;
EmplID emplID;
EmplTable emplTable;
CustTable custTable;
VendTable vendTable;
DirPartyTable dirPartyTable,DP;
Address address;
DirPartyAddressRelationship dirPartyAddressRelationship;
DirPartyAddressRelationshipMapping dirPartyAddressRelationshipMapping;
DirECommunicationAddress dirECommunicationAddress;
DirPartyECommunicationRelationship dirPartyECommunicationRelationship;
NumberSequenceTable numberSequenceTable;
HRMPartyEmployeeRelationship hRMPartyEmployeeRelationship;
DirPartyID DPID;
DirPartyType DPT= DirPartyType::Person;
RecID DPTRecID,PhoneRecID,TelexRecID,CellularPhoneRecID,PagerRecId,PhoneLocalRecID,EmailRecID,UrlRecID,emplRecID;
TableID tblID,AddrRecID,DirPRelShipID;
str 20 Phone,PhoneLocal,Telex,CellularPhone,Pager;
str 80 Email;
str 255 Url;
;
/*
The Class we created is a copy of the ''SysDataImport' class
Just copying it, will create some error at compilation, but we dont use those in our class so
ignore them.
This class method will create Employees and their adresses from an AX3 employee export file
Because AX5 is radically changed concerning adresses, the way they are imported is tedious.
Tables which will be touched are:
EmplTable :The actual employees
DirPartyTable :References between almost all tables used
Address :The actual adresses
DirPartyAddressRelationship :First relation part between adresses and Employee and
some Employee address options
DirPartyAddressRelationshipMapping :Second relation part between the adresses and employees
and for which company they are
DirECommunicationAddress :Communication options /types for the employee
DirPartyECommunicationRelationship :The actual relations between the Communication types/options
and the employee and some employee communication options
HRMPartyEmployeeRelationShip :The employee link table, this must be used to see the adresses in the employee forms
The following sequence has to be used to create a succesfull import:
1 First set the Number sequence for adresses using the menu option Basis/instellen/NummerReeksen/nummerReeksen //Yes it's dutch, sorry!
Create a line containing:
nummerreeksCode :Adressen
Naam :Adressen
Kleinste :1
Grootste :999999
Volgende :1
Indeling :######
Then assign this number sequence to the adress module by using the menu option Basis/Instellingen/
NummerReeksen/Verwijzingen ad find the module 'Algemeen adresboek' assign the Adresses in the 'NummerReeksCode' section
2 Clear all used Tables
3 Reset by code the address number sequence to 1(if you tried importing before, the numberseq. will now be reset)
4 create a PartyID record (Do not use DirPartyTable::GetNewPartyId() method as the number will not be stored, but the number sequence will advance leaving you with a wrong partyID)
5 Import the Employee record using the newly created PartyID
6 in the same loop, importing the employees also add the following entrys in the other tables:
- Update the PartyTable with some data like first and lastname
- Address using the PartyTable recID and storing it's RecordID
- DirECommunicationAddress storing it's RecID
- DirPartyECommunicationRelationShip using the PartyID
. ValuesRecID = DirECommunicationAddress stored recID
- HRMPartyEmployeeRelationship
. EmplID = EmplID
*/
//First we're going to insert the EmplTable and it's adresses
if (!path)//If no path has been privided the you must get the default path set in the
path = My_SysDataImport::My_DefaultPath();
_permEmpl=new FileIoPermission(path + "EmplTable.csv","r"); //initialize permissions
_permEmpl.assert(); //Assign the read permission
file2 = new CommaIo(path + "EmplTable.csv", "r" );
CodeAccessPermission::revertAssert(); //To be able to acces more the one file you must add this line!!
while (file2.status() == IO_Status::Ok){//get the total rows in the file for the couter
c = file2.read();
if (conlen(c)>0) { w++; }
}
_permEmpl=new FileIoPermission(path + "EmplTable.csv","r"); //again initialize permissions
_permEmpl.assert(); //Assign the read permission
file = new CommaIo(path + "EmplTable.csv", "r" );
p.setTotal(w,1);
i=0;
//First delete all EmplTable and adjacent table Records
print("Deleting customers, employees, vendors and all address related tables.");
p.setCaption("Delete Employee records");
w1=(select count(recid) from EmplTable).recid;
p.setTotal(w1,1);
i=w1;
emplTable.ttsbegin();
While select forupdate * from EmplTable{
i--;p.setCount(i,1);p.setText("Employee: " + emplTable.EmplId);
EmplTable.delete();
}
emplTable.ttscommit();
//Delete DirpartyTable
p.setCaption("Delete DirpartyTable records");
w1=(select count(recid) from DirPartyTable).recid;
p.setTotal(w1,1);
i=w1;
dirPartyTable.ttsbegin();
While select forupdate * from dirPartyTable{
i--;p.setCount(i,1);p.setText("DirpartyName: " + dirPartyTable.Name);
dirPartyTable.delete();
}
dirPartyTable.ttscommit();
//Delete Address Table
p.setCaption("Delete Address records");
w1=(select count(recid) from Address).recid;
p.setTotal(w1,1);
i=w1;
Address.ttsbegin();
While select forupdate * from Address{
i--;p.setCount(i,1);p.setText("Address name: " + Address.Name);
Address.delete();
}
Address.ttscommit();
//Delete dirPartyAddressRelationship table
p.setCaption("Delete Address relationship records");
w1=(select count(recid) from dirPartyAddressRelationShip).recid;
p.setTotal(w1,1);
i=w1;
dirPartyAddressRelationship.ttsbegin();
While select forupdate * from dirPartyAddressRelationship{
i--;p.setCount(i,1);p.setText("Address relation partyID: " + dirPartyAddressRelationship.PartyId);
dirPartyAddressRelationship.delete();
}
dirPartyAddressRelationship.ttscommit();
//delete dirPartyAddressRelationshipMapping table
dirPartyAddressRelationshipMapping.ttsbegin();
While select forupdate * from dirPartyAddressRelationshipMapping{
dirPartyAddressRelationshipMapping.delete();
}
dirPartyAddressRelationshipMapping.ttscommit();
//Delete dirECommunicationAddress table
dirECommunicationAddress.ttsbegin();
While select forupdate * from dirECommunicationAddress{
dirECommunicationAddress.delete();
}
dirECommunicationAddress.ttscommit();
//Delete dirPartyECommunicationRelationship table
p.setCaption("Delete Communication relationship records");
w1=(select count(recid) from dirPartyECommunicationRelationship).recid;
p.setTotal(w1,1);
i=w1;
dirPartyECommunicationRelationship.ttsbegin();
While select forupdate * from dirPartyECommunicationRelationship{
i--;p.setCount(i,1);p.setText("Comm. relation partyID: " + dirPartyECommunicationRelationship.PartyId);
dirPartyECommunicationRelationship.delete();
}
dirPartyECommunicationRelationship.ttscommit();
//Delete hRMPartyEmployeeRelationship table
hRMPartyEmployeeRelationship.ttsbegin();
While select forupdate * from hRMPartyEmployeeRelationship{
hRMPartyEmployeeRelationship.delete();
}
hRMPartyEmployeeRelationship.ttscommit();
//Delete all CustTable Records
custTable.ttsbegin();
While select forupdate * from custTable{
custTable.delete();
}
custTable.ttscommit();
//Delete all VendTable Records
vendTable.ttsbegin();
While select forupdate * from vendTable{
vendTable.delete();
}
vendTable.ttscommit();
//Reset addres number sequence, (Do this only with the employee table import, as this is the first table to be imported)
numberSequenceTable.ttsbegin();
select forupdate numberSequenceTable where numberSequenceTable.NumberSequence=="Adressen";
numberSequenceTable.NextRec=1;
numberSequenceTable.update();
numberSequenceTable.ttscommit();
//Finished, deleted all records.
// return;
p.setCaption("Importing Employees");
p.setTotal(w,1);
i=0;
print("Starting import employees");
while (file.status() == IO_Status::Ok){//Now we start the import
c = ConDel(c,1,255);
c = file.read();
EmplId = conpeek(c,1);
i++;
p.setCount(i,1);
p.setText("Employee: " + EmplId, 1);
//If we got an EmplID then insert the employee
if (conlen(c)>0) {
// print(EmplID);
dirPartyTable=dirPartyTable::createNew(DPT, conpeek(c,2));
DPID = dirPartyTable.PartyId;
emplTable.ttsbegin();
Select forupdate emplTable where EmplTable.EmplId == EmplID;
if (!EmplTable) {
EmplTable.clear();
EmplTable.InitValue();
EmplTable.EmplID = EmplID;
}
EmplTable.Dimension[1] = conpeek(c,6); //6 department
EmplTable.BirthDate = conpeek(c,17); //17 birthdate
EmplTable.status = HRMEmplStatus::Employed;
emplTable.PartyId = DPID;
EmplTable.write();
EmplTable.ttsCommit();
hRMPartyEmployeeRelationship.ttsbegin();
Select forupdate hRMPartyEmployeeRelationship where hRMPartyEmployeeRelationship.EmplId==EmplID;
If (!hRMPartyEmployeeRelationship) {
hRMPartyEmployeeRelationship.clear();
hRMPartyEmployeeRelationship.initValue();
hRMPartyEmployeeRelationship.EmplId=EmplID;
}
hRMPartyEmployeeRelationship.write();
hRMPartyEmployeeRelationship.ttscommit();
//Lets update the created DirPartyTable record
dP.ttsbegin();
Select forupdate dP where DP.ReciD==dirPartyTable.RecId;
dP.FirstName = conpeek(c,3);
dP.MiddleName = conpeek(c,4);
dP.LastName = conpeek(c,5);
dP.write();
dP.ttscommit();
DPTRecID=0;
DPTRecID=dirPartyTable.RecId;
EmplRecID=0;
while (EmplRecID == 0) { EmplRecID=EmplTable::find(emplID).RecId; }
//Okay now we will add the adresses of the Employees
address.ttsbegin();
tblID = conpeek(c,23);
Select forupdate address where address.AddrRecId==DPTRecID && address.AddrTableId==tblID;
if (!address) {
address.clear();EmplTable
address.initValue();
address.AddrRecId = DPTRecID;
address.AddrTableId = tblID;
}
address.type = addressType::Home;
Address.IsPrimary = NoYes::Yes;
address.Name = conpeek(c,2);
address.Address = conpeek(c,7);
address.CountryRegionId = conpeek(c,11);
address.ZipCode = conpeek(c,10);
&nbEmplTablesp; address.City = conpeek(c,20);
address.Street = conpeek(c,21);
address.write();
address.ttscommit();
addrRecID=0;
while(addrRecID == 0) {
Select * from address where address.AddrRecId==DPTRecID && address.AddrTableId==tblID;
if (address.RecId){ AddrRecID=address.RecId;}
}
//And finally adding the communication data
//First add the Communications and store the recordID
phone="";phonelocal="";telex="";email="";url="";cellularphone="";pager="";
PhoneRecID=0;TelexRecID=0;CellularPhoneRecID=0;PagerRecId=0;PhoneLocalRecID=0;EmailRecID=0;UrlRecID=0;
//Some fields are misused by the users in AX3, trying to compensate those errors here..
phone = conpeek(c,8); //Phone
PhoneLocal = conpeek(c,9); //PhoneLocal
Telex = conpeek(c,15); //TeleFax
Email = conpeek(c,12); //Email
URL = conpeek(c,13); //Url
CellularPhone = conpeek(c,14); //Mobile phone
Pager = conpeek(c,22);
if (phone != "") {//Do we have a Phone number? then create a Communication item
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.Phone == Phone &&
dirECommunicationAddress.ECommunicationTypeId == "HomePhone";
if (!dirECommunicationAddress) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "HomePhone";
}
dirECommunicationAddress.Phone = Phone;
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.Phone == Phone &&
dirECommunicationAddress.ECommunicationTypeId == "HomePhone";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its homephone record, Check this out!!");
} else {
PhoneRecId = dirECommunicationAddress.RecId;
}
}
if (phoneLocal != "") {
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.PhoneLocal == PhoneLocal &&
dirECommunicationAddress.ECommunicationTypeId == "Phone";
if (!dirECommunicationAddress) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "Phone";
}
dirECommunicationAddress.PhoneLocal = PhoneLocal;
dirECommunicationAddress.Phone = "0123456" + PhoneLocal; //Use your partial phonenumber + the phonelocal
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.PhoneLocal == PhoneLocal &&
dirECommunicationAddress.ECommunicationTypeId == "Phone";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its LocalPhone speeddial record, Check this out!!");
} else {
PhoneLocalRecId = dirECommunicationAddress.RecId;
}
}
if (Telex != "") {
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.Telex == Telex &&
dirECommunicationAddress.ECommunicationTypeId == "BusinessFax";
if (!dirECommunicationAddress) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "BusinessFax";
}
dirECommunicationAddress.Telex = Telex;
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.Telex == Telex &&
dirECommunicationAddress.ECommunicationTypeId == "BusinessFax";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its Fax record, Check this out!!");
} else {
TelexRecID = dirECommunicationAddress.RecId;
}
}
if (CellularPhone != "") {
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.Phone == CellularPhone &&
dirECommunicationAddress.ECommunicationTypeId == "CellularPhone";
if (!dirECommunicationAddress) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "CellularPhone";
}
dirECommunicationAddress.Phone = CellularPhone;
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.Phone == CellularPhone &&
dirECommunicationAddress.ECommunicationTypeId == "CellularPhone";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its CellularPhone record, Check this out!!");
} else {
CellularPhoneRecId = dirECommunicationAddress.RecId;
}
}
if (Pager != "") {
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.Phone == Pager &&
dirECommunicationAddress.ECommunicationTypeId == "Pager";
if (!dirECommunicationAddress) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "Pager";
}
dirECommunicationAddress.Phone = Pager;
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.Phone == Pager &&
dirECommunicationAddress.ECommunicationTypeId == "Pager";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its Pager record, Check this out!!");
} else {
PagerRecId = dirECommunicationAddress.RecId;
}
}
if (Email != "") {
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.Email == Email &&
dirECommunicationAddress.ECommunicationTypeId == "HomeEmail";
if (!dirECommunicationAddress) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "HomeEmail";
}
dirECommunicationAddress.Email = Email;
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.Email == Email &&
dirECommunicationAddress.ECommunicationTypeId == "HomeEmail";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its HomeEmail record, Check this out!!");
} else {
EmailRecId = dirECommunicationAddress.RecId;
}
}
if (URL != "") {
dirECommunicationAddress.ttsbegin();
select forupdate dirECommunicationAddress where dirECommunicationAddress.Url == URL &&
dirECommunicationAddress.ECommunicationTypeId == "PersonalHomePage";
if (!dirECommunicationAddress.RecId) {
dirECommunicationAddress.clear();
dirECommunicationAddress.initValue();
dirECommunicationAddress.ECommunicationTypeId = "PersonalHomePage";
}
dirECommunicationAddress.URL = URL;
dirECommunicationAddress.write();
dirECommunicationAddress.ttscommit();
select * from dirECommunicationAddress where dirECommunicationAddress.URL == URL &&
dirECommunicationAddress.ECommunicationTypeId == "PersonalHomePage";
if (!dirECommunicationAddress.RecId){
info(EmplID + " is missing its Personal homepage record, Check this out!!");
} else {
UrlRecId = dirECommunicationAddress.RecId;
}
}
//Finally we're going to add the CommunicationRelationShips
if (UrlRecID>0){//is there a URL record then create an Url Relationship
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==UrlRecID;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = UrlRecID;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
if (EmailRecID>0){
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==EmailRecID;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = EmailRecID;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
if (PagerRecID>0){
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==PagerRecID;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = PagerRecID;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
if (CellularPhoneRecID>0){
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==CellularPhoneRecID;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = CellularPhoneRecID;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
if (TelexRecID>0){
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==TelexRecID;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = TelexRecID;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
if (PhoneLocalRecId>0){
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==PhoneLocalRecId;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = PhoneLocalRecId;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
if (PhoneRecId>0){
dirPartyECommunicationRelationship.ttsbegin();
select forupdate dirPartyECommunicationRelationship where dirPartyECommunicationRelationship.PartyId==DPID &&
dirPartyECommunicationRelationship.ValuesRecId==PhoneRecId;
if (!dirPartyECommunicationRelationship) {
dirPartyECommunicationRelationship.clear();
dirPartyECommunicationRelationship.initValue();
dirPartyECommunicationRelationship.PartyId = DPID;
dirPartyECommunicationRelationship.PrivacyGroupId = "Employee";
}
dirPartyECommunicationRelationship.ValuesRecId = PhoneRecId;
dirPartyECommunicationRelationship.write();
dirPartyECommunicationRelationship.ttscommit();
}
}
}
}
I've exported this data from Axapta 3 to CSV like:
file1.write( EmplTable.EmplId,
EmplTable.Name,
Firstname,
Middlename,
Lastname,
EmplTable.Dimension[1],
EmplTable.Address,
EmplTable.Phone,
EmplTable.PhoneLocal,
EmplTable.zipcode,
EmplTable.Country,
EmplTable.Email,
EmplTable.URL,
EmplTable.CellularPhone,
EmplTable.TeleFax,
EmplTable.CalendarId,
EmplTable.BirthDate,
EmplTable.Alias,
EmplTable.ItemBuyerGroupId,
EmplTable.City,
EmplTable.Street,
EmplTable.Pager);
You can call this classmethod from a job, like:
My_SYSDataImport::My_ImportEmployeeAndAddresses();
Try this code, I know it has some rough edges but the main thing is that it's working.
Tell me things which are wrong with it so we can find a good solution to this problem.
Good luck...
