Lookup on PersonnelNumber and Name in Ax2012

In Ax2009 EmplTable now replaced with hcmWorker Table. To get emplid and emplname below is the lookup code:
public void lookup()
{
    HcmWorker               hcmWorker;
    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(hcmWorker.TableId, this);
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource = query.addDataSource(hcmWorker.TableId);

    queryBuildDataSource.addSortField(fieldNum(HcmWorker, PersonnelNumber));

    sysTableLookup.addLookupfield(fieldNum(HcmWorker, PersonnelNumber));
    sysTableLookup.addLookupfield(fieldNum(HcmWorker, Person));

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

Happy - Daxing :)-

AX 2012: Enabling Allow Check Reuse for Canadian Environments & India also....!!

Per TechNet article (CAN, USA) Reuse a check number [AX 2012] environments that are on AX 2012 with the Feature Pack have the ability to enable the Allow Check Reuse feature in Cash and Bank Management Parameter for US and Canadian Companies. I wanted to provide information that will allow you to enable the Allow Check Reuse feature for Canadian Companies if you have updated to AX 2012 Feature Pack and do not see the option to enable Allow Check Reuse in Cash and Bank Management Parameters for your Canadian company.
In order to enable the Allow Check Reuse for Canadian Companies the CountryRegionCodes that are part of the BankParameters table may need to be updated via the AOT. Please note that this is not a documented/tested HotFix so making the below changes will be at you own risk and testing should be done in a Sandbox environment prior to applying to a production environment.

1. Open the AOT (Ctrl+D).
2. Expand Data Dictionary
3. Expand Tables.
4. Locate the BankParameters table.
5. Expand the BankParameters table.
6. Expand Fields.
7. Click on AllowCheckReuse
8. In the Properties pane locate CountryRegionCodes (US should be listed by default)
9. Add IN to the CountryRegionCodes along with US,CA (US, CA,IN).
10. Compile to bring the changes into the environment.

Below is a screenshot showing the properties window where the changes need to be made.

Already there are US,CA pls add IN (India) also.
Daxing :)

fetch data and time from UTCDateTime control in Ax2012

public static void testDateTimeConversion()
{
    utcDateTime               dateTime;
    date                             dateInUserTimeZone;
    TimeOfDay                 timeInUserTimeZone;

    dateTime = DateTimeUtil::utcNow();

   dateInUserTimeZone = DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(dateTime,  DateTimeUtil::getUserPreferredTimeZone()));

    timeInUserTimeZone = DateTimeUtil::time(DateTimeUtil::applyTimeZoneOffset(dateTime, DateTimeUtil::getUserPreferredTimeZone()));

    dateTime = DateTimeUtil::newDateTime(dateInUserTimeZone, timeInUserTimeZone, DateTimeUtil::getUserPreferredTimeZone());
}

Happy Daxing :)

Convert Number to string in Ax 2012

Examples
Num2Str(12345.6,10,2,2,1)
returns "12.345,60".
Num2Str(12345.6,1,0,1,0)
returns "12346".

Convert Between utcdatetime and System.DateTime in AX 2012

static void JobDateTimeGlobalMarshal(Args _args)
{
    System.DateTime netDttm;
    utcdatetime xppDttm;
    str xppString;
    ;
    xppDttm = 2007-06-05T23:22:21; // ISO standard format.
   
    // Convert X++ to .NET.
    netDttm = Global::utcDateTime2SystemDateTime(xppDttm);
   
    // Convert .NET to X++.
    xppDttm = Global::CLRSystemDateTime2UtcDateTime(netDttm);
   
    xppString = DateTimeUtil::toStr(xppDttm);
    info("xppDttm: " + xppString);
}

Binding for buffer allocation space - error in Ax2009

While defining record level security for the user group's if there are too many queries instead of specifying based on customer group if user's define based on customer id etc.. and too many wild characters like (*). then client machine performance will be very slow and system may hangsup and some times u get an errror like "binding for buffer allocation space".

Thanks,
Daxing :)

Lookup on breakdown structure defined on a perticular projectid:

For a perticular projectid there can be any number of work breakdown structure defined based on activityid. If you develop a new table and form and get that plan names besed on selected project then : Here NewPaymentPlanTable is the new table.

Below code is on form control - lookup method:

public void lookup()
{
HierarchyTreeTable                  hierarchyTreeTable,hierarchyTreeTable1;
ProjTable                                  projTable1;
Query                                       query = new Query();
SysTableLookup                      sysTableLookup;
projTable1 = ProjTable::find(NewPaymentPlanTable_ProjId.text());
super();
select firstonly Name,ElementNumber from HierarchyTreeTable where HierarchyTreeTable.Name == projTable1.ProjId;
select firstOnly ElementNumber from hierarchyTreeTable1 where hierarchyTreeTable1.ParentElementNumber == hierarchyTreeTable.ElementNumber;
sysTableLookup = SysTableLookup::newParameters(tableNum(hierarchyTreeTable), this);
// Add name field to be shown in the lookup form.
sysTableLookup.addLookupfield(fieldNum(HierarchyTreeTable, SiblingNumber));
sysTableLookup.addLookupfield(fieldNum(HierarchyTreeTable, Name));
sysTableLookup.addLookupfield(fieldNum(HierarchyTreeTable, ElementNumber));
sysTableLookup.addLookupfield(fieldNum(HierarchyTreeTable, ElementNodeType));
query = new Query();
query.addDataSource(tableNum(HierarchyTreeTable)).addRange(fieldNum(HierarchyTreeTable, ParentElementNumber)).value(queryValue(hierarchyTreeTable.ElementNumber));
sysTableLookup.parmQuery(query);
// Perform the lookup.
sysTableLookup.performFormLookup();
}

lookup for SalesId based on customer id in the payment journal ->AR (Ax2012)

public void lookup()
{
    CustTable                           custTable;
    SalesTable                          salesTable;
    Query                               query = new Query();
    SysTableLookup                      sysTableLookup;
    LedgerDimensionAccount              ledgerDim;
    ledgerDim = DimensionStorage::getDynamicAccount(LedgerJournalTrans_AccountNum.valueStr(), LedgerJournalACType::Cust);
    custTable = CustTable::find(DimensionAttributeValueCombination::find(ledgerDim).DisplayValue);
    super();
    // Only user defined dimensions will have a financial tag category
    select firstonly CustAccount from salesTable where salesTable.CustAccount == custTable.AccountNum;
    sysTableLookup = SysTableLookup::newParameters(tableNum(SalesTable), this);
    // Add name field to be shown in the lookup form.
    sysTableLookup.addLookupfield(fieldNum(SalesTable, SalesId));
    sysTableLookup.addLookupfield(fieldNum(SalesTable, CustAccount));
    query = new Query();
    query.addDataSource(tableNum(SalesTable)).addRange(fieldNum(SalesTable, CustAccount)).value(queryValue(custTable.AccountNum));
    sysTableLookup.parmQuery(query);
    // Perform the lookup.
    sysTableLookup.performFormLookup();
}

AX freeze up, “Cannot create a record in Number sequence list (NumberSequenceList)”

When I went into one record in the AP journal, I would get this message: Cannot create a record in Number sequence list (NumberSequenceList). … The record already exists. On other record in AP, I could get into it, but when I clicked any buttons, AX Client would freeze. I had to kill the process to exit AX.

This was only happening in one company, I could not recreate the problem in the dev or test environments. I tried viewing the following logs, which were no help: Windows Event Log, AX logs, SQL Logs.

 

It seems as if the table SysUtilElementsLog is some kind of table locking/latching mechanism that was added in AX 2009 (possibly for helping manage resources shared by load-balanced servers or something). This table contains a list of the tables (presumably, ones that would have a lot of contention and you might want to manage via some locking scheme). The most popular tables on the list contained the highest numbers. I tried truncating the values (stop AOS, backup the db, set "UseCount =1" for all rows, start AOS). That seemed to help for me, but not the other users. #fail.

Then, I thought, maybe it was a permission issue. (try….) Nope. #fail.

I did notice that, every time I restarted the AOS, I could get in and do stuff, but it always started by giving me a message (ONCE) about Cannot create a record in Number sequence list (NumberSequenceList)… I went into Basic, Number Sequence, and did a cleanup of the sequence in question and the error went away. It seemed odd that it kept coming up every time I restarted the AOS. It seemed like that sequence wasn’t incrementing or it got incremented but not saved to the database.

I did some more looking around in AX until I found the following tables:

- NumberSequenceList - the settings for all number sequences

- NumberSequenceTable - the state & locking table for number sequences

- NumberSequenceTTS - the transaction table for number sequence locks

If you query SQL server with the following query:

SELECT * FROM NumberSequenceList WHERE DataAreaID = ‘[company with the locking problem]‘ AND TransID > 0

This will show you the tables with open/active transactions. Any one of these may be locking things up.

You really want to keep an eye on records with a [Status] = 1. This means that the record is in use and may be blocking someone else from using it.

Compare the results of that query to this one:

SELECT * FROM NumberSequenceTTS WHERE DataAreaID = ‘[company with the locking problem]‘

EVERY record in this table represents an open transaction and may result in a lock that blocks other users or freezes up the AX Client for people.

It is particularly bad if there is a row in the NumberSequenceList that does not have a matching record in the NumberSequenceTTS table (matched on TransID). This would mean there is a lock in one table and no reference by which to remove that lock. Basically an orphaned lock. Pure evil!

It just happened that my orphaned record was on the NumberSequenceList but not the NumberSequenceTTS table.

The way I resolved the problem and cleared the log jam was to :

1. Stop the AOS

2. Backup the SQL database

3. Removed the orphaned record from the NumberSequenceList Table

4. Start the AOS

All of the other records that were in the NumberSequenceTTS table were, apparently, waiting on the orphaned record to clear, because they all got processed and cleared out.

The system was working, no lockups or freeze ups.

Please, the action status its " Freed "

 

 

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.