Microsoft Dynamics AX / D365: 2016

AX 2012 X++ code to export the data to the excelsheet using SysExcelWorksheetHelper and SysExcelHelper classes

static void RB_SysExcelWorksheetHelper(Args _args)
{
PdsApprovedVendorList pdsApprovedVendorList;
SysExcelWorksheetHelper worksheetHelper;
SysExcelHelper sysExcelHelper;
SysExcelWorksheet worksheet;
int currentRow = 1;
str worksheetName;
int redColor = WinAPI::RGB2int(255, 0, 0);
SysExcelRange range;
COMVariant cellValue = new COMVariant(COMVariantInOut::Out);
str fileName;
ItemId previousItemId, newItemId;
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.ItemId(1)
#define.ItemName(2)
#define.SupplierId(3)
#define.SupplierName(4)

worksheetName = "Approved Supplier";

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(#ItemId, tablenum(PdsApprovedVendorList), fieldnum(PdsApprovedVendorList, ItemId));

worksheetHelper.addColumn(#ItemName, "Item Name", Types::String);

worksheetHelper.addColumnFromTableField(#SupplierId, tablenum(PdsApprovedVendorList), fieldnum(PdsApprovedVendorList, PdsApprovedVendor));

worksheetHelper.addColumn(#SupplierName, "Supplier Name", Types::String);

// Business logic includes grouping , you can write your logic as per the requirements
while select pdsApprovedVendorList
where pdsApprovedVendorList.ValidTo >= systemDateGet()
{
newItemId = pdsApprovedVendorList.ItemId;
if (newItemId != previousItemId)
{
currentRow ++;
worksheetHelper.setCellValue(#ItemId, currentRow, pdsApprovedVendorList.ItemId);
worksheetHelper.setCellValue(#ItemName, currentRow, InventTable::find(pdsApprovedVendorList.ItemId).itemName());
worksheetHelper.setCellValue(#SupplierId, currentRow, pdsApprovedVendorList.PdsApprovedVendor);
worksheetHelper.setCellValue(#SupplierName, currentRow, VendTable::find(pdsApprovedVendorList.PdsApprovedVendor).name());
}
else
{
currentRow ++;
worksheetHelper.setCellValue(#ItemId, currentRow, '');
worksheetHelper.setCellValue(#ItemName, currentRow, '');
worksheetHelper.setCellValue(#SupplierId, currentRow, pdsApprovedVendorList.PdsApprovedVendor);
worksheetHelper.setCellValue(#SupplierName, currentRow, VendTable::find(pdsApprovedVendorList.PdsApprovedVendor).name());
}
previousItemId = pdsApprovedVendorList.ItemId;
}

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();
}

AX7 Steps to create a new form

In this post will follow the same model and project to create new elements for building Simple List form.

In order to construct Simple List form in AX7, we will first create a table that will be populated from the form and use as data source of the form.

To create a new table, first add new item to the project.

After clicking new item, it will open the AX7 Artifacts. Select Data model from the left pane and table from the list of artifacts. Give appropriate name to your table.

Table is created now new fields can be added either by dropping EDT's over field node or right click and create new fields. Add fields that are required on the form. These steps are mostly same as in earlier versions of AX.

New index can also be created in same way. I have created 'Non duplicate' index in my table after adding table fields.

Next is to create the form again by adding new item in the project. This time we need to select User interface from left pane and select form artifact from the list.

Give name to the form and select add. This will add new form to the project. Now the important thing is to apply pattern to the design. It can be done by right clicking the design node of the form and navigate to Apply pattern -> Simple List (As we are building Simple list form so i have selected this pattern, choose patterns you like to add on forms design).

After applying the pattern, you can see it automatically gives you the pattern of design which needs to be completed in order to build form design. Make sure there is no missing control on the from design otherwise your project will not be build successfully. New controls can be added by right clicking on design node, hover to new and select appropriate control from the list.

For Simple List form, we need to add following controls on it:

1. Action pane and Action pane Tab

After adding action pane and tab, we also need to add command buttons of ADD and DELETE same as we do in earlier versions.

2. Custom Filter Group

Custom Filter group needs to be added on this form to filter the form data. One issue i faced while adding custom group on design is that we also need to apply sub Patterns on it otherwise it wont work. Patterns can be applied in same way as we did on design node.

3. Finally we need a grid control on the form to display, edit and insert data on the form.

So after adding all controls we can see that pattern shows no missing control.

As form also need some data source, so we will drag and drop the table created earlier on form data source.

Now select the fields from the data source and drop them to the grid.

This completes our table and form creation

In order to show above form in Fleet management area page, we need to create a display menu item of the form. Follow the same steps we did for form creation just need to select Display Menu Item artifact from list.

Finally add this menu item to Fleet management area page. This can be done by extending the Fleet management Menu. Right click the Fleet management Menu from fleet management model under User interface-> Menus and click on create extension. This will create extension of Fleet Management Menu.

Now add the menu item within setup region of this menu.

This completes our implementation. In last you need to perform two steps:

1. Build your project in order to compile the new package.

2. Synchronize project with database (this is important otherwise you will get sql errors on form).

Now Navigate to Fleet management -> Setup -> Vehicle Category

Here is the basic simple list form.

This is basic tutorial so i hope everyone get basic insight of AX7.

AX 7 Development Steps

This blog will show how you can start making a customization in AX 7 by showing you the steps needed to make a simple script

Before starting customizing AX it is recommended to create your own Model

The first step is to log into Visual Studio as an Adminstrator

Once within Visual Studio, go to the AX7 menu and select Model Management and ‘Create model’. A model is not exactly the same as a layer, but it might be a useful analogy to think of it as being similar to a layer in the sense, it is a way to isolate and manage your customizations

Create the model in the form that appears and fill out the fields as appropriate

Click ‘Next’ and select ‘Existing package’ and choose ‘Application Suite’

Press ‘Next’ – accept the default values suggested and press ‘Finish’

In the ‘Save’ screen that appears, select the project DynamicsAX7 and give it a name of your choice and press ‘OK’

Notice in the Solution Explorer to the right in the screen a new Project has been created in your newly created Model

On the Solution Explorer go to the project and rightclick and select ‘Properties’

In here note that the project is in the new Model just created and that the customizations will be saved in the ‘USR’ layer

Also, it’s a good idea to specify which company you wish to execute the code in, here ‘DEMF’

Press OK

Now to add ‘Runnable Class(Job) to the Project do the following. On the project rightclick and select ‘Add’ and then select ‘New Item’

In the screen shat appears select ‘Code’ and then ‘Runable Class(Job)’

And press ‘Add’

Note that now in the Solution explorer that the Runable Class has been added

Doubleclick on the ‘Runable class’ and enter the code editor window and give your job a meaningful name and write your code like you would do any X++ job as known in previous versions

Save the project(Ctrl + S) and to compile the job, select ‘Build’ and ‘Build Solution’ from the toolbar

Wait until the output window reports that the build has completed

Now in order to run the job, you need to set it as a ‘Startup Object’

Go to the Solution Explorer and on the Job, righclick and select ‘set as Startup Object’

Now to execute the job, select ‘Debug’ in the toolbar and then ‘Start without Debugging’

And note the output in the browser

And that’s it. Happy AX7 developing !

X++ code to convert the formatted string to Numbers in AX 2012 R3

static void RB_formattedStr2Num(Args _args)
{
str s = "(200+100) / 2 * 4";

info(strFmt("%1", formattedStr2num(s)));
}

AX 2012 How to hide the modules as per user roles across the company

Steps to hide the modules in AX 2012 R3:

Step 1. Create a new Class

class RBHideModules
{
}

Step 2 : Create a new Static method to replace the "Navigation Option" in all the companies.
if you want the same modules reflect in all the companies , then the below method is required.

public static void replaceNavOptionsInAllCompanies()
{
#define.elementName('Usersetup')
#define.designName('NavPaneOptionsButtons')

DataArea dataArea;
SysLastValue sysLastValue, sysLastValue2;

select sysLastValue
where
sysLastValue.company == curext() &&
sysLastValue.userId == curUserId() &&
sysLastValue.elementName == #elementName &&
sysLastValue.designName == #designName;

if (sysLastValue)
{
while select id from dataArea
where !dataArea.isVirtual && dataArea.Id != curext()
{
select forUpdate sysLastValue2
where sysLastValue2.company == dataArea.Id &&
sysLastValue2.userId == curUserId() &&
sysLastValue2.elementName == #elementName &&
sysLastValue2.designName == #designName;

ttsBegin;

sysLastValue2.company = dataArea.Id;
sysLastValue2.userId = curUserId();
sysLastValue2.elementName = sysLastValue.elementName;
sysLastValue2.recordType = sysLastValue.recordType;
sysLastValue2.designName = sysLastValue.designName;
sysLastValue2.isKernel = sysLastValue.isKernel;
sysLastValue2.value = sysLastValue.value;

sysLastValue2.write();

ttsCommit;
}
}
}

Step 3. Create a new static method in the class as shown below (Save and Close it) to hide the modules.

//Code for Hide Modules Functionality
public static void HideModulesForRole()
{
container cont_navButtons;
container cont_UserRoles;

TreeNode menunode;
TreeNode mainmenunode;
TreeNodeIterator menuitemiter;

str aotName;

int i = 1;
int j = 1;
int loc;

boolean isAdmin = false;

SecurityRole securityRole;
SecurityUserRole securityUserRole;

#AOT
#define.Zero('0')
#define.MainMenuLocation('\\Menus\\MainMenu')

//Fetch all roles for currently logged in User and insert in variable cont_UserRoles.
while select securityUserRole
join securityRole
where securityUserRole.User == curUserId()
&& securityRole.RecId == securityUserRole.SecurityRole
{
cont_UserRoles += securityRole.AotName;

if (securityRole.AotName == '-SysAdmin-')
{
isAdmin = true;
}
}

if (!isAdmin && conFind(cont_UserRoles, 'YourRolesName'))
{
mainmenunode = TreeNode::findNode(#MainMenuLocation);
menuitemiter = mainmenunode.AOTiterator();
menunode = menuitemiter.next();

while(menunode != null)
{
aotName = menunode.AOTname();

if(aotName == 'Home' || aotName == 'InventoryManagement')
{
// Prefix 1 to show module
cont_navButtons = conIns(cont_navButtons, j, '1' + aotName);
}

else
{
// Prefix 0 to hide module
cont_navButtons = conIns(cont_navButtons, j, '0' + aotName);
}
j++;

menunode = menuitemiter.next();
}
// Hide Modules with 0 as prefix
infolog.navPane().setCurrMenuButtons(cont_navButtons);

// this is required only incase if you want to hide the modules in all the companies
RBHideModules::replaceNavOptionsInAllCompanies();
}
}

Step 4 : Open the method "startupPost" from the Class -> Info , Call the newly created class static method as shown. (Save & Close)

void startupPost()
{
RBHideModules::HideModulesForRole();
}

Notes : The above code will works only for the startup company, In case if the user changes the company , all the modules will be reflected again. To Avoid that please continue with the step 4:

Step 4: Open the form "SysDataAreaSelect" , add the code to the method "SwitchCompany" in the last line before the loop ends.

void SwitchCompany(boolean NewWorkspace)

{
(..... means standard code already available)
.....
......
RBHideModules::HideModulesForRole();
}

Step 5: Restrict the user using the Navigation Pane option manually to add the modules.

Edit the "OkButton" clicked method of the Form "SysNavPaneOptionsDialog", write the below code above the super();

RBHideModules::HideModulesForRole();

This will restrict the user to add the modules manually.

Result : Whenever the user changes the company from the company dialog , it hides the modules as you expect.

Migrating users and security settings between different Dynamics AX 2012 instances

One of the common questions we deal with is around the migration of security settings between two Dynamics AX instances. To understand how you achieve it, let’s lay down the basics.

Security in Dynamics AX 2012 consists of roles, duties and privileges. These are stored as Metadata within the AOT. A user is then assigned to one or more security role(s) and based on that security role membership, they are able to get access to various parts of the system. This information is stored in the database in Dynamics AX 2012

In essence, when we talk of migrating security, we need to consider two different aspects.

Migrating the security role information
Migrating the user-role memberships

Depending upon what your configuration and setup is, you may want to do one or both of those above things. Let’s look at how you would these steps in the following sections.

Migrating the security role information

Changes to the behavior of roles, duties, and privileges also change the metadata stored in the AOT. These changes are reflected in the model that the person making the change is working in.

People in the following roles are most likely to modify security:

– Security administrators with the responsibility for making changes to the security objects by using the Microsoft Dynamics AX client.

Changes made by the security administrator are most likely to be stored in the USR model.

– Microsoft Dynamics AX developers.

Changes made by developers are most likely to be stored in the CUS or VAR models.

It is essential that the security administrator and developer collaborate to make security changes.

Scenario 1: Move security changes from production to staging

We recommend that you migrate security changes from the production to the staging environment before making further changes in the staging environment.

Follow the steps in the section Recreate the staging environment from the production system in the documenthttp://download.microsoft.com/download/7/9/6/7964C5AD-2A28-4ACC-92D5-2BCC72B70C69/Deploying%20customizations%20across%20Microsoft%20Dynamics%20AX%202012%20environments.pdf.

The advantage of this approach is that all the changes made from the production environment are brought over to staging and can be tested, compiled etc. in the staging environment before they are reapplied. Note that changes that were made in the production environment by an administrator cannot be uniquely identified in the staging environment.

Scenario 2: Move security changes from staging to production

To ensure that recent security changes made by the administrator will not be lost in the production system, we recommend that you back up the changes from the production environment, apply the changes from the staging environment to the production environment, and then recent security changes to the production environment

When you deploy on production, instead of the step where you import the staging model store, do the following:

1. Back up the security changes from the production environment by exporting the USR model from the USR layer of the production system (or the layer in which the security administrator made the changes).

2. Import the model store of the staging system that contains changes to security artifacts.

3. Reapply the original production security changes by importing the USR model file from step 1.

4. Recompile the production environment.

You can find additional information about managing customizations at http://download.microsoft.com/download/7/9/6/7964C5AD-2A28-4ACC-92D5-2BCC72B70C69/Deploying%20customizations%20across%20Microsoft%20Dynamics%20AX%202012%20environments.pdf

Migrating the user-role memberships

Below are the steps to move users and their role assignments between environments:

On the source environment:

Open System Administration\Common\Data export/import\Definition groups
Press New
Enter definition group: UserRoles
Click Clear
Click Ok
Click “Select Tables”
Add tables “UserInfo” and “SecurityUserRole” and close window
Click “Export to”
Enter file name
Select file type: Comma and click Ok. Confirm any other dialogs.
Open the exported dat file, optionally remove lines for users that do not need to be imported on target environment such as Admin, Guest, etc.
Move the exported .dat and corresponding .def file to target environment

On the target environment

Open System Administration\Common\Data export/import\Import
Select the .dat file from step 12
Switch to tab ‘Advanced’ and enable following options:
1. Include shared tables: Yes
2. Include system tables: Yes
3. Update existing record: No
Click ok
Confirm dialog for import into related tables

Click Ok in the dialog to select which tables you want to delete before import, do not select any tables

AX 2012 R3 Import Main Accounts using DIXF

This post illustrate how to import the Main Accounts using the data import framework in Dynamics AX 2012 R3

Go to General ledger --> Setup --> Charts of Accounts--> Chart of accounts.

1- Create New Chart of Accounts (chart of account, Description) then close the form.

2- Go to General ledger --> Setup --> Ledger.

3- Assign the new chart of account to the ledger(from step 2),select the fiscal calendar and the Accounting Currency then close the form.

4- Go to Data import export framework --> Setup --> Data import / export framework Parameters.

5- Click browse to select shared working directory then click Validate button then close the form.

6- Go to Data import export framework --> Setup --> Source Data format, Create new source name and make sure to chose File in the Type then Change the Column delimiter if needed.

7- Go to Data import export framework --> Common --> Processing Group, Create New Group then Click the Entities Button.

8- in the entities form select the main Account Entity, CSV in the Sources data format, then Click the generate source file button.

9- in the wizard make sure to select the following fields:
Chartofaccounts (Chart of account code: from step 2)
MainAccountId (Account Code)
Name (Account Description)
Type (Account Type )
then click Generate sample file.