Tuesday, September 9, 2008

Dynamics Ax - Use the Arithmetic Functions with query classes

Dynamics Ax - Using the Arithmetic functions with the query classes. Smile

Query query;
QueryBuildDataSource queryBuildDataSource;
query = new Query();
queryBuildDataSource = query.addDataSource(TableNum(AssetTrans));
queryBuildDataSource.orderMode(OrderMode::GROUPBY);
queryBuildDataSource.addSelectionField(FieldNum(AssetTrans,amountMST), SelectionField::SUM);
queryBuildDataSource.addSelectionField(FieldNum(AssetTrans,revaluationAmount), SelectionField::SUM);
queryBuildDataSource.addSortField(FieldNum(AssetTrans, transType));
queryBuildDataSource.addRange(FieldNum(AssetTrans,assetId)).value(rangeAssetId);
queryBuildDataSource.addRange(FieldNum(AssetTrans,bookId)).value(rangeBookId);
queryBuildDataSource.addRange(FieldNum(AssetTrans, transDate)).value(queryRange(dateFrom, dateTo));
return query;

Result of the above Query: Hot SELECT SUM(AmountMST), SUM(RevaluationAmount) FROM AssetTrans GROUP BY AssetTrans.TransType ASC WHERE ((AssetId = FA-000001)) AND ((BookId = COMPUTERS)) AND ((TransDate<=12/31/2153))

Another simple query using the query classes.Tongue out

Query query = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;

queryBuildDataSource = query.addDataSource(tablenum(PurchParmTable));
queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, Ordering));
querybuildrange.value(queryvalue(documentstatus::PackingSlip));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, PurchId));
querybuildrange.value(queryvalue(purchparmtable.PurchId));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, ParmJobStatus));
querybuildrange.value(queryvalue('Executed'));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, Invoiced));
querybuildrange.value(queryvalue('No'));

this.query(query);

These are the simple examples using the query classes. Eye-rolling

Dynamics Ax - Update the records with single query statement.

For updating the ‘N’ no. of records we can use simple query statement instead of using while loop.

(i.e. while select inventTrans where )

By using update_recordset we can update multiple records at a time.



oldRFQCaseId = this.orig().RFQCaseId;



ttsbegin;



update_recordset inventTrans

setting TransRefId = this.RFQCaseId

where inventTrans.TransRefId == oldRFQCaseId;



ttscommit;

Dynamics Ax - AIF File system adapter Inbound common error

Common Error: Cannot be read because the submitting user could not be determined. The default owner for objects created by members of the Administrators group must be set to the object creator.

To avoid the above error do the following steps for file system adapters.

Before you create the folder for the inbound file system adapter on the server, complete the following:

1. Click Start > Programs > Administrative Tools > Local Security Policy.

2. On the Local Security Settings menu, navigate to Security Settings > Local Policies > Security Options.

3. Change the Security Settings for the System Objects: Default owner for objects created by members of the administrator's group from Adminstrator's group to Object creator.

4. Log off and log back on to the computer.

5. Create the folder for the inbound file system transfer.

6. Verify that the owner of the folder is the user sending the document to Microsoft Dynamics AX (the submitting user) by:

a. Right-clicking the folder and selecting Properties, and then

b. Clicking Advanced on the Security tab to view the Advanced Security Settings.

The owner of the folder is shown on the Owner tab.



After these settings are done place the XML file in that particular folder. Now run the AIF job to read the xml and to create the records in Dynamics – Ax.

Thursday, September 4, 2008

Document Handling in AX - setup and Example

Some initial setups for enabling document handling in AX-

On the Tools menu, select Options.
Under Document handling, select Document handling active.
Select Update toolbar line button to highlight the Document handling icon on the toolbar when you select a record with documents references.

Documents are stored in a default document archive directory and before you start creating documents, you must select the location in the Parameters form.

Click Basic > Setup > Document management > Parameters.
In Archive directory, type the path to the archive directory, or use the browse button (...) to select a folder on your network.
Select the Number sequences tab.
In the Number sequence code list, select the number sequence to use for naming your documents.

The document management system handles several types of documents including letters, worksheets, and simple notes. Before you can create documents of a certain type, the document type must be created in the Document type form.

By default, all documents are stored in the Archive directory selected on the Parameters form. However you can select alternative folders for the individual document types.

Also by default, all document types are available from all forms, but some document types are only relevant for certain tables, such as, if you only want to create customer letters for records in the Customers form. When you associate a document type with a specific table, it is not possible to create any documents of that type, in other tables.

Create new document type

Click Basic > Setup > Document management > Document types.
Press CTRL+N to create a new document type.
In Type, type a code for the document type.
In Name, type a descriptive name for the document type.
In the Job description list, select the type of document to create.
In the Group list, select a group for the document type.

Now, I would like to explain document handling with an example for sales orders form of DOC type.

Initially set the parameters for document handling.

Go to - >Basic -> setup -> Document Management - > Parameters form

set the archive diretory path ( where the document has to be stored like c:\AxDocuments). Remember when ever u create document for a record, the document gets stored in the above location specified.

Check - use Active document table checkbox.

In the number sequences tab - select the number sequence you would like to use for the documents.

Then, If you want to enable documents for salestable - Go to - > Basic -> setup -> Document Management - > Active document tables form . Then select the name of the table here as salestable and in enable the always enabled checkBox.

Now when you open the salestable form you can find the document handling enabled on the toolbar for the salestable form. Double click the document handling icon and create a new document for that record by clicking the new button and selecting the Document menuitem button.Now you can create documents for the salestable.Once you create documents the documents will be stored in the archive path selected in the parameters form.

When ever u create a document, it hits docuref and docuvalue tables.

In the docuref,it creates a record with the tableid of the salestable, the record id of the salestable and the dataareaid ..etc..and correspondingly a record gets generated in the docuvalue with all the filenames, extensions,path etc

To view the documents in the salestable from itself, i mean in the gird itself here is the way...

Open the salestable form, Then , I would like to use icons for every record in the salestable.So i will write a display method at the salestable level.

//BP Deviation Documented
display smmDocIconNum showDocHandIcon()
{
#macrolib.resource
;
if ((select firstonly docuRef where docuRef.RefCompanyId == this.DataAreaId && docuRef.RefTableId == this.TableId && docuRef.RefRecId == this.RecId).RecId)
{
return #RES_NODE_DOC;
}

return #RES_AM_NEW;
}

Now create a new window control in the gird. Add the datasource as salestable and datamethod as showDocHandIcon.

The main class where all the business logic is written is docuAction Class. Now i am going to use this class to open the documents for the records.

Now please override the Mouseup() method of the window control

public int mouseUp(int _x, int _y, int _button, boolean _Ctrl, boolean _Shift)
{
int ret;
args args;
docuRef docuRef;
;
ret = super(_x, _y, _button, _Ctrl, _Shift);

element.showDocument(); // Method at form level
return ret;
}

Now add showDocument() method at form level

void showDocument()
{
args args;
docuref docuref;
;

args = new args();
docuRef = docuref::find(salesTable.dataAreaId,tablenum(SalesTable),salesTable.RecId,today());
args.record(docuRef);
args.parmEnumType(enumnum(Docucode));
args.parmEnum(Docucode::Open);
docuaction::main(args);
}

NOW YOU CAN VIEW YOUR DOCUMENTS FROM THE GRID ITSELF BY DOUBLE CLICKING THE WINDOW CONTROL.

Graphical Indicators in AX(Analogmeters)

Graphical indicators(Analogmeters) are used to display the results of a query or statistics related to measurements.

For each graphical indicator, you need to define the following:

* A starting and ending value for a segmented semi-circle
* The maximum value for each segment, or area, of the semi-circle
* A header and a unit for all values
* A color for each segment, text, value, and background.

Here is a small job which will create form and analogmeter at runtime. Values(Min,Max,Pos etc)are hardcoded in the below example.You can change the code to take the values from the tables.

static void createAnalogMeter(Args _args)
{
Form formBrowse;
FormActivexControl activex;
FormRun formRun;
Args args;

TreeNode treeNode;
Object waitObject = new Object();
;

formBrowse = new Form('AnalogMeter Example', true);

formBrowse.design().width(500);
formBrowse.design().height(500);
formBrowse.design().caption('Analogmeter');
formBrowse.design().addControl(FormControlType::ActiveX, 'Browser');

args = new Args(formBrowse.name());
args.name(formBrowse.name());
args.object(formBrowse);

formRun = classFactory.formRunClass(args);
formRun.init();

activex = formRun.design().controlName('Browser');
activex.height(1,1);
activex.width(1,1);
activex.className('{706FF037-D46D-11D2-AB8D-0008C7631C69}');
activex.BackColor(winapi::RGB2int(255,255,236));
activex.displayHotArea(true);
activex._Caption('Analog meter Example');
activex.minValue(22000);
activex.maxValue(40000);
activex.addMarker(25000,255,"This is where i stand");
activex.NeedleColor(winapi::RGB2int(0,128,0));
activex.pos(25000);
formRun.run();

formRun.detach();

while (activex.pos() != 40000)
{

waitObject.setTimeOut('Notify', 1000);
waitObject.wait();
activex.pos(activex.pos() + 1000);
}

}

This is how analogmeter looks

Create Technical Document from AX

Creating detail technical document in projects is very time consuming. Isn’t it?

Particularly if there are many objects in the project it will take more time in creating it.

Here is the solution.This job will list the table fields properties like table field name,help text(Description),type, Size etc.

static void sgx_TehnicalDocument_Tables(Args _args)

{

COM document;

COM wordDocument;

COM wordRange;

COM app;

str tableContent;

SysDictTable dictTable;

SysDictField dictField;

Counter fieldCounter;

Counter arrayCounter;

str typeofTheField(DictField df, Types _types)

{

str typeName;

SysDictType dictType;

SysDictEnum dictEnum;

;

switch(_types)

{

case Types::UserType : dictType = new SysDictType(dictField.typeId());

return dictType.name();

case Types::Container : return 'container';



case Types::Date : return 'date';



case Types::Enum : dictEnum = new SysDictEnum(dictField.enumId());

if (dictEnum)

return dictEnum.name();

case Types::Integer :

return 'int';

case Types::Int64 :

return 'int64';

case Types::Real :

return 'real';

case Types::Record :

return 'record';

case Types::VarString :

case Types::RString :

case Types::String : return 'str';

case Types::BLOB : return 'blob';

case Types::DateTime : return 'datetime' ;

case Types::Guid : return 'guid' ;

default : return '';

}

}

;

app = new com("Word.Application");

app.visible(true);

document = app.Documents();

wordDocument = document.add();

wordDocument.activate();

wordRange = wordDocument.range(0,0);

dictTable = new SysDictTable(tablenum(CustTable)); // Mention your table here

for (fieldCounter = 1; fieldCounter <= dictTable.fieldCnt(); fieldCounter++)

{

dictField = new SysDictField(dictTable.id(), dictTable.fieldCnt2Id(fieldCounter));

if (!dictField.isSystem())

{

for (arrayCounter = 1; arrayCounter <= dictField.arraySize(); arrayCounter++)

{

dictField = new SysDictField(dictTable.id(), dictTable.fieldCnt2Id(fieldCounter), arrayCounter);

tableContent += dictField.name() + "\t" + dictField.help() + "\t" + typeofTheField(dictField, dictField.type()) + "\t" + int2str(dictField.stringLen()) + "\n";

}

}

}

wordRange.insertAfter(strfmt(tableContent));

wordRange.convertToTable();

}

Note: This has been developed as per my requirements. Beautifications and customizations can be done as per your requirements. I wrote simple job to do this. But it can be customized in such a way that you can right click the project- context menu- create technical document and will create the technical document for all the objects(Enums,EDT,Forms,classes etc) in the project.This development is in progress and will surely update this in the blog soon.

Now this is how the word document will be loaded with the data after running the job

Stock Market Anlaysis(DOW JONES) using X++

As a commodities trader, I've never lost my fascination for the marketsMoney. This job will download historical trading data from Yahoo Finance for any valid specified date range and issue symbol, including indexes such as the Dow Industrials Average (that's "^DJI" with Yahoo).

URL Used : http://finance.yahoo.com/q/hp?s=IBM&a=00&b=2&c=2004&d=04&e=8&f=2005&g=d&z=66&y=66

* s - ticker symbol( MSFT - Microsoft, IBM etc)
* a - start month
* b - start day
* c - start year
* d - end month
* e - end day
* f - end year
* g - resolution (e.g. 'd' is daily, 'w' is weekly, 'm' is monthly)
* y is the offset (cursor) from the start date
* z is the number of results to return starting at the cursor (66
maximum, apparently)

Here is the Job:

static void StockMarket_Analysis(Args _args)

{

System.Net.WebClient web = new System.Net.WebClient();

TextBuffer txtBuffer = new TextBuffer();

str result;

container recordContainer, fieldContainer;

str stockContent;

int i;

FileName nameOfTheFile;

str tmpContent;

str urlQuery = @"http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=00&b=2&c=2007&d=04&e=8&f=2008&g=d&ignore=.csv";

CCHTMLString htmlString;

CCHTMLString formHTML(Container hmtlContainer, int tmp = 0)

{

CCHTMLString html;

int h;

str start_td_th;

str end_td_th;

str value;

;

if (tmp == 1)

{

start_td_th = @"";

end_td_th = @'';

}

else

{

start_td_th = @"";

end_td_th = @'';

}

html += '';

for (h = 1; h <= conlen(hmtlContainer); h++)

{

if (h == 6 && isInteger(conpeek(hmtlContainer, h)))

value = int2str(conpeek(hmtlContainer, h));

else

value = conpeek(hmtlContainer, h);

html += start_td_th + value + end_td_th;

}

html += '';

return html;

}

;

result = web.DownloadString(urlQuery);

result = strreplace(result,"\r", "");

recordContainer = str2con(result,'\n');

htmlString += @'html>body>Stock Market Alert ... <(IMG) SRC="http://www.wharton-pec.org/conf2006/images/DowJones_Logo.jpg" HEIGHT=40 WIDTH=82

able1 border="1" CELLSPACING="5" CELLPADDING="5" BGCOLOR="lightblue" WIDTH="80%>';

for (i = 1; i <= conlen(recordContainer); i++)

{

tmpContent = conpeek(recordContainer, i);

fieldContainer = str2con(tmpContent,',');

if (i == 1)

stockContent += formHtml(fieldContainer, 1);

else

stockContent += formHtml(fieldContainer);

}

htmlString += stockContent + '
';

txtBuffer.setText(htmlString);

nameOfTheFile = winApi::getTempPath() + 'StockMarket.html';

txtBuffer.toFile(nameOfTheFile);

infolog.urlLookup(nameOfTheFile);

}