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

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. 🙂

 

EXISTS JOIN/NOT EXISTS JOIN versus NOT EXISTS JOIN/EXISTS JOIN

Not the prettiest headline so far. I do admit that. Nevertheless it is actually quite relevant. Here is the question to be answered: If I have multiple exists joins in my select statement and one of them is a not exists how can I be certain that I will get the expected result set back from the SQL server.

Let us start off by lining up 3 tables:

Trans

GroupId SubGroupId
A 123
B 456
C 789
D 987

 

GroupTable

GroupId
A
D
G

 

SubGroupTable

SubGroupId
123
456

 

The structure should be fairly obvious. Trans has 2 fields that refers to the sub tables. What I would like is to write a select statement that gives me all records from Trans where the GroupId isn’t present in GroupTable and where the SubGroupId is found in SubGroupTable.

This could be done like this:

select trans
    exists join subGroupTable.SubGroupId
        where subGroupTable.SubGroupId == trans.SubGroupId
            not exists join grouptable
                where grouptable.GroupId == trans.GroupId

Another way could be by turning it a bit upside down like this:

select trans
    not exists join grouptable
        where grouptable.GroupId == trans.GroupId
            exists join subGroupTable.SubGroupId
                where subGroupTable.SubGroupId == trans.SubGroupId

What happens when we look at what the SQL server receives as the TSQL-statement is a bit different than expected:

select GroupId, SubGroupId
    from Trans as t
        where EXISTS (SELECT 'x'
            FROM GroupTable as g
                where g.GroupId = t.GroupId
                    and not (exists (select 'x'
                    from SubGroupTable as s
                        where s.SubGroupId = t.SubGroupId)))

and

select GroupId, SubGroupId
    from Trans as t
        where not (EXISTS (SELECT 'x'
            FROM SubGroupTable as s
                where s.SubGroupId = t.SubGroupId
                    and (exists (select 'x'
                    from GroupTable as g
                        where g.GroupId = t.GroupId))))

 

What to notice is that in the first statement the EXISTS and NOT EXISTS are considered as one and in the second statement we now have 2 EXISTS that is   encapsulated by a NOT giving it a bit of a twist compared to what we might have expected.

The result set for the first statement is

D 987

since

A is existing in SubGroupTable which was ruled out by our NOT EXISTS

B is in SubGroupTable and not in GroupTable which both goes against our EXISTS/NOT EXISTS

C is in SubGroupTable A was

D is present in GroupTable and not in SubGroupTable

 

and for the second the result set is

B 456

C 789

D 987

since

A is in both GroupTable and SubGroupTable and we specified by having two EXISTS’s and adding these to a NOT that this was a no go.

B is returned although being in SubGroupTable it was not in GroupTable making the double EXISTS false and thereby pleasing the NOT

C is not present in neither GroupTable nor SubGroupTable and therefore matching the NOT

D is – like B – returned since it only exists in GroupTable and not i SubGroupTable and the EXISTS’s then returning false

 

I realise that this can be a bit tricky to get the head around but nevertheless very important to keep in mind when doing those large selects with lots of tables and EXISTS/NOT EXISTS joins.

New feature in AX 2012 R3 – Inserting data in table directly from query

A new version/release of Dynamics AX is finally gone RTM and one of the new cool features is the ability to insert data from a query directly into a table. And why is that so clever you might think? Well, consider the performance boost of not having to loop through thousands of records including the roundtrips between SQL server, AOS server, client and back. That is why this is so interesting.

Here is a quick demo of how it works:

I have created a table called TESTQuery2Record. It contains 4 fields: CustGroupId, CustGroupName, PaymTermId and PaymTermDesc. It should be fairly obvious what to expect from these fields. 🙂

The purpose of the demo scenario is to get customer group data including the corresponding payment term information inserted in our test table. To do this I have written this job:

 

static void DEMO_Query_InsertRecordSet(Args _args)
{
    TESTQuery2Record testTable;
    Map fieldMapping;
    Query query;
    QueryBuildDataSource qbds_custGroup;
    QueryBuildDataSource qbds_paymTerm;
    QueryBuildFieldList fldList_custGroup;
    QueryBuildFieldList fldList_paymTerm;

    // Empty the target test table
    // ---------------------------
    delete_from testTable;

    // Build the query
    // ---------------
    query = new Query();
    qbds_custGroup = query.addDataSource(tableNum(CustGroup));
    qbds_paymTerm = qbds_custGroup.addDataSource(tableNum(PaymTerm));
    qbds_paymTerm.addLink(fieldNum(CustGroup, PaymTermId), fieldNum(PaymTerm, PaymTermId));

    // Field lists are required
    // ------------------------
    fldList_custGroup = qbds_custGroup.fields();
    fldList_custGroup.addField(fieldNum(CustGroup, CustGroup));
    fldList_custGroup.addField(fieldNum(CustGroup, Name));
    fldList_custGroup.dynamic(QueryFieldListDynamic::No);

    fldList_paymTerm = qbds_paymTerm.fields();
    fldList_paymTerm.addField(fieldNum(PaymTerm, PaymTermId));
    fldList_paymTerm.addField(fieldNum(PaymTerm, Description));
    fldList_paymTerm.dynamic(QueryFieldListDynamic::No);

    // Specify the mapping between target and source
    // ---------------------------------------------
    fieldMapping = new Map(Types::String, Types::Container);
    fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupId), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, CustGroup)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupName), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, Name)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermId), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, PaymTermId)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermDesc), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, Description)]);

    // Let AX handle getting data from the query to the target table
    // -------------------------------------------------------------
    query::insert_recordset(testTable, fieldMapping, query);

    // Done!
    // -----
}

So what is happening when we execute the above. Besides the delete_from we will see to queries executed on the SQL server.

1. Execution of the query and adding the data to a temporary table:

SELECT T1.CUSTGROUP AS f1,
       T1.NAME AS f2,
       T2.PAYMTERMID AS f3,
       T2.DESCRIPTION AS f4,
       N'usmf' AS DATAAREAID,
       1 AS RECVERSION,
       5637144576 AS PARTITION,
       IDENTITY(BIGINT, 1, 1) AS RECID
INTO [##ax_tmp_usmf8_113_103617]
FROM CUSTGROUP T1
CROSS JOIN PAYMTERM T2
WHERE (
        (T1.PARTITION = @P1)
    AND (T1.DATAAREAID = @P2)
      )
    AND (
    (
        (T2.PARTITION = @P3)
    AND (T2.DATAAREAID = @P4)
    )
    AND (T1.PAYMTERMID = T2.PAYMTERMID)
      )
ORDER BY T1.CUSTGROUP

2. Inserting the data from the temporary table in the target table

INSERT INTO TESTQUERY2RECORD (
    CUSTGROUPID,
    CUSTGROUPNAME,
    PAYMTERMID,
    PAYMTERMDESC,
    DATAAREAID,
    RECVERSION,
    PARTITION,
    RECID
 )
SELECT F1,
       F2,
       F3,
       F4,
       DATAAREAID,
       RECVERSION,
       PARTITION,
       RECID + 5637144596
FROM [##ax_tmp_usmf8_113_103617]

The effect of this is x number of records queried and inserted in 1 roundtrip to the SQL server and still based on a query.

AOS cannot start after extending string length on EDT

All though AX 2012 is constantly moving the boundaries for what we can do we ended up hitting the roof on field/record sizes the other day.

After heavily increasing the string length on an EDT and a crashed synchronisation the AOS was unable to start up again. The error message received in the Event log was this:

Object Server 01: Unexpected situation
More Information: Total field length cannot exceed 64K. Please re-factor your table <tablename>

The obvious solution is to decrease the length of the EDT to get below the 64K limit.

But – to cut this short –  we now have an AOS that cannot start due to an error we need the AOS running to fix. Well, we knew that we had a backup taken a couple of hours ago giving us a loss of changes that we could deal with if it were. However, it was worth giving it a shot trying to fix this.

The solution was quite simple. We found a similar versioned environment and did the following:

  1. Create a new model
  2. Make a small customisation to the EDT giving us the problems putting it in the new model
  3. Export the model
  4. Import the model in the model database of the AOS that will not start
  5. Start the AOS
  6. Skip the models changed dialog and do a full synchronisation from the AOT

Voila! We are back on track with no loss except for the time spent doing the above thanks to the model concept and AXUtil.

Make your own add-ins in the AOT context menu

When spending a lot of your working day in the AOT you end up with a lot of ideas to tools that could make your development tasks easier, help you do things faster or just automate repetitive tasks.

In this blog post I will show you how to quickly make the foundation for a new add-in. The example will create a record counter that simply returns a record count in the info log when activated from a table node in the AOT.

To do so we need 4 things:

  1. The class that does the actual work
  2. A menu item that triggers the class
  3. A reference to this menu item in the context menu
  4. Few lines of code in a standard class to make sure the menu item only shows when positioned on a table

Let us start up with the class. In this example I have a class that looks like this:

class DemoAOTTool
{
}
private void run(Args _args)
{
    TreeNode treeNode;
    // Check that we are executing from a context menu
    if (SysContextMenu::startedFrom(_args))
    {
       // Get the tree node of the context menu
       treeNode = _args.parmObject().first();
      // Check that we have a hit and that we are on a table element
       if (treeNode && treeNode.utilElement().recordType == UtilElementType::Table)
       {
          // Present the record count to the user
          info(strFmt("@SYS97212", new SysDictTable(tableName2id(treeNode.treeNodeName())).recordCount()));
       }
    }
}
public static void main(Args _args)
{
   new DemoAOTTool().run(_args);
}

That is a very simple example; but to show the concept it should do.

Next step is to create a menu item. There is no fancy stuff here. Just a plain menu item.

In the AOT find the SysContextMenu under the Menus node. This is the menu shown when you right click in the AOT and select Add-ins. Drag your new menu item to the menu to the position in the menu you prefer.

We are almost there. All we need now is to make sure that the menu item only is shown when you right click on a table. To do so find the class SysContextMenu and the method called verifyItem.

What this method does is to look at the menu item and menu item type given as parameters and return a 0 or 1 equals hide or show. The method is structured fairly simple with switches to locate the switch on menu item type matching your above created menu item and in the nested switch add the following case :

case menuitemDisplayStr(DemoAOTTool):
    // Check that we are not on an old node and only one node is selected
    if (this.selectionCount() != 1 || firstNode.AOTIsOld())
       {
          return 0;
       }
       // Check that we are on a table node
       if (!docNode && _firstType==UtilElementType::Table)
       {
          return 1;
       }
       return 0;

And now your done. Test it by right clicking on a table in the AOT, select Add-ins and your new menu item.