Axapta’s WinAPI class has a bunch of static methods to handle files. The code example below shows how to utilize some of these methods to find files.

The two methods used to fetch all files matching the search criteria are findFirstFile() and findNextFile(). Don’t forget to clean up after yourself with findClose().

 

The code also uses three different find methods:

• fileExists(_name) returns true, if _name is an existing file
• folderExists(_name) returns true, if _name is an existing file or folder
• pathExists(_name) returns true, if _name is an existing folder

The example uses the infolog for output. As with any infolog beware of performance and limitation to 10.000 lines.

 

static void FindFile(Args _args)

{

    #File

 

    FileName fullFileName(FileName _path, FileName _fileName)

    {

        FileName    pathName;

        FileName    fileName;

        FileName    fileExtension;

        ;

        [pathName,fileName,fileExtension] = fileNameSplit(_fileName);

 

        return _path + '\\' + fileName + fileExtension;

    }

 

    void findFiles(FileName _path,

                   FileName _fileName,

                   boolean _inclSubDir = true,

                   FileName _prefix = fullFileName(_path,_fileName))

    {

        FileName    fileName;

        int         hdl;

        ;

 

        setprefix(_prefix);

 

        if (WinAPI::folderExists(_path))

        {

            [hdl,fileName] = WinApi::findFirstFile(fullFileName(_path,_fileName));

 

            while (fileName)

            {

                if (WinAPI::fileExists(fullFileName(_path,fileName)))

                    info(fileName);

 

                fileName = WinApi::findNextFile(hdl);

            }

 

            WinApi::findClose(hdl);

 

            if (_inclSubDir)

            {

                [hdl, fileName] = WinAPI::findFirstFile(_path+'\\'+#AllFiles);

 

                while (fileName)

                {

                    if (strlwr(fileName) != strlwr(_fileName) &&

                        strlwr(fileName) != strlwr('.')       &&

                        strlwr(fileName) != strlwr('..')      &&

                        WinAPI::pathExists(fullFileName(_path,fileName)))

                        findFiles(fullFileName(_path,fileName), _fileName, _inclSubDir, fileName);

 

                    fileName = WinApi::findNextFile(hdl);

                }

 

                WinApi::findClose(hdl);

            }

        }

 

    }

 

    findFiles('c:\\Program Files','*.doc');

}

As the name implies QueryRun is the executor of a query linked to it. To construct a query you want QueryRun to execute, you need build classes:

• Query
•  QueryBuildDataSource
• QueryBuildRange
• QueryBuildFieldList
• QueryBuildLink
• QueryBuildDynaLink

 

Today’s example will use the first four to demonstrate a query that sums the credit limit field in CustTable grouped by Country and Currency. Additionally a count field indicates how many records are represented in the sum. Ranges are implemented for AccountNum and Country, but the user is allowed to add additional range criteria in the dialog. To demonstrate the status() property I have locked it, so the user can not change it.

 

QueryRun

QueryRun executes the query. If needed the familiar query dialog can be opened before the query is run. This is done using the prompt() method. SysQueryRun extends QueryRun and has a total of 7 prompt*() methods, with different default behavior. For example promptAllowAddRange() would allow the user to add new where-conditions.

 

Query

A query represents the select statement. This is where all the strings come together.

 

QueryBuildDataSource

Using QueryBuildDataSources you add all the tables you want joined (just one in this example). This is also where you define how the resultset is to be sorted. The orderMode() method lets you define

• OrderBy
• GroupBy

 

QueryBuildRange

Ranges represent where conditions. Multiple ranges are connected with AND conditions. Unfortunately there is no easy way to change that.

 

QueryBuildFieldList

Represents the selected fields of the query. By default all fields are selected. In regular queries you probably wouldn’t use them that often, but when grouping this is the way to define which fields are calculated. SelectionField is an enum with the following values

•  Avg
• Count
• Database
• Max
• Min
• Sum   

 

The result of Test_Query on my test system looks something like this:

CA CAD     1120,00 (3 records)

CA USD      500,00 (1 records)

DE EUR        0,00 (1 records)

DK CAD     5000,00 (1 records)

DK EUR      300,00 (6 records)

DK GBP      560,00 (2 records)

ES EUR        0,00 (1 records)

IE EUR        0,00 (1 records)

NL EUR       20,00 (2 records)

NO EUR     3000,00 (1 records)

 

 

static void Test_Query(Args _args)

{

    CustTable            custTable;

    Query                query      = new Query();

    QueryRun             qr         = new queryRun(query);

    QueryBuildDataSource qbds       = qr.query().addDataSource(tableNum(CustTable));

    QueryBuildRange      qbrAccN    = qbds.addRange(fieldNum(CustTable,AccountNum));

    QueryBuildRange      qbrCountry = qbds.addRange(fieldNum(CustTable,Country));

    QueryBuildFieldList  qbfl       = qbds.fields();

    ;

 

    qbrAccN.value('4000..4050');

    qbrAccN.status(RangeStatus::Locked);

    qbrCountry.value('CA..NO');

 

    qbfl.addField(fieldNum(CustTable,CreditMax),SelectionField::Sum);

    qbfl.addField(fieldnum(CustTable,RecId),SelectionField::Count);

 

    qbds.addSortField(fieldnum(CustTable,Country));

    qbds.addSortField(fieldNum(CustTable,Currency));

    qbds.orderMode(OrderMode::GroupBy);

 

    if (qr.prompt())

    {

 

 

        while (qr.next())

        {

            custTable = qr.get(tableNum(CustTable));

 

            print strfmt("%1 %2 %3 (%4 records)",custTable.Country,custTable.Currency,

                         num2str(custTable.CreditMax,10,2,0,0),custTable.RecId);

        }

 

    }

 

    pause;

}

I am not using lists a lot, because I find it too restrictive not to be able to move back in the list. Also, I find it annoying that end() moves beyond the last element, not to the last element. Nevertheless, there are some uses for lists and I want to give an example how it can be used.

 

The example is a puzzle: How do you place eight queens on a chessboard, so they can not beat each other?

The algorithm below is a brute force approach to the problem: Find a place for the next queen, place it. If it was the 8^th queen, save solution. If the queen could not be placed, undo last move, try next possible field and so forth. The problem can be solved nicely using iteration or recursion. I like recursion.

 

I am using two lists. The first one is for the current solution. All placed queens are placed in this list. It basically is a simple stack. All elements are added to the start of the list. To undo a move, the first element is deleted.

The second list is a list of lists, a list of all solutions that worked out.

To access the contents you need an iterator similar to sets and maps. You can see those in the last block, when solutions are output via the infolog.

  

static void eightQueens(Args _args)

{

    int             board[64];

    int             found;

    str             solution;

    list            listAllSolution = new list(types::Class);

    list            listOneSolution = new list(types::String);

    listIterator    li_OneSolution  = new listIterator(listOneSolution);

    listIterator    li_AllSolution  = new listIterator(listAllSolution);

 

    int board(int _row, int _col, int _upd = 0)

    {

        if (_row < 1 || _row > 8 || _col < 1 || _col > 8)

            return -1;

        else

            board[(_row-1)*8+_col] += _upd;

 

        return board[(_row-1)*8+_col];

    }

 

    void updateBoard(int _row, int _col, int _upd)

    {

        int i;

        ;

        for (i=1;i<=8;i++)

        {

            board(i,_col,_upd);

            board(_row,i,_upd);

            board(i,_col-_row+i,_upd);

            board(i,_col+_row-i,_upd);

        }

    }

 

    void placeQueen(int _row = 1, int _col = 1)

    {

        int i;

        ;

 

        while (_col <= 8)

        {

            while (_col <= 8 && board(_row, _col))

                _col++;

 

            if (_col <= 8)

            {

                updateBoard(_row,_col,1);

                listOneSolution.addStart(strfmt("%1%2 ",num2char(64+_row),int2str(_col)));

 

                if (_row == 8)

                {

                    listAllSolution.addEnd(List::merge(listOneSolution,new list(Types::String)));

                }

                else

                    placeQueen(_row+1,1);

 

                updateBoard(_row,_col,-1);

                li_OneSolution.begin();

                li_OneSolution.delete();

            }

 

            _col++;

        }

    }

 

    ;

 

    placeQueen();

 

    li_AllSolution.begin();

    while (li_AllSolution.more())

    {

        found++;

        solution = "";

        li_OneSolution = new listIterator(li_AllSolution.value());

        li_OneSolution.begin();

        while (li_OneSolution.more())

        {

            solution = li_OneSolution.value() + " " + solution;

            li_OneSolution.next();

        }

        info(strfmt("%1: %2",found,solution));

        li_AllSolution.next();

    }

 

}

A table with recId columns (other than its own recId) will not show these in the table browser. Take reqTransCov for example. It has two additional recId columns, linking it to reqTrans: ReceiptRecId and IssueRecId. You will not see those in the table browser. That is because adding them to a grid will always fail. Axapta will pretend it did add them, but in fact it did not. Try it out.

Most of the time it will make sense not to display them as they are internal information and not of interest to the user. However, it might sometimes be helpful to see them, if just for debugging purpose. You can use the display-method workaround to do so:

1. Add a new display method to the datasource (or table).
2. Add an intEdit field and link it to the display method.

 

This display method is a (datasource) method to display reqTransCov.receiptRecid:

 

display recid ReceiptRecid(common _record)

{

    return _record.(fieldNum(ReqTransCov, ReceiptRecId));

}   

 

The more general approach would of course be to implement this capability into \Classes\SysTableBrowser and Forms\SysTableBrowser. Maybe I’ll pick that up in a later post…

Steen Andreasen’s book is expected to be released in January. He has uploaded a free preview demo chapter online here. The chapter deals with what most developers dread – reports. Check it out, lots of code samples, illustrations and in depth explanations. Nice.