Wednesday, March 26, 2014

Error - The number of joins in the statement is 28. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack.




Table inheritance in AX 2012 is a nice feature, but it has to be used carefully due to the performance impact it might have. Putting the performance impact aside, this post is mainly focus on the error due to exceeded maximum table join, which is caused by Table inheritance and Replacement key.

The error message - "The number of joins in the statement is 28. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack." (the 28 is due to the form has 28 table join and the 26 is a server settings for AOS, so these two numbers are not fixed, it depends on your form's datasource and server settings)

Brief info
- Forms\CustTable has an additional datasource. Eg. CustTableExtended
- CustTableExtended is InnerJoin to CustTable

A brief history of sample issue
While it is working OK the day before, the next day this error suddenly occur on Forms\CustTable. A further investigation found that the table join has increased due to a new field has been added to a table (Eg. CustTableExtended) which is InnerJoin with CustTable.

Cause of error
- The new table field on CustTableExtended has a relation to a derived/child table
- This derived/child table has a relation to the parent table
- On the parent table, it has a Replacement key.

When the CustTable form is opened, this additional field is joined to the child table, which is then joined to the parent table, this has caused an additional two table join, which happens to hit the limit of maximum table join.

Resolution
A. Consider redesign the solution

B. Remove Replacement key (usually won't choose this option due to presentable on form level is not user friendly - Eg. Showing RecId. See screenshot at the bottom of this post)

C. Increase the maximum table join (System administration > Setup > Server configuration > 'Performance optimization' tab > 'Performance settings' group > 'Maximum number of tables in join' field)





Simpler example
1. Current server settings: Maximum number of tables in join = 30

2. Tables\DemoBaseGroup
Parent table, has replacement key, SupportInheritance = Yes




3. Tables\DemoGroupA - Q (17 tables)
Derived/child table, extends DemoBaseGroup, SupportInheritance = Yes




4. Tables\DemoMainTable
Standalone table, , SupportInheritance = No, contain 17 fields (GroupA - Q), each field has a relation to its related table (DemoGroupA - Q) on RecId




5. Forms\DemoMainTable
Only 1 DataSource (DemoMainTable)





When the form is opened, it hit the maximum tables join error.
Given there's only 1 table in the DataSource, this error would first appears to be weird, but once you know field(s) with relation to inherited table which has Replacement key will cause an auto table join to the related tables, then it will make sense.




Below are the query generated when the form is run.
Two table join is created for each field with relation to inherited table with Replacement key.
Eg. 17 fields in the table will cause 34 table joins.


SELECT *
FROM DEMOMAINTABLE T1
LEFT OUTER JOIN (DEMOBASEGROUP T2 INNER JOIN DEMOGROUPA T3 ON ((T3.DATAAREAID=?) AND (T3.RECID=T2.RECID))) ON ((T2.DATAAREAID=?) AND (T1.GROUPA=T3.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T4 INNER JOIN DEMOGROUPB T5 ON ((T5.DATAAREAID=?) AND (T5.RECID=T4.RECID))) ON ((T4.DATAAREAID=?) AND (T1.GROUPB=T5.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T6 INNER JOIN DEMOGROUPC T7 ON ((T7.DATAAREAID=?) AND (T7.RECID=T6.RECID))) ON ((T6.DATAAREAID=?) AND (T1.GROUPC=T7.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T8 INNER JOIN DEMOGROUPD T9 ON ((T9.DATAAREAID=?) AND (T9.RECID=T8.RECID))) ON ((T8.DATAAREAID=?) AND (T1.GROUPD=T9.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T10 INNER JOIN DEMOGROUPE T11 ON ((T11.DATAAREAID=?) AND (T11.RECID=T10.RECID))) ON ((T10.DATAAREAID=?) AND (T1.GROUPE=T11.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T12 INNER JOIN DEMOGROUPF T13 ON ((T13.DATAAREAID=?) AND (T13.RECID=T12.RECID))) ON ((T12.DATAAREAID=?) AND (T1.GROUPF=T13.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T14 INNER JOIN DEMOGROUPG T15 ON ((T15.DATAAREAID=?) AND (T15.RECID=T14.RECID))) ON ((T14.DATAAREAID=?) AND (T1.GROUPG=T15.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T16 INNER JOIN DEMOGROUPH T17 ON ((T17.DATAAREAID=?) AND (T17.RECID=T16.RECID))) ON ((T16.DATAAREAID=?) AND (T1.GROUPH=T17.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T18 INNER JOIN DEMOGROUPI T19 ON ((T19.DATAAREAID=?) AND (T19.RECID=T18.RECID))) ON ((T18.DATAAREAID=?) AND (T1.GROUPI=T19.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T20 INNER JOIN DEMOGROUPJ T21 ON ((T21.DATAAREAID=?) AND (T21.RECID=T20.RECID))) ON ((T20.DATAAREAID=?) AND (T1.GROUPJ=T21.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T22 INNER JOIN DEMOGROUPK T23 ON ((T23.DATAAREAID=?) AND (T23.RECID=T22.RECID))) ON ((T22.DATAAREAID=?) AND (T1.GROUPK=T23.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T24 INNER JOIN DEMOGROUPL T25 ON ((T25.DATAAREAID=?) AND (T25.RECID=T24.RECID))) ON ((T24.DATAAREAID=?) AND (T1.GROUPL=T25.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T26 INNER JOIN DEMOGROUPM T27 ON ((T27.DATAAREAID=?) AND (T27.RECID=T26.RECID))) ON ((T26.DATAAREAID=?) AND (T1.GROUPM=T27.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T28 INNER JOIN DEMOGROUPN T29 ON ((T29.DATAAREAID=?) AND (T29.RECID=T28.RECID))) ON ((T28.DATAAREAID=?) AND (T1.GROUPN=T29.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T30 INNER JOIN DEMOGROUPO T31 ON ((T31.DATAAREAID=?) AND (T31.RECID=T30.RECID))) ON ((T30.DATAAREAID=?) AND (T1.GROUPO=T31.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T32 INNER JOIN DEMOGROUPP T33 ON ((T33.DATAAREAID=?) AND (T33.RECID=T32.RECID))) ON ((T32.DATAAREAID=?) AND (T1.GROUPP=T33.RECID))
LEFT OUTER JOIN (DEMOBASEGROUP T34 INNER JOIN DEMOGROUPQ T35 ON ((T35.DATAAREAID=?) AND (T35.RECID=T34.RECID))) ON ((T34.DATAAREAID=?) AND (T1.GROUPQ=T35.RECID))
WHERE (T1.DATAAREAID=?) ORDER BY T1.RECID OPTION(FAST 5)

By removing the Replacement key, the error is gone and the query only has one table, but the form will show the RecId value itself only, which appears to be not user friendly/non-readable. So this usually won't be the solution. The best to do is either redesign the solution or increase the maximum table join.




One might think, why design a table with so many fields with relation to separate inherited tables? Bear in mind the demo above is just to show how the error occur, it wasn't a real life example, but on CustTable, that could easily be the real life example due to the massive table join in standard AX.

The 'DirPartyPostalAddressView' in Forms\CustTable itself already has many table, together with other datasource, that's more than 10, if the form is further developed/customized (Eg. added one or two custom table and those table each has two or three field relation to inherited tables with Replacement key) then it would be easy to reach the limit.

Saturday, March 15, 2014

Enterprise portal overview


Walkthrough: Adding a Page to Navigation [AX 2012]

When you create a page in Enterprise Portal, you will add it to the navigation to enable users to access it. This walkthrough demonstrates how to add the Tutorial_MessagePrompt page to the Quick Launch navigation for the Sales module site in Enterprise Portal. It illustrates the following tasks:

1. Creating a Web Menu Item
2. Modifying the Quick Launch Menu
3. Viewing the Page in Enterprise Portal
Prerequisites to complete this walkthrough: 1. Microsoft Dynamics AX 2. Enterprise Portal

#1: Creating a Web Menu Item
A web menu item points to a specific page in Enterprise Portal. You must create a web menu item for each page that you will be adding to the navigation.
To create a web menu item:
1. In the AOT, expand the Web node, and then expand the Web Menu Items node.
2. Right-click URLs, and then click New URL.
3. Right-click the new URL that you created in step 2, and then click Properties.
4. Set the Name property to Tutorial_MessagePrompt Prompt.
5. Set the Label property to Message Prompt Tutorial.
6. Specify the URL property, which has the following form:Module/Enterprise%20Portal/PageName.aspx. The Tutorial_MessagePrompt page is found in the main Enterprise Portal site, so it does not have a Module in its path. The value you must enter for the URL property to access the Tutorial_MessagePrompt page is: http://<<server>>/sites/DynamicsAx/Enterprise%20Portal/Tutorial_MessagePrompt.aspx



7. Right-click the new URL item, and then click Save.

#2: Modifying the Quick Launch Menu
To add menu items to the Quick Launch area for a module site, you must first determine which web menu resource is being used for the Quick Launch.

To modify the Quick Launch menu:

1. In the AOT, expand the Web node, expand the Web Modules node, and then expand the Home node.
2. Right-click the Sales node, and then click Properties.
3. Examine the QuickLaunch property. It is set to EPSalesQuickLaunch. This is the web menu resource that defines the menu items displayed in the Quick Launch area for the Sales module site. You will add the new web menu item that you created to this web menu.


4. In the AOT, expand the Web node, and then expand the Web Menus node.
5. Locate and expand the EPSalesQuickLaunch node.
Right-click the Common node. Click New, and then click Menu item. A new menu item will be added at the end of the list.
6. Right-click the node for the new menu item, and then click Properties.


7. Use the drop-down list for the MenuItemName property to select the Tutorial_MessagePrompt menu item you created in the previous procedure.
8. In the AOT, right-click the EPSalesQuickLaunch Web menu, and then click Save.
#3: Viewing the Page in Enterprise Portal
After you have added the menu item to the Quick Launch, you can view it in Enterprise Portal.

To view the page in Enterprise Portal

1. Using a web browser, open Enterprise Portal. The typical URL to access Enterprise Portal is: http://<server>/sites/DynamicsAx/ Substitute the name of the server on which Enterprise Portal is installed.
2. Click Sales on the top link bar to display the Sales module site.
3. In the Quick Launch, examine the last item in the Common group. The Message Prompt Tutorial item should be listed.

Important : Sometime you might not see the new item in the list, the caches for Enterprise Portal may need to be refreshed.
And for this, you just have to go to Home and press “Refresh AOD” in the quick launch menu and then you should be able to see your new menu.


4. Click the Message Prompt Tutorial item to open the Tutorial_MessagePrompt page.






Walkthrough: Adding a Field to a User Control [AX 2012]



In this example, the customer page in EP displays detailed information about a customer. The User Control displayed in a User Control web part is used to display the data on the page and the AxForm is the main component of the User Control used.

A common customization is adding/changing the fields that are displayed in an entity overview page.
This walkthrough demonstrates how to add a field to the first group that appears on the View customer entity overview page. The below figure illustrates the tasks be performed:



Prerequisites: Basic prerequisites to be checked and available before proceeding for the walkthrough are:

1. Microsoft Dynamics AX

2. Enterprise portal

3. Visual Studio 2010

4. Visual Studio Tools installed (Ax Installation)

5. EP Administrator rights

#1: Determining the User Control to Modify

Before you can modify the fields displayed on the View customer page, you must determine which User Control is being used for that page.

To determine the User Control to modify:

1. Using a web browser, open Enterprise Portal. << http://<servername>/sites/DynamicsAx/ >>

2. Click Sales on the top link bar, this displays the All Customers list page. Select one of the customers in the list, and then click View. The View customer page with customer information is displayed.

3. In the ribbon, display the Page tab.
Click the Edit Page command.





4. Locate the View customer web part in the middle column. In the drop-down menu for this web part, click Edit Web Part. The drop-down menu is the small arrow found on the upper-right corner of the web part.



5. In the list of properties for the web part, locate the Managed content itemproperty. It is set to CustomerOverview, which is the User Control that is being displayed in the web part. This is the User Control that will be modified.



6. Click Cancel to close the list of web part properties.

7. Click Stop Editing to return to the View customer page. Close the page.

#2: Creating the EP Web Application Project

Visual Studio is used to modify User Controls for Enterprise Portal.

To create the EP Web Application project:


1. Start Visual Studio. To make sure you start Visual Studio with administrative privileges, right-click the shortcut for Visual Studio and then click Run as administrator.

2. In the File menu, click New, and then click Project.

3. In the New Project window, select .NET Framework 3.5 as the framework version to use.
Important: For this release of Microsoft Dynamics AX, the EP Web Application project must target the .NET Framework 3.5 to work correctly.





4. In the Installed Templates list, select Microsoft Dynamics AX. If you do not see this project template, make sure that you have Visual Studio Tools for Microsoft Dynamics AX installed.

5. Choose the EP Web Application template.


6. Specify a name for the project, and the location of the folder where you want to store the files for the project.

7. Click OK to create the project.

#3:Adding the User Control to the EP Web Application Project
You must add the User Control to the EP Web Application project so that you can modify the User Control's properties.

To add the User Control to the EP Web Application project


1. In the View menu in Visual Studio, click Application Explorer.


2. In the Application Explorer, expand the Web > Web Files > Web Controls node.




3. In the Web Controls list, locate the CustomerOverview control.

4. Right-click the CustomerOverview control, and then click Add to project. The control and several related controls are added to the project.

5. Close the Application Explorer.

#4: Modifying the User Control
Use Visual Studio to make changes to the User Control.

To modify the User Control


1. Locate the CustomerOverview.ascx component in Solution Explorer.

2. Right-click the CustomerOverview component in Solution Explorer, and then click View Designer.

3. After a few moments, you will see several components in the control layout. The AxDataSource component is used to access data for the customer overview page.
The AxMultiSection component contains all of the expandable sections that you see in the View customer page.
The sections contain AxGroup components, which are used to display fields.

4. Click the Customer AxGroup component directly in the layout to select it. This component contains the fields that are displayed at the top of the General section of the View customer page.



5. To modify the fields that are displayed in the group, use one of the following methods:

a. Display the context menu at the upper-right corner of the AxGroup control in the layout. Click Edit Fields to display the Bound Field Designer.



-or-

b. Locate the Fields property in the Properties list. Click the ellipsis button to display the Bound Field Designer.

6. The Selected Field list contains the fields that appear in the group. Only the AccountNum field is included. In the Available Fields list, select the OrderEntryDeadlineGroupId field. Click Add Field to add this field to the list of fields that will be displayed in the group.

7. Make sure that the OrderEntryDeadlineGroupId field is selected in the Selected Field list.

8. In the BoundField Properties list, locate the HeaderText property. Set the value of this property to Order entry deadline:.

9. Click OK to save the changes to the list of fields for the AxGroup.



10. In the File menu, click Save CustomerOverview.ascx to save the changes you made to the User Control. The changes are exported automatically to the AOT. The updated User Control is also deployed to the Enterprise Portal server.



#5: Viewing the Customization in Enterprise Portal
After the field has been added to the User Control, you can view it in Enterprise Portal.

To view the customization in Enterprise Portal

1. Using a web browser, open Enterprise Portal.


2. Click Sales on the top link bar. Display the All Customers list page. Select one of the customers in the list, and then click View. The View customer page with the customer information is displayed. You will see the customer details, including the Order entry deadline field that you added.




Document management in AX 2012

In Dynamics Ax 2012, you can attach documents to specific records. This can be done by clicking the "Attachments" button on the Ribbon for many forms. Below is a screen capture for Workers form:






The picture isn't clear, this is just to give an idea where you should possibly look for Attaments button.


But before you go ahead with Document attachments, you need to setup few things listed below:
Activate Document management
Set up Archive Directory - place to store the documents
Create required Document types
Use Document management to attach files to records in any Dynamics Ax Form.


Activate Document Management:

Complete the following procedure to activate document management.

Click File > Tools > Options.


Click General and then expand the Miscellaneous FastTab.

Select the Document handling active check box.

Select Show attachment status to highlight the Document handling control on the status bar
and Select Action Pane when you select a record that has documents references.




Set up document Archive Directory:

Complete the following procedure to set default document archive directory.
Click Organization administration > Setup > Document management > Document management parameters.
In the General area of the form, in the Archive directory field, enter the path to the archive directory.
Click Number sequences, and select the ID reference.
In the Number sequence code field, select the number sequence code to use for naming your documents.




Note: You can select an alternative archive directory for each document type in theDocument types form. If you change the archive for your documents, existing document references will not work unless you have already copied all existing documents to the new archive.
Create required Document types:

The document can be attached if and only if the file is of any type that is set up in the Document types form.
Follow the below steps to create a new document type:
Click Organization administration > Setup > Document management > Document types.
Press CTRL+N to create a new document type.
or you can click the "New" button.
In the Type field, enter a code for the document type.
This Type field is shown while adding documents to records, so recommended to have a descriptive name here.
In the Name field, type a descriptive name for the document type.
More description if needed.
In the Class field, select the type of document to create.
To specify which class do you want the new document type to fall in.
In the Group field, select a group for the document type.
Choose among the pre-defined groups avaliable.
Close the form to save your changes.



Using Document management:

You can actually perform any of the below actions:

Create a new document

Create a Copy of an existing document

Copy to clipboard to copy the document elsewhere in the System

Below are examples of how to do it, and are performed on Prospect details form (Sales and marketing module)
Create a document
Click Sales and marketing > Common > Prospects > All prospects.
Select the prospect record that you want to create an attachment for or attach a document to.
Click Attachments to open the form.
Press CTRL+N to create a new document.
Select the document type in the Documents form and then click OK.

Note: The document icon in the Prospects form changes from an empty sheet of paper to a book. This indicates that there are documents attached to the prospect record.

You can create documents for other users by selecting their user names from the list in the Author field. You can also transfer a document to another author in the Author field.

Copy a document
Click Sales and marketing > Common > Prospects > All prospects.
In the Prospects form, select the prospect record from which you want to copy a document.
Click Attachments to open the Documents form.

Verify that the document attached is a type that can be opened, such as a Microsoft Word file type or a Microsoft Excel worksheet.
Click Copy.
In the Description field, update the description of the newly copied document.

Copy a document to the Clipboard
Click Sales and marketing > Common > Prospects > All prospects.
In the Prospects form, select the prospect record that has the document that you want to copy to the clip board.
Click Attachments to open the Documents form.`
Click Copy to clipboard.


The active document is now copied to the Clipboard and can be pasted into any file that accepts text (such as email or a document).
Press CTRL+V to paste the copied document into the destination.

Friday, March 14, 2014

Create detail page in Enterprise portal

Details pages

A details page in Enterprise Portal displays detailed information about a specific selected record.
The following list describes the high-level steps that you can follow to create a details page:

1. Start Visual Studio, and use the EP Web Application Project template (found under the Mi-crosoft Dynamics AX category) to create a new project.
2. Add a new item to the project by using the EP User Control with Form template (found under the Microsoft Dynamics AX category). This will also automatically add the control to the AOT.
3. Switch to design view, select the AxDataSource control, and set the DataSet name.
4. Select the AxForm control, and ensure that DataSourceID is set to the AxDataSource.
5. Set the DataMember and DataKeyNames properties on the form as appropriate.
6. If required, change the default mode of the form to Edit or Insert (it is ReadOnly by default).
7. To auto generate the Save and Close buttons:
a. In ReadOnly mode: Set AutoGenerateCancelButton to true.
b. In Edit mode: Set AutoGenerateEditButton to true.
c. In Insert mode: Set AutoGenerateInsertButton to true.
d. Select an AxGroup control, and ensure that the FormID property is set.
8. Click the Edit Fields link, and add the required fields to the AxGroup control.
9. Compile the EP Web Application by using the Build menu. Ensure that there are no errors. This will also automatically deploy the control to the SharePoint directory.
10. Start the Development Workspace, and navigate to \Web\Web Content\Managed.

11. Right-click the Managed item that maps to the web user control that you have created, and click Deploy to EP.

12. When prompted, select the module to deploy the page to.This will automatically create a SharePoint Web Part page for Enterprise Portal and put your web user control on the page by using the User Control Web Part. It will also create a URL web menu item and import the corresponding Page Definition into the AOT.

13. Select the URL web menu item that you created for the page, and set the WindowMode prop-erty to Modal. This will cause the details page to open in a modal window.
14. Create a new Display Menu Item, and set the WebMenuItemName property to the URL Web Menu Item that is linked to the details page.
15. Use this Display Menu Item to link to the details page from the list page grid, as described in the “Model-drive list pages” section.


Visual studio customization for enterprise portal

get particular table field value in visual studio
<dynamics:AxGroup ID="AxFieldGroupDefaultsProject" runat="server"
                    Caption="<%$ AxLabel:@SYS4534 %>">
                <Fields>
                    <dynamics:AxBoundField DataSet="TrvExpTableNew"DataSetView="TrvExpTable"
                        DataField="ProjId" SortExpression="ProjId" AutoPostBack="true" OnDataChanged="ProjId_OnDataChanged"/>
                </Fields>
            </dynamics:AxGroup>
While adding the bound fields to the AxForm, we won’t get this attributeAutoPostBack="true" to the AxBoundField.
To raise the OnDataChanged event explicitly need to add the above attribute toAxBoundField.
Then I had added the following code to the event …
protected void ProjId_OnDataChanged(object sender,AxBoundFieldDataChangedEventArgs e)
    {
        string expProjId = ((System.Web.UI.WebControls.TextBox)(e.BoundControl)).Text.ToString();

                if (expProjId != "")
                {
                    ButtonOK.Enabled = true;
                }
                else
                {
                    ButtonOK.Enabled = false;
                }          
    }