How to create HR Excel Templates for Ax2009

Finance and Operations/AX Developer Forum
1

Hello,

Does anyone ever import data using excel spreadsheet for Human Resources Module for Ax 2009? I need an advice on a proper way to create the templates for import purposes. I want to create a templates for end users to key the required data to be imported to Ax2009. Is it possible to do so since I find that HR module for Ax2009 is different from Ax4.0. There is this global address book and job details in Ax2009 which is not in Ax4.0. And it is tied to each other? I am really confuse on how it related with each other? Please advice.

Thank you

2

For templates, the same features should exist in 2009 as in 4.0. Go to Administration, Periodic, Import / Export. As an alternative you could use Atlas XL which makes the import process a bit neater.

For the GAB - it is a directory where every entity (organization, business relation or person) gets listed. When in company A you create an employee, it receives an ID in the GAB. Assuming you don’t share the employees table, if you switch to company B and create the same employee you can still relate it to the one you previous created in company A via the unique GAB ID.

This ensures integrity - single ID for a single entity in your Dynamics AX database, accross companies. In addition, the GAB allows you to setup relationships between entities but that’s another story.

Carlton.

3

hi copy this code into text file and made it as a xpo

Exportfile for AOT version 1.0 or later
Formatversion: 1

***Element: CLS

; Microsoft Dynamics Class: PwCImport unloaded
; --------------------------------------------------------------------------------
CLSVERSION 1

CLASS #PwCImport
PROPERTIES
Name #PwCImport
Extends #ObjectRun
RunOn #Called from
ENDPROPERTIES

METHODS
Version: 3
SOURCE #classDeclaration
#public class PwCImport extends objectrun
#{
#}
ENDSOURCE
SOURCE #readexcel
#public static server PwC_Import readexcel(filenameopen _file)
#{

#Excel

#AviFiles

SysExcelApplication excelApp = SysExcelApplication::construct();

SysOperationProgress progress = new SysOperationProgress();

SysExcelWorkSheet excelWorksheet;

SysExcelRange excelRange;

SysExcelCells excelCells;

SysExcelCell excelCell;

ComVariant cellValue = new ComVariant() ;

boolean Flag_Found ;

PwC_Import PwC_ImportLocal;

int colno, colno_excel, colno_grid;

int i;

int TotalRow ;

str strvalue ;

COMVariant VariantValue ;

Description Error;

excelApp.workbooks().open( _File);

excelWorksheet = excelApp.worksheets().itemFromNum(1);

excelCells = excelWorksheet.cells();

startlengthyoperation();

flag_found=true ; TotalRow=1 ;

//________________________________________________________

excelCell=excelWorksheet.cells().item(TotalRow+1,1);

strvalue=StrUpr(excelCell.value().bStr());

if(strvalue!="**********")

{

excelworksheet = null;

excelapp.quit();

Throw Error(“Invalid Excel Template. Please use template specified”);

}

//________________________________________________________

progress.setCaption(“Data Import”);

