ACCESSING THE DATABASE in Dynamics Ax 2012

To Retrieve data from the database using a select statement

Table Buffers:

A table buffer is declared like a variable – the table name is specified in the declaration. A table buffer stores complete records in a variable.

Select Statements:

Select statements are used to retrieve data from a database. The select statement returns records to a table buffer.

Example:

static void Q1_Select1(Args _args) { CustTable CustTable; ; select Address from CustTable; where CustTable.AccountNum == '1102'; print CustTable.Address; pause; } // To Loop Records while select AccountNum, Name, Address from CustTable { print CustTable.AccountNum+ ": " + CustTable.Name + ": " + CustTable.Address; } pause;
Sort:

You can sort data retrieved from the database in many ways. This includes:

  • Using existing indexes on the tables.
  • Using the order by clause in a select statement.
  • Using the group by clause in a select statement.

Example:

while select custTable index AccountIdx { print custTable.AccountNum, " ", custTable.currency; }

Inner join:

while select ledgerTable join ledgerTrans where ledgerTrans.accountNum == ledgerTable.accountNum { amountMST += ledgerTrans.amountMST; }


Exist:

while select AccountNum, Name from custTable order by AccountNum exists join * from ctr where (ctr.AccountNum == custTable.AccountNum)


notExist:

while select AccountNum, Name from custTable order by AccountNum notExists join * from ctr where (ctr.AccountNum == custTable.AccountNum)

outer:

while select AccountNum from custTable order by AccountNum outer join * from custBankAccount where custBankAccount.AccountNum == custTable.AccountNum { print custTable.AccountNum, " , ", custBankAccount.DlvMode; } pause;

Count:

CustTable xCT; int64 iCountRows; ; Select COUNT(RecID) from xCT; iCountRows = xCT.RecID;

Create:

static void Test_Insert(Args _args) { CustTable CustTable; ; CustTable.AccountNum = "supposedAccount1"; CustTable.Name = "SupposedName1"; CustTable.insert(); info("Inserted"); }


Update:

static void Test_Update(Args _args) { SalesTable SalesTable; ; ttsbegin; while select forupdate SalesTable where SalesTable.CustAccount == "1102" { SalesTable.SalesName = "aaaaa"; SalesTable.update(); info("Updated Successfully"); } ttscommit; } SalesTable SalesTable; ; update_recordset SalesTable setting salesName = "Update RecordSet", DeliveryStreet = "New Address" where SalesTable.CustAccount == "1102 “; info("Updated Successfully via RecordSet");

Delete:

static void Q16_Delete(Args _args) { CustTable CustTable; ; ttsbegin; select forupdate CustTable where CustTable.AccountNum == "supposedAccount1"; CustTable.delete(); info("Deleted"); ttscommit; } CustTable CustTable; ; while select forupdate CustTable where CustTable.AccountNum == "4018" delete_from CustTable where CustTable.AccountNum == "4018";


Transaction Integrity Checking:

It is important to ensure the integrity of all transactions within the system. When a transaction begins, to ensure data consistency, it must finish completely with predictable results.

The following keywords help in integrity checking:

• ttsbegin – Indicates the beginning of the transaction.

•  ttscommit – Indicates the successful end of a transaction. This ensures the transaction performed as intended upon completion.

•  ttsabort – Used as an exception to abort and roll back a transaction to the state before the ttsbegin.

Queries:

A query is an application object in the AOT

A query performs the same function as the select statements, but is a better option as it allows for more flexible user interaction when defining which records are to be retrieved.

Queries Using X++:

Queries can also be created and manipulated using X++. There are a number of classes available that you can use to achieve this.

Two important classes when executing a query are:

Query():

The Query() class provides the framework for the query

QueryRun():

QueryRun() class starts this framework dynamically.

 Queries Using X++:

	static void Test_ViaXPlusPlus(Args _args)
	{
	    Query query;
	    QueryBuildDataSource qbds;
	    QueryBuildRange qbr;
	    QueryRun queryrun;
	    CustTable CustTable;
	    ;
	    query =  new Query();
	    qbds = query.addDataSource(TableNum(CustTable));
	    qbr = qbds.addRange(FieldNum(CustTable,AccountNum));
	    qbr.value('1101');
	    qbds.addSortField(FieldNum(CustTable,AccountNum));
	    queryrun = new QueryRun(query);
	    while(queryrun.next())
	    {
	        CustTable = queryrun.get(TableNum(CustTable));
	        Print CustTable.AccountNum + ": " + CustTable.Name;
	    }
	    Pause;
	}

Accessing data from Different Companies:

static void Q10_CrossCompanies1(Args _args)
{
    Container ConComapnies = ['cee','ceu'];
    CustTable CustTable;
    ;
    while select crossCompany : ConComapnies CustTable
    {
        Print CustTable.Name;
    }
    pause;
}

Setup User workflow email notifications in AX2012

In order for users to be notified of new work-items that have been assigned to them you will need to ensure that they have their notification settings enabled. To do this you can follow the following procedure.

  • Navigate to the user options screen.
    • System Administration -> Common -> Users

    • Highlight the user and click Options.

  •  Enter the users E-mail address

  • If this is for yourself, you can locate user options from the File -> Tools -> Options menu.
  • If this is for other users you can access the form via System Administration -> Common -> Users -> Select the correct user -> Click options on the toolbar
  • Select the notifications tab
  • Check the “Send notifications in email” checkbox and Apply. By default this is disabled.

AX 2012: Bank Account Statement Import and advanced reconciliation

This is the New feature of AX 2012:

Use this form to create a relation between the bank transaction type and the transaction code in bank statements. This relation is also used during the reconciliation process

  • A much-needed functionality. AX 2012 now allows importing electronic bank statements and then performing advanced reconciliations.
  • To be able to use the back statement import and reconciliation functionality, you will need a series of one time configurations. Below are the high level steps to do all required configurations .
  • The first step is to configure the setup required for the electronic bank statement import which includes the parameters, bank statement format, transaction code mapping and bank reconciliation matching rules etc.

  • The next step is to set up the required AIF inbound ports(Services) and import formats.

  • After all the setups are done, finally we can import the electronic bank statements in required formats and perform advanced reconciliation.

Create Forms by Using the AOT

You can use forms to display data to users. You can create a form by using the Application Object Tree (AOT)

Create a Form by Using the AOT.

  1. Right-click the Forms node and then click New Form.
  2. Add a data source to the new form by doing the following:
    1. Expand the Data Dictionary node, right-click the Tables node, and then click Open New Window.
    2. Drag a table from the Tables node to the Data Sources node of the form. Repeat this step to add additional tables to the form.
    3. Modify table fields as needed by right-clicking a field, and then clicking Properties. Form data source field properties are described in the following table

Property

Description

 

 

AllowAdd Allows users to add the field to their personalized view of a form.

The default value is Restricted. The user can add this field if a copy of the field already exists in the form. For example, a user might want a copy of a field on a different tabbed page.

If AllowAdd is set to Yes, the user can add the field to the form, even if the field is not already on the form.

Note :The container control where the field is to be added must have the AllowUserSetup property set to Yes.

AllowEdit Allows modifications to the field.
Enabled Allows controls that display in the field to receive focus.

The default value is Yes. If set to No, controls bound to this field are disabled.

Note: If a field is used in several places on a form, set the Enabled property on the data source instead setting it on every control.

Mandatory Informs the user that this field must be completed. If set to Yes, the field has a red wavy line in the user interface.
Skip Determines whether controls bound to the field are skipped when the TAB key is used to navigate the form.

The default value is No.

Note: Even if the Skip property is set to Yes, users can still select the control by using the mouse pointer. You can also set the Skip property on a specific control.

Visible Determines whether controls bound to the field are visible.

The default value is Yes.

Note: You cannot use the Visible property on controls to enforce access restrictions—the user can change the visibility for the controls in the Form Setup dialog.

Note: It is best practice to use the properties on data source fields rather than the properties on the controls. This ensures that all occurrences of the field on the form are displayed consistently and helps make upgrades easier.

  1. Design the layout of the form by doing the following:
    1. Expand the Designs node of the form you created in step 1, right-click Design, and then click New Control. The following table lists controls that are used for Web forms

Control

Description

WebButton Displays a push button that allows a user to perform a specific action.
WebCheckBox Displays a check box control.
WebComboBox Displays a string value in a combo box control.
WebDate Displays a date value.
WebDateTime Displays a DateTime value in local date/time.
WebEdit Displays a string value.
WebGrid Displays a group of values in a grid format. Other Web controls such as WebEdit controls are added to the grid.
WebGroup Used for grouping related controls. The WebGroup control is defined first. The other Web Controls in the group appear as child nodes in the AOT.
WebGuid Displays a GUID value.
WebImage Displays an image from the AOT on a Web form.
WebInt64 Displays a 64-bit integer value.
WebInteger Displays an integer value.
Weblet Associates a weblet with a control on a Web form.
WebRadioButton Displays a radio button control.
WebReal Displays a real value.
WebStaticText Displays a static text value.
WebTab Displays a set of tabs. WebTabPage controls are added to the WebTab control.
WebTabPage Groups the items that are displayed for a tab.
WebTime Displays a time value.
WebUserDefined Enables you to present custom HTML by overriding the layout method, which is a form control method.
WebButton Displays a push button that allows a user to perform a specific action.
WebCheckBox Displays a check box control.
WebComboBox Displays a string value in a combo box control.
WebDate Displays a date value.

The data source for a control can be set when the control is created (if you drag a field or field group onto the form design), or it can be set by using the properties described in the following table. If you drag a field onto the form design, the data type of the field determines the kind of control that is created.

Control name

Description

Data source

Comments

ActiveX Any ActiveX control available on the computer. The control type is selected when you create the control, and is displayed in the ClassName property.
Animate A control used to display an .avi file. The .avi file is set by using the AnimateFile property. The .avi file must not have a sound track.

Note: If you set the AutoPlay property to Yes and the Loops property to 0, the system plays the designated .avi file indefinitely.

Button A rectangular button. The behavior for the button is set by overriding the clicked method for the control.

If the button displays an image, the image is set by using the NormalImage/DisabledImage or the NormalResource/DisabledResource property.

Buttons are usually grouped under a ButtonGroup control.

If you want to create a button for a commonly used command (for example, Open or Save) or to open a system menu, use a CommandButton control.

The ButtonDisplay property determines whether the button displays an image.

If the DefaultButton property is set to Yes, the button is selected when the user presses Enter on the form.

ButtonGroup A control used to group buttons that belong together under a heading. Not applicable. Not applicable.
CheckBox A square box that is selected or cleared to turn an option on or off. More than one check box can be selected. The data source is set by using the DataSource and DataField properties, or by using the DataMethod property. This type of control is automatically created if you drag a field onto the form design that has an enumerated data type with two values.
ComboBox A text box with an attached list box. You can select an item from the list, or type in a new value. The data source is set by using the DataSource and DataField properties, or by using the DataMethod property. This type of control is automatically created if you drag a field onto the form design that has an enumerated data type with more than two values.
CommandButton A button to activate a command. For example, to close the form, to save all, or to activate one of the system menus. Not applicable. The function of the control is set by using the Command property. You can choose from many options. For example, New, Open, Save, Undo, Select All, and Next Tab Page.
DateEdit A control used to display and edit dates. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property, or the DataMethod property. The following properties are inherited from the data type on which the control is based:

  • Alignment
  • DateFormat
  • DateSeparator
  • DateYear
  • DateMonth
  • DateDay
DateTimeEdit Automatically converts Coordinated Universal Times (UTC) into local date/time for display. Also converts local date/time into UTC for storage. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property, or the DataMethod property.
Grid A table that displays records from a database table. The data source is set by using the DataSource property. All data entry forms should open on an Overview tab that displays records in a grid.
Group A facility used to group several fields under a heading. The data source is set by using the DataSource property. This type of control is automatically created if you drag a field group onto the form design.
GuidEdit A control used to display and edit GUIDs. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property or the DataMethod property. Not applicable.
HTML A control used to display and edit HTML-based text. The content (if any) for this control is set by using the FormHTMLControl.setText method. For an example of this control, see the tutorial_Form_Controls form.
IntEdit and Int64Edit The controls used to display and edit integers. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property, or the DataMethod property. IntEdit controls are for 32-bit integers.

Int64Edit controls are for 64-bit integers.

Users can enter an expression in the control. The result is evaluated when they leave the control.

The following properties are inherited from the data type on which the control is based:

  • Alignment
  • DisplaceNegative
  • ShowZero
  • SignDisplayRotateSign
Listbox A box presents the user with a list of choices. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property, the EnumType property, or the DataMethod property. N/A
ListView A list box that displays a collection of items where each item consists of an icon and a label. The content and behavior of a ListView control must be defined by using code. For an example, see the tutorial_Form_ListControl form. The icons in a ListView are supplied by using an image list. For more information.

ListView controls can display content in the following views:

  • Large Icon
  • Small Icon
  • List
  • Details

These views are similar to those in Microsoft Windows Explorer.

MenuButton A button that opens a submenu. Not applicable. A MenuButton control can contain Button, CommandButton, and MenuItemButton controls, and separators.
MenuItemButton A button to activate a menu item. The MenuItemType and MenuItemName properties identify the menu item to be activated. Not applicable.
Progress A control used to show the percentage of completion of a lengthy operation. It consists of a bar that “fills” from left to right. The code to control the progress bar should be written on the clicked method for the Button control. Note   It is best practice to use the Progress Indicators framework instead of a Progress control on a form. For more information.
RadioButton A round button used to select one of a group of mutually exclusive options. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property, the EnumType property, or the DataMethod property. Not applicable.
RealEdit A control used to display and edit real numbers. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property or the DataMethod property. Users can enter an expression in the control. The result is evaluated when they leave the control.

The following properties are inherited from the data type on which the control is based:

  • Alignment
  • AutoInsSeparator
  • DecimalSeparator
  • DisplaceNegative
  • NoOfDecimals
  • RotateSign
  • ShowZero
  • SignDisplay
  • ThousandSeparator
StaticText A control used to display text. The text to display in the form is specified by using the Text property. To enable users to edit the text, use a StringEdit control.
StringEdit A control used to display and edit text strings. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType property or the DataMethod property. A StringEdit control can span multiple lines. This is defined by the MultiLine property.

The following properties are inherited from the data type on which the control is based:

  • Alignment
  • Uppercase
Tab A group control that contains TabPage controls. Not applicable. The Tab property controls which tab pages should be displayed when the user opens the form. Best practice is to leave this property set to Auto to display the first tabbed page.
Table A spreadsheet-like table with rows and columns. The content of a Table control must be defined by using code. For an example, see the tutorial_Form_Table form. The Table control is not associated with the database. This control looks similar to a Grid control, but it can display values for different types within a single column.
TabPage An additional page on the form. Not applicable. A TabPage control is typically used when there is too much information to display in a single dialog box.

You can create a TabPage control only within a Tab control.

The Caption property determines the text that is displayed on the tab.

TimeEdit A control used to display and edit a time. The data source is set by using the DataSource and DataField properties, or by using the ExtendedDataType or the DataMethod property. The following properties are inherited from the data type on which the control is based:

  • Alignment
  • TimeFormat
  • TimeHours
  • TimeMinute
  • TimeSeconds
  • TimeSeparator
Tree A special list box control that displays a set of objects as an indented outline based on their logical hierarchical relationship. The content and behavior of a Tree control must be defined by using code. For an example, see the tutorial_Form_TreeControl form. The icons in a Tree control are supplied by using an image list.
Window A window that displays a bitmap. The bitmap can be specified by using the ImageName property, the ImageResource property, or the DataSource and DataField properties, or the DataMethod property. For more information about Window controls,

Note: If you set the data source for a control by using the DataMethod property and the method that you want to use is on a table used as a data source in the form, you must also set the DataSource property. You do not need to set the DataSource property if the method is on the form or is on the Data Source node within the form.

  1. Right-click a control and then click Properties to modify the control. To display data in a control, set the DataSource and DataField properties.
  2. Repeat Steps a – b to add additional controls to the form.
  3. To change the form name, right-click the form you created in step 1, and then click Properties.

The name of a form should be identical to the table used as a data source for the form.

  1. Right-click the form, and then click Save to save your changes.

View a Form

  • Right-click the form, and then click Open.