Axapta 7 Introduction

Introduction to  Dynamics 365 

• The first step is to log into Visual Studio as an Adminstrator

Once within Visual Studio, go to the AX7 menu and select Model Management and ‘Create model’. A model is not exactly the same as a layer, but it might be a useful analogy to think of it as being similar to a layer in the sense, it is a way to isolate and manage your customizations

Create the model in the form that appears and fill out the fields as appropriate

Click ‘Next’ and select ‘Existing package’ and choose ‘Application Suite’

Press ‘Next’ – accept the default values suggested and press ‘Finish’

In the ‘Save’ screen that appears, select the project DynamicsAX7 and give it a name of your choice and press ‘OK’

Notice in the Solution Explorer to the right in the screen a new Project has been created in your newly created Model

On the Solution Explorer go to the project and rightclick and select ‘Properties’

In here note that the project is in the new Model just created and that the customizations will be saved in the ‘USR’ layer

Also, it’s a good idea to specify which company you wish to execute the code in, here ‘DEMF’

Press OK

 Now to add ‘Runnable Class(Job) to the Project do the following. On the project rightclick and select ‘Add’ and then select ‘New Item’

In the screen shat appears select ‘Code’ and then ‘Runable Class(Job)’

And press ‘Add’

Note that now in the Solution explorer that the Runable Class has been added

Doubleclick on the ‘Runable class’ and enter the code editor window and give your job a meaningful name and write your code like you would do any X++ job as known in previous versions

Save the project(Ctrl + S) and to compile the job, select ‘Build’ and ‘Build Solution’ from the toolbar

Wait until the output window reports that the build has completed

Now in order to run the job, you need to set it as a ‘Startup Object’

Go to the Solution Explorer and on the Job, righclick and select ‘set as Startup Object’

Now to execute the job, select ‘Debug’ in the toolbar and then ‘Start without Debugging’

And note the output in the browser, Make sure it is Synchronized with Data Base.

And that’s it Axaptians...🔺 !

TYPES OF QUERY RANGES

BASIC Query Classes  Query

QueryBuildDataSource

QueryBuildRange

QueryBuildFieldList

QueryBuildLink

QueryBuildDynaLink

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;

} ***************************************************************** Query “join table A with table B and table A with table C” Query query = new Query(); QueryBuildDataSource qbds,qbds1,qbds2; qbds = query.addDataSource(tableNum(CustTable)); qbds1 = qbds.addDataSource(tableNum(CustTrans)); qbds2 = qbds1.addDataSource(tableNum(CustLedgerTransTypeMapping)); qbds1.addLink(fieldNum(CustTrans,AccountNum),fieldNum(CustTable,AccountNum)); qbds2.addLink(fieldNum(CustTrans,TransType),fieldNum(CustLedgerTransTypeMapping,CustSettleTransType)); info(query.toString()); *********************************************************************************************************************************

JOIN QUERY WITH X++

qbds1 = query.addDataSource(tablenum(Table1));

qbds2 = qbds1.addDataSource(tablenum(Table2));
qbds2.relations(true);

qbds3 = qbds2.addDataSource(tablenum(Table3));
qbds3.relations(true); 

qbds4 = qbds3.addDataSource(tablenum(Table4));
qbds4.relations(true);

qbds5 = qbds4.addDataSource(tablenum(Table5));
qbds5.relations(true);

qbds6 = qbds5.addDataSource(tablenum(Table6));
qbds6.relations(true);
qbds6.joinMode(JoinMode::ExistsJoin);

*****************************************************************************************************************************

JOINS IN X++ With EXAMPLE: 

    Query                                    query = new Query();
    QueryBuildDataSource     queryBuildDSSalesTable;
    QueryBuildDataSource     queryBuildDSSalesLine;
    QueryBuildRange              queryBuildRange;
    QueryRun                            queryRun;
    SalesLine                            salesLine;
    ;
    
 // Setup the primary datasource.
    queryBuildDSSalesTable  = query.addDataSource(tablenum(SalesTable));
    queryBuildDSSalesTable.addRange(fieldnum(SalesTable,SalesId)).value('XXXXXX');

// Setup the secondary (joined) datasource.
 queryBuildDSSalesLine   = queryBuildDSSalesTable.addDataSource(tablenum(SalesLine));
 queryBuildDSSalesLine.joinMode(JoinMode::InnerJoin);
 queryBuildDSSalesLine.relations(true);
 queryRun = new QueryRun(query);
    while(queryRun.next())
    {
        salesLine   = queryRun.get(tablenum(SalesLine));
        info(strfmt('%1',salesLine.SalesId));
    }

***************************************************************************************************************

In the example below, we construct a query and add a single datasource.

The range is then added, using the DataAreaId field on each table. Any field can be used, but using an unusual one such as DataAreaId helps remind a casual reader of the code that it's not a normal range.

query = new Query();

dsInventTable = query.addDataSource(tableNum(InventTable));

// Add our range

queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

Given the above, the following are valid range specifications:

 

Simple criteria

Find the record where ItemId is B-R14. Take note of the single quotes and parenthesis surrounding the entire expression.

queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("B-R14")));

Find records where the ItemType is Service. Note the use of any2int().

queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));

Find records where the ItemType is Service or the ItemId is B-R14. Note the nesting of the parenthesis in this example.

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))', 

    any2int(ItemType::Service),

    queryValue("B-R14")));

Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.

queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));

Find records where the Field is blank (null) or an empty string. For more see Sys::Query Docs

qbrStatement = this.query().dataSourceName("BankAccountTrans2").addRange(fieldnum(BankAccountTrans,AccountStatement));

//qbrStatement.value("!?*");//this is the old way that may not work in future versions of AX

qbrStatement.value(sysquery::valueEmptyString());//this is the new way 

 

Complex criteria with combined AND and OR clauses

Find all records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard range syntax.

Note also that in this example, we are using the fieldStr() method to specify our actual field names and again, that we have nested our parenthesis for each sub-expression.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',

    fieldStr(InventTable, ItemType),

    any2int(ItemType::Service),

    any2int(ItemType::Item),

    fieldStr(InventTable, ProjCategoryId),

    queryValue("Spares")));

 

WHERE clauses referencing fields from multiple tables

For this example below, we construct a query consisting of two joined datasources (using an Exists join). Note that we specify the datasource names when adding the datasources to the query.

The ranges are then added, using the DataAreaId field on each table as described in the earlier example.

query = new Query();

dsInventTable = query.addDataSource(tableNum(InventTable), tableStr(InventTable));

dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), tableStr(InventItemBarCode));

dsInventItemBarCode.relations(true);

dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);

// Add our two ranges

queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));

Find all records where a bar code record exists for an item and was modified later than the item was modified.

In this example, we are using the range on the BarCode table. Therefore the unqualified ModifiedDate reference will relate to InventItemBarCode.ModifiedDate. The other field is a fully-qualified one, using the DatasourceName.FieldName syntax.

queryBuildRange2.value(strFmt('(ModifiedDate > InventTable.ModifiedDate)'));

Note that if we had added our InventTable datasource using the following code

dsInventTable = query.addDataSource(tableNum(InventTable), "InventTableCustomName"); // Note that we are manually specifying a different datasource name 

then the query range would need to appear as follows

queryBuildRange2.value(strFmt('(ModifiedDate > InventTableCustomName.ModifiedDate)'));

 

Conditional joins

We will modify our previous example slightly, to remove the automatic addition of relations for the join.

query = new Query();

dsInventTable = query.addDataSource(tableNum(InventTable), "InventTable");

dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), "InventItemBarCode");

dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);

// Add our two ranges

queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));

We can now use the query expression to specify whatever we like as the join criteria.

Find all records where either the ItemType is Service, or the ItemType is Item and a barcode exists. The join criteria is only applied in the second half of the expression, so all Service items will appear irrespective of whether they have a bar code. Again, this is not possible to achieve using the standard query ranges.

queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',

    query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1

    fieldStr(InventTable, ItemType), // ItemType %2

    any2int(ItemType::Service), // %3

    any2int(ItemType::Item), // %4

    fieldStr(InventTable, ItemId), // ItemId %5

    fieldStr(InventItemBarCode, ItemId))); // %6 

Using the techniques above, it is possible to create queries with almost as much flexibility as using SQL statements directly.

Filter on array fields

queryBuildRange.value(strFmt('((%1.%2 == "%4") || (%1.%3 == "%5"))', 

    queryBuildDataSource.name(),

    fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Center))), 

    fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Purpose))), 

    "some dim2 value", 

    "some dim3 value"));

Note: you must always specify the datasource name if you use Query Expression syntax to filter on array fields. See also Limitations section at the bottom of the page.

 

Using wildcards and comma-separated range values

Again, the previous example here was using standard syntax, not the special syntax using expressions. It's not possible to modify the above examples to work with wildcards.

The above statement applies to AX versions < 5.0

AX 5.0 introduced solution to wildcards - while you still cannot directly use wildcards in ranges, now it supports the 'LIKE' keyword.

(AccountNum LIKE "*AA*" || Name LIKE "*AA*")

Query With Enum:

qbds.addRange(fieldNum(EIRTenderRecordTable, Status))
        .value(queryValue(EIRStatus::Active));
qbds.addRange(fieldNum(EIRTenderRecordTable, StartDate))
        .value(SysQuery::range(_requisitionDate, dateNull()));
qbds.addRange(fieldNum(EIRTenderRecordTable, EndDate))
        .value(SysQuery::range(dateNull(), _requisitionDate));