SalesTableListPage shows no orders

I had to resolve an issue where exactly one user did not see any orders in the sales table list page. The user had system administrator role. Default Admin Voodoo (clearing usage data, delete + re-import user) did not solve the problem.

SalesTableListPage shows no orders

However, opening the SalesTable form shows all orders.

SalesTable form shows all orders

In first step I used Dynamics AX Trace Parser to get the SQL statement for the sales table list page.

SQL Query in Dynamics AX Trace Parser

In detail the SQL Select statement was:

SELECT T1.SALESID,
    T1.SALESNAME,
    T1.RESERVATION,
— much more fields here
    T2.PERSON,
    T2.RECVERSION,
    T2.RECID,
    T3.NAME,
    T3.RECID,
    T3.RECVERSION,
    T3.INSTANCERELATIONTYPE,
    T3.RECVERSION,
    T3.RECID
FROM  SALESTABLE T1 LEFT
OUTER
JOIN HCMWORKER T2 ON ((T2.PARTITION=5637144576)
    AND (T1.WORKERSALESTAKER=T2.RECID)) LEFT
OUTER
JOIN DIRPARTYTABLE T3 ON (((T3.PARTITION=5637144576)
    AND (T2.PERSON=T3.RECID))
    AND (T3.INSTANCERELATIONTYPE IN (2975) ))
WHERE (((T1.PARTITION=5637144576)
    AND (T1.DATAAREAID=’iax‘))
    AND ((
    NOT ((T1.RETURNSTATUS=4))
    AND 
    NOT ((T1.RETURNSTATUS=1)))
    AND (T1.CUSTACCOUNT=“)))
    ORDER BY T1.SALESID DESC OPTION(FAST 5)

There was this one strange WHERE constraint CUSTACCOUNT=’’ . I compared this statement with the SQL statement for another user who saw all orders in the sales table list page. There was no such constraint regarding the CustAccount.

In a second step I had a detailed look on the SalesTableListPageInteraction class. The initializeQuery() method modifies the query. When the query is passed there was no range on the CustAccount. However, the isCustomer flag was set and the query was converted to a Self-Service Query.

public void initializeQuery(Query _query)
{
    QueryBuildDataSource    qbds;
    ProjInvoiceTable        projInvoiceTable;

    if (isCustomer)
    {
     EPQuery::makeMyCustomerSelfServiceQuery(_query,
                                             tableNum(SalesTable));
    }

    // more code here

    super(_query);
}

The isCustomer flag gets set in the SaleTableListPageInteraction.initializing() method and calls the EP::isCustomer() method. There, the code checks if the actual user is also a contact person for a customer in the system.

select firstonly crossCompany RecId from dirPersonUser
                where dirPersonUser.User == curUserId()
                exists join dirPartyRelationship
                    where dirPartyRelationship.ChildParty ==
                                   dirPersonUser.PersonParty
                exists join custTable
                    where custTable.Party ==
                          dirPartyRelationship.ParentParty;

In my case the problem was solved by removing the user as contact for the customer

SalesTableListPage show sales orders

Enterprise Portal Installation fails due not enough memory

A typical Dynamics AX 2012 (R2) installation requires lot of RAM to work properly. Especially SharePoint, the basis for Role Center and Enterprise Portal want’s lot of it. I came across an issue where EP installation failed. The DynamicsSetupLog.txt revealed the issue; an exception at the SecurityTokenService.

The service ‚/SecurityTokenServiceApplication/securitytoken.svc‘ cannot be activated due to an exception during compilation.The exception message is: Memory gates checking failed because the free memory (723009536 bytes) is less than 5% of total memory

There are some blogs regarding this issue: Restart NodeRunner, Set the App Domain, Configure minFreeMemoryPercentageToActivateService . The simplest solution however is to provide more memory Smiley

MCTS: 70-573 SharePoint 2010 Application Development

image

On Friday 13th Smiley I was certified for SharePoint 2010 Application Development. Since every Dynamics AX Silver+ Partner need at least on SharePoint guy, I’m the one for InsideAx. I’m already experienced in maintaining SharePoint 2012 (mainly Foundation) for those of our Customers running Rolecenter in AX 2009. Furthermore I’ve upgraded and developed Enterprise Portal applications in the last two years. However, EP development and classic SharePoint development does diverge. Everybody who ever tried to modify EP using SharePoint designer knows what I’m taking about.

I don’t want to say that all the cool SharePoint features cannot be used to extend Dynamics AX (e.g. I’ve built a web based appointment app using SharePoint and its lists and calendars to visualize the schedules). All those who are now forced to get in touch with SharePoint may experience that it can be used in may cases to provide cool solutions for their customers. But I’d like Microsoft to strengthen the web / enterprise portal development aspect of Dynamics AX by extending the development course materials and provide an Enterprise Portal Certification.

Enterprise Portal Custom Filter Error after a short time

Again I experienced a strange behavior within Dynamics AX 2009 Enterprise Portal. I’ve created a AxGridView using an AxDataSource connected to a DataSet in Dynamics AX. The DataSet holds a setFilter method to set values on some QueryBuildRanges. Moreover I’ve create a button in my AxUserControl Webpart that invokes the setFilter method with some values.

protected void SearchButton_Click(object sender, EventArgs e)
{
   string value1 = TextBox1.Text;
   string value2 = TextBox2.Text;
   AxDataSourceMyTable.GetDataSet().DataSetRun.AxaptaObjectAdapter.Call  
    (“setFilter”,value1,value2);
}

public void setFilter(str _value1, str value2)
{;
   qbrSomeField.value(_value1);
   qbrAnotherField.value(_value2);
   MyTable_DS.executeQuery();
}

This implementation worked fine the first time using the webpart. However, after a very short time I got an error telling me that no Business Connector Session was found.

Microsoft.Dynamics.Framework.BusinessConnector.Session.Exceptions.NoKernelSessionException

First I thought of some kind of timeout and played with IIS settings. But Google found this discussion where it is explained that Dynamics AX deletes the QueryBuildRange objects after a very short time, and therefore the call fails. The solution is to use SysQuery::findOrCreateRange .

public void setFilter(str _value1, str value2)
{
   QueryBuildDataSource qbds;
   ; 
   qbds = MyTable_DS.query().dataSourceTable(tableNum(MyTable))
   SysQuery::findOrCreateRange(qbds,fieldNum(MyTable,Field1)).value(_value1);
   SysQuery::findOrCreateRange(qbds,fieldNum(MyTable,Field2)).value(_value2);
   MyTable_DS.executeQuery();
}

Color Rows in Enterprise Portal AxGridView

image

  1. Create a new display method webBackgroundColor at InventTable

    public display Color webBackgroundColor()
    {;
        switch(this.ItemType)
        {
            case ItemType::BOM:     return "Red";
            case ItemType::Item:    return "Green";
            case ItemType::Service: return "Blue";
            default: return "White";
        }
    }

  2. Create a new Dataset and add InventTable as DataSource
  3. Create or open a new Dynamcis Ax web project in Visual Studio
  4. Create or open an AxWebUserControl and add an AxDataSource using InventTable data set from AX
  5. Add an AxGridView and link it with the AxDataSource
  6. Add webBackroungColor**, ItemId and ItemName to the grid
    image
  7. Select the AxGridView, change to events and modify the RowDataBound event

    protected void AxGridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        string axColor = e.Row.Cells[0].Text.ToLower();
       
        if(axColor.Equals("red"))
            e.Row.BackColor = System.Drawing.Color.Red;
        else if(axColor.Equals("green"))
            e.Row.BackColor = System.Drawing.Color.Green;
        else if(axColor.Equals("blue"))
            e.Row.BackColor = System.Drawing.Color.Blue;       
    }

  8. Load the user control in an enterprise portal site

Use an Extended Datatype (Color instead of str) for the display method, otherwise you may experience strange behaviour in your ASP.NET application.