Wednesday, May 26, 2010

Generic SysTableLookup Method

It is often needed to write a custom lookup method and the SysTableLookup class can be useful to create lookups from code. However the following method uses the SysTableLookup in the background but can be called easier.

When using the SysTableLookup class, for most of the simple lookups (1 datasource table) it is alway the same. You need the following :

TableId, LookupControl, LookupFields, ReturnFields, SortFields and sometimes the use of a tmpTable.

Now following method is a generic method to user the SysTableLookup class :
public static void doLookup(TableId             _tableId,
                            Container           _lookupFields,
                            Container           _sortFields,
                            FormStringControl   _control,
                            FieldId             _returnItemFieldNum,
                            Map                 _queryRanges    = null,
                            Boolean             _useTmpTable = false,
                            Common              _tmpBuffer = null
    SysTableLookup          sysTableLookup  = SysTableLookup::newParameters(_tableId, _control);
    Query                   query           = new Query();
    QueryBuildDataSource    qbds;
    int                     i;
    fieldId                 lookupFieldId;
    for(i=1;i <= conlen(_lookupFields);i++)
        lookupFieldId = conPeek(_lookupFields, i);
        if(lookupFieldId == _returnItemFieldNum)
            sysTableLookup.addLookupfield(lookupFieldId, true);
    qbds = query.addDataSource(_tableId);
    for(i=1;i <= conlen(_sortFields);i++)
        qbds.addSortField(conPeek(_sortFields, i));
        rangeEnumerator = _queryRanges.getEnumerator();
        while (rangeEnumerator.moveNext())

Now when you want to create a lookup you can do it easier by doing the following :
public void lookup()
    Container   fieldNums       = [FieldNum(CustTable, AccountNum), FieldNum(CustTable, Name)];
    Container   sortFields      = [FieldNum(CustTable, AccountNum)];
    FieldId     returnFieldId   =  FieldNum(CustTable, AccountNum);
    Map         queryRanges     = new Map(Types::Integer, Types::String);
    queryRanges.insert(FieldNum(CustTable, AccountNum), '4000');
    LIBSysTableLookup::doLookup(TableNum(CustTable), fieldNums, sortFields, this, returnFieldId, queryRanges);

So the only thing you need to do is specify the fields, returnfields and sortfields…

Ans let’s look at the following example : We need a lookup with a temporary table. Then we can do it like this :
Container   fieldNums       = [FieldNum(TmpIdRef, Name), FieldNum(TmpIdRef, HelpTxt)];
    Container   sortFields      = [FieldNum(TmpIdRef, Name)];
    FieldId     returnFieldId   = ConPeek(fieldNums, 1);
    TmpIdRef    tmpTable;
    tmpTable = LIBDifferenceAction::BuildActionClassList();
    XYZSysTableLookup::doLookup(TableNum(TmpIdRef), fieldNums, sortFields, this, returnFieldId, true, tmpTable);

refresh, reread, research, executeQuery - which one to use?

X++ developers seem to be having a lot of trouble with these 4 datasource methods, no matter how senior they are in AX.

1. Common mistakes
Often, developers call 2 of the mentioned methods in the following order:







formDataSource.refresh() / formDataSource.reread()

All of these are wrong, or at least partially redundant.
Hopefully, after reading the full post, there will be no questions as to why they are wrong. Leave a comment to this post if one of them is still unclear, and I will try to explain in more detail.

2. Refresh
This method basically refreshes the data displayed in the form controls with whatever is stored in the form cache for that particular datasource record. Calling refresh() method will NOT reread the record from the database. So if changes happened to the record in another process, these will not be shown after executing refresh().

Does a redraw of the grid rows, depending on the optional argment for specifying the number of the record to refresh (and this means the actual row number in the grid, which is less useful for AX devs). Special argument values include -1, which means that all records will be redrawn, and -2, which redraws all marked records and records with displayOptions. Default argument value is -2.
This method should be used sparingly, in cases where multiple rows from the grid are updated, resulting in changes in their displayOptions, as an example. So you should avoid using it as a replacement for refresh(), since they actually have completely different implementations in the kernel.
Also, note, that refreshEx() only redraws the grid, so the controls not in the grid might still contain outdated values. Refresh() updates everything, since this is its intention.

3. Reread
Calling reread() will query the database and re-read the current record contents into the datasource form cache. This will not display the changes on the form until a redraw of the grid contents happens (for example, when you navigate away from the row or re-open the form).
You should not use it to refresh the form data if you have through code added or removed records. For this, you would use a different method described below.

How are these 2 methods commonly used?
Usually, when you change some values in the current record through some code (for example, when the user clicks on a button), and update the database by calling update method on the table buffer, you would want to show the user the changes that happened.
In this case, you would call reread() method to update the datasource form cache with the values from the database (this will not update the screen), and then call refresh() to actually redraw the grid and show the changes to the user.

Clicking buttons with SaveRecord == Yes
Each button has a property SaveRecord, which is by default set to Yes. Whenever you click a button, the changes you have done in the current record are saved to the database. So calling reread will not restore the original record values, as some expect. If that is the user expectation, you as a developer should set the property to No.

4. Research
Calling research() will rerun the existing form query against the database, therefore updating the list with new/removed records as well as updating all existing rows. This will honor any existing filters and sorting on the form, that were set by the user.

The research method starting with AX 2009 accepts an optional boolean argument _retainPosition. If you call research(true), the cursor position in the grid will be preserved after the data has been refreshed. This is an extremely useful addition, which solves most of the problems with cursor positioning (findRecord method is the alternative, but this method is very slow).

5. ExecuteQuery
Calling executeQuery() will also rerun the query and update/add/delete the rows in the grid. The difference in behavior from research is described below.
ExecuteQuery should be used if you have modified the query in your code and need to refresh the form to display the data based on the updated query.

formDataSource.queryRun().query() vs formDataSource.query()
An important thing to mention here is that the form has 2 instances of the query object - one is the original datasource query (stored in formDataSource.query()), and the other is the currently used query with any user filters applied (stored in formDataSource.queryRun().query()).
When the research method is called, a new instance of the queryRun is created, using the formDataSource.queryRun().query() as the basis. Therefore, if the user has set up some filters on the displayed data, those will be preserved.
This is useful, for example, when multiple users work with a certain form, each user has his own filters set up for displaying only relevant data, and rows get inserted into the underlying table externally (for example, through AIF).
Calling executeQuery, on the other hand, will use the original query as the basis, therefore removing any user filters.
This is a distinction that everyone should understand when using research/executeQuery methods in order to prevent possible collisions with the user filters when updating the query.

Friday, May 21, 2010

Getting started with tracing

Install the trace parser

Set up the server to capture tracing

1. Go to your server configuration in administrative tools.

2. Manage > Create configuration, give it a name, ok

3. Tracing tab:

Check X++ method calls

Check Function calls, Check the 'Sql Statement', 'Bind Variables'

Set number of nested calls to 99

Check Allow client tracing on AOS instance

4. Hit Apply

5. Hit OK

6. Go to your Client configuration in administrative tools.

7. Manage > Create configuration, give it a name, ok

8. Tracing tab:

Check X++ method calls

Check Function calls

Set number of nested calls to 99

9. Hit Apply

10. Hit OK.

11. Restart your AOS.

12. Re-launch your Client configuration screen.

Capture a trace

1. Launch your client as 'Administrator' by righ clicking and perform the action you want to trace once without tracing on (to warm up the caches)

2. In the client config click on "Start tracing"

3. Walk the action you want to trace again.

4. In the Client config click on "Stop tracing".

5. Go to the directory specified at the top of the tracing tab in the server config and sort by modified date. The most recent file should be a .trc file.

Analyze the trace

1. Start the trace parser. If it’s the first time you run it, you’ll be asked a bunch of questions. Be agreeable.

2. File > Import, pick that .trc files you just captured.

3. Start analyzing

I normally first go to the X++/RPC tab and filter by type, totals. Sort by Exclusive RPC calls first, see what’s causing issues. Then sort by inclusive RPC calls and see what’s causing issues. Then Exclusive duration, inclusive duration, etc. General rule of thumb is if you see “your code” in the top couple pages of data for any of those things, you should work to make it better. You can jump to the call stacks causing the calls and stuff. Just look for random ways to make your stuff faster, pull from caches instead of doing round trips, cut down on how many times you’re called, etc. No real science to it, just look for crappy stuff and fix it.

If you want to trace the client it’s the exact same thing, only use the client config instead.

If it doesn’t work

· Make sure you have at least 20 gig of free space on the drive where your log files are located. ETW doesn’t like to run with less than that.

· Make sure you restarted the client or AOS after setting up the tracing options.

· Make sure you wait about 15 seconds after you click “start trace” before you start doing stuff. It takes a couple seconds (usually about 5) before the tracing turns on.