Creating a factbox in a list page in AX 2012

Today I want to create a factbox which shows only the open orders in purchTableListPage.

Query :

• First of all create a new query PurchOpenStatus with the datasources vendTable and PurchTable.
• PurchTable has joinmode inner join and fetchmode 1:n with relations yes.
• Important to note that whenever you add a datasource to a query set the dynamic property on fields to yes so that all the fields are visible here.
• As I want to get only the open orders details in the factbox i need to add a range on the purchTable datasource with purchStatus and value open order.

Parts :

• The next step is to create a part PurchOpenStatusPart and the newly created query in the properties window.
• On the orders and set the datasource as VendTable, repeating yes and  showCaption yes.
• Right click on the orders and add the fields purchId, status and createdDate with the datasource as purchTable and the respective datafields and datamethods.

MenuItem :

• Now drag this infopart into display menuitem.

Add factbox to listPage:

• Drag this menuitem into the parts node of PurchTableListPage and set the required datasource and datasourceRelation.

      Save your changes and your factbox should be visible on the form

Pass values between one form to other with the 2nd form showing filtered records based on values passed from 1st form

Todays blog is an example of how values are passed from one form to other and the 2nd form displays the filtered records based on the values passed from 1st form.

1) Create a form TripPlanner with style dialog and fields start date, end date and department (financial dimension field with lookup) and menuitem button OK.

Create the following parm method in the form methods which stores the values provided in the form in a container.

public container parmDateCon()
{
    container con;

    con = [StartDate.valueStr(),EndDate.valueStr(),Department.valuestr()];
    return con;
}

NOTE :
a) StartDate , EndDate and Department are the form controls whose auto declaration property is set to yes.

b) StartDate and EndDate are the fields from SMAServiceOrderLine_TripPlanner (My new table).

2) Override the lookup method of Department form control and add the following code

public void lookup()
{
    SysTableLookup sysTablelookup = SysTableLookup::newParameters(tableNum(DimAttributeOMDepartment),this);
    Query query = new Query();
    QueryBuildDataSource qbds;
       
    qbds = query.addDataSource(tableNum(DimAttributeOMDepartment));

    sysTablelookup.addLookupfield(fieldNum(DimAttributeOMDepartment,Value));
    sysTablelookup.addLookupfield(fieldNum(DimAttributeOMDepartment,Name));
           
    sysTablelookup.parmQuery(query);
    sysTablelookup.performFormLookup();
}

3) Create a menuitem button under Button Group in the group dialog commit with text OK and menuitem name as PlannedDetails. This PlannedDetails is the new form that gets opened on click of OK button and the values entered in TripPlanner are passed to the new form PlannedDetails.

Write the following code in the clicked method of the button.

void clicked()
{
    super();

    element.close();
}

4) The form looks like this

5)  Now create the 2nd form PlannedDetails with fields ServiceOrderId, Customer, StartDate, EndDate, Description and Driver.

Add the datasources SMAServiceOrderLine and SMAServiceOrderLine_TripPlanner

public class FormRun extends ObjectRun
{
    str                     sDate;
    str                     eDate;
    str                     dept;

    FromDateTime            startDate;
    ToDateTime              endDate;
    FromDateTime            stDate;
    ToDateTime              edDate;

    container               cont;

    QueryBuildDataSource    qbds;
    QueryBuildRange         qbrDate;
    QueryBuildRange         qbrDept;
}

6) Add the following code in the form init.

public void init()
{
    object  callerFormObject;
    callerFormObject = element.args().caller();

    if(formHasMethod(callerFormObject, literalStr(parmDateCon)))
    {
       cont =  callerFormObject.parmDateCon();
    }

    sDate = conPeek(cont,1);
    startDate = str2datetime(sDate, 213);

    eDate = conPeek(cont,2);
    endDate = str2datetime(eDate, 213);

    dept = conPeek(cont,3);

    super();
}

7) Under the datasource SMAServiceOrderLine_TripPlanner override init method and add the below code

public void init()
{
    super();

    qbds    = this.query().dataSourceTable(tableNum(SMAServiceOrderLine_TripPlanner));
    qbrDate = qbds.addRange(fieldNum(SMAServiceOrderLine_TripPlanner, DataAreaId));
    qbrDept = qbds.addRange(fieldNum(SMAServiceOrderLine_TripPlanner, Department));

    if((startDate && endDate) != 0)
    {
        qbrDept.value(queryValue(dept));

        qbrDate.value(strFmt('(((%2 > %4) && (%1 < %3)) || ((%1 < %3) && (%2 > %4)))', fieldStr(SMAServiceOrderLine_TripPlanner, StartDate), fieldStr(SMAServiceOrderLine_TripPlanner, Enddate), DateTimeUtil::toStr(endDate),DateTimeUtil::toStr(startDate)));
        info(queryvalue(qbds.toString()));
    }
}

8) Now when you click the OK button in the form TripPlanner, the values are passed from this form to new form PlannedDetails and this shows the filtered records based on the start date, end date and department values provided in the form TripPlanner.

Capturing infolog messages in AX 2012

Today I am going to explain how to capture infolog messages for a particular record and show those logs on click of a log button on a form.

1) Create a job which inserts records into a table, prints some info messages and capture them.

logTable contains three fields EmpId, name and info. info is a field created with EDT infologData and visible property is set to NO. info is a container which stores the infolog messages for the particular record.

static void logs(Args _args)
{
     logTable       insertLog;
 
     NumberSeq  numberSeq;
     InfologData  msg;

     ttsbegin;
     insertLog.initValue();

     numberSeq                          =   NumberSeq::newGetNum(InventParameters::numRefEmpID());
     insertLog.EmpId           =   numberSeq.num();
     insertLog.Name                  =   'ABC';

     info(strFmt("%1", insertLog.EmpId));
     info(strFmt("%1", insertLog.Name));
     info(strFmt("Employee with %1 & name %2 created successfully", insertLog.EmpId,insertLog.Name));
     msg        =    infolog.infologData();
     insertLog.Info = msg;

     insertlog.insert();
     infolog.clear();

     ttscommit;
}

NOTE :
i) Save compile and run this job. EmpId is generated through number sequence and name is hard coded. These two values are inserted into table.

ii) The info stored for this record are :

EMPID-0001
New
Employee with EMPID-0001 & name ABC created successfully

2)  Now create a form with grid showing the logTable datasource fields and button named log.
Write the following code in clicked method of the button

void clicked()
{
    InfologData  msg;

    msg = logTable.info;
    infolog.import(msg);
}

Now when you select this particular record and press the log button the infolog messages stored for this record are displayed in info.

Inserting records in a table through a job in AX 2012

Create a table name StudentTable and fields StudentId, StudentName respectively.
StudentId should be generated through number sequence. Creation of number sequence is shown in my previous blog.

Now create a job with the following code :

static void studentData(Args _args)
{
    studentTable   student;
    NumberSeq     numberSeq;

    ttsbegin;
    student.initValue();

    numberSeq            =    NumberSeq::newGetNum(HRMParameters::numRefSTUDID());
    student.StudentId  =    numberSeq.num();
    student.name         =    "ABC";

    student.insert();
    info(strFmt("Student with studentId : %1 and name : %2 is created successfully", student.StudentId, student.StudentName));
    ttscommit;
}

Generating number sequence in AX 2012

First of all to create a number sequence for a field in a table create a table with name Departments and having two fields deptId and deptName.

deptId is a new EDT created with name dept_Id and deptName with name EDT.

1) Goto organisation administration -> number sequences -> number sequences -> new number sequence -> 

Fill in the fields under identification fast tab and under segments click on add

Constant ---   DEPT

Constant ---   -

Alplanumeric --- ####

below that format now appears as DEPT-####

Remember that under general smallest is 1 and largest is 9999 as number of # specified under segments is 4 so write 9999(four 9's).

save it and generate the number sequence.

2) Classes -> NumberSequenceHRM -> loadmodule

    datatype.parmDatatypeId(extendedtypenum(Dept_Id));

    datatype.parmReferenceHelp(literalstr("@SYS852"));

    datatype.parmReferenceLabel(literalstr("@SYS386"));

    datatype.parmWizardIsContinuous(true);

    datatype.parmWizardIsManual(NoYes::No);

    datatype.parmWizardIsChangeDownAllowed(NoYes::No);

    datatype.parmWizardIsChangeUpAllowed(NoYes::No);

    datatype.parmWizardHighest(9999);

    datatype.parmSortField(13);

    this.create(datatype);

you can copy and paste from the method but change the extendedtypenum, help, label, wizardhighest and sortfield number increase to next number.

3) DataDictionary -> Tables -> HRMParameters -> methods ->

create a new method numRefDeptId with the following code :

static client server NumberSequenceReference numRefDeptId()

{

    return NumberSeqReference::findReference(extendedTypeNum(Dept_Id));

}

4) In the newly created table Departments override the initValue and write the following code :

public void initValue()

{

    NumberSeq numberSeq;

    NumberSequenceReference numSeqRef;

    numSeqRef = InventParameters::numRefDeptId();

    numSeqRef = NumberSeqReference::findReference(extendedTypeNum(Dept_Id));

    super();

    numberSeq = NumberSeq::newGetNum(numSeqRef);

    this.deptId = numberSeq.num();

}

5) Create a job

static void numberSeqDeptId(Args _args)

{

    NumberSeqModuleHRM obj = new NumberSeqModuleHRM();

    obj.load();

}

save compile and run the job

6) Goto HumanResource -> Setup -> Parameters -> Human resources shared parameters

Under number sequences a new number sequence is created now with the label you mentioned now right click and view details. under general in use should be checked so that you can use this number sequence.

7) To check the number sequence goto table browser and ctrl-N 

DEPT-0001 comes under deptId

This is how a number sequence is generated and used .... :)

Export data to text file in ax2012

static void textFileExport(Args _args)
{
    TextIo  textIo;
    container line;
    Filename    filename;
   
    SiteTable    site;
   
    filename = @"C:\Users\Desktop\sitedetails.txt";
   
    textIo = new TextIo(filename, 'W');
    textIo.outFieldDelimiter(';');
   
    if(!textIo || textIo.status() != IO_Status::Ok)
    {
        error("File cannot be opened");
    }
   
    while select site
    {
        line = [site.SiteId, site.Name];
        textIo.writeExp(line);
    }
}

Importing table data from CSV file to AX using CommaIo class in AX 2012

Create a class with the following code :

class importCSVCommaIO extends RunBase
{
    Filename        filename;
    DialogField     dialogFilename;
}

public static void main(Args    _args)
{
    importCSVCommaIO    importDept = new importCSVCommaIO();
    FormRun formRun;
    Args    args;

    if(importDept.prompt())
    {
        importDept.importDeptTable();
    }
}

public Object dialog()
{
    DialogRunbase       dialog = super();

    dialogFilename   = dialog.addField(extendedTypeStr(FilenameOpen));
    dialogFilename.value(filename);
    return dialog;
}

public boolean getFromDialog()
{
    fileName = dialogFileName.value();
    return super();
}

void importDeptTable()
{
    CommaIo         commaIO;
    DepartmentTable department;
    DepartmentTable departmentTable;

    Container       con;
    boolean first = true;
    FileIOPermission    readPermission;
    #file

    readPermission = new FileIOPermission(filename,#io_read);
    readPermission.assert();

    commaIO = new CommaIo(filename,'r');
    commaIO.inFieldDelimiter(',');
    CodeAccessPermission::revertAssert();

    if (commaIO)
    {

        while(commaIO.status() == IO_Status::OK)
        {
            con = commaIO.read();
            if (conLen(con) > 0)
            {
                if(first)
                {
                    first = false;
                }
                else
                {
                    ttsbegin;
                   
                    department = DepartmentTable ::find(conpeek(con,1), true);
                    if(department.RecId == 0)
                    {
                        department.DeptId       =   conpeek(con,1);
                        department.Description  =   conpeek(con,2);
                        department.insert();
                    }
                    else
                    {
                        department.selectForUpdate(true);
                        select departmentTable where departmentTable.DeptId == department.DeptId;
                        department.Description = conPeek(con,2);
                        department.update();
                    }
                     ttscommit;
                }

            }
        }

    }
}

select the csv file from the browse button and on press OK the data gets imported into the table.

Exporting data from AX to csv file using commaIO class in AX 2012

Write a job with the following code :

static void ExportDataToCSVcommaIO(Args _args)
{
    Query                                query;
    QueryBuildDataSource    qbds;
    QueryBuildRange            qbr;
    QueryRun                        qRun;
    CommaIo                         commaIO;
    Filename                          fileName;
 
    DepartmentTable          department;
    #WINAPI;
 
    fileName = WinAPI::getFolderPath(#CSIDL_DESKTOPDIRECTORY) + "\\" + "Department details" + ".csv";
 
    commaIO = new CommaIo(fileName,'W');
 
    query = new Query();
    qbds = query.addDataSource(tableNum(DepartmentTable));
    qbr = qbds.addRange(fieldNum(DepartmentTable,DeptId));
 
    qRun = new QueryRun(query);
 
    commaIO.write("DeptId","Description");
    while(qRun.next())
    {
        department = qRun.get(tableNum(DepartmentTable));
     
        commaIO.write(department.DeptId, department.Description);
    }
 
    WinAPI::createFile(fileName);
}

NOTE :

1) #CSIDL_DESKTOPDIRECTORY gets the desktop of the current user logged into.
2) A new file gets created with the specified name in the filename in the given folder path.

Exporting data using shellexecute method of WINAPI class:

fileName       = WINAPI::getTempPath() + "Department Details" + ".csv";

WINAPI::shellExecute(fileName);

When you run the job with the above code excel file with the table data gets opened automatically.

Moving all files from a folder of specified format from one folder to other using WINAPI class in AX 2012

The following piece of code filters the source file path folder for the files with extension .docx and copies them into the destination path specified.

public void moveAllFiles()
{
    fileFilter = '\\*.docx';

    sourcePath = dialogSource.value();

    destPath = dialogDestination.value();

    [fileHandle, filename] = WinAPI::findFirstFile(sourcePath + fileFilter);

    while(fileName)
    {
        info(strFmt("%1 %2", sourcePath , fileName));

        copyFromSourceFolder = sourcePath + "\\" + fileName;
       
        copyToDestFolder = destPath + "\\" + filename;
       
        WinAPI::copyFile(copyFromSourceFolder, copyToDestFolder);

        fileName = winAPI::findNextFile(fileHandle);
    }
}

NOTE :

1) The extension format may be anything .docx .txt .xpo or anthing.
2) Here source and destination dialog fields are of type filepath.

File Operation using WinAPI in Dynamics AX 2012

Axapta’s WinAPI class has a bunch of static methods to handle files. It is a very useful class when handling files that need to be accessed outside of the AX environment

The examples below show how to utilize some of these methods.

1) Create a dialog which takes source and destination file name and file path respectively and on click of OK button COPY the file from source folder to destination folder.

Create a class example_WINAPI with the following methods :

class example_WINAPI extends RunBase
{
    FilenameOpen    fileNameOpen;

    Filename    filename;
    Filename    filePath;
    Filename    fileType;
    Filename    destPath;

    DialogField dialogSource;
    DialogField dialogDestination;

    str sourceFileName;

    str destFileName;
}

public static void main(Args    args)

{

    example_WINAPI  fileDialog = new example_WINAPI();

    fileDialog.createDialog();

}

public void createDialog()

{

    Dialog dialog;

    dialog = new Dialog("Move files");

    dialog.caption('Move file');

    dialogSource        =   dialog.addField(extendedTypeStr(FilenameOpen), "@SYS1425");

    dialogDestination   =   dialog.addField(extendedTypeStr(FilePath), "@SYS37735");

    dialogSource.value(filename);

    if(dialog.run())

    {

        this.copyFile();

    }

}

public void copyFile()

{

    sourcefileName = dialogSource.value();

    [filePath, fileName, fileType] = fileNameSplit(sourcefileName);

    destPath = dialogDestination.value();

    destFileName = destPath + "\\" + conPeek(fileNameSplit(sourceFileName),2) +    conPeek(fileNameSplit(sourceFileName),3);

    info(queryValue(destFileName));

    WinAPI::copyFile(sourceFileName, destFileName);

}

2) Create a dialog which takes source and destination file name and file path respectively and on click of OK button MOVE the file from source folder to destination folder.

Add one more method moveFile to the above class and call it from createDialog method

public void moveFile()

{

    sourcefileName = dialogSource.value();

    if(WinAPI::fileExists(sourceFileName))

    {

        [filePath, fileName, fileType] = fileNameSplit(sourcefileName);

        destPath = dialogDestination.value();

        destFileName = destPath + "\\" + conPeek(fileNameSplit(sourceFileName),2) + conPeek(fileNameSplit(sourceFileName),3);

       

        WinAPI::moveFile(sourceFileName, destFileName);

    }

    else

    {

        warning("This file doesnot exist in the specified path");

    }

}

NOTE :

In the above method one more method fileExists is used from WINAPI class to check whether the specified file is present in the path or not. If present then executes the code else throws the warning message.

3) Delete a file in a specified path :

WINAPI::deleteFile(fileName);

Simple query filter in ax 2012

The following is an example of filtering based on itemId selected.

Basically you have a form which has a grid at the top and item id , item group text fields where you need to select both item id and item group from the lookup and the grid is filtered based on the values selected.

1) In the class declaration :

class FormRun extends ObjectRun
{
    InventTable inventTable;  // create a buffer for the datasource

    QueryBuildRange qbrItemId;
    QueryBuildRange qbrItemGroup;
}

2) Add inventTable in the datasource node and write the following code in the init of that datasource

public void init()
{
    QueryBuildDataSource qbds;

    super();

    qbds           =   this.query().dataSourceTable(tableNum(InventTable));
    qbrItemId   =  qbds.addRange(fieldNum(InventTable, ItemId));
}

3) Add InventItemGroupItem in the datasource node and write override init

public void init()
{
    QueryBuildDataSource qbds;

    super();

    qbds                   =   this.query().dataSourceTable(tableNum(InventItemGroupItem));
    qbrItemGroup    =  qbds.addRange(fieldNum(InventItemGroupItem, ItemGroupId));
}

4) Add ItemId stringedit form control to the design node and override modified method in the methods node and write the following code

public boolean modified()
{
    boolean ret;
    str     value;

    ret = super();

    if(ret)
    {
        value = qbrItemId.value('*' + InventTable_ItemId1.valueStr() + "*");
     
        InventTable_ds.executeQuery();
        InventTable_ds.refresh();
        InventTable_ds.research();
    }

    return ret;
}

NOTE :

In the above code InventTable_ItemId1 is the form control name whose auto declaration is set to yes.

5) Add ItemGroupId form control to design node and override lookup method

public void lookup()
{
    SysTableLookup        sysTableLookup =     SysTableLookup::newParameters(tableNum(InventItemGroup), this);
    Query                 query = new Query();
    QueryBuildDataSource  qbdsInventItemGroup;

    qbdsInventItemGroup = query.addDataSource(tableNum(InventItemGroup));
    qbdsInventItemGroup.addOrderByField(fieldNum(InventItemGroup, ItemGroupId));

    qbdsInventItemGroup.addRange(fieldNum(InventItemGroup, Name)).value(SysQuery::valueNotEmptyString());

    sysTableLookup.addLookupfield(fieldNum(InventItemGroup, ItemGroupId),true);
    sysTableLookup.addLookupfield(fieldNum(InventItemGroup, Name),false);

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

6) Override modified method

 public boolean modified()
{
    boolean ret;

    ret = super();

    if(ret)
    {
        qbrItemGroup.value('*' + InventItemGroupItem_ItemGroup.valueStr() + '*');
   
        InventTable_ds.executeQuery();
        InventTable_ds.research();
    }

    return ret;
}

Passing values from one form to other using args AX 2012

You have two fields in a form and on click of OK button new form should open and values should be passed to new form.

add this code in the OK clicked method :

    FormRun formRun;
    container con;
    str strcon;

    Args args = new Args();

    con =    [SMAServiceOrderLine_W_StartDate.valueStr(),SMAServiceOrderLine_W_EndDate.valueStr()];
    strcon = con2Str(con);
    args.parm(strcon);

    args.name(formstr(NEWFORMNAME));

    formRun = ClassFactory.formRunClass(args);
    formRun.init();

    formRun.run();
    formRun.wait();


The values entered in this form can be retrieved in other form using container. Start date and end date are the two values that are passed from this form

Enabling and disabling fields based on enum type in AX 2012

Example you have transaction type Enum in SMAServiceOrderLine table,
then you have a requirement to enable some fields in SMAServiceOrderLine_W only when smatransaction type = hours else disabled.

Then goto datasource methods and override active method and write the following code :

public int active()
{
    int ret;

    ret = super();

    if(smaserviceorderline.transactiontype != smatransactiontype::hour)
    {
        smaserviceorderline_w_ds.allowedit(false);
    }
    else
    {
         smaserviceorderline_w_ds.allowedit(true);
    }

    return ret;
}