progress.setAnimation(#AviFormLetter);

startlengthyoperation();

ttsbegin;

while (Flag_Found==true)

{

progress.setText(strfmt(“Scanning Records %1”, TotalRow));

//Marked____________________________________________________

PwC_ImportLocal.Marked =NoYes::Yes;

//TransDate_________________________________________________

if(excelWorksheet.cells().item( TotalRow+2,1).value().date())

{

PwC_ImportLocal.Transdate =excelWorksheet.cells().item( TotalRow+2,1).value().date();

}

else

{

Error=Error+"Date ; ";

PwC_ImportLocal.Transdate =datenull();

PwC_ImportLocal.Marked =NoYes::No;

}

//Account___________________________________________________

If(LedgerTable::exist(excelWorksheet.cells().item( TotalRow+2,2).value().bStr()))

{

PwC_ImportLocal.Account=excelWorksheet.cells().item( TotalRow+2,2).value().bStr();

}

else

{

Error =Error+"<A/C>; ";

PwC_ImportLocal.Account ="";

PwC_ImportLocal.Marked =NoYes::No;

}

//Transaction Text_____________________________________________

PwC_ImportLocal.TransTxt =excelWorksheet.cells().item( TotalRow+2,3).value().bStr();

//Offset Account_______________________________________________

If(LedgerTable::exist(excelWorksheet.cells().item( TotalRow+2,4).value().bStr()))

{

PwC_ImportLocal.OffsetAccount =excelWorksheet.cells().item( TotalRow+2,4).value().bStr();

}

else

{

Error =Error+"<Offset A/C>; ";

PwC_ImportLocal.OffsetAccount ="";

PwC_ImportLocal.Marked =NoYes::No;

}

//Amount Check_________________________________________________

if( (excelWorksheet.cells().item(TotalRow+2,5).value().double() && excelWorksheet.cells().item(TotalRow+2,6).value().double()) ||

(!excelWorksheet.cells().item(TotalRow+2,5).value().double() && !excelWorksheet.cells().item(TotalRow+2,6).value().double()) )

{

Error =Error+"<Dr & Cr>; ";

PwC_ImportLocal.Marked =NoYes::No;

}

//DebitAmount__________________________________________________

PwC_ImportLocal.Debit = excelWorksheet.cells().item(TotalRow+2,5).value().double();

//Credit Amount________________________________________________

PwC_ImportLocal.Credit = excelWorksheet.cells().item(TotalRow+2,6).value().double();

//Dimensions___________________________________________________

PwC_ImportLocal.Dimensions[1] =excelWorksheet.cells().item(TotalRow+2,7).value().bStr();

PwC_ImportLocal.Dimensions[2] =excelWorksheet.cells().item(TotalRow+2,8).value().bStr();

PwC_ImportLocal.Dimensions[3] =excelWorksheet.cells().item(TotalRow+2,9).value().bStr();

//Error Description____________________________________________

PwC_ImportLocal.ErrorDesc=Error;

Error=""; // Reset to Null

TotalRow++;

PwC_ImportLocal.insert();

progress.incCount();

if(excelWorksheet.cells().item(TotalRow+2,2).value().bstr()=="")

Flag_Found=false;

}

ttscommit;

endlengthyoperation();

excelworksheet = null;

excelapp.quit();

return PwC_ImportLocal;

#}
ENDSOURCE
ENDMETHODS
ENDCLASS

***Element: FRM

; Microsoft Dynamics Forms unloaded
; --------------------------------------------------------------------------------
FRMVERSION 5

FORM #PwC_Import
PROPERTIES
Name #PwC_Import
ENDPROPERTIES

METHODS
Version: 3
SOURCE #classDeclaration
#public class FormRun extends ObjectRun
#{

FormStringControl fileNameControl;

#define.expImpFileExtension(".xls")

LedgerJournalNameId LJournalName;

#define.currentVersion(1)

#localmacro.CurrentList

LJournalName

#endMacro

#}
ENDSOURCE
SOURCE #run
#public void run()
#{

super();

xSysLastValue::getLast(this);

JournalName.text(LJournalName);

#}
ENDSOURCE
SOURCE #unpack
#public boolean unpack(container packedClass)
#{

int version = runbase::getVersion(packedClass);

switch (version)

{

case #CurrentVersion:

[version,#CurrentList] = packedClass;

return true;

default :

return false;

}

return false;

#}
ENDSOURCE
SOURCE #pack
#public container pack()
#{

return [#CurrentVersion,#CurrentList];

#}
ENDSOURCE
SOURCE #close
#public void close()
#{

super();

LJournalName=JournalName.text();

xSysLastValue::saveLast(this);

#}

ENDSOURCE
SOURCE #initParmDefault
#/ARIJIT
#This method is called when no SysLastValue record can be found in xSysLastValue.GetLast()
#/
#void initParmDefault()
#{
#}

ENDSOURCE
SOURCE #lastValueDesignName
#private IdentifierName lastValueDesignName()
#{

return ‘’;

#}

ENDSOURCE
SOURCE #lastValueElementName
#private IdentifierName lastValueElementName()
#{

return this.name();

#}

ENDSOURCE
SOURCE #lastValueType
#private UtilElementType lastValueType()
#{

return UtilElementType::Form;

#}

ENDSOURCE
SOURCE #lastValueUserId
#private UserId lastValueUserId()
#{

return curuserid();

#}

ENDSOURCE
SOURCE #lastValueDataAreaId
#public dataAreaId lastValueDataAreaId()
#{

return curExt();

#}

ENDSOURCE
SOURCE #init
#public void init()
#{

;

xSysLastValue::getLast(this);

super();

CreateJournal.enabled(false);

PrintTable.enabled(false);

#}

ENDSOURCE
SOURCE #fileNameLookupFilename
#str fileNameLookupFilename()
#{

Filename filepath;

Filename fileName;

Filename fileType;

[filepath, fileName, fileType] = fileNameSplit(fileNameControl.text());

return fileName + fileType;

#}
ENDSOURCE
SOURCE #fileNameLookupTitle
#// AOSRunMode::client
#str fileNameLookupTitle()
#{

return 'Lookup Title: ’ + fileNameControl.label();

#}
ENDSOURCE
SOURCE #fileNameLookupFilter
#container fileNameLookupFilter()
#{

#file

return [WinApi::fileType(’.xls’), #allfilesName+’.xls’, #allFilesType, #allFiles];

#}
ENDSOURCE
SOURCE #fileNameLookupInitialPath
#// AOSRunMode::client
#str fileNameLookupInitialPath()
#{

#WinAPI

Filename filepath;

Filename fileName;

Filename fileType;

[filepath, fileName, fileType] = Global::FileNameSplit(fileNameControl.text());

return filepath;

#}
ENDSOURCE
ENDMETHODS
OBJECTBANK
DATASOURCE
OBJECTPOOL
PROPERTIES
Name #PwC_Import
Table #PwC_Import
AllowEdit #No
AllowCreate #No
AllowDelete #No
InsertIfEmpty #No
ENDPROPERTIES

FIELDLIST
ENDFIELDLIST
ENDOBJECTPOOL
METHODS
Version: 3
SOURCE #displayOption
#public void displayOption(Common _record, FormRowDisplayOption _options)
#{

PwC_Import PwC_Importlocal;

PwC_Importlocal = _record;

super(_record, _options);

if(PwC_Importlocal.ErrorDesc != “”)

{

_options.backColor(8421631); //RED

}

#}
ENDSOURCE
ENDMETHODS
ENDDATASOURCE
ENDOBJECTBANK

JOINS
ENDJOINS

DESIGN
PROPERTIES
Caption #Data Import
TitleDatasource #PwC_Import
SaveSize #Yes
ENDPROPERTIES

CONTAINER
CONTROL GROUP
;==== controlId:12290, containerId: 0, containerdata: 0
;----------------------------------------------------------
PROPERTIES
Name #Group
Width #Column width
Height #Column height
FrameType #Raised 3D
ENDPROPERTIES

CONTAINER
CONTROL GROUP
;==== controlId:12291, containerId:12290, containerdata:12290
;----------------------------------------------------------
PROPERTIES
Name #Header
Width #Column width
FrameType #Edged 3D
Columns #4
ArrangeGuide
ARRAY INDEX Columns
#0
#0
#0
#0
&nb

4

Carlton,

I read your post and I would like to ask you if you have some information (tutorials or anything really) that can teach me how to import a spreadsheet to AX uning Atlas 3.5.

Thanks,

Eduardo

5

Eduardo,

I only used the documentation coming with the product.You can download it from here:

http://www.atlasxl.com.au/Download.htm

Additionally, you may contact the developers at support@atlasxl.com.au

Hope that helps,

C.

6

Hello,

I am new to AX. Can I use your program to import employee details in AX? If yes, how do I proceed after I have created the .xpo file.