COMVariantType for Real values in Dynamics AX

Case Study: Reading cell content from excel template for COM variant type VT_R4 or VT_R8 is always little tricky.

Observation: Reading real value can be done in following ways
1) num2Str0(_variant.double(), 0);
2) num2str(_variant.double(), 0, numOfDec(_variant.double()), 1, 0);

Here is the output which is generated where the first function value is always a round-off value compared with the second function which returns the exact content with correct scale and precision.

/* Build excel template as following and specify the path @ excel ======================================= Column Integer Real ======================================= Rows(1) 123 60.9756097560976 Rows(2) 234 5.69105691056911 ======================================= */

static void SR_VariantType(Filename excel = @'C:\Projects\Data.xlsx')
{
    int                 rows;
    int                 columns;

    COMVariant          variant;
    SysExcelCells       sysExcelCells;
    SysExcelWorkbook    sysExcelWorkbook;
    SysExcelWorkbooks   sysExcelWorkbooks;
    SysExcelWorksheet   sysExcelWorksheet;
    SysExcelWorksheets  sysExcelWorksheets;
    SysExcelApplication sysExcelApplication;

    str variant2Str(COMVariant _variant)
    {
        str valueStr;
        ;

        switch(_variant.variantType())
        {
            case COMVariantType::VT_EMPTY   :
                valueStr = '';
                break;

            case COMVariantType::VT_BSTR    :

                valueStr = _variant.bStr();
                break;

            case COMVariantType::VT_R4      :
            case COMVariantType::VT_R8      :

                if(_variant.double())
                {
                    valueStr = strFmt("@SYS311964",
                                      num2Str0(_variant.double(), 0),
                                      num2str(_variant.double(),
                                      0,
                                      numOfDec(_variant.double()),
                                      1,
                                      0));
                }
                break;

            default                         :
                throw error(strfmt("@SYS26908",
                                   _variant.variantType()));
        }

        return valueStr;
    }
    ;

    sysExcelApplication = SysExcelApplication::construct();
    sysExcelWorkbooks   = sysExcelApplication.workbooks();

    try
    {
        sysExcelWorkbooks.open(excel,
                               false /*Update links*/,
                               true /*Read only*/);
    }
    catch (Exception::Error)
    {
        throw error(strFmt("@SYS76826", excel));
    }

    sysExcelWorkbook   = sysExcelWorkbooks.item(1);
    sysExcelWorksheets = sysExcelWorkbook.worksheets();

    // Only considering Sheet 1
    sysExcelWorksheet  = sysExcelWorksheets.itemFromNum(1);
    sysExcelCells      = sysExcelWorksheet.cells();

    // Since in first row there will be field names.
    for ( rows = 2; rows <= 3; rows++)
    {
        for (columns = 1; columns <= 2; columns++)
        {
            variant = sysExcelCells.item(rows, columns).value();
            print variant2Str(variant);
            pause;
        }
    }

    // Close Excel
    sysExcelApplication.quit();

    variant             = null;
    sysExcelWorkbooks   = null;
    sysExcelWorkbook    = null;
    sysExcelWorksheet   = null;
    sysExcelCells       = null;
    sysExcelApplication = null;
}

Ax 2009 disk full error

Hi all,

This problem only seems to occur on 64-bit operating systems and it can be very tricky to pin down the cause

It occurs on both Terminal servers and Citrix Servers seemingly at random, we've noticed that the most consistent way to reproduce it (for this implementation at least) is by trying to generate a Customer Account Statement, but it can occur also occur when using Select Criteria, trying to generate a report, or when trying to view the permissions tree in a user group

The problem

 

Cannot create temporary file: C:\Program Files (x86)\Microsoft Dynamics AX\50\Client\appl\standard\tmp\$tmp001303e8.$.

The disk may be full.

Which is strange since the disk isn't full on the AOS server or the Citrix/Terminal server

There is a workaround which is to run the Ax client as an Administrator but that can be a major security flaw and isn't a long term solution.

The Cause

There's a registry setting which tries to create a temporary sub-directory each time a user connects to a Terminal/Citrix server.

The problem is that when this temporary sub-directory gets created, it doesn't appear that the Ax client has access to it

So while the user may have the correct access to the %temp% directory within their Citrix/Terminal server user profile, they don't have access to the %temp%\1 or %temp%\27 or %temp%\942 sub-directory (the exact number varies and is rarely the same)

 

The Solution

