Macros

#define.Text('This is a test of macros')
#define.Number(200)

static void Datatypes_macro_library(Args _args)
{
    // Referencing macro library has to be done in the class declaration
    // or in the declaration like in this example
    #MacroTest

    ;
    info(strfmt("Text: %1. Number: %2", #Text, #Number));
}

Local Macro

static void Local_Macro(Args _args)
{
    // Define the local macro    #localmacro.WelcomeMessage    {        info("Welcome to Carz Inc.");
        info("We have the best offers for rental cars");
    }

    #endmacro;  

    // Use the local macro  
    #WelcomeMessage
}

Pack and Unpack

ClassDeclaration

class Tutorial_RunbaseBatch extends RunBaseBatch
{
    // Packed variables
    TransDate       transDate;
    CustAccount     custAccount;

    // Dialog fields
    DialogField     dlgCustAccount;
    DialogField     dlgTransDate;

    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        transDate,
        custAccount
    #endmacro
}

Dialog

public Object dialog()
{
    DialogRunbase       dialog = super();
    #resAppl
;
    dialog.addImage(#ImageEmployee);
    dialog.addInfoImage();
    dlgTransDate = dialog.addFieldValue(typeid(TransDate),transDate);

    dialog.addTabPage("@SYS76580");
    dlgCustAccount = dialog.addFieldValue(typeid(CustAccount),custAccount);

    return dialog;
}

Pack

public container pack()
{
    return [#CurrentVersion,#CurrentList];
}



Unpack

public boolean unpack(container packedClass)
{
    Version version = RunBase::getVersion(packedClass);
;
    switch (version)
    {
        case #CurrentVersion:
            [version,#CurrentList] = packedClass;
            break;
        default:
            return false;
    }

    return true;
}

Main

static void main(Args args)
{
    Tutorial_RunbaseBatch    tutorial_RunBase;
   
;
    tutorial_RunBase = Tutorial_RunbaseBatch::construct();

    if (tutorial_RunBase.prompt())
    {
        tutorial_RunBase.run();
    }

用Axapta发Email

{

    SysMailer mailer = new SysMailer();

    mailer.body("This is the body of the mail");

    mailer.subject("The message subject");

    mailer.fromAddress("Axapta_header@navision.com");

    mailer.fromName("Sell Axapta contract");

    mailer.tos().add("Dave@Microsoft.com");

    mailer.tos().add("Bill@Microsoft.com");

    mailer.attachments().add("c:\\YouOrder.xls");

    mailer.sendMail();

    pause;

}

善用Axapta当中的exists join和inner join

其实我也发现这个问题了，但是Junevoful已经说的很详细了，我就补充两点：

1，使用exists join不可以引用第二个表的数据，表示÷这条数据一旦存在于第二个表中，就认为满足条件，但是使用inner join可以引用第二个表的数据，是内关联。

2，大家可以使用Axapta本省带的Sql性能监视器来监视Sql语句的执行效率，启动的方法是：

下面我就引用一下Junevoful的文章，他说的很详细，也很精彩，谢谢他给我带了这么好的文章！

前几天，在做系统优化的时候，居然发现代码当中存在while嵌套循环语句

while select table 1

{

..

while select table2

以前并不太在意，但是既然要系统优化，就只怕没找到东西可以改的。

突然忽发奇想，何不测试一下这样做的系统开销有多大呢？于是写了三个job，进行测试

static void TestInnerJoinUsingWhile(Args _args)

{

    ...

    ;

    startTime = WinApi::getTickCount();

    while select ledgerTrans

    where ledgerTrans.AccountNum == accountNum &&

    ((ledgerTrans.TransDate >= 1\7\2005 && ledgerTrans.TransDate <= 31\7\2005))

    {

        while select projTransPosting

        where projTransPosting.Voucher == ledgerTrans.voucher &&

        projTransPosting.Account == accountNum

        {

            ...

            tmpFRD_LedgerDriDwnContractDtls.insert();

        }

    }

    endTime = WinApi::getTickCount();

    duration = endTime - startTime;

    Info(int2str(duration));

}

static void TestInnerJoinUsingJoin(Args _args)

{

    while select ledgerTrans

    where ...

    join projTransPosting

    where ...

}

static void TestExistsJoinUsingJoin(Args _args)

{

    ...

    while select ledgerTrans

    where ...

    exists join projTransPosting

    where ...

}

结果发现使用嵌套while的时间是4012微秒，Inner join是1986微秒，exists join是1689微秒。

可见在写代码的时候,还是需要按照Best Practice的要求,这样才能获得最好的performance。

动态的得到某个表里的字段的类型和长度

static void GetFieldType(Args _args)

{

    SysDictField df;

    dictTable dt;

    TableId tableId = tablenum(InventTable);

    int i;

    ;

    dt=new Dicttable(tablenum(InventTable));

    for(i=1;i<=dt.fieldCnt();i++)

    {

        df = new SysDictField(dt.id(),dt.fieldCnt2Id(i));

        switch (df.baseType())

            {

                case Types::Date:

                info(' Field Name: '+dt.fieldName(dt.fieldCnt2Id(i))+

                ' Data Type: '+'Date'+' size:'+int2str(df.fieldSize()));

                break;

                

                case Types::Integer:

                info(' Field Name: '+dt.fieldName(dt.fieldCnt2Id(i))+

                ' Data Type: '+ 'Integer'+' size:'+int2str(df.fieldSize()));

                break;

                case Types::String:

                info('Field Name: '+dt.fieldName(dt.fieldCnt2Id(i))+

                ' Data Type: '+'String'+' size:'+int2str(df.fieldSize()));

                break;

                case Types::Integer:

                info('Field Name: '+dt.fieldName(dt.fieldCnt2Id(i))+

                ' Data Type: '+'String'+' size:'+int2str(df.fieldSize()));

                break;

                case Types::DateTime:

                info('Field Name: '+dt.fieldName(dt.fieldCnt2Id(i))+

                ' Data Type: '+'String'+' size:'+int2str(df.fieldSize()));

                break;

            }

    }

}

调用DataSource里的方法。

void modified()

{

    object fieldNameRef;

    super();

    fieldNameRef = tableName_ds.object(fieldNum(tableName, fieldName));

    fieldNameRef.methodName();

}

Dialog Boxes

static void Simple_Dialog(Args _args)
{
dialog dlg;
dialogGroup dlgGroup;
dialogField dlgField;
;
dlg = new dialog("Simple Dialog");
dlgGroup = dlg.addGroup("Customer");
dlgField = dlg.addField(TypeID(custAccount),"Account Number");

if (dlg.run())
{
print dlgField.value();
pause;
}
}

Lookup

// create in Form/Fields/fields name/Methods
public void lookup(FormControl _formControl, str _filterStr)
{

    ;
    lookupMaster::lookupMasterNumber(_formControl);
}




//Create in class/ new method
public static boolean lookupMasterNumber(FormStringControl   _ctrl)

{
    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(Master1), _ctrl);
    Query                   query;
    QueryBuildDataSource    qdbsTransport;
    AvVendorRoleTable       avVendorRoleTable;
 
    ;
    //display
    sysTableLookup.addLookupfield(fieldnum(Master1, MasterNumber), true);
    sysTableLookup.addLookupfield(fieldnum(Master1, MasterID));

    //logic and assign table & field
    query           = new Query();
    qdbsTransport   = query.addDataSource(tablenum(Master1));
    qdbsTransport.addRange(fieldnum(Master1, AvStatus)).value(queryValue(Avstatus::Approve));
 

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
    return true;
}

Struct

static void Collection_Struct (Args _args)
{
    //Create a struct with two fields
    struct myCar = new struct ("int ModelYear; str Carbrand");
    int i;
    ;
    // Set values to the fields
    mycar.value ("ModelYear", 2000);
    mycar.value ("Carbrand", "BMW");

    // Add a new field and give it a value
    myCar.add ("Model", "316");

    //Loop through the fields of the struct
    for ( i = 1; i <= myCar.fields(); i++)
    {
         info ( strfmt ( "FieldType: %1, FieldName: %2, Value: %3",
                          myCar.fieldType ( i ), myCar.fieldName ( i ), myCar.Value ( myCar.fieldName ( i ))));
    }
}

Set

static void  Collection_Set (Args _args)
{
    // Create a new set of type String
    Set cars = new Set (Types :: String);
    SetEnumerator setE;
    ;

    // Add elements to the Set
    cars.add ("Toyota");
    cars.add ("Ford");
    cars.add ("Mazda");
 
    // Check to see if an element exist in the set
    if (cars.in ("Toyota"))
    into ("Toyota is part of the set");
 
    //Display the content of the set
    info (cars.toString());

    // Get the enumerator of the set to loop through it
    setE = cars.getEnumerator();
    while (setE.moveNext())
    {
        info(setE.current());
    }
}

Map

static void Collection_Map (Args _args)
{
    // Create a new map with a key and value type
    Map cars = newMap (Types :: Integer, Type :: String);
    MapEnumerator mapE;

    ;
    // Insert values to the map
    cars.insert (1, "Volvo");
    cars.insert (2, "BMW");
    cars.insert (3, "Chrysler");
    //Display the content of the map
    info (cars.toString()) ;
    // Get the enumerator to loop
    // through the elements of the map
    mapE = cars.getEnumerator();
    while (mapE.moveNext())
    {
        info (strfmt ("Car %1: %2, mapE.currentKey(), mapE.currentKey());
    }
}

List

static void Collection_List(Args _args)
{
    // Create a new list of type string
    List names = new List (Types :: String);
    ListEnumerator listE;

    ;
    // Add elements to the list
    names.addEnd ("Lucas");
    names.addEnd ("Jennifer");
    names.addEnd ("Peter");

    // Display the content of the list
    info (names.toString());
    // Get the enumerator of the list
    // to loop through it
    listE = names.getEnumerator();
    while (listE.moveNext())
  {
        info ( strfmt ("Name: %1", listE.current()));
  }
}

While select full statement

[while] select [reverse] [firstfast] [firstonly]
[forupdate] [nofetch] [crosscompany]
[forcelitterals | forceplaceholders] [forcenestedloop]
[forceselectorder]
[ * | <fieldlist> from] <tablebuffer>
[ index [hint] <indexname> ]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ]
[ where <expression> ]
[ [ outer | exists | notexists ] join [reverse]
[ * | <fieldlist> from] <tablebuffer>
[ index <indexname> ]
[ aggregate [sum] [avg] [minof] [maxof] [count]]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ]
[ where <expression> ]
]
<fieldlist> ::= <field> | <fieldlist> , <field>
<field> ::= fieldname | <function>(<field>)
<function> ::= sum | avg | minof | maxof | count

Data Manipulation - Insert, Update, Delete

// INSERT

custTable custTable;
;
custTable.accountNum ="1234";
custTable.name ="John Customer";
custTable.insert();
_________________________________________________________________________________

//UPDATE

SalesTable salesTable;
;
ttsbegin;
while select forupdate salesTable
where salesTable.CustAccount =="2001"
{
salesTable.SalesName ="New Enterprises";
salesTable.update();
}
ttscommit;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


InventTable InventTable;
;
ttsbegin;
while select forupdate InventTable
where InventTable.ItemGroupId =="Television"
{
InventTable.ItemPriceToleranceGroupId ="2%";
InventTable.update();
}
ttscommit;
Microsoft

_________________________________________________________________________________

//DELETE

CustTable custTable;
;
ttsbegin;
Select forUpdate custTable
where custTable.accountnum =="2032";
custTable.delete();
ttscommit;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
//DELETE_from

CustTable custTable;
;
delete_from custTable
where custTable.AccountNum =="4018";

While Select

CustTable custTable;
while select accountNum, name, address from custTable
{
print custTable.AccountNum, " ", custTable.Name, " ",
custTable.Address;
pause;
}
_________________________________________________________________________________

CustTable custTable;
;
while select custTable
where custTable.AccountNum > "4005"
&& custTable.AccountNum < "4017"
{
print custTable.AccountNum , " ",custTable.Name;
}
pause;
_________________________________________________________________________________
//Sorting Obtion index

CustTable custTable;
;
while select custTable index AccountIdx
{
print custTable.AccountNum, " ", custTable.Name;
}
pause;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 //ORDER BY


CustTable custTable;
;
while select custTable order by AccountNum desc
{
print custTable.AccountNum, " ", custTable.Name;
}
pause;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

//GROUP BY
SalesTable salesTable;
;
while select count(salesid) from salesTable group by
CustGroup
{
print salesTable.CustGroup," ",salesTable.salesid;
}
pause;

_________________________________________________________________________________

//Aggregate
sum -->Returns the sum of the values in a field.

avg -->Returns the average of the values in a field.

maxof -->Returns the maximum of the values in a field.

minof -->Returns the minimum of the values in a field.

count -->Returns the number of records that satisfy the statement. Count can only be used on numerical fields, since the result will be returned in that field.

select sum(amountMST) from ledgerTrans;
sumAmountMST = ledgerTrans.amountMST;
select count(recId) from ledgerTrans;
countLedgerTrans = ledgerTrans.recId;





_________________________________________________________________________________

Queries

EXPANDED QUERY IN THE AOT

Query query;

QueryRun queryRun;

QueryBuildDataSource qbds;

QueryBuildRange qbr;

SalesTable SalesTable;

;

query = new Query();

//this line attaches a table to the qbds data source object

qbds = query.addDataSource(TableNum (SalesTable));

//this line attaches a range to the 'SalesTable' 

//data source, the range is the CustAccount

qbr = qbds.addRange(FieldNum (SalesTable,CustAccount));

// The range is set to '2001'

qbr.value ('2001');

// The query will sort by sales id

qbds.addSortField (FieldNum(SalesTable,SalesId));

// The queryRun object is instantiated using the query

queryRun = new QueryRun(query);

// The queryRun object loops through all records returned

while (queryRun.next())

{

// The current record is assigned to the salesTable variable

SalesTable = queryRun.get(tableNum(SalesTable));

print SalesTable.SalesId;

}

EXCEPTION HANDLING using TRY and CATCH

CustTable custTable;
;
try
{
custTable.AccountNum ="0002";
custTable.custGroup ="20";
custTable.Name ="Demonstration of try catch";
if (!custTable.validateWrite())
{
throw error("Record failed validation");
}
custTable.insert();
info("Record was inserted in database");
}
catch (Exception::error)
{
error("There was an error while inserting the
record.");
}

_________________________________________________________________________________

CustTable custTable;
;
try
{
custTable.AccountNum =“0001”;

custTable.custGroup =“50”;
custTable.Name =“Example of try catch”;
custTable.insert();
}
catch (Exception::error)
{
error(“There was an error while inserting the
record.”);
}
catch (Exception::deadlock)
{
retry;
}


_________________________________________________________________________________
//Throw Statement


try
{
select custTable
where custTable.accountNum == '1019';
if (!custTable.RecId)
throw error("Customer does not exist");

}
catch (exception::error)
{
error ("Process was aborted");
}