How to delete a label file in AX 2012

If you end up with an obsolete label file in AX 2012 and want to delete it you cannot just right click and select delete from the popup menu.

To get rid of the label file you can do the following:

  1. Create a new model. In this example named DeleteMe
  2. Move your label file to the DeleteMe model
  3. Use AXUTILs delete function to delete DeleteMe
  4. Restart the AOS

You have successfully removed the label file from your installation.

Advertisements

Prefixing a strings with @’s – multi lines and escape characters

This post just shows a couple of ways to use the feature of putting a @ in front of a text string and what the result is.

The first example shows how to use it when referring to eg. a file with backslashes.

Example 2 shows how to split a text line into two or more lines for easier reading.

 

static void coupleOfStringTricks(Args _args)
{
    str text1;
 
    // Example 1
    // ---------

    // Using a backslash in the text messes up the result
    text1 = "c:\temp\someFile.txt";
    info(text1);

    // An option is to use double backslash
    text1 = "c:\\temp\\someFile.txt";
    info(text1);

    // Or use the @ as prefix to the string
    text1 = @"c:\temp\someFile.txt";
    info(text1);


    // Example 2
    // ---------

    // Allowing to extend a text string over multiple lines
    text1 = @"This text is toooooooooooooooooooo loooooooooooooooong
    to keep in one line for easy reading";
    info(text1);
}

Using HAVING in a query

With AX 2012 we now have the option of using HAVING in a query. This allows us to limit a result set based on aggregated fields.

The advantage is that we let the SQL server do some filtering and receive less records compared to the old-school version were we had to receive all records and then use an IF or the likes to filter away the records that did not match the criterias.

In this simple example we want to get all sales ids on orders having lines with a line amount total of 100000 and beyond.

 

static void SimpleQueryWithHavingExample(Args _args)
{
    Query query;
    QueryRun queryRun;
    QueryBuildDataSource qBDS_SalesTable;
    QueryBuildDataSource qBDS_SalesLine;
    SalesTable salesTable;
    SalesLine salesLine;
 
    // Init query
    query = new Query();
 
    // Add datasources and use standard relations
    qBDS_SalesTable = query.addDataSource(tableNum(SalesTable));
    qBDS_SalesLine = qBDS_SalesTable.addDataSource(tableNum(SalesLine));
    qBDS_SalesLine.relations(true);
 
    // Add a group by on SalesTable
    qBDS_SalesTable.addGroupByField(fieldNum(SalesTable, SalesId));

    // Add aggregation on LineAmount
    qBDS_SalesLine.addSelectionField(fieldNum(SalesLine, LineAmount), SelectionField::Sum);
 
    // Add the having filter
    query.addHavingFilter(qBDS_SalesLine, fieldStr(SalesLine, LineAmount), AggregateFunction::Sum).value(SysQuery::range(100000, ''));
 
    // Create and run the queryRun object
    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        salesTable = queryRun.get(tablenum(SalesTable));
        salesLine = queryRun.get(tableNum(salesLine));
        info(strFmt("%1 %2", salesTable.SalesId, salesLine.LineAmount));
    }
}

 

I am sure that this will come in handy. 🙂

 

The effect of unnecessary tables in a query

The other day I was asked to look at a performance issue with a form opening slow. After going through the normal motions I ended up with a method being called hundreds of time. It did not take that long time to execute but all in all it was the reason for the form slowing down.

What the method did was to call a view and summarise a quantity field. Not something extra ordinary but I gave the view a look anyhow. And that is what triggered me to do this small post on a simple but often effective question: Do I really need that table? 🙂

Let me show you what I found with a simple example. We want to have listed all purchase order ids, item ids, the line status and quantity sorted by purchase order id, line status and item id.

This is a simplified example of the real life example and the demo view looks like this:

SELECT TOP (100) PERCENT dbo.PURCHTABLE.PURCHID
	, dbo.PURCHLINE.ITEMID
	, dbo.PURCHLINE.PURCHSTATUS
	, dbo.PURCHLINE.QTYORDERED
FROM dbo.PURCHTABLE
INNER JOIN dbo.PURCHLINE
	ON dbo.PURCHTABLE.PURCHID = dbo.PURCHLINE.PURCHID
		AND dbo.PURCHTABLE.DATAAREAID = dbo.PURCHLINE.DATAAREAID
		AND dbo.PURCHTABLE.PARTITION = dbo.PURCHLINE.PARTITION
ORDER BY dbo.PURCHTABLE.PARTITION
	, dbo.PURCHTABLE.DATAAREAID
	, dbo.PURCHTABLE.PURCHID
	, dbo.PURCHLINE.PURCHSTATUS
	, dbo.PURCHLINE.ITEMID

It returns the data as expected, it executes in a few ms and everybody is happy. Well, until you execute it 500 times in a row and get tired of waiting. Looking at what data we actually need returned points us towards the PurchTable. The only thing we get from this table is the PurchId which is present at the PurchLine table as well and fully indexed there as well.

So if we change the view to look like this instead we get a significant effect:

SELECT TOP (100) PERCENT PURCHID
	, ITEMID
	, PURCHSTATUS
	, QTYORDERED
FROM dbo.PURCHLINE
ORDER BY PARTITION
	, DATAAREAID
	, PURCHID
	, PURCHSTATUS
	, ITEMID

The result set is the same but the performance has increased. Let us have a quick look at the statistics.

First what happens when executing the first view:

View1 - statistics

And then what happens with the updated view:

View2 - statistics

Well, several things indicates that we made a score. Packages received from the server has been reduced from 6 to 4 (33%) and bytes received reduced from 13921 to 5885 (58%). With this example only returning very few records (9) we will not see an effect in the execution times being dramatic, however we see an improvement.

All we did was to simplify and still got the exact same result at a much lower cost. This is not only relevant for views but any query made against the SQL server.