Create new Purchase Order Line with X++

Example on how to create a new PO line: 

purchLine.clear();purchLine.purchId = purchTable.PurchId;
purchLine.ItemId = itemId;purchLine.createLine(NoYes::Yes, NoYes::Yes, NoYes::Yes,
                     NoYes::Yes, NoYes::Yes, NoYes::Yes);

Delete lines from purch line table based on specific criteria

Delete lines from purch line table based on specific criteria we will write this code inside function or event for example clicked :

void clicked()
{
     PurchLine _purchline;
     int Counter=0;
    ;
    try
    {
        ttsbegin;
          while select forupdate _purchline where(_purchline.PurchReceivedNow==0 &&    _purchline.DIST==noyes::No)
         {
            if(_purchline.receivedInTotal()==0 && substr(_purchline.PurchId,5,-5)!='ERSDX')
            {
                _purchline.delete();                Counter++;
            }
         }
         ttscommit;
         info(strfmt("Number of Lines Deleted : %1",Counter));
    }
 catch
 {
       info(_purchline.PurchId);
 }
}

How to build form by code Dynamics AX 2012

    Form                   form;
    FormRun             formRun;
    Args                    args;
    FormBuildDesign         formBuildDesign;
    FormBuildControl        formBuildControl;
    FormBuildTabControl     formBuildTabControl;
    FormBuildTabPageControl formBuildTabPageControl;
    FormBuildGridControl    formBuildGridControl;
    FormBuildDatasource     formBuildDatasource;
    FormBuildStringControl  formString;
    ;

    form = new Form();
    formBuildDatasource = form.addDataSource(tableStr(PurchTable));
    formBuildDesign = form.addDesign('design');
    formBuildTabControl = formBuildDesign.addControl(FormControlType::Tab, 'Tab');
    formBuildTabPageControl = formBuildTabControl.addControl(FormControlType::TabPage, 'TabPage');
    formBuildGridControl = formBuildTabPageControl.addControl(FormControlType::Grid, 'Grid');
    formString = formBuildGridControl.addDataField(formBuildDatasource.id(), fieldNum(PurchTable, PurchId));
    formString.label("PurchId");

    args    = new Args();
    args.object(form);
    formRun = classFactory.formRunClass(args);
    formRun.init();
    formRun.run();
    formRun.wait();

SysExcelWorksheetHelper and SysExcelHelper classes in AX 2012–Quickly create excel [X++]

Friends,

SysExcelWorksheetHelper class in AX 2012 will help to quickly create an excel with the desired fonts, summation[sum ranges] features, auto fit columns, populate field names as headers from the tables etc.

SysExcelHelper class will help to create and manage the overall workbook. Remember the same thing can be achieved using SysExcel* classes.

Below is the sample code. This example displays Account Number, Currency, Customer group and BalanceMST fields and it will sum the BalanceMST fields and will displays as total at the end.

static void SR_SysExcelWorksheetHelper(Args _args)

{

    CustTable               custTable;

    SysExcelWorksheetHelper worksheetHelper;

    SysExcelHelper          sysExcelHelper;

    SysExcelWorksheet       worksheet;

    int                     currentRow = 1;

    int                     sumRow;

    str                     worksheetName;

    int                     redColor = WinAPI::RGB2int(255, 0, 0);

    SysExcelRange           range;

    COMVariant              cellValue = new COMVariant(COMVariantInOut::Out);

    str                     fileName;

    str attachmentPath      = "C:\\";

    // Sets the font color for a range of cells

    void setRangeFont(int _fromColumn, int _fromRow, int _toColumn, int _toRow, int _rgbIntColor)

    {

        range = worksheetHelper.getWorksheetRange(_fromColumn, _fromRow, _toColumn, _toRow);

        worksheetHelper.setFontColor(range, _rgbIntColor);

    }

    // Defines the columns of the spreadsheet

    #define.AccountNum(1)

    #define.Currency(2)

    #define.CustGroup(3)

    #define.BalanceMST(4)

    worksheetName = "@SYS135880";

    sysExcelHelper = SysExcelHelper::construct();

    sysExcelHelper.initialize();

    worksheet = sysExcelHelper.addWorksheet(worksheetName);

    worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);

    // Populate the header row with the appropriate field labels and format the columns

    worksheetHelper.addColumnFromTableField(#AccountNum, tablenum(CustTable), fieldnum(CustTable, AccountNum));

    worksheetHelper.addColumnFromTableField(#Currency, tablenum(CustTable), fieldnum(CustTable, Currency));

    worksheetHelper.addColumnFromTableField(#CustGroup, tablenum(CustTable), fieldnum(CustTable, CustGroup));

    worksheetHelper.addColumn(#BalanceMST, "Balance MST", Types::Real);

    while select custTable

    {

        currentRow ++;

        worksheetHelper.setCellValue(#AccountNum, currentRow, custTable.AccountNum);

        worksheetHelper.setCellValue(#Currency, currentRow, custTable.Currency);

        worksheetHelper.setCellValue(#CustGroup, currentRow, custTable.CustGroup);

        worksheetHelper.setCellValue(#BalanceMST, currentRow, custTable.balanceMST());

    }

    if (currentRow > 1)

    {

        sumRow = currentRow + 2;

        worksheetHelper.setCellValue(#BalanceMST, sumRow, "@SYS58659");

        worksheetHelper.sumRange(worksheetHelper.getWorksheetRange(#BalanceMST, 2, #BalanceMST, currentRow), #BalanceMST, sumRow);

        setRangeFont(#BalanceMST, 2, #BalanceMST, currentRow, redColor);

        cellValue = worksheet.cells().item(sumRow, #BalanceMST).value();

        if (cellValue.currency() > 0)

        {

        setRangeFont(#BalanceMST, sumRow, #BalanceMST, sumRow, redColor);

        }

    }

    worksheetHelper.autoFitColumns();

    worksheetHelper.formatWorksheetTableStyle(sysExcelHelper.getOfficeVersion());

    // Generate the file using the current UTC date time (without the ‘:’ character)

    // since it is not allowed for file names.

    fileName = strfmt(‘%1%2%3′, attachmentPath, strReplace(DateTimeUtil::toStr(DateTimeUtil::utcNow()), ‘:’, ”), sysExcelHelper.getFileExtension());

    sysExcelHelper.save(filename);

    sysExcelHelper.launchExcel();

}

Output:

Happy Dax6ng,

Sreenath Reddy

Adding a role to all or multiple users using X++ Code [Dynamics AX 2012]

Friends,

In AX 2012, we can add a role to all the users by running the automated role assignment job based on the rule which we define in the “Assign users to Role” form.

Navigation : System administration >> Setup >> Security >> Assign users to roles

And the code behind this is :

SysSecurityDynamicRoleAssignment::synchronize(args.record().RecId); // selected Role (this is Security Role table buffer) in the form

I tried to run the above code independently by passing the “Budget clerk” Security Role but did not work due to obvious reasons as it needed a rule assignment table buffer.

The equivalent code to assign a role to all users is below.

This doesn’t need to add any rules like what we do in the form.This is just an experiment to handle the roles through code and request to test the code before using it.

static void SR_AssignRoleToAllUsers(Args _args)

{

    SecurityRole        role;

    SecurityUserRole    userRole;

    boolean             added;

    UserInfo            userInfo;

    ;

    select role where role.Name == ‘Budget clerk’;

    while select userInfo

    {

        select * from userRole

            where userRole.SecurityRole == role.RecId &&

                userRole.User == userInfo.id;

        if (!userRole || (userRole.AssignmentStatus != RoleAssignmentStatus::Enabled))

        {

            info(strFmt(‘Role %1 added to the user %2 successfully.’, role.Name, userInfo.id));

            userRole.User = userInfo.id;

            userRole.SecurityRole = role.RecId;

            userRole.AssignmentMode = RoleAssignmentMode::Manual;

            userRole.AssignmentStatus = RoleAssignmentStatus::Enabled;

            SecuritySegregationOfDuties::assignUserToRole(userRole, null);

        }

        else

        {

            warning(strFmt(‘skipping – Role %1 to the user %2.’, role.Name, userInfo.id));

        }

    }

}

Happy dax6ng,

Sreenath Reddy

SysTableBrowser customized to show Field List [AX 2012 and 5.0]

Friends,

I have tweaked SysTableBrowser form to show only the fields/Field list based on the query we give in the Table browser. It is little difficult to see the field values in the browser specially if a table has more fields [we need to use scroller and sometimes becomes difficult to identify the field in the table browser]. Please note: This is only helpful for the developers to quickly see the fields he would like to in Table browser.

In the standard, If we write field list in the query and run it, only the fields queried upon will be retrieved and other fields/columns will not be retrieved. But this will still not help us as we need to still scroll and search for the field values in the Table Browser.

Standard Table browser as of now:

Below is the Sales Table browser: I am trying to retrieve SalesId and CommissionGroup as shown below. As we see, rest all fields values cannot be retrieved except SalesId and CommissionGroup but still I have to scroll in the grid to find out the sales ID value.

To overcome this : I have tweaked SysTableBrowser form to display only the fields which developers are looking for.

Please note: This is only helpful for the development team as they will be knowing the Fieldnames in the table.

Below is the tweaked SysTableBrowser: As you can see Only commission Group and SalesId are visible in the Table Browser

along with the system fields.

To see all the fields again, change the query back to SELECT * from SalesTable

Please note: This is quick enhancement and can be customized/optimized more as per the developer needs. Please test this before using it.

Add a new method to SysTableBrowser form as shown below.

void enableFieldList(str _statement)

{

    #define.from(‘FROM ‘)

    Container fieldIdsCon;

    int       i, j;

    FieldName       fieldName;

    DictTable       dt;

    DictField       dictField;

    fieldId         fieldId;

    SysDictField    sysDictField;

    SqlDictionary   dictionary;

    str tmpStatement = strreplace(_statement, #from + dictTable.name(), ”);

    ;

    tmpStatement = strdel(tmpStatement, 1, 7); // remove select reserve word

    if (tmpStatement != ‘* ‘)

    {

        fieldIdsCon = str2con(tmpStatement, ‘,’);

    }

    if (conlen(fieldIdsCon) > 0)

    {

        dt = new DictTable(tablename2id(dictTable.name()));

        for (fieldId = dictTable.fieldNext(0);fieldId;fieldId = dictTable.fieldNext(fieldId))

        {

            dictField = dictTable.fieldObject(fieldId);

            if (!dictField.isSystem() && dictField.visible())

            {

                if (dictField.arraySize() > 1)

                {

                    for( j = 1; j <= dictField.arraySize(); j++)

                    {

                        sysDictField = new SysDictField(dictTable.id(), dictField.id(), j);

                        ds_ds.object(fieldname2id(tablename2id(dictTable.name()), dictField.name() + strfmt(‘[%1]‘,j))).visible(false);

                    }

                }

                else

                {

                    ds_ds.object(fieldname2id(tablename2id(dictTable.name()), dictField.name())).visible(false);

                }

            }

        }

        for (i = 1 ; i <= conlen(fieldIdsCon); i++)

        {

            fieldName  = strlrtrim(strfmt(‘%1′,conpeek(fieldIdsCon, i)));

            select firstonly dictionary where dictionary.TabId == TableName2id(dictTable.name()) &&

                dictionary.name == fieldName;

            if (dictionary || strendswith(fieldName, "]"))

            {

               ds_ds.object(fieldname2id(tablename2id(dictTable.name()), fieldName)).visible(true);

            }

        }

    }

    else

    {

        dt = new DictTable(tablename2id(dictTable.name()));

        for (fieldId = dictTable.fieldNext(0);fieldId;fieldId = dictTable.fieldNext(fieldId))

        {

            dictField = dictTable.fieldObject(fieldId);

            if (!dictField.isSystem() && dictField.visible())

            {

                if (dictField.arraySize() > 1)

                {

                    for( j = 1; j <= dictField.arraySize(); j++)

                    {

                        sysDictField = new SysDictField(dictTable.id(), dictField.id(), j);

                        ds_ds.object(fieldname2id(tablename2id(dictTable.name()), dictField.name() + strfmt(‘[%1]‘,j))).visible(true);

                    }

                }

                else

                {

                    ds_ds.object(fieldname2id(tablename2id(dictTable.name()), dictField.name())).visible(true);

                }

            }

        }

    }

}

Call the above method in the ExecuteSQL button >> clicked method as shown below.

 Happy Dax6ng,

Sreenath Reddy