The solution is to set the registry on the terminal server to not create these sub-directories within the temp folder and just to use the main %temp% directory instead

How do you do that? Well I'm glad you asked, all it takes is a little registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server]

"PerSessionTempDir"=dword:00000000

This change needs to be made in the registry on all the Citrix/Terminal servers that are used to connect to Ax

Here's a handy KB article with a bit more information about these keys
http://support.microsoft.com/kb/243215

Couldn't find this solution posted anywhere else on the internet so it looks like this is a first. Hurrah!

Thanks to Microsoft Support for their help in troubleshooting this tricky issue

Creating Number Sequence for a New Module

Say you want to create a new module called Pre Purchase, and for simplicity, we will create just One new Number sequence.

Here’s what to do:-

1. Edit the baseEnum NumberSeqModule, adding a reference for your new module (Pre Purchase)

2. Create a new EDT say PurchaseRequisitionId which will be used in the module

3. Create new class NumberSeqReference_PrePurchase that extends NumberSeqReference
Add 3 methods to that class

public class NumberSeqReference_PrePurchase extends NumberSeqReference

{
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

protected void loadModule()

{
NumberSequenceReference numRef;
;

/* Setup PurchaseRequisitionId */

numRef.dataTypeId = typeid2extendedtypeid (typeid (PwC_PurchaseRequisitionId));
numRef.referenceHelp = literalStr("Unique key for Purchase Requisition identification. The key is used when creating new Purchase Requisitions."); // Use Labels here
numRef.wizardContinuous = true;
numRef.wizardManual = NoYes::No;
numRef.wizardAllowChangeDown = NoYes::No;
numRef.wizardAllowChangeUp = NoYes::No;
numRef.sortField = 1;
this.create(numRef);
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

static NumberSeqModule numberSeqModule()

{
return NumberSeqModule::PrePurchase;
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4. Modify the NumberSeqReference Class for the following methods

\Classes\NumberSeqReference\moduleList

Add the following code
// PrePurchase Begin
moduleList += NumberSeqReference_PrePurchase::numberSeqModule();
// PrePurchase End
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

\Classes\NumberSeqReference\construct

Add the following code

Pre Purchase addition begin
case (NumberSeqReference_PrePurchase::numberSeqModule()):
return new NumberSeqReference_PrePurchase(_module);
// Pre Purchase addition end

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

5. Create a parameters table and form

You should create a parameters table and form for your new module. The easiest way is generally to duplicate an existing Parameters table and modify it as required.

The important elements on the new parameter table are the numberSeqModule() and numberSeqReference() methods.

client server static NumberSeqModule numberSeqModule()

{
    return NumberSeqReference_ PrePurchase::numberSeqModule();
}
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

client server static NumberSeqReference numberSeqReference()

{
    return NumberSeqReference::construct(NSParameters::numberSeqModule());
}

In the parameters form, you must ensure that the code in the numberSeqPreInit(), numberSeqPostInit() and NumberSequenceType.executeQuery() methods correctly reflect your new number sequence elements.

6. Calling a number sequence

Add this code into this TableParameters
static client server NumberSequenceReference numRefSomeMethodID()
{
return NumberSeqReference::findReference(typeId2ExtendedTypeId(typeid(Your EDT)));
}

7. Add this declaration in the form ClassDeclaration

public class FormRun extends ObjectRun
{
NumberSeqFormHandler numberSeqFormHandler;
}

8. Add this method to the form where you want Number sequences

NumberSeqFormHandler numberSeqFormHandler()
{
if
(!numberSeqFormHandler)
{
numberSeqFormHandler=
NumberSeqFormHandler::newForm(YourTableParameters::numRefSomeMethodID().NumberSequence,
element,
YourTable_DS,
fieldnum(YourTable,YourField));
}
return numberSeqFormHandler;
}

9. Add Create, Write and Delete methods to the Data source in the Form:

Create:
void create(boolean append = false)
// If created externally
{
;
element.numberSeqFormHandler().formMethodDataSourceCreatePre();
super(append);
element.numberSeqFormHandler().formMethodDataSourceCreate();
}

Write:
void write()
{
ttsbegin;
element.numberSeqFormHandler().formMethodDataSourceWrite();
super();
ttscommit;
}

Delete:

void delete()
{
ttsbegin;
element.numberSeqFormHandler().formMethodDataSourceDelete();
super();
ttscommit;
}

10. Create a new Number Sequence in BASIC module and Run Wizard

Basic -> Setup -> NumberSequences -> Number Sequences

Create New Number Sequence code and assign the format. Click "Wizard" button and choose your number sequence in the next screen.

Now open your form to see the Number Sequence you created.

Hiding Content pane of AX 2009

The below code is to hide the content pane of Ax 2009

 

static void hideContentPaneWindow(Args _args)

{

#WinApi

HWND contentPane = WinApi::findWindowEx(WinAPI::findWindowEx(infolog.hWnd(), 0, ‘MDIClient’, ”),0,’ContentFrame’,” );

;

if (contentPane)

WinApi::ShowWindow(contentPane,#SW_HIDE); // To restore use #SW_RESTORE

}

Insert Values to Item Group Table from Excel

Hi Everyone,
Here I published the X++ code for Inserting Excel Data in to ItemGroup Table.
Condition: If the importing values are same as the values in the table then the values will not be uploaded, if the description of the item differs then the description only updated in the table.

Void Clicked()
{
SysExcelApplication application;
SysExcelWorkbooksworkbooks;
SysExcelWorkbookworkbook;
SysExcelWorksheetsworksheets;
SysExcelWorksheetworksheet;
SysExcelCellscells;
COMVariantType type;
System.DateTimeShlefDate;
FilenameOpenfilename;
dialogFielddialogFilename;
Dialogdialog;
InventItemGroupinventItemGroup1;
ItemGroupId itemGroupId;
Name itemGroupName,itemGroupName1;
introw;
#Excel

// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
;

dialog=new Dialog("Upoad from Excel");
dialogFilename=dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLS,"@SYS28576",#XLSX]);
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Upload from Excel");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename=dialogFilename.value();
application=SysExcelApplication::construct();
workbooks=application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook =workbooks.item(1);
worksheets=workbook.worksheets();
worksheet=worksheets.itemFromNum(1);
cells=worksheet.cells();

try
{
ttsbegin;
do
{
row++;
itemGroupId=COMVariant2Str(cells.item(row, 1).value());
itemGroupName=COMVariant2Str(cells.item(row,2).value());
if(row>1)
{
if(substr(itemGroupId,1,1) ==’6′)
{
itemGroupId = substr(itemGroupId,2,strlen(itemGroupId));
inventItemGroup = inventItemGroup::find(itemGroupId);
itemGroupName1= inventItemGroup::find(itemGroupId).Name;
if(inventItemGroup)
{

if(itemGroupName1!=itemGroupName)
{
select forupdate inventItemGroup1 where inventItemGroup1.ItemGroupId == itemGroupId;
inventItemGroup1.Name = itemGroupName;
inventItemGroup1.update();
info(strfmt("Item Description for Item GroupId (%1) Updated succesfully",ItemGroupId));
}
}
else
{
inventItemGroup1.ItemGroupId = itemGroupId;
inventItemGroup1.Name= itemGroupName;
inventItemGroup1.insert();
info(strfmt("Item GroupId (%1) uploaded succesfully",ItemGroupId));
}
}
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
ttscommit;
}
catch
{
Error("Upload Failed");
application.quit();
}
}

 

Create LookUps Using X++ in Axapta

Hi All,
Here I created Lookups using X++ Code (without Ax Table or EDT relation). For that i override a String(Text) Control’ s lookup method.

public void lookup()
{
//super();
// Added by Ashok Koduri
// Declaration
Query LookupQuery = new Query();
QueryBuildDataSource LookupQueryBuildDataSource;
QueryBuildRange LookupQueryBuildRange;
SysTableLookup CustomSysTableLookup = SysTableLookup::newParameters(tableNum(InventTable), this);
;

// Add fields that you want in Lookups
CustomSysTableLookup.addLookupField(fieldNum(InventTable, ItemId));
CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemName));
CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemGroupId));
CustomSysTableLookup.addLookupField(fieldNum(InventTable,NameAlias));
CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemType));
CustomSysTableLookup.addLookupField(fieldNum(InventTable,DimGroupId));
// Add DataSource, Range and Value
LookupQueryBuildDataSource = LookupQuery.addDataSource(tableNum(InventTable));
LookupQueryBuildRange = LookupQueryBuildDataSource.addRange(fieldNum(InventTable,ItemVisibility));
LookupQueryBuildRange.value(queryValue(NOYESCOMBO::Yes));
// Execute the Query
CustomSysTableLookup.parmQuery(LookupQuery);
CustomSysTableLookup.performFormLookup();
// Added by Ashok Koduri
}

Using AOT Query Object in X++ Code

Hi,
Here i posted the code to use the AOT Query Object in our X++ code.
Steps:
1) Create a AOT Query Object as your requirement.
2) Create a Job and paste the below code to check the Query Object.

static void ExecuteAOTQuery(Args _args)
{
QueryRun queryRun;
Counter totalRecords;
;
queryRun = new QueryRun(queryStr(CreatedAOTQueryName));
if (queryRun.prompt())
{
while (queryRun.next())
{
totalRecords++;
}
}
info(strFmt(“Total Records : %1”, totalRecords));
}

Dynamic Query Using Aggregative Functions and Joins in Axapta 2009

Hi All,
Here i written the code to use the Aggregate functions and Joins inDynamic Query in spite of using normal Select statement in Ax 2009
Normal Select Statement:
while select sum(qty) from inventTrans where inventTrans.ItemId == “OL-2500” join  inventDimgroup by inventBatchId where inventDim.InventDimId == inventTrans.InventDimId
{
// Our Code Here
}
Dynamic Query:
static void Vasanth_Query_Eg1(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
Qty total;
InventTrans inventTrans;
;
query = new Query(); queryBuildDataSource = query.addDataSource(tableNum(InventTrans));
queryBuildDataSource.addSelectionField(fieldNum(InventTrans,Qty),
SelectionField::Sum);
queryBuildDataSource.orderMode(OrderMode::GroupBy);
queryBuildRange = queryBuildDataSource.addRange(fieldNum(InventTrans,ItemId));
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(InventDim));
queryBuildDataSource.orderMode(OrderMode::GroupBy);
queryBuildDataSource.addSortField(fieldNum(InventDim,InventBatchId));
queryBuildDataSource.relations(true);
queryRun = new QueryRun(query);
if (queryRun.prompt())
{
while (queryRun.next())
{
inventTrans = queryRun.get(tableNum(InventTrans));
total = inventTrans.Qty;
}
}
info(strFmt(“Quantity: %1″, total));
}

RegularExpression in Axapta

Hi All,
Here i posted a small job , to explain how we use regular expressin in Axapta. Try it.
static void RegularExpression_Test()
{
int nameLength,myStringLength;
str myname = “Ashok”;
str myString = “1234″;
str formatString , formatString1;
System.Text.RegularExpressions.Regex regEx;
System.Text.RegularExpressions.Regex regEx1;
System.Text.RegularExpressions.Match regMatch;
InteropPermission permission = new InteropPermission(InteropKind::ClrInterop);
boolean retVal, retVal1;
;
nameLength = strLen(myname);
myStringLength = strLen(myString);
formatString = strfmt(@”^[0-9]{%1}”, myStringLength);
formatString1 = strfmt(@”^[a-zA-Z ]{%1}”, nameLength);
permission.assert();
//BP Deviation documented
regEx = new System.Text.RegularExpression.Regex(formatString);
regEx1 = new System.Text.RegularExpression.Regex(formatString1);
regMatch = regEx.Match(myString);
retVal = regMatch.get_Success();
print retVal; // Returns True;
regMatch = regEx1.Match(myname);
retVal1 = regMatch.get_Success();
print retVal1 // Returns True;
}

“Go To Main Table” Option in our Axapta

Hi All,
We all know about “Go To Main Table” Option in our Axapta Forms and Tables. Here I wrote one sample for how to get that functionality in our forms or table. This is done by three ways:
1) By EDT Relations
2) By using JumpRef method
3) By using FormRef property in Table
EDT Relations:
If you use an EDT in tables which have relation with some other table fileds, that time you can able to navigate the main table or main form.
FormRef Property:
Select the Table and go to properties and select the required form in the FormRef property.
JumpRef method:
If you are not having that option, simply write a override the JumpRef method in a field of DataSource or in a Form Control. Here i show you a sample jumpRef method code:
public void jumpRef()
{
Args args;
MenuFunction menuFunction;
;
args = new Args();
menuFunction = new MenuFunction(menuitemDisplayStr(“FormName”), MenuItemType::Display);
args = new Args(menuFunction.object());
args.caller(element);
args.record(“RecordName”); // to be a datasource which added in the current form
menuFunction.run(args);
}
In the form init() which we called here, we have to check the condition whether the form is called by any dataset or not.................!!!