Access D365 FinOps Services with OAuth 2.0

So before we jump in how to, we need to understand some basics how OAuth2 works.

well basically OAuth2 is one of the authentication out there, the purpose is to is validate and authenticate with Azure AD, permission granted or not to access the apps. if granted, it will generate “TOKEN”.

now we heading to D365FinOps , in D365 FinOps we have users and all of its security roles, so basically user that can call D365FinOps services (example OData or Custom Services) via OAuth 2.0 should be only legitimate D365 FinOps users.

There is some grant_type for OAuth 2.0 that works with D365 FinOps :
1. Authorization Code : when user access or call some services, they need to provide login information . (works with D365 FinOps, just provide user and password when microsoft login window popup) , then TOKEN generated.
ref : https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-oauth2-auth-code-flow

2. Password Credentials : the user and password credentials is hardcoded in the call, so no popup window login, then TOKEN generated afterward.
ref : https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-oauth-ropc

other grant_type seems not working wih D365 FinOps because cant provide legitimate D365 FinOps User in the call parameter.

Now let us try shall we ??
the summary for the tutorial (will update in next blog post) :

1. Setup Apps Registrations in Azure AD and Setup in D365 FinOps
2. Generate Oauth2 Token using grant_type Authorization Code
3. Generate Oauth2 Token using grant_type Password Credentials
4. Testing OAuth2 Token to call OData Services
5. Create Simple D365 FinOps custom services
6. Testing Oauth2 Token to call D365 FinOps custom services.

~fanddy

get filter value from grid

example code :

public void getQueryValue()
{
    QueryBuildRange range;
    int ct, i;

    ct = PMTRoutingTable_ds.queryRun().query().dataSourceTable(tablenum(PMTROutingTable)).rangeCount();

    for (i=1 ; i<=ct; i++)
    {
        range = PMTRoutingTable_ds.queryRun().query().dataSourceTable(tablenum(PMTROutingTable)).range(i);
        info(strfmt('Range Field – %1, Value – %2',range.AOTname(),range.value()));
    }
}

references :
https://jkmsdax2012.wordpress.com/2015/05/05/get-the-ranges-and-values-from-query-dynamicaot-using-x-in-ax-2009/

error : “Target principal name is incorrect” when running SSRS AX 2012 R3

Example of the error :
Capture3

In my case , the issue occurred because outdated *.AXC files (ax configuration files) in report server folder.  error cause is still unknown, my client not provided details on what changes they did in the ax server.

How to solve this issue :

  1. Create New AX Configuration Files, refresh WCF (using Refresh Configuration).

Capture3

  1. Export it to a file and rename it to Microsoft.Dynamics.AX.ReportConfiguration

Capture

  1. then replace the file with new one in folder path C:\Program Files\Microsoft SQL Server\MSRS11.\Reporting Services\ReportServer\binCAPTURE1

Get purchase invoice id from packing slip trans (VendPackingSlipTrans)

static void ExampleJob(Args _args)
{
    VendPackingSlipTrans packingSlipTrans;
    VendInvoicePackingSlipQuantityMatch VendInvoicePackingSlipQuantityMatch;
    vendInvoicetrans vendInvoiceTrans;

    select firstOnly packingSlipTrans where packingSlipTrans.RecId == 5637155083;

    select firstonly vendInvoiceTrans
    join VendInvoicePackingSlipQuantityMatch
        where VendInvoiceTrans.SourceDocumentLine == VendInvoicePackingSlipQuantityMatch.InvoiceSourceDocumentLIne
            && VendInvoicePackingSlipQuantityMatch.PackingSlipSourceDocumentLine == packingSlipTrans.SourceDocumentLine;

    info(VendInvoiceTrans.InvoiceId);

}

credited to : EYO

Missing reference for “display method returning object” error when build form in AX7

Error example :
When build the project contains new custom form and datasource “CustInvoiceJour” , i encountered error missing module reference for EDT logisticsAddressing.

capture

Solutions :
Find the object using search function, then you can see the model reference for that object. After that, update your model parameters and checklist the missing references.

capture

Note:
The new model that my project referenced to is using Extension Model approach.

Consuming webservices (SOAP) from AX example code

public static container UserInfoByIDRequest(str _storeName, str _idType, str _idNo, str _extRef,str _username = "",str _password = "")
{
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.Summary summary;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.RemittanceClient svcClient;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDRequest request;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDResponse response;

    CLRObject clrObject;
    System.Exception ex;
    str errorCode, errorMesg;
    container  returnCon;
    boolean    canConnectAPI;
    str        errorCanConnectAPI;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        clrObject = CLRInterop::getType("TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.RemittanceClient");
        svcClient = AifUtil::createServiceClient(clrObject);

        request = new TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDRequest();

        //if no username or password provided, get from AX
        if(!_username || !_password)
        {
            _username = TIDIDSSimponiWebConsumeMethod::getUserNamePasswordStore(_storeName).IPMS_GaleriUserName;
            _password = TIDIDSSimponiWebConsumeMethod::getUserNamePasswordStore(_storeName).IPMS_GaleriPassword;
        }

        request.set_Username(_username);
        request.set_Password(_password);
        request.set_StoreName(_storeName);
        request.set_IdType(_idType);
        request.set_IdNo(_idNo);
        request.set_ExtRef(_extRef);

        //consuming webservice API
        response = svcClient.GetUserInfoByID(request);
        summary = response.get_Summary();

        //unpack to variable
        errorCode = summary.get_ErrorCode();
        errorMesg = summary.get_ErrorMessage();

        //packing all variable to container and return it
        returnCon += errorCode;
        returnCon += errorMesg;



        //sucess connect to webAPI
        canConnectAPI = true;

    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(ex.ToString());

        canConnectAPI = false;
        errorCanConnectAPI =ex.ToString();
    }

    return [canConnectAPI, errorCanConnectAPI, returnCon];
}

set WaterMark on textbox c# AX POS

extension method :

public static class TextBoxWatermarkExtensionMethod
{
     private const uint ECM_FIRST = 0x1500;
     private const uint EM_SETCUEBANNER = ECM_FIRST + 1;

     [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = false)]
     private static extern IntPtr SendMessage(IntPtr hWnd, uint Msg, uint wParam, [MarshalAs(UnmanagedType.LPWStr)] string lParam);

     public static void SetWatermark(this TextBox textBox, string watermarkText)
     {
        SendMessage(textBox.Handle, EM_SETCUEBANNER, 0, watermarkText);
     }
}

how to use it example on form constructor :

public TIDFrmIDSKirimUang()
{
     InitializeComponent();

     TextBoxWatermarkExtensionMethod.SetWatermark(txtTNoHP,"0815xxxxxxx");
     TextBoxWatermarkExtensionMethod.SetWatermark(txtNoHP,"0815xxxxxxx");
}

reference :
http://vidmar.net/weblog/archive/2008/11/05/watermarked-textbox-in-windows-forms-on-.net.aspx

How to access AX 7 from other machine (preview VM Version : AX7 Update 1)

version : AX7 Update 1.
When we need multiple users to test the new AX7 , it’s imposible to ask everyone to run VM on their local because not everyone have the specs to run the VM. So what i do is to make AX7 available on local network so our team can test its new features.

Step by step how to do it :
1. Setup network Bridged Adapter on VM settings

Capture

2. setup your network ip on inside your VM,  (cmd : ipconfig /all on host machine to view ip config)
Capture

3. Edit your local host file on C:\Windows\System32\drivers\etc . add AX 7 VM IP and usnconeboxax1aos.cloud.onebox.dynamics.com

Capture

4. Copy and install SSL certificate from VM to avoid HTTPS error when open the link. How to copy certificate : open IIS -> Website (AOSServiceDSC) -> Site Binding (on right pane) -> Edit Site Binding -> View SSL Certificate -> Details -> Copy to file .

Capture

5. install SSL certificate from VM to avoid HTTPS error when open the link. How to install certificate on the chrome.  url help link:

https://support.globalsign.com/customer/portal/articles/1211541-install-client-digital-certificate—windows-using-chrome

 

6. Now open your browser, and open the ax 7 at url at https://usnconeboxax1aos.cloud.onebox.dynamics.com , login using your credential, and voila !! done..

Capture

marking inventTrans example code

public static void FNDMarkInventTrans(InventTransId _inventTransId,InventTransId _refInventTransId,InventQty _qtyTomark)
{
    InventTrans issueInventTrans;
    TmpInventTransMark tmpInventTransMask;
    Map mapMarkNow;
    container con;
    real qty;
    Map mapTmp;
    MapEnumerator mapEnumerator;

    InventTransOriginId issueInventTransOriginId =
        InventTransOrigin::findByInventTransId(_inventTransId).RecId;

    InventTransOriginId receiptInventTransOriginId =
        InventTransOrigin::findByInventTransId(_refInventTransId).RecId;

    InventQty qtyToMark = _qtyTomark;

    ttsBegin;

    issueInventTrans = InventTrans::findByInventTransOrigin(
        issueInventTransOriginId);

    [con, qty] = TmpInventTransMark::packTmpMark(
        InventTransOrigin::find(issueInventTransOriginId),
        issueInventTrans.inventDim(),
        issueInventTrans.Qty);

    mapTmp = Map::create(con);
    mapEnumerator = mapTmp.getEnumerator();
    while (mapEnumerator.moveNext())
    {
        tmpInventTransMask = mapEnumerator.currentValue();

        if (tmpInventTransMask.InventTransOrigin == receiptInventTransOriginId)
        {
            tmpInventTransMask.QtyMarkNow = qtyToMark;
            tmpInventTransMask.QtyRemain -= tmpInventTransMask.QtyMarkNow;
            mapMarkNow = new Map(Types::Int64, Types::Record);
            mapMarkNow.insert(tmpInventTransMask.RecId, tmpInventTransMask);

            TmpInventTransMark::updateTmpMark(
                issueInventTransOriginId,
                issueInventTrans.inventDim(),
                -qtyToMark,
                mapMarkNow.pack());
        }
    }

    ttsCommit;
}

reference code :
https://community.dynamics.com/ax/b/dynamicsaxnotesonthecuffs/archive/2013/01/08/marking-from-code-part-2

get Available budget example code

source code from form : BudgetControlStatistics
Capture

example code :

  static void TIDF_getBudgetAvailable(Args _args)
{
    
    //class and table
    BudgetControlStatisticsManager budgetControlStatsManager = new BudgetControlStatisticsManager();
    
    //table
    BudgetTmpControlStatistics  currentBudgetTmpControlStatistics;
    BudgetCycle                 budgetCycle;
    BudgetCycleTimeSpan         budgetCycleTimeSpan;
    FiscalCalendar              fiscalCalendar;
    DimensionAttributeValueCombination dimensionAttributeValueCombination;
    DimensionHierarchy          dimensionHierarchy;
    
    //variable
    DimensionValue  dim1;
    container       conDimValue;
    MainAccountNum  mainAccountNum;
    RecId           ledgerDimension;
    
    //financial dimension
    mainAccountNum = "6100-1001";
    dim1           = "5102";
    
    select firstonly dimensionAttributeValueCombination 
    where 
        dimensionAttributeValueCombination.DisplayValue == strFmt("%1-%2",mainAccountNum,dim1)
    &&  dimensionAttributeValueCombination.LedgerDimensionType == LedgerDimensionType::BudgetControl;
    
    //budget cycle
    fiscalCalendar   = fiscalCalendar::findByCalendarId("2016");
    BudgetCycleTimeSpan = budgetCycleTimeSpan::findByNameAndCalendar("SEM",fiscalCalendar.RecId);
    BudgetCycle =  budgetCycle::findBudgetCycleByDate(BudgetCycleTimeSpan.RecId,today());
    
    currentBudgetTmpControlStatistics = budgetControlStatsManager.fillBudgetTmpControlStatistics(Ledger::current(),
                                                                                                'ALL',
                                                                                                BudgetControlStatisticsPeriodOption::FiscalPeriod,
                                                                                                BudgetBalanceType::Accumulated,
                                                                                                BudgetCycle.RecId,
                                                                                                dimensionAttributeValueCombination.RecId,
                                                                                                0,
                                                                                                0,
                                                                                                true,
                                                                                                NoYes::No,
                                                                                                true);
    
   while select  currentBudgetTmpControlStatistics
   {
        info(strFmt("%1 -- %2 ",currentBudgetTmpControlStatistics.PeriodStartDate,currentBudgetTmpControlStatistics.TotalFundsAvailableAmountMST));
   }

}

result :
Capture

Auto Approve Workflow by Code x++

Example code :

static void TIDF_autoApproveWFOvertime(Args _args)
{
    WorkflowWorkItemTable WorkflowWorkItemTable;

    while select WorkflowWorkItemTable
    where
        WorkflowWorkItemTable.UserId == '32501112'
    &&  workflowWorkItemTable.Type == WorkflowWorkItemType::WorkItem
    &&  workflowWorkItemTable.Status == WorkflowWorkItemStatus::Pending
    &&  WorkflowWorkItemTable.RefTableId == tableNum(HrsOvertime)
    {
        WorkflowWorkItemActionManager::dispatchWorkItemAction(
                                    WorkflowWorkItemTable,
                                    "Auto Approve by ADMIN", // comment
                                    '32501112', // << user ID
                                    WorkflowWorkItemActionType::Complete,
                                    "HRSOvertimeApprove", // << menu item action approval workflow
                                    false); //is not web Menu Item
    }

}

Capture

Resume Workflow from x++ example code

example code :

static void TIDF_resumeWFOverTime(Args _args)
{
    WorkflowTrackingStatusTable WorkflowTrackingStatusTable;
    HrsOvertime                 hrsOverTime;
    
    
    while select WorkflowTrackingStatusTable 
    where 
        WorkflowTrackingStatusTable.TrackingStatus == WorkflowTrackingStatus::Faulted 
    && WorkflowTrackingStatusTable.WorkflowType == WorkflowTrackingStatusWorkflowType::Workflow
    && WorkflowTrackingStatusTable.ContextTableId == tableNum(HrsOvertime)
    {
        //consistency check valid data
        select firstOnly hrsOverTime where hrsOverTime.RecId == WorkflowTrackingStatusTable.ContextRecId;
        if(hrsOverTime)
        {
            Workflow::resumeWorkflow(WorkflowTrackingStatusTable.CorrelationId);      
        }
    }
}

source : https://community.dynamics.com/ax/f/33/t/126119

Workflow status for selected record display method

Display WorkflowTrackingStatus WFStatus()
{
    WorkflowTrackingStatusTable   WorkflowTrackingStatusTable;
    ;
    select firstOnly  WorkflowTrackingStatusTable 
    order by WorkflowTrackingStatusTable.RecId DESC
    where 
        WorkflowTrackingStatusTable.ContextTableId         == this.TableId
    &&  WorkflowTrackingStatusTable.ContextRecId           == this.RecId;
    
    
    return WorkflowTrackingStatusTable.TrackingStatus;
}

get USER ID and Security Role from HcmWorker

Example Code :

display Notes TIDgetUserIdAndRoles()
{
    DirPerson               DirPerson;
    DirPersonUser           DirPersonUser;
    SecurityUserRole        SecurityUserRole;
    SecurityRole            SecurityRole;
    Notes                   returnNotes;
    str value;
    ;


    DirPerson       = DirPerson::find(this.Person);
    DirPersonUser   = DirPersonUser::findWorker(this.RecId);
    returnNotes      = strFmt("User ID : %1 \n\n ",DirPersonUser.User);
    returnNotes      = strFmt("%1Security Roles : \n",returnNotes);
    
    while select * from SecurityUserRole 
        where SecurityUserRole.User == DirPersonUser.User
    join SecurityRole
        where SecurityRole.RecId == SecurityUserRole.SecurityRole
    {
        if(subStr(SecurityRole.Name,1,1) == '@')
        {
            value = SysLabel::labelId2String2(SecurityRole.Name, companyinfo::languageId());
            returnNotes = strFmt("%1 %2\n",returnNotes,value);
        }
        else
        {
            returnNotes = strFmt("%1 %2\n",returnNotes, SecurityRole.Name);
        }
    }
    
    return returnNotes;
}

get Report to Position Worker Name with Employment Recid SQL Query

Example code :

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
	(select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
	INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
	AND HCMPOSITION.RECID = 
		(select TOP 1 ParentPosition from  hcmPositionHierarchy 
			INNER JOIN  hcmPositionHierarchyType on hcmPositionHierarchy.PositionHierarchyType = hcmPositionHierarchyType.RECID AND hcmPositionHierarchyType.HierarchyType = 0
			where hcmPositionHierarchy.Position = 
				(select top 1 POSITION from HCMPOSITIONWORKERASSIGNMENT  
				INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
				where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO 
				AND HCMPOSITIONWORKERASSIGNMENT.WORKER = 
					(select TOP 1 WORKER from hcmEmployment where hcmEmployment.RECID = 5637152077)))
	where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

get worker name from position with SQL Queries

example code :

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
	(select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
	INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
	AND HCMPOSITION.POSITIONID = 'P0003' 
	where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

Job upload production order

//coded by fanddy
static void uploadProductionOrder(Args _args)
{
    // Progress Bar Variables
    #AviFiles
    SysOperationProgress        _ProgressBar = new SysOperationProgress();

    // Excel Variables
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    str                     Filename;
    COMVariantType          type;
    int                     row;
    FilenameFilter filenameFilter = ['*.xlsx,*.xls'];

    //variable
    ProdId              prodId;
    ItemId              itemid;
    Qty                 qtySched;
    TransDate           StartDate,endDate,deliveryDate,bomDate;
    InventSiteId        inventSiteId;
    InventLocationId    inventLocationId;
    ProdStatus          prodStatus;
    ProdBackStatus      prodBackStatus;
    ProdReservation     prodReservation;
    BOMId               bomId;

    //tables
    ProdTable       prodtable;
    InventTable     inventTable;
    InventDim       inventDim;
      // 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 '';
    }




    filename = WinAPI::getOpenFileName(0, filenameFilter, '', '');
    if (filename=='')
    {
        warning('Filename must be specified correctly.');
        return;
    }

    //Filename = "C:\\prodorder.xls";
    application = SysExcelApplication::construct();
    workbooks   = application.workbooks();

    // gets the default ExchangeType RecId
    try
    {
        workbooks.open(Filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();
    row         = 1;

    // Start Progress Bar
    _ProgressBar.setCaption("Upload Production Order...");
    _ProgressBar.setAnimation(#AviTransfer);
    ttsBegin;
    do
    {
        //Incrementing the row line to next Row
        row++;

        _ProgressBar.setText(strFmt('Processing %1...', row));
        prodId              = COMVariant2Str(cells.item(row,1).value());
        itemid              = COMVariant2Str(cells.item(row,2).value());
        qtySched            = cells.item(row,3).value().double();
        startDate           = cells.item(row,4).value().date();
        endDate             = cells.item(row,5).value().date();
        inventSiteId        = COMVariant2Str(cells.item(row,6).value());
        inventLocationId    = COMVariant2Str(cells.item(row,7).value());
        prodStatus          = str2enum(ProdStatus,COMVariant2Str(cells.item(row,8).value()));
        prodBackStatus      = str2enum(ProdBackStatus,COMVariant2Str(cells.item(row,9).value()));
        deliveryDate        = cells.item(row,10).value().date();
        prodReservation     = str2enum(prodReservation,COMVariant2Str(cells.item(row,11).value()));
        bomDate             = cells.item(row,12).value().date();
        bomId               = COMVariant2Str(cells.item(row,13).value());


        // Initialize InventTable
        inventTable.clear();
        inventTable = inventTable::find(itemid);

        // Initialize the base values
        prodtable.clear();
        prodtable.initValue();
        prodtable.initFromInventTable(inventTable);
        prodtable.ProdId                = prodId;
        prodtable.ItemId                = inventTable.ItemId;
        prodtable.QtySched              = qtySched;
        prodtable.SchedStart            = StartDate;
        prodtable.SchedEnd              = endDate;
        prodtable.DlvDate               = deliveryDate;
        prodtable.RemainInventPhysical  = qtySched;
        prodtable.BOMDate               = today();
        prodtable.ProdStatus            = prodStatus;
        prodtable.BackorderStatus       = prodBackStatus;
        prodtable.Reservation           = prodReservation;

        // Initialize InventDim (Obrigatory)
        inventDim.clear();
        inventDim.InventSiteId          = inventSiteId;
        inventDim.InventLocationId      = inventLocationId;
        inventDim                       = inventDim::findOrCreate(inventDim);
        prodtable.InventDimId           = inventDim.inventDimId;

        // Set the active BOM and Route
        if(!bomId)
        {
            prodtable.BOMId = BOMVersion::findActive(prodtable.ItemId,
                                                        prodtable.BOMDate,
                                                        prodtable.QtySched,
                                                        inventDim).BOMId;
        }
        if(!prodtable.RouteId)
        {
            prodtable.RouteId = RouteVersion::findActive(prodtable.ItemId,
                                                         prodtable.BOMDate,
                                                         prodtable.QtySched,
                                                         inventDim).RouteId;
        }

        // Initialize BOMVersion
        prodtable.initBOMVersion();
        // Initialize RouteVersion
        prodtable.initRouteVersion();
        //Use ProdTableType class to create the production order
        prodtable.type().insert();
        

        print (strFmt('Line number %1 - Production order %2 - Item ID %3', row,prodtable.ProdId, itemId));

        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    
    ttsCommit;


    // quits the application
    application.quit();

    info('Upload success');
}

Hands on AX7 version CTP8 (Technical Preview)

Just got AX7 VM yesterday. I can’t say much due to the Microsoft Confidential Policy about this Tehnical Preview of AX7. But below is the screenshot of AX7. And i tried to develop a simple form on USR layer too.

 

Capture14

Capture3

Capture2

Capture11

Capture12

Capture6

Capture7

Capture10

Capture15

Capture

how to access Technical Preview and all the presentation about AX7 from microsoft :
https://mbs.microsoft.com/customersource/global/AX/news-events/news/Microsoft_Dynamics_AX_Public_Preview

Hello AX 7 !!

Capture

AX7 is becoming hot topic on several technical groups forum. The most epic changes is AX7 development now using Visual Studio and not using MorphX like the previous version of AX.

AX7 Development code still using x++ , but the ways of the developing is similar to .NET application development. And now it doesn’t need direct connection to AOS service and all the source code is saved as XML file on the project folder. But it still have the same AX layers model architecture.

ax-7-preview_499x275

dynaxblog2

The UI itself is Metro Style and beautiful. It doesn’t looks like an ERP and more like some mobile apps style. and from what i saw on the introduction video from Microsoft, it is Web Based now and the AX AOS hosted on IIS.

Enterprise portal has been removed on AX7. I personnaly agree with that. Customization on enterprise portal has been very painful and not efficient, you need to incremental CIl, deploying more than 1 object. and sometimes need to restart IIS and AOS for just displaying updated webpage.

anyway, Hello AX7 !!. and welcome to hellodax as new category.

full links on introduction video :
https://mbspartner.microsoft.com/AX/Videos/829

source screenshot :
https://sbsgroupusa.files.wordpress.com/2015/12/dynaxblog2.png?w=610
http://www.to-increase.com/media/524952/ax-7-preview_499x275.jpg

Hide(remove) menu item action approval workflow

Some of our clients don’t need “Delegate” or “Deny” on workflow,
so here below is how to hide Workflow Approval menu items,

just remove “ActionMenuItems” and “WebActionMenuItems” for web on the Workflow Approval AOT properties.
Capture.PNG

do Full CIL and Incremental CIL after remove the properties,
menu items will be remove from workflow user approval.

result
Capture

check user workflow hierarchy by code x++

i found a nice code for checking user workflow hierarchy approver and solved the related error.

static void navax_workflowHierarchyTester(Args _args)
{
    WorkflowTypeName                    workflowTemplateName = 'TIDEmploymentLeave';
    //TrvExpTrans                         trvExpTrans;
    //TrvExpNumber                        trvExpNum = '000015'; //Expense id

    HcmEmploymentLeave                  hcmEmploymentLeave;
    str                                 nodeId = '23511014'; //Starting worker id
    WorkflowHierarchyLevel              level = 0;
    WorkflowContext                     workflowContext;
    SysWorkflowTable                    workflowTable;
    WorkflowLimitHierarchyProvider      workflowLimitHierarchyProvider;
    WorkflowHierarchyProviderNode       workflowHierarchyProviderNode;
    HRPWorkerLimit                      workerLimit = new HRPWorkerLimit();
    HcmWorker                           hcmWorker;
    DirPersonUser                       dirPersonUser;
    RefRecId                            hcmPositionRecId;
    HcmPosition                         hcmPosition;
    container                           spendingCon, approvalCon;
    UserId                              userId;

    select firstOnly workflowTable
        where workflowTable.TemplateName == workflowTemplateName;

    select firstOnly hcmEmploymentLeave
        where hcmEmploymentLeave.HRSLeaveReqId ==  &quot;LV-00635&quot; ;

    workflowContext = WorkflowContext::newWorkflowContext(
        curext(),
        tableNum(hcmEmploymentLeave),
        hcmEmploymentLeave.RecId,
        workflowTable.WorkflowCorrelationId);

    workflowLimitHierarchyProvider = new WorkflowLimitHierarchyProvider();

    //Level 1
    while (nodeId &amp;&amp; level &lt; 20) //20 is just a fall back. In case it goes into an endless loop.
    {
        workflowHierarchyProviderNode   = workflowLimitHierarchyProvider.getNextNode(nodeId, level, workflowContext);
        nodeId                          = workflowHierarchyProviderNode.getnodeId();
        hcmWorker                       = HcmWorker::findByPersonnelNumber(nodeId);
        userId                          = DirPersonUser::findParty(hcmWorker.Person).User;
        hcmPositionRecId                = HcmWorker::getPrimaryPosition(hcmWorker.RecId);
        hcmPosition                     = HcmPosition::find(hcmPositionRecId);

        spendingCon = workerLimit.getWorkerSigningLimit(
            hcmWorker.RecId,
            SourceDocumentRelationType::ExpenseReport,
            HRPLimitType::Spending,
            hcmPosition.PositionId);

        approvalCon = workerLimit.getWorkerSigningLimit(
            hcmWorker.RecId,
            SourceDocumentRelationType::ExpenseReport,
            HRPLimitType::Approval,
            hcmPosition.PositionId);

        info(strFmt(&quot;UserId: %1 | WorkerId: %2 | PositionId: %3 | ReportsToPosition: %4 | SpendingLimit: %5 | ApprovalLimit: %6&quot;,
            userId,
            hcmWorker.PersonnelNumber,
            hcmPosition.PositionId,
            hcmPosition.reportsToPosition(),
            con2Str(spendingCon),
            con2Str(approvalCon)
            ));

        level++;
    }
}

source :
https://community.dynamics.com/ax/b/dynamicsnavax/archive/2015/06/08/workflow-hierarchy-assignment-common-errors-explained-and-tester-job-ax-2012

Job upload fixed assets master data with value models example code

Job

static void TIDF_uploadFAwithValueModels(Args _args)
{
    AssetId             assetId;
    AssetGroupId        assetGroupId;
    AssetName           assetName;
    AssetLocationId     assetLocationId;
    AssetServiceLife    assetServiceLife;
    AssetLifeTimeRest   assetLifeTimeRest;
    AssetPostingProfile assetPostingProfile;
    AssetBookId         assetBookId;
    TransDate           depreciationStartDate,LastDepreciationDate,AcquisitionDate;
    RecId               recIdDefaultDimension;
    AssetLongDescription AssetLongDescription;

    AxAssetTable        axAssetTable;

    //table
    AssetTable          assetTable;
    AssetGroup          assetGroup;
    AssetLocation       assetLocation;
    AssetBook           assetBook;
    AssetLedger         assetLedger;

    container   financialDimensionFromExcel;
    container   conDimensionName;

    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename;
    ;

    startLengthyOperation();
    //variable yang diisi manual
    //C:\#TECTURA\Master Data
    filename              = "C:\\#TECTURA\\Master Data\\FA Master_Budi00.xlsx";
    assetPostingProfile   = "FA POSTING";
    depreciationStartDate = today();
    LastDepreciationDate  = today();
    AcquisitionDate       = today();

    try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();
            conDimensionName = TIDgetDimensionName();

            row = 5;

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,7).value().bStr();
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,8).value().bStr();
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,9).value().bStr();
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                //generateDefaultDimension
                recIdDefaultDimension       = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);

                //variable
                assetId           = TIDComVariant2STR(cells.item(row,2).value());
                assetGroupId      = TIDComVariant2STR(cells.item(row,1).value());
                assetLocationId   = TIDComVariant2STR(cells.item(row,6).value());
                assetName         = TIDComVariant2STR(cells.item(row,3).value());
                assetServiceLife  = str2num(TIDComVariant2STR(cells.item(row,4).value()));
                assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
                assetBookId          = TIDComVariant2STR(cells.item(row,13).value());
                AssetLongDescription = TIDComVariant2STR(cells.item(row,14).value());
                //table
                assetTable      = assetTable::find(assetId);
                assetGroup      = AssetGroup::find(assetGroupId);
                assetLocation   = AssetLocation::find(assetLocationId);

                //validation
                if(!assetGroup || !assetLocation)
                {
                    throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
                }
                //create asset Table
                if(!assetTable)
                {
                    axAssetTable    = new AxAssetTable();
                    axAssetTable.parmAssetId(assetId);
                    axAssetTable.parmAssetGroup(assetGroupId);
                    axAssetTable.parmName(assetName);
                    axAssetTable.parmLocation(assetLocationId);
                    axAssetTable.parmLocationMemo(AssetLongDescription);
                    axAssetTable.save();
                }

                //create assetBook
                assetBook = assetBook::find(assetId,assetBookId);
                if(!assetBook)
                {
                    assetBook.clear();
                    assetBook.initValue();
                    assetBook.AssetId               = assetId;
                    assetBook.BookId                = assetBookId;
                    assetBook.PostingProfile        = assetPostingProfile;
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.insert();
                }
                else
                {
                    assetBook.selectForUpdate(true);
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.update();
                }


                print strFmt('FA Group : %1 FAID : %2 Name : %3',assetGroupId,assetId,assetName);
                row++;
            }
            ttsCommit;
            info("Success");
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Fixed Asset upload Error");
        application.quit();
    }
}

TIDGetDimensionName

public static container TIDgetDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

TIDCreateDefaultDimension

static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    int                                 i;
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

TIDComVariant2STR

public static str TIDComVariant2STR(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;
}

Add AOT Fields to AOT table via X++

static void TIDF_addFieldAOT(Args _args)
{
    #AOT
    #TreeNodeSysNodeType
    
    TreeNode  tableNode = TreeNode::findNode(#TablesPath).AOTfindChild('TIDProdPlanOverViewTmp');
    AOTTableFieldList lst;
    int                i;
    ;
    
    lst = tableNode.AOTfindChild("fields");
    for(i=1;i<=31;i++)
    {
        lst.addDate(strFmt("date_%1",i));
    }
    tableNode.AOTsave();
    
}

source :
http://www.junctionsolutions.com/dynamicsax/creating-pivot-table-forms-and-reports-in-ax/

error : DNS Lookup Failed when open EP pages from another computer

Problem :

Capture

I tried to ping with Sharepoint Server IP and got response, but i cant ping with computer name. The EP itself is working fine if i open it on another computer that already joined domain.

temporary solution :
just go to drive “C:\Windows\System32\drivers\etc”
on file “Hosts” , add
example xxx.xxx.xxx.xx COMPUTERNAME
Capture

or you can set use IP on sharepoint at alternate access mapping menu :
Capture20

Convert int to color C# example code

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {
        if (e.Day.IsSelected == true)
        {
            int color = SelectedDates[e.Day.Date];

            byte[] values = BitConverter.GetBytes(color);
            if (!BitConverter.IsLittleEndian) Array.Reverse(values);

            Color myColor = Color.FromArgb(values[0], values[1], values[2]);
            e.Cell.BackColor = myColor;
        }
    }

reference :
http://stackoverflow.com/questions/6131438/mapping-an-integer-to-an-rgb-color-in-c-sharp

Filter DataSets Field Lookup Enterprise Portal example code

on AOT datasets fields method, add this code below :

void dataSetLookup(SysDataSetLookup sysDataSetLookup)
{
    List list = new List(Types::String);
    Query query = new Query();

    // Add the table to the query.
    query.addDataSource(tableNum(HcmLeaveType));

    //filter query
    query.dataSourceTable(tableNum(HcmLeaveType)).addRange(fieldNum(HcmLeaveType,ValidToCredit)).value(SysQuery::value(NoYes::Yes));

    /* uncomment if the relation is not based on reference recid
    // Specify the fields to use for the lookup.
    list.addEnd(fieldStr(HcmLeaveType,LeaveTypeId));
    list.addEnd(fieldStr(HcmLeaveType,Description));

    // Supply the set of lookup fields.
    sysDataSetLookup.parmLookupFields(list);

    // Specify the field that is returned from the lookup.
    sysDataSetLookup.parmSelectField(fieldStr(HcmLeaveType,LeaveTypeId));
    sysDataSetLookup.parmHideSelectField(true);
    */ 

    // Pass the query to the SysDataSetLookup so that the query is used.
    sysDataSetLookup.parmQuery(query);
}

preview after lookup filtered by code :
Capture

reference on MSDN :
https://msdn.microsoft.com/en-us/library/hh830903.aspx

Step by step deploying AX ListPage form to Enterprise portal

Listpage type form on ax can be deploy directly to enterprise portal.

And steps below show you how to do it :
1. Do incremental CIL first before deploy
2. On menu item display, right click on it and select deploy to EP and select module.
Capture

3. After deploy, AX will create two web object on AOT, WebUrls (Web -> Web Menu Items -> Web URLs) and Web Page Definition (Web -> Web Files -> Web Page Definition)
Capture

4. On Web Page Definitions properties “Module”, add submodule if you want to add this listpage on subModule on Enterprise portal , then right click on it select Deploy element.
Capture

5. On Web URLs properties URL , add submodule same as page definition (optional), then right click on it and select import page
Capture

6. Restart IIS
https://hellodax.com/2015/05/22/restart-iis-command-prompt/

7. Clear AOT cache
Capture

8. To check the newly added custom webcontrol, just copy paste your Default Enterprise portal URLS (System Administration -> Setup -> Enterprise portal -> Websites) and webcontrol urls (from Web Urls Properties) to web browser.

default enterprise portal urls :
Capture

full urls on web browser :
Capture

another guide to add to quick launch sidebar :
https://hellodax.com/2015/05/22/add-new-custom-webparts-data-grid-to-enterprise-portal-quick-launch-sidebar-tutorial/

error: Cannot create record. The Record already exists.

Problem :
Our client recently facing this error during creating any journal lines (LedgerJournalTrans) .

we did restart aos, full CIL, and any standard technical troubleshoot hoping it was a cache error or something like that. And we tried drop the table and inject it with the copied data. After doing some checking on table SystemSequences with table id 212 (ledgerJournalTrans), its nextVal RecId value record is already exist on that table.

reference for RecId on AX :http://www.axaptapedia.com/RecId

solution :
1. Stop the AOS
2. get the max recid from the table on SQL with:

select max(recId) from ledgerjournaltrans;

3. update the systemSequences table on SQL with:

update SYSTEMSEQUENCES set NEXTVAL = 'MAXRECID from table' where tabid= 'tableID'

4. restart AOS and run AX

Conclusion :
they did not doing some sort of AX Version upgrade that will impact whole data
and the main problem why the AX stored procedure on SQL returning next RecId value for the next record that already exist on the table remains a mystery.

Reference :
1. https://community.dynamics.com/ax/f/33/t/109379
2. https://waytoax.wordpress.com/2014/06/01/cannot-create-a-record-in-table-the-record-already-exists-2/

Step by step to deploy newly added WebControl to Enterprise Portal AX 2012

Deploying newly added webcontrol is much complicated than deploying SSRS.
I hope microsoft will simplify it on the next version of AX.

note :
this guide below is only for newly addded webcontrols.
if you already done the steps below, and want to update your webpage by using webcontrol, just right click on web controls AOT, and select deploy. It will replace the old web page with the new web page.

here is the steps for deploying webcontrols :

1. deploy the webcontrol on aot (Web -> Web Files -> Web Controls) , right click and choose “Deploy”
Capture

2. then deploy Web Content Managed on aot (Web -> Web Content -> Managed) , and choose the module
Capture

3. after you deploy web content managed , you will get two new object on AOT,
Web Urls (Web -> Web Menu Items -> URLs), and Web Page Definition (Web -> Web Files -> Page Definitions) , Right click on web page definitions , select “Deploy Elements”, right clicks on web urls, and select “Import Pages”

Web page definitions
Capture

Web Ulrs
Capture

4. Restart di IIS with IISRESET on your sharepoint server , see the reference post below
https://hellodax.com/2015/05/22/restart-iis-command-prompt/

5. Clear cache for making sure the enterprise portal is displaying the newly added webparts. (on some cases you must generate Incremental CIL too)
Capture

6. To check the newly added custom webcontrol, just copy paste your Default Enterprise portal URLS (System Administration -> Setup -> Enterprise portal -> Websites) and webcontrol urls (from Web Urls Properties) to web browser.

default enterprise portal urls :
Capture

web control urls :
Capture

full urls on web browser :
Capture

another guide to add to quick launch sidebar :
https://hellodax.com/2015/05/22/add-new-custom-webparts-data-grid-to-enterprise-portal-quick-launch-sidebar-tutorial/

Search and get table name with parameter table name and have data inside. SQL

this query is useful to check table name with data inside after running the class SysDatabaseTransDelete.

i put another parameter table name, for only search only customize table on AX with Developer identifier.

preview :
Capture

code below

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'TID%'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF 
	(@table_name LIKE @search_string)
	BEGIN
		SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ')'+ 'PRINT ''' + @table_name +''''
		EXECUTE(@sql_string)
	END
    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

Useful powershell command for export and install AXModel

Export axmodel layer example :
Export-AXModel -Model CUS Model -config MicrosoftDynamicsAXUAT -file CUS.axmodel
Capture2

Import axmodel with replace and create parent example :
Install-AXModel -File CUS.axmodel -Conflict Overwrite -Config MicrosoftDynamicsAXUAT -createparents
Capture

reference link :
http://axbloggerblog.blogspot.co.id/p/powershell-commands.html

do full compile, full sync db, full CIL and incremental. Re-deploy website EP if you are customize the EP

Visualize with ax data to ASP calendar COLOR highlight example code on Enterprise Portal C#

i’ll explain the code below on another day .
when we remove the highlights on calender with clicks, it will remove the data on ax
and do the same when we add highlights on calendar.

Preview :
Capture

on design

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="HRSUploadTimeSheet.ascx.cs" Inherits="HRSUploadTimeSheet" %>
<dynamics:AxDataSource ID="hrsScheduleRequestWorkerTmpDS" runat="server" 
    DataSetName="HRSScheduleRequestWorkerTmpDS" 
    ProviderView="HRSScheduleRequestWorkerTmp">
</dynamics:AxDataSource>
<dynamics:AxDataSource ID="HRSJmgProfileDayDS" runat="server" 
    DataSetName="HRSJmgProfileDayDS" 
    ProviderView="JmgProfileDay">
</dynamics:AxDataSource>

<dynamics:AxForm ID="AxForm1" runat="server">
    <dynamics:AxMultiColumn ID="AxMultiColumn1" runat="server">
        <dynamics:AxColumn ID="AxColumn1" runat="server">
            <dynamics:AxGridView ID="AxGridView1" runat="server" BodyHeight="" 
                DataKeyNames="ProfileId" DataMember="HRSScheduleRequestWorkerTmp" 
                DataSetCachingKey="09d6aa10-4163-4e45-9074-8449211996ae" 
                DataSourceID="hrsScheduleRequestWorkerTmpDS" EnableModelValidation="True" 
                AllowPaging="False" 
                onselectedindexchanged="AxGridView1_SelectedIndexChanged" 
                ShowFilter="False">
                <Columns>
                    <asp:TemplateField ConvertEmptyStringToNull="False" 
                        HeaderText="<%$ AxLabel:@SYS303656 %>" SortExpression="PersonnelNumber">
                        <ItemTemplate>
                            <asp:Label ID="PersonnelNumber" runat="server" Text='<%# Bind("PersonnelNumber") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <dynamics:AxBoundField DataField="Name" DataSet="HRSScheduleRequestWorkerTmpDS" 
                        DataSetView="HRSScheduleRequestWorkerTmp" SortExpression="Name">
                    </dynamics:AxBoundField>
                    <asp:TemplateField ConvertEmptyStringToNull="False" 
                        HeaderText="<%$ AxLabel:@SYS7607 %>" SortExpression="ProfileId">
                        <ItemTemplate>
                            <asp:Label ID="ProfileId" runat="server" Text='<%# Bind("ProfileId") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </dynamics:AxGridView>
        </dynamics:AxColumn>

         <dynamics:AxColumn ID="AxColumn2" runat="server">
            <asp:Calendar ID="Calendar1" runat="server" 
                                    OnPreRender="Calendar1_PreRender" 
                                    OnSelectionChanged="Calendar1_SelectionChanged" 
                                    ondayrender="Calendar1_DayRender" Height="226px" 
                    Width="427px">
            </asp:Calendar>
            <dynamics:AxGridView ID="AxGridView2" runat="server" BodyHeight="" 
                DataKeyNames="RecId" DataMember="JmgProfileDay" 
                DataSetCachingKey="a1fd011a-2d33-4680-a45d-c70b2dcaf863" 
                DataSourceID="HRSJmgProfileDayDS" EnableModelValidation="True"
                ondatabound="AxGridView2_databound" ShowFilter="False" AllowPaging="False"
                >
                <Columns>
                    <asp:TemplateField ConvertEmptyStringToNull="False" 
                        HeaderText="<%$ AxLabel:@SYS67221 %>" SortExpression="Color">
                        <ItemTemplate>
                            <asp:Label ID="Color" runat="server" Text='<%# Bind("Color") %>' Visible="false"></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ConvertEmptyStringToNull="False" 
                        HeaderText="<%$ AxLabel:@SYS7607 %>" SortExpression="Profile">
                        <ItemTemplate>
                            <asp:Label ID="Profile" runat="server" Text='<%# Bind("Profile") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ConvertEmptyStringToNull="False" HeaderText="Profile Key" 
                        SortExpression="HRSProfleKey">
                        <ItemTemplate>
                            <asp:Label ID="ProfileKey" runat="server" Text='<%# Bind("HRSProfleKey") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <dynamics:AxBoundField DataField="StartWorkTime" DataSet="HRSJmgProfileDayDS" 
                        DataSetView="JmgProfileDay" SortExpression="StartWorkTime">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField DataField="EndWorkTime" DataSet="HRSJmgProfileDayDS" 
                        DataSetView="JmgProfileDay" SortExpression="EndWorkTime">
                    </dynamics:AxBoundField>
                </Columns>
            </dynamics:AxGridView>

        </dynamics:AxColumn>
    </dynamics:AxMultiColumn>
</dynamics:AxForm>

.cs file

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using System.IO;
using System.Data.OleDb;
using System.Data;
using Proxy = Microsoft.Dynamics.Framework.BusinessConnector.Proxy;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using System.Drawing;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;


public partial class HRSUploadTimeSheet : System.Web.UI.UserControl
{
    private string profileId { get; set; }
    private string scheduleRequestId { get; set; }
    private string personnelNumber { get; set; }
    private Boolean canEdit { get; set; }

    Dictionary<DateTime, int> dict = new Dictionary<DateTime, int>();

    public Dictionary<DateTime, int> SelectedDates
    {
        get
        {
            if (ViewState["Dates"] == null)
            {
                dict.Add(DateTime.MaxValue.AddDays(-2), 0);
                ViewState["Dates"] = dict;
            }

            return (Dictionary<DateTime, int>)ViewState["Dates"];
        }
        set
        {
            ViewState["Dates"] = value;
        }
    }

    private ISession AxSession
    {
        get
        {
            AxBaseWebPart webPart = AxBaseWebPart.GetWebpart(this);
            return webPart == null ? null : webPart.Session;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        IAxaptaRecordAdapter currentRecord = AxBaseWebPart.GetWebpart(this).ExternalRecord;
        if (currentRecord != null)
        {
            string status = currentRecord.GetField("HRSRequestStatus").ToString();
            if (status != "0")
            {
                canEdit = false;
            }
            else
            {
                canEdit = true;
            }
            scheduleRequestId = currentRecord.GetField("ScheduleReqestId").ToString();
        }
        if (!IsPostBack)
        {
            getProfileIdAndPersonnelNumber(true);
            getCalendarDataFromAX();
        }
      
    }

    #region grid
    protected void AxGridView2_databound(object sender, EventArgs e)
    {
        for (int i = 0; i <= AxGridView2.Rows.Count - 1; i++)
        {
            Label colorTxt = (Label)AxGridView2.Rows[i].FindControl("Color");
            Color myColor = Color.FromArgb(Convert.ToInt32(colorTxt.Text));
            AxGridView2.Rows[i].Cells[0].BackColor = myColor;
        }

        getProfileIdAndPersonnelNumber(true);
        getCalendarDataFromAX();
        FilterRows();
   
    }

    protected void AxGridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        getProfileIdAndPersonnelNumber();
        getCalendarDataFromAX();
        FilterRows();

    }

    protected void getProfileIdAndPersonnelNumber(Boolean isInit = false)
    {
        Label profileIdLbl;
        Label PersonnelNumberLbl;

        if (isInit)
        {
            profileIdLbl = (Label)AxGridView1.Rows[0].FindControl("ProfileId");
            PersonnelNumberLbl = (Label)AxGridView1.Rows[0].FindControl("PersonnelNumber");
        }
        else
        {
            profileIdLbl = (Label)AxGridView1.Rows[AxGridView1.SelectedIndex].FindControl("ProfileId");
            PersonnelNumberLbl = (Label)AxGridView1.Rows[AxGridView1.SelectedIndex].FindControl("PersonnelNumber");
        }
        profileId = profileIdLbl.Text;
        personnelNumber = PersonnelNumberLbl.Text;
    }

    protected void getCalendarDataFromAX()
    {
        SelectedDates.Clear();
        SelectedDates.Add(DateTime.MaxValue.AddDays(-2), 0);

        DateTime keyDate = System.DateTime.Now;
        int valueInt = 0;
        Microsoft.Dynamics.AX.ManagedInterop.Container axContainer;

        axContainer = (Microsoft.Dynamics.AX.ManagedInterop.Container)this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "getDataScheduleByWorker", personnelNumber, scheduleRequestId);

        for (int i = 1; i <= axContainer.Count; i++)
        {
            if (i % 2 != 0)
            {
                keyDate = Convert.ToDateTime(axContainer.get_Item(i));
            }
            else
            {
                valueInt = Convert.ToInt32(axContainer.get_Item(i));
                if (SelectedDates.ContainsKey(keyDate))
                {
                    SelectedDates.Remove(keyDate);
                }

                SelectedDates.Add(keyDate, valueInt);

            }
        }

        ViewState["Dates"] = SelectedDates;
    }

    protected void ShowRows()
    {
        // Display all the rows
        foreach (GridViewRow row in AxGridView2.Rows)
        {
            row.Visible = true;
        }
    }

    protected void FilterRows()
    {
        ShowRows();
        string colProfileId;
        // Loop through the rows and apply the filter
        int i = 0;
        foreach (GridViewRow row1 in AxGridView2.Rows)
        {
            Label lblProfile = (Label)AxGridView2.Rows[i].FindControl("Profile");
            // Get the filter column values
            colProfileId = lblProfile.Text;

            // If the name, visit and discharge fields are not equal to "All" and not equal to the filter value then the row is hidden.
            if (colProfileId != profileId)
            {
                row1.Visible = false;
            }
            i++;
        }

    }

    #endregion

    #region calendar
    protected void Calendar1_PreRender(object sender, EventArgs e)
    {
        // Reset Selected Dates 
        Calendar1.SelectedDates.Clear();
        // Select previously Selected Dates 

        foreach (var dt in SelectedDates)
        {
            Calendar1.SelectedDates.Add(dt.Key);
            Color myColor = Color.FromArgb(Convert.ToInt32(dt.Value));
            Calendar1.SelectedDayStyle.BackColor = myColor;
        }

    }

    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        if (canEdit)
        {
            getProfileIdAndPersonnelNumber();
            //Check if selected Date is in the saved list 
            // Remove the Selected Date from the saved list 
            if (SelectedDates.ContainsKey(Calendar1.SelectedDate))
            {
                SelectedDates.Remove(Calendar1.SelectedDate);
                //delete record in ax , call custom table static method
                this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "deleteScheduleRequestWorker", personnelNumber, scheduleRequestId, Calendar1.SelectedDate.Date);
            }

            else
            {
                Label colorTxt = (Label)AxGridView2.Rows[AxGridView2.SelectedIndex].FindControl("Color");
                Label profileKey = (Label)AxGridView2.Rows[AxGridView2.SelectedIndex].FindControl("ProfileKey");
                SelectedDates.Add(Calendar1.SelectedDate, Convert.ToInt32(colorTxt.Text));
                //insert to ax , call custom table static method
                this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "insertScheduleRequestWorker", personnelNumber, scheduleRequestId, Calendar1.SelectedDate.Date, profileId, profileKey.Text);
            }

            ViewState["Dates"] = SelectedDates;
        }
    }

    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {
        if (e.Day.IsSelected == true)
        {
            int color = SelectedDates[e.Day.Date];
            Color myColor = Color.FromArgb(color);
            e.Cell.BackColor = myColor;
        }
    }

    #endregion
}

get return container from AX and iterate(loop) items example code EP (Enterprise Portal) C#

protected void getCalendarDataFromAX()
    {
        SelectedDates.Clear();

        DateTime keyDate = System.DateTime.Now;
        int     valueInt = 0;
        Microsoft.Dynamics.AX.ManagedInterop.Container axContainer;

        axContainer = (Microsoft.Dynamics.AX.ManagedInterop.Container)this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "getDataScheduleByWorker", personnelNumber, "SCH-000001");

        for (int i = 1; i <= axContainer.Count; i++)
        {
            if (i % 2 != 0)
            {
                keyDate = Convert.ToDateTime(axContainer.get_Item(i));
            }
            else
            {
                valueInt = Convert.ToInt32(axContainer.get_Item(i));
                if (SelectedDates.ContainsKey(keyDate))
                {
                    SelectedDates.Remove(keyDate);
                }
            
                SelectedDates.Add(keyDate, valueInt);
              
            }
        }

        ViewState["Dates"] = SelectedDates;
    }

how to add custom DLL reference to resolve error “missing an assembly reference” on enterprise portal AX 2012

if you add custom DLL to your VS project. When you open up web page you will receive error “are you missing an assembly reference?”.

to resolve this issue.. just copy paste the custom dll to folder (Sharepoint Server) C:\inetpub\wwwroot\wss\VirtualDirectories\PORT_NUMBER\_app_bin .

do incremental CIL and reset IIS

Reset the IIS :
https://hellodax.com/2015/05/22/restart-iis-command-prompt/

Capture

example custom code submitManager class for both Rich Client and EP (Enterprise Portal) x++

differentiate the rich client and ep using the menu items name.
always remember to do INCREMENTAL CIL every time you modify any of workflow classses

public void submit(Args _args)
{
    WorkflowComment         note = "";
    WorkflowSubmitDialog    workflowSubmitDialog;
    WorkflowCorrelationId   workflowCorrelationId;
    recId                   recId;
    workflowTypeName        workflowTypeName = workflowtypestr("TIDEmploymentLeave");
    WorkflowComment         initialNote = "";
    HcmEmploymentLeave      HcmEmploymentLeave;
    FormDataSource          HcmEmploymentLeave_ds;
    EPWorkflowControlContext workflowControlContext;
    str                     menuItemName = _args.menuItemName();


    //from rich client
    if(menuItemName == menuitemactionstr(TIDEmploymentLeaveSubmitMenuItem) || menuItemName == menuitemActionStr(HRSLeaveCreditSubmitMenuItem))
    {
        // Workflow is starting from the Windows client. This can be determined
        // because the menu item for submitting the workflow on the Windows
        // client was chosen by the user.

        // Opens the submit to workflow dialog.
        workflowSubmitDialog = WorkflowSubmitDialog::construct(_args.caller().getActiveWorkflowConfiguration());
        workflowSubmitDialog.run();

        if (workflowSubmitDialog.parmIsClosedOK())
        {
            // Find what record from the Work Orders table is being submitted to workflow.
            recId = _args.record().RecId;

            // Get comments from the submit to workflow dialog.
            initialNote = workflowSubmitDialog.parmWorkflowComment();

            try
            {
                // Update the record in the FCMWorkOrders table that is being submitted to workflow.
                // The record is moved to the 'submitted' state.
                ttsBegin;
                HcmEmploymentLeave = _args.record();
                HcmEmploymentLeave.selectForUpdate(true);
                HcmEmploymentLeave.Status = HRSLeaveStatus::Submitted;
                HcmEmploymentLeave.update();
                // Activate the workflow.
                workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName, recId, initialNote, NoYes::No);

                 ttsCommit;

                // Updates the workflow button to diplay Actions instead of Submit.
                _args.caller().updateWorkflowControls();
            }

            catch(exception::Error)
            {
                info("Error on workflow activation.");
            }
        }
    }
    //from EP
    else
    {
         // Workflow is starting from Enterprise Portal.

         // Retrieve the workflow controller context from the arguments passed in.
         workflowControlContext = _args.caller();

         // Find what record from the Work Orders table is being submitted to workflow.
         recId = _args.record().RecId;

         // Get comments from the submit to workflow dialog.
         initialNote = workflowControlContext.getWorkflowComment();

        try
        {
            ttsBegin;
            workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName, recId, initialNote, NoYes::Yes);
            HcmEmploymentLeave = _args.record();
            HcmEmploymentLeave.selectForUpdate(true);
            HcmEmploymentLeave.Status = HRSLeaveStatus::Submitted;
            HcmEmploymentLeave.update();
            ttsCommit;
        }
        catch(exception::Error)
        {
            info("Error on workflow activation.");
        }
    }
}

error : Paging is not supported for queries that do not have an ordering property.

example error :
Capture

if you are using list page for EP,
at your list page interaction class on AX.

do override method initializeQuery and add sortbyfield like example below

public void initializeQuery(Query _query)
{

_query.dataSourceTable(tableNum(HRSBenefitEntitlement)).addOrderByField(fieldNum(HRSBenefitEntitlement,Employment));
    super(_query);

}

do Incremental CIL after you modify the code.

error : The type or namespace name ‘Portal’ does not exist in the namespace ‘Microsoft.Dynamics’ (are you missing an assembly reference?)

i found this error when tried to edit and build solution for standard web control on visual studio.

Capture

how to resolve :
Right Click on your project and select add EP Proxy Project.
Capture

source :
https://msdn.microsoft.com/en-us/library/gg846077.aspx

convert null to 0 type BIGINT (INT64 on AX) when outer join on view using SysComputedColumn

public static server str DefaultDimension()
{

    str field = SysComputedColumn::returnField(tableStr(HRSJobPositionDefaultDimension),
                    identifierStr(HcmPositionDefaultDimension),
                    fieldStr(HcmPositionDefaultDimension, DefaultDimension));
    
    return SysComputedColumn::if(
        SysComputedColumn::isNullExpression(field),
        SysComputedColumn::cast("0",'BIGINT'),  
        SysComputedColumn::cast(field,'BIGINT'));
}

add new default dimension (financial dimension) on new table and form example tutorial

This example tutorial will guide you to add new default dim on table and forms.

1. Add new field with type Int64 and set the properties.
Capture

2. Add relation on table relation and set the properties.
Capture

3. on class declaration form , add code below :

public class FormRun extends ObjectRun
{
    DimensionDefaultingController   dimensionDefaultingController;
}

4. add tab page Financial Dimension on form design , and set properties like img below
Capture

5. on TabFinancialDimensions , override method pageActivated

public void pageActivated()
{
    dimensionDefaultingController.pageActivated();

    super();
}

6. on init form, like code below :

public void init()
{
    super();
    dimensionDefaultingController = DimensionDefaultingController::constructInTabWithValues(true, true, true, 0, this, TabFinancialDimensions, "@SYS138487", curext());
    dimensionDefaultingController.parmAttributeValueSetDataSource(HRSJobPowerPlan_ds, fieldStr(HRSJobPowerPlan, DefaultDimension));
}

7. finnaly, on datasource form method override method :
on active()

public int active()
{
    int ret;

    ret = super();
    dimensionDefaultingController.activated();

    return ret;
}

on write()

public void write()
{
    dimensionDefaultingController.writing();
    super();
}

on delete()

public void delete()
{
    dimensionDefaultingController.deleted();
    super();
}

8. preview example form:
Capture

calculate field on view with SysComputedColumn example code

public static server str getOutstanding()
{
    return strFmt("Sum(%1) + Sum(%2)",
            SysComputedColumn::returnField(
            tableStr(TIDIndentOrderUninvoiceTrans),
            identifierStr(TIDIndentOrderPaymentTrans),
            fieldStr(TIDIndentOrderPaymentTrans, amountTendered)),
            SysComputedColumn::returnField(
            tableStr(TIDIndentOrderUninvoiceTrans) ,
            identifierStr(TIDIndentOrderPaymentTrans),
            fieldStr(TIDIndentOrderPaymentTrans, grossAmount)));
}

add group by and ranges to Queries Forms example code

on form class declaration

public class FormRun extends ObjectRun
{
    QueryBuildRange             indentTransDateRange;
    QueryBuildRange             invoiceDateRange;

    QueryBuildRange             customerRange;
    QueryBuildRange             storeRange;
}

on init form method

public void init()
{
    super();

    indentTransDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, PaymentTransDate));

    invoiceDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, InvoicePostingDate));

    customerRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, CustAccount));

    storeRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tableNum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldNum(TIDIndentOrderUninvoiceTrans, Store));

    TransactionDate.dateValue(systemdateGet());
}

on execute Query datasource form

public void executeQuery()
{
    this.Query().clearGroupBy();

    indentTransDateRange.value(strFmt('<%1',(TransactionDate.dateValue() + 1)));
    invoiceDateRange.value(strFmt('>%1',TransactionDate.dateValue()));

    customerRange.value(strFmt(customer.text()+'*'));
    storeRange.value(strFmt(StoreId.text()+'*'));

    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,Store));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,CustAccount));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,SalesId));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,GrossAmount));
    this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,SumOfamountTendered),SelectionField::Sum);
    this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,OutstandingAmount),SelectionField::Sum);

    super();
}

Capture

Formating cells when export to excel example code

private void setInvoiceHeader(SysExcelWorkSheet    xlsWorkSheet,CustInvoiceJour custInvoiceJour)
{
    xlsWorkSheet.cells().item(row,1).value("FK");
    xlsWorkSheet.cells().item(row,2).value(str2num(CustTable::find(custInvoiceJour.InvoiceAccount).TECFakturPajakTypeId));
    xlsWorkSheet.cells().range(strFmt("B%1:B%1",row)).numberFormat("00");
    xlsWorkSheet.cells().item(row,3).value(enum2int(custInvoiceJour.TECIsSubtitute));
    xlsWorkSheet.cells().item(row,4).value(str2num(strFmt("%1", strRem(custInvoiceJour.TECFakturPajakListId, ".-"))));
    xlsWorkSheet.cells().range(strFmt("D%1:D%1",row)).numberFormat("0000000000000");
    xlsWorkSheet.cells().item(row,5).value(mthOfYr(custInvoiceJour.InvoiceDate));
    xlsWorkSheet.cells().item(row,6).value(year(custInvoiceJour.InvoiceDate));
    xlsWorkSheet.cells().item(row,7).value(date2str(custInvoiceJour.InvoiceDate, 213, 1, 4, 1, 4, 4));
    xlsWorkSheet.cells().item(row,8).value(str2num(strFmt("%1", strRem(custInvoiceJour.custTable_InvoiceAccount().TECNPWP, ".-"))));
    xlsWorkSheet.cells().range(strFmt("H%1:H%1",row)).numberFormat("00000000000000");
    xlsWorkSheet.cells().item(row,9).value(CustTable::find(custInvoiceJour.InvoiceAccount).name());
    xlsWorkSheet.cells().item(row,10).value(strRTrim(CustTable::find(custInvoiceJour.InvoiceAccount).postalAddress().Street));
    xlsWorkSheet.cells().item(row,11).value(round(custInvoiceJour.SalesBalance,0));
    xlsWorkSheet.cells().range(strFmt("K%1:K%1",row)).numberFormat("0");
    xlsWorkSheet.cells().item(row,12).value(round(custInvoiceJour.SumTax,0));
    xlsWorkSheet.cells().range(strFmt("L%1:L%1",row)).numberFormat("0");
    xlsWorkSheet.cells().item(row,13).value(0);
    xlsWorkSheet.cells().item(row,14).value("");
    xlsWorkSheet.cells().item(row,15).value(0);
    xlsWorkSheet.cells().item(row,16).value(0);
    xlsWorkSheet.cells().item(row,17).value(0);
    xlsWorkSheet.cells().item(row,18).value(0);
    xlsWorkSheet.cells().item(row,19).value(custInvoiceJour.InvoiceId);
    row++;
    totalCount++;
}

Capture

more example and source :
http://patrikluca.blogspot.cz/2010/04/export-to-excel-with-x-code-sequel.html

Job upload Fixed Asset Journal

static void TIDF_UploadFAJournal(Args _args)
{
    container                       accEntryPattern;
    container                       offSetEntryPattern;
    //for default dimension
    container                       financialDimensionFromExcel;
    DimensionDefault                defaultDimension;
    int                             countLedger;
    int                             countCon;

    AssetBookTable                  _AssetBookTable;
    AssetBookId                     _AssetBookId;
    LedgerJournalTrans_Asset        _LedgerJournalTrans_Asset;
    LedgerJournalTrans              _LedgerJournalTrans;
    LedgerJournalTable              _LedgerJournalTable;

    AssetBookTableDerived           _AssetBookTableDerived;
    AssetBookTableDerivedJournal    _AssetBookTableDerivedJournal;
    AssetTransTypeJournal           _AssetTransTypeJournal;
    AssetTransType                  _AssetTransType;

    LedgerJournalACType             LedgerJournalACType;
    ledgerJournalName               ledgerJournalName;
    journalID                       JournalID;
    LedgerJournalId                 _journalId;
    axLedgerJournalTable            axLedgerJournalTable;
    axledgerJournalTrans            axLedgerJournalTrans;
    LedgerJournalType               JournalType = ledgerJournalType::Daily;


    TransDate                       _TransDate;
    LedgerTransType                 _LedgerTransType;
    str                             AccountNum;
    LedgerJournalACType             _AccountType;
    CurrencyCode                    _CurrencyCode;
    real                            _ExhcangeRate;
    real                            _AmountCurDebit;
    real                            _AmountCurCredit;
    str                             _InvoiceId;
    TransTxt                        _TransTxt;
    DueDate                         _DueDate;
    DocumentNum                     _DoucumentNum;
    DocumentDate                    _DocumentDate;
    str                             Branch;
    str                             Departement;
    str                             Product;
    str                             Project;
    TaxGroupJournal                 _TaxJournal;
    TaxGroupJournal                 _TaxItemJournal;
    NoYes                           _Transferred;
    BankTransactionType             _BankTransactionType;
    TaxCode                         _TaxCode;
    LedgerJournalACType             _OffsetAccountType;
    str                             _OffsetAcountNum;
    VendPostingProfile              _PostingProfile;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;
    str                                         strAccount,strAccountOffset;
    Name                                        dimensionName1,dimensionName2,dimensionName3,dimensionName4;
    str                                         MainAcc,Dim1,Dim2,Dim3,Dim4,OffsetAcc;
    str                                         AccountType,OffsetAccountType;
    str                                         PayReff,BankTransType,DocNo,PostingProfile,noInvoice, transType;
    TransDate                                   DocDate;
    str                                         StrDate;

    boolean         _return = true;
    container dimensionAttrName = Global::TIDgetDimensionName();
    _OffsetAcountNum    = '9900-0000';
    startLengthyOperation();
    filename = @"C:\\#Tectura#\fanddy\UploadFAJournal";//FA Dep Commercial.xlsx";//Depreciation commercial
    //filename = _filePath;
    try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();

            row = 6;

            ttsBegin;

            while (cells.item(row,5).value().bStr() != '' )
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                if(cells.item(row,10).value().bStr() != '' ) {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                if(cells.item(row,11).value().bStr() != '' ) {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                if(cells.item(row,12).value().bStr() != '' ) {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,13).value().bStr();
                if(cells.item(row,13).value().bStr() != '' ) {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,14).value().bStr();
                if(cells.item(row,14).value().bStr() != '' ) {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,15).value().bStr();
                if(cells.item(row,15).value().bStr() != '' )  {countLedger++;}

                //start from A
                _journalId          = cells.item(row,1).value().bStr();
                _TransDate          = cells.item(row,2).value().date();
                AccountNum          = cells.item(row,3).value().bStr();
                transType           = cells.item(row,4).value().bStr();
                _AssetBookId        = cells.item(row,5).value().bStr();
                _CurrencyCode       = cells.item(row,6).value().bStr();
                _ExhcangeRate       = cells.item(row,7).value().double();
                _AmountCurDebit     = cells.item(row,8).value().double();
                _AmountCurCredit    = cells.item(row,9).value().double();

                //_TransDate          = str2Date(StrDate, 123);
                defaultDimension    = TIDcreateDefaultDimension(dimensionAttrName,financialDimensionFromExcel);

                offSetEntryPattern =
                [   _OffsetAcountNum,
                    _OffsetAcountNum,
                    countLedger
                ];
                countCon = 1;
                while(countCon <= countLedger)
                {
                    offSetEntryPattern += conPeek(dimensionAttrName,countCon);
                    offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                    countCon++;
                }

                _AssetTransType             = str2enum(_AssetTransType, transType);
                _AssetTransTypeJournal      = str2enum(_AssetTransTypeJournal, transType);

                _LedgerJournalTable         = LedgerJournalTable::find(_journalId);
                if (_LedgerJournalTable)
                {
                    _LedgerJournalTrans.clear();
                    _LedgerJournalTrans.JournalNum          = _journalId;
                    _LedgerJournalTrans.TransDate           = _TransDate;
                    _LedgerJournalTrans.AccountType         = LedgerJournalACType::FixedAssets;
                    _LedgerJournalTrans.LedgerDimension     = DimensionStorage::getDynamicAccount(AccountNum, LedgerJournalACType::FixedAssets);
                    _LedgerJournalTrans.DefaultDimension    = defaultDimension;
                    _LedgerJournalTrans.CurrencyCode        = _CurrencyCode;
                    _LedgerJournalTrans.ExchRate            = _ExhcangeRate;
                    _LedgerJournalTrans.PostingProfile      = _PostingProfile;

                    if(_AmountCurDebit != 0)
                    {
                        _LedgerJournalTrans.AmountCurDebit  = _AmountCurDebit;
                    }

                    if(_AmountCurCredit != 0)
                    {
                        _LedgerJournalTrans.AmountCurCredit = _AmountCurCredit;
                    }

                    if(_OffsetAcountNum != "")
                    {
                        _LedgerJournalTrans.OffsetAccountType       = LedgerJournalACType::Ledger;
                        _LedgerJournalTrans.OffsetLedgerDimension   = AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern);
                    }

                    _LedgerJournalTrans.Voucher = NumberSeq::newGetNumFromId(_LedgerJournalTable.ledgerJournalName().NumberSequenceTable).num();
                    _LedgerJournalTrans.insert();

                    if (_LedgerJournalTrans)
                    {
                        if (_AssetBookId)
                        {
                            _AssetBookTable = AssetBookTable::find(_AssetBookId);

                            if (_AssetBookTable)
                            {
                                _LedgerJournalTrans_Asset.clear();
                                _LedgerJournalTrans_Asset.RefRecId      = _LedgerJournalTrans.RecId;
                                _LedgerJournalTrans_Asset.AssetId       = AccountNum;
                                _LedgerJournalTrans_Asset.TransType     = _AssetTransTypeJournal;
                                _LedgerJournalTrans_Asset.BookId        = _AssetBookTable.BookId;
                                _LedgerJournalTrans_Asset.insert();
                            }



                            select firstOnly _AssetBookTableDerived
                                where _AssetBookTableDerived.BookId == _AssetBookId
                                   && _AssetBookTableDerived.AssetTransType == _AssetTransType;

                            if (_AssetBookTableDerived)
                            {
                                _AssetBookTableDerivedJournal.clear();
                                _AssetBookTableDerivedJournal.RefRecId  = _LedgerJournalTrans.RecId;
                                _AssetBookTableDerivedJournal.AssetBookId = _AssetBookTableDerived.BookIdDerived;

                                if (_LedgerJournalTrans.AmountCurCredit > 0)
                                {
                                    _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurCredit;
                                }
                                else if (_LedgerJournalTrans.AmountCurDebit > 0)
                                {
                                    _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurDebit;
                                }

                                _AssetBookTableDerivedJournal.insert();
                            }

                        }
                    }
                    print strFmt('Row Number %1 - Asset ID : %2', row,AccountNum);
                    row++;
                }
            }

            info(strFmt('Imported  %1 Items, Journal Numbers : %2',row ,_journalId));
             ttsCommit;
        }
    }
    catch(Exception::Error)
    {
        _return = false;
    }

    application.quit();
}

Job upload General Journal example code 2

static void TIDF_UploadJournal(Args _args)
{
    container   accEntryPattern;
    container   offSetEntryPattern;
    container   financialDimensionFromExcel;
    container   conDimensionName;
    int         countCon;
    int         countLedger;
    RecId       recIdDefaultDimension;

    DimensionDynamicAccount parmLedgerDimension;
    DimensionDynamicAccount parmOffsetLedgerDimension;

    LedgerJournalTable   ledgerJournalTable;
    LedgerJournalACType  LedgerJournalACType;
    ledgerJournalName    ledgerJournalName;
    journalID            JournalID;
    axLedgerJournalTable axLedgerJournalTable;
    axledgerJournalTrans axLedgerJournalTrans;
    LedgerJournalType JournalType = ledgerJournalType::Daily;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;

    boolean         _return = true;
    ;

    startLengthyOperation();
    filename = "C:\\test.xlsx";
    JournalID = "G0615-0040";// <<< JOURNALNUM
    //filename = _filePath;

    try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();

            row = 5;

            ledgerJournalTable = ledgerJournalTable::find(JournalID);
            conDimensionName = Global::TIDgetDimensionName();

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //empty container
                financialDimensionFromExcel = conNull();
                countLedger = 0;

                //Create Trans ...
                axLedgerJournalTrans = new axLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(JournalID);
                //start from A
                axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
                axLedgerJournalTrans.parmAccountType(str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,17).value().bStr();
                if(cells.item(row,17).value().bStr() != "") {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,18).value().bStr();
                if(cells.item(row,18).value().bStr() != "") {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,19).value().bStr();
                if(cells.item(row,19).value().bStr() != "") {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,20).value().bStr();
                if(cells.item(row,20).value().bStr() != "") {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,21).value().bStr();
                if(cells.item(row,21).value().bStr() != "") {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,22).value().bStr();
                if(cells.item(row,22).value().bStr() != "")  {countLedger++;}

                //ledger Dimension
                if(axLedgerJournalTrans.parmAccountType() == LedgerJournalACType::Ledger)
                {
                    accEntryPattern =
                    [   cells.item(row, 3).value().bStr(),
                        cells.item(row, 3).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        accEntryPattern += conPeek(conDimensionName,countCon);
                        accEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
                }
                else
                {
                    parmLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 3).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, LedgerDimension), false);
                axLedgerJournalTrans.parmLedgerDimension(parmLedgerDimension);

                //offset dimension
                axLedgerJournalTrans.parmOffsetAccountType(str2Enum(LedgerJournalACType, cells.item(row,8).value().bStr()));
                if(axLedgerJournalTrans.parmOffsetAccountType() == LedgerJournalACType::Ledger)
                {
                    offSetEntryPattern =
                    [   cells.item(row, 9).value().bStr(),
                        cells.item(row, 9).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        offSetEntryPattern += conPeek(conDimensionName,countCon);
                        offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmOffsetLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
                }
                else
                {
                    parmOffsetLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 9).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                
                axLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
                axLedgerJournalTrans.parmOffsetLedgerDimension(parmOffsetLedgerDimension);
                if(cells.item(row,5).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
                }

                if(cells.item(row,6).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
                }

                axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
                axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
                axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
                axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
                axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
                axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
                axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
                axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
                //
                axLedgerJournalTrans.parmPostingProfile(cells.item(row,23).value().bStr());

                recIdDefaultDimension = Global::TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                axLedgerJournalTrans.parmDefaultDimension(recIdDefaultDimension);
                axLedgerJournalTrans.parmOffsetDefaultDimension(recIdDefaultDimension);

                axLedgerJournalTrans.save();
                row++;
            }
               ttsCommit;

            info('Success !!');
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Journal Upload Error");
        application.quit();
    }

}

Job upload fixed asset ( AssetTable and AssetBook) example code

static void TIDF_uploadFAwithValueModels(Args _args)
{
    AssetId             assetId;
    AssetGroupId        assetGroupId;
    AssetName           assetName;
    AssetLocationId     assetLocationId;
    AssetServiceLife    assetServiceLife;
    AssetLifeTimeRest   assetLifeTimeRest;
    AssetPostingProfile assetPostingProfile;
    AssetBookId         assetBookId;
    TransDate           depreciationStartDate,LastDepreciationDate,AcquisitionDate;
    RecId               recIdDefaultDimension;
    
    AxAssetTable        axAssetTable;
    
    //table
    AssetTable          assetTable;
    AssetGroup          assetGroup;
    AssetLocation       assetLocation;
    AssetBook           assetBook;
    AssetLedger         assetLedger;
    
    container   financialDimensionFromExcel;
    container   conDimensionName;
    
    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename;
    ;
    
    startLengthyOperation();
    //variable yang diisi manual
    filename              = "C:\\FA master.xlsx";
    assetPostingProfile   = "FA POSTING";
    depreciationStartDate = today();
    LastDepreciationDate  = today();
    AcquisitionDate       = today();
    
    try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();
            conDimensionName = TIDgetDimensionName();

            row = 5;
            
            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,7).value().bStr();
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,8).value().bStr();
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,9).value().bStr();
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                //generateDefaultDimension
                recIdDefaultDimension       = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                
                //variable
                assetId           = TIDComVariant2STR(cells.item(row,2).value());
                assetGroupId      = TIDComVariant2STR(cells.item(row,1).value());
                assetLocationId   = TIDComVariant2STR(cells.item(row,6).value());
                assetName         = TIDComVariant2STR(cells.item(row,3).value());
                assetServiceLife  = str2num(TIDComVariant2STR(cells.item(row,4).value()));
                assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
                assetBookId       = TIDComVariant2STR(cells.item(row,13).value());
                //table
                assetTable      = assetTable::find(assetId);
                assetGroup      = AssetGroup::find(assetGroupId);
                assetLocation   = AssetLocation::find(assetLocationId);
                
                //validation
                if(!assetGroup || !assetLocation)
                {
                    throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
                }
                //create asset Table
                if(!assetTable)
                {
                    axAssetTable    = new AxAssetTable();
                    axAssetTable.parmAssetId(assetId);
                    axAssetTable.parmAssetGroup(assetGroupId);
                    axAssetTable.parmName(assetName);
                    axAssetTable.parmLocation(assetLocationId);
                    axAssetTable.save();
                }
                
                //create assetBook
                assetBook = assetBook::find(assetId,assetBookId);
                if(!assetBook)
                {
                    assetBook.clear();
                    assetBook.initValue();
                    assetBook.AssetId               = assetId;
                    assetBook.BookId                = assetBookId;
                    assetBook.PostingProfile        = assetPostingProfile;
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.insert();
                }
                else
                {
                    assetBook.selectForUpdate(true);
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.update();
                }
                
              
                row++;
            }
            ttsCommit;
            application.quit();
        }
    }    
    catch(Exception::Error)
    {
        info("Fixed Asset upload Error");
        application.quit();
    }
}

TIDGetDimensionName

public static container TIDgetDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

TIDcreateDefaultDimension

static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    int                                 i;
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

TIDComVariant2STR

public static str TIDComVariant2STR(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;
}

source :
http://krishhdax.blogspot.com/2012/05/ax2012-import-fixed-assets-table.html
https://axblog4u.wordpress.com/2012/09/06/tip-comvarianttype-for-real-values-in-dynamics-ax/

Convert null date from outer join on view to maxDate with SysComputedColumn

Example Code :

public static server str ReleasePostingDate()
{

    return SysComputedColumn::if(
            SysComputedColumn::isNullExpression(SysComputedColumn::returnField(
            tableStr(TIDAPUninvoiceConsignmentItem) ,
            identifierStr(LedgerJournalTableRealease),
            fieldStr(LedgerJournalTable, PostedDateTime))),
            SysComputedColumn::cast(strFmt("'%1'",DateTimeUtil::date(DateTimeUtil::maxValue())),'NVARCHAR'),
            SysComputedColumn::cast(SysComputedColumn::returnField(
            tableStr(TIDAPUninvoiceConsignmentItem) ,
            identifierStr(LedgerJournalTableRealease),
            fieldStr(LedgerJournalTable, PostedDateTime)),'NVARCHAR')
            );
}

create Address , Phone , Email for Worker example code

Static void WorkerHireAddressUpdate(HCMworker _worker,StreetName _address = "",str _Phone = "",str _email = "")
{
    
    HcmWorker                   hcmWorker;
    DirPersonRecId              _dirPersonRecid;
    DirPartyPostalAddressView   addressView;
    dirPartyContactInfoView     contactView;
    DirParty                    dirParty;
    container                   roles;
    
    HcmWorker = _worker;
    _dirPersonRecid = HcmWorker.Person;
    roles  = [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Home).RecId];
    
    //create address
    DirParty = DirParty::constructFromPartyRecId(_dirPersonRecid);
    if(_address)
    {
        //addressView.LocationName  
        //addressView.City    = 
        //addressView.State   = 
        //addressView.ZipCode =
        
        addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;
        addressView.Street  = _address;
        addressView.IsPrimary   = NoYes::Yes;
        addressView.Party   = _dirPersonRecid;
        DirParty.createOrUpdatePostalAddress(addressView,roles);
    }
    
    //create primary email
    if(_email)
    {
        contactView.clear();
        contactView.LocationName                = 'Primary Email';
        contactView.Locator                     = _email;
        contactView.Type                        = LogisticsElectronicAddressMethodType::Email;
        contactView.Party                       = _dirPersonRecid;
        contactView.IsPrimary                   = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }
    
    //create primary phone
    if(_Phone)
    {
        contactView.clear();
        contactView.LocationName                = 'Primary Phone';
        contactView.Locator                     = _Phone;
        contactView.Type                        = LogisticsElectronicAddressMethodType::Phone;
        contactView.Party                       = _dirPersonRecid;
        contactView.IsPrimary                   = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }

}

Get all financial dimension value and description from DimensionAttribute

Capture

static void TIDF_getDimensionValue(Args _args)
{
    DimensionAttribute              dimAttr;
    DimensionAttributeViewContract  dimAttrViewContract;
    Query                           q;
    QueryRun                        qR;
    QueryBuildDataSource            qbds;
    Common                          common;

    dimAttr = DimensionAttribute::findByName("1. STORE");


    dimAttrViewContract =  DimensionAttribute::getViewContract(dimAttr.RecId);

    q = new Query();

    changecompany (dimAttr.company())
    {
        qbds = q.addDataSource(dimAttrViewContract.parmViewId());
        qbds.addSortField(dimAttrViewContract.parmValueFieldId(),SortOrder::Ascending);

         // Apply ranges on language ID fields.
        DimensionAttribute::addTranslViewRangesToBackingEntityQuery(dimAttr.RecId, qbds);

        if (DimensionCache::instance().dimensionAttributeHasCategorization(dimAttr.RecId))
        {
            if (dimAttr.Type == DimensionAttributeType::MainAccount)
            {
                qbds.addRange(dimAttrViewContract.parmCategoryFieldId()).value(queryValue(LedgerChartOfAccounts::current()));
            }
            else if (dimAttr.Type == DimensionAttributeType::CustomList)
            {
                qbds = qbds.addDataSource(tablenum(DimensionAttributeDirCategory), 'DimAttDirCat');
                qbds.relations(true);
                qbds.joinMode(JoinMode::InnerJoin);
                qbds.fetchMode(QueryFetchMode::One2One);
                qbds.addLink(dimAttrViewContract.parmCategoryFieldId(), fieldnum(DimensionAttributeDirCategory, DirCategory));
                qbds.addRange(fieldnum(DimensionAttributeDirCategory, DimensionAttribute)).value(strfmt('%1', dimAttr.RecId));
            }
        }
    }

    qR = new QueryRun(q);
    while(qR.next())
    {
        common = qR.get(dimAttrViewContract.parmViewId());

        info(strFmt("%1 %2",common.getFieldValue(dimAttrViewContract.parmValueFieldName()), common.getFieldValue(dimAttrViewContract.parmNameFieldName())));
    }

}

Auto settle from custTransOpen when create journal payment lines via x++

example code :

public static void autoSettlementJournalFromPAYDetails(JournalId    _journalNum)
{
    CustVendOpenTransManager manager;
    CustTrans                custTrans;
    CustTransOpen            custTransOpen;
    LedgerJournalTrans       ledgerJournalTrans;
    AxLedgerJournalTable     axLedgerJournalTable;
    AxLedgerJournalTrans     axLedgerJournalTrans;
    ExchangeRateHelper       exchangeRateHelper;
    AmountCur                totalSettleAmount;
    AmountCur                amountCurDebit,amountCurCredit;
    HRSPayJournalDetails     hrsPayJournalDetails;
    HRSPayJournalTable       hrsPayJournalTable;
    
    int                      Row;


    AxLedgerJournalTable = AxLedgerJournalTable::newLedgerJournalTable(LedgerJournalTable::find(_journalNum));
    ttsBegin;
    //details journal lines from Pay Journal Details
    while select hrsPayJournalTable
    join hrsPayJournalDetails
    where
        hrsPayJournalTable.HRSPayJournalId == hrsPayJournalDetails.HRSPayJournalId
    &&  HRSPayJournalTable.JournalNum      == _journalNum
    {
        CustTrans    = CustTrans::findFromInvoice(hrsPayJournalDetails.ReferenceNumber,HRSPayJournalDetails.AccountNum);
        CustTransOpen  = CustTransOpen::findRefId(CustTrans.RecId);

        if(CustTransOpen)
        {

           //Create Trans ...
            axLedgerJournalTrans = new axLedgerJournalTrans();
            axLedgerJournalTrans.parmJournalNum(AxLedgerJournalTable.ledgerJournalTable().JournalNum);

            axLedgerJournalTrans.parmTransDate(today());
            axLedgerJournalTrans.parmDue(today());
            axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Cust);
            axLedgerJournalTrans.parmLedgerDimension(DimensionStorage::getDynamicAccount(hrsPayJournalDetails.AccountNum, LedgerJournalACType::Cust));
            axLedgerJournalTrans.parmAmountCurCredit(hrsPayJournalDetails.Credit);
            axLedgerJournalTrans.parmAmountCurDebit(hrsPayJournalDetails.Debit);
            axLedgerJournalTrans.save();

            manager = CustVendOpenTransManager::construct(axLedgerJournalTrans.ledgerJournalTrans());
            manager.updateTransMarked(custTransOpen,true);
            if(hrsPayJournalDetails.Debit)
            {
                manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Debit);
            }
            else
            {
                manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Credit);
            }

            //update after settle
            ledgerJournalTrans = axLedgerJournalTrans.ledgerJournalTrans();
            ledgerJournalTrans.selectForUpdate(true);
            ledgerJournalTrans.SettleVoucher =  SettlementType::SelectedTransact;
            ledgerJournalTrans.update();
            Row++;
        }

    }
    ttsCommit;
    
    info(strFmt("Total Lines settled : %1",Row));

}

Join ItemName from (EcoResProductTranslation ) and search name from InventTable to OnHand Form for search matter

it is common for user to search item based on name. but currently on AX 2012, onHand form can’t provide search based on item name or search name. It will take a little modification from technical side.

example how to do it :

1. you can join directly on form datasource or create view like example (i’m using the view that already created for another purpose)
Capture

2. on InventSumTable add relations like this.
Capture

3. then go to form onHand (InventOnHandItem), and add the view to datasource
Capture

4. open class InventDimCtrl_Frm_OnHand and modify method modifyQuery , on class declaration parameter, add new parameter FormDataSource with default null parameter. then define new variable QueryBuildDataSource.
Capture

5. on the last line of the method modifyQuery , add code like example :

if(_tidInventLookUp)
{
    qbsInventLookup = query.dataSourceName(_tidInventLookUp.name());

    //filter current company language
    qbr = SysQuery::findOrCreateRange(qbsInventLookup, fieldnum(TIDInventLookup, LanguageId));
    qbr.value(queryValue(CompanyInfo::languageId()));

    qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,Name));
    qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,NameAlias));

}

5. then back to the form, on datasource inventSUm, on executeQuery , modify the code from

void executeQuery()
{
    element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS);

    super();
}

to

void executeQuery()
{
    element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS,TIDInventLookUp_ds);

    super();
}

6. drag the field from newly add datasource on form to the grid :
Capture

7. Voila done, example result, :
Capture

note: remember to GROUPBY on code if you add new field to the form, or the field on form will show “UNRETRIEVED”

Change properties form control from listPage class example code

public void initializeQuery(Query _query)
{
    // ListPageLabelChange =>
    Common              externalRecord;
    FormDataSource      frmDs;
    FormRun             formRun;
    FormControl         frmCtrl;
    // ListPageLabelChange <=
    
    _query.dataSourceTable(tableNum(HRSBenefitEntitlement)).addRange(fieldNum(HRSBenefitEntitlement,Employment)).value(SysQuery::value(HcmEmployment::findByWorkerLegalEntity(HcmWorker::userId2Worker(curUserId()),CompanyInfo::find().RecId).RecId));
    
    super(_query);
    
     // ListPageLabelChange =>
    externalRecord = this.listPage().activeRecord(_query.dataSourceTable(tableNum(HRSBenefitEntitlement)).name());//No intrisic function for form DS?
    if(externalRecord.isFormDataSource())
    {
        frmDs   = externalRecord.dataSource();
        formRun = frmDs.formRun();
        if(formRun)
        {
            frmCtrl = formRun.design().controlName(formControlStr(TIDBenefitEntitlementListPage,HRSBenefitEntitlement_Entitlement));
            if(frmCtrl)
            {
                frmCtrl.userPromptText("newName");
            }
        }
    }
    // ListPageLabelChange <=
}

clear usage data if no changes on form.

ODBC from x++ example code

public client static void processMDBData(str _StrDSN, str _StrTableName, date _DateFrom, date _DateTo)
{
    #AviFiles
    SysOperationProgress    progress = new SysOperationProgress();
    OdbcConnection          C;
    Statement               S;
    LoginProperty           LP = new LoginProperty();
    ResultSet               RS;
    HcmPersonnelNumberId    _EmplID;
    TimeHour24              _Time;
    str                     strquery,strDSN,tableName;
    Date                    _Date;
    int                     Idx;
    HRSTMAttendanceTemp     attDataTemp;

    ;

    try
    {   
        strDSN    = _StrDSN;
        tableName = _StrTableName;

        ttsbegin;
        LP.setDSN(_strDSN);
        LP.setServer("S-AOS1TEST");

        C   = new OdbcConnection(LP);
        S   = C.createStatement();


        strquery    = strFmt("SELECT * FROM %1 where dateLog >= #%2# AND dateLog <= #%3#",tableName,_DateFrom,_DateTo);
        RS  = S.executeQuery(strquery);
        while (RS.next())
        {
            //Progress.incCount();
            _EmplID = Rs.getString(1);
            _Date   = RS.getDate(2);
            _Time   = str2time(RS.getString(3));
            Idx     = str2int(rs.getString(4));
            
            //insert to temp table
            if(_EmplID == '') continue;
            select forupdate attDataTemp
            where attDataTemp.PersonnelNumber == _EmplID && attDataTemp.Time == _Time
                                  && attDataTemp.TransDate == _Date;
            if(!attDataTemp)
            {
                attDataTemp.clear();
                attDataTemp.PersonnelNumber      = _EmplID;
                attDataTemp.Time        = _Time;
                attDataTemp.TransDate   = _Date;
                attDataTemp.Idx         = Idx;
                attDataTemp.insert();
            }
            else
            {
                attDataTemp.Idx         = Idx;
                attDataTemp.ClockId     = '';
                attDataTemp.update();
            }

            Progress.setText(strfmt("Upload Id %1 - %2 %3", _EmplID, _date, time2str(_time, 1, 1)));
        }
        ttsCommit;

   }
   catch
   {
       ttsAbort;
       return;
   }
}

example code on custom class ApprovalResubmitActionMgr

example code :

public static void main(Args _args)
{
    recID                           recID               = _args.record().RecId;
    tableId                         tableId             = _args.record().TableId;
    HcmEmploymentLeave              HcmEmploymentLeave  = _args.record();
    WorkflowWorkItemTable           workItem            = _args.caller().getActiveWorkflowWorkItem();
    str                             menuItemName        = _args.menuItemName();
    WorkflowWorkItemActionDialog    workflowWorkItemActionDialog;
    WorkflowSubmitDialog            workflowSubmitDialog;
     
    Debug::assert(tableId == tablenum(HcmEmploymentLeave));
    Debug::assert(recId != 0);

    // The method has not been called correctly.
    if (tableId != tablenum(HcmEmploymentLeave) ||
        recId == 0)
    {
        throw error(strfmt("@SYS19306", funcname()));
    }

    // The journal has an active workflow.
    if (workItem.RecId > 0)
    {
        try
        {
            // The journal does support workflow approvals.
            workflowWorkItemActionDialog = WorkflowWorkItemActionDialog::construct( workItem,
                                                                                    WorkflowWorkItemActionType::Resubmit,
                                                                                    new MenuFunction(_args.menuItemName(),_args.menuItemType()));
            workflowWorkItemActionDialog.run();
            if (workflowWorkItemActionDialog.parmIsClosedOK())
            {

                workItem    = _args.caller().getActiveWorkflowWorkItem();
                WorkflowWorkItemActionManager::dispatchWorkItemAction(  workItem,
                                                                    workflowWorkItemActionDialog.parmWorkflowComment(),
                                                                    workflowWorkItemActionDialog.parmTargetUser(),
                                                                    WorkflowWorkItemActionType::Resubmit,
                                                                    _args.menuItemName(),
                                                                    false);
              
            }
        }

        catch(Exception::Error)
        {
            throw error(strfmt("Leave %1 can't be submitted", HcmEmploymentLeave.HRSLeaveReqId));
        }
    }
    // Make the form refresh its common workflow UI controls.
    _args.caller().updateWorkflowControls();
}

error : Errors in the high-level relational engine. A connection could not be made to the data source with the of ”,

i found this error when i tried deploying cube via ax wizard.
Capture

how to fixed error :
1. Just run the analysis service as admin domain or user domain that have the right priveleges. Set it up on SQL Server Management Configuration.
Capture3

2. Then redeploy the cube :
Capture2

related source :
1. https://msdn.microsoft.com/en-us/ms345578.aspx
2. https://technet.microsoft.com/en-us/library/cc197365%28v=office.12%29.aspx

send email via x++ example code

static boolean sendEmail(str toAddress, str theSubject, str body, str fromAddress)
{
    SysEmailParameters parameters = SysEmailParameters::find();

    System.Net.Mail.SmtpClient            mailClient;
    System.Net.Mail.MailMessage           mailMessage;
    System.Net.Mail.MailAddress           mailFrom;
    System.Net.Mail.MailAddress           mailTo;
    System.Net.Mail.MailAddressCollection mailToCollection;
    System.Net.Mail.MailAddressCollection mailCCCollection;
    System.Net.Mail.AttachmentCollection  mailAttachementCollection;
    System.Net.Mail.Attachment            mailAttachment;
    System.Net.NetworkCredential          mailCredentials;

    System.Exception                      e;
    List            toList;
    ListEnumerator  le;


    SMTPRelayServerName mailServer;
    SMTPPortNumber mailPortNumber;
    SMTPUserName mailUserName;
    SMTPPassword mailPassword;

    InteropPermission interopPermission;
    ;

    try
    {
        interopPermission = new InteropPermission(InteropKind::ComInterop);
        interopPermission.assert();

        if (parameters.SMTPRelayServerName)
        {
            mailServer = parameters.SMTPRelayServerName;
        }
        else
        {
            mailServer = parameters.SMTPServerIPAddress;
        }

        mailPortNumber  = parameters.SMTPPortNumber;
        mailUserName    = parameters.SMTPUserName;
        mailPassword    = SysEmailParameters::password();
        mailCredentials = new System.Net.NetworkCredential(mailUserName,mailPassword);


        mailClient = new System.Net.Mail.SmtpClient(mailServer, mailPortNumber);
        mailFrom = new System.Net.Mail.MailAddress(parameters.SMTPUserName); //fromAddress);

        toList = strSplit(toAddress,';');
        le = toList.getEnumerator();
        le.moveNext();

        mailTo  = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
        mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);

        mailToCollection = mailMessage.get_To();
        while(le.moveNext())
        {
            mailToCollection.Add(strLTrim(strRTrim(le.current())));
        }


        mailMessage.set_From(mailFrom);
        mailMessage.set_ReplyTo(mailFrom);
        mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
        mailMessage.set_Subject(theSubject);
        mailMessage.set_IsBodyHtml(true);
        mailMessage.set_Body(body);

        if(parameters.TIDUsingSSLorTSL)
        {
            mailClient.set_EnableSsl(true);
            mailClient.set_UseDefaultCredentials(false);
            mailClient.set_Credentials(mailCredentials);
        }
        else
        {
            mailClient.set_EnableSsl(false);
        }


        mailClient.Send(mailMessage);

        mailMessage.Dispose();

        CodeAccessPermission::revertAssert();

        //info("Email has been send.");
    }
    catch (Exception::CLRError)
    {
        e = ClrInterop::getLastException();
        while (e)
        {
            info(e.get_Message());
            e = e.get_InnerException();
        }
        CodeAccessPermission::revertAssert();
        return false;
    }
}

source : my senior’s code

find Physical Inventory Qty by date

i modified code originated from DAXDUDE ,
i added inventQty = inventSumDateDim.postedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty() to get physical inventory

static void TIDF_getInventPhysicalbyDate(Args _args)
{
    InventDimParm inventDimParmCrit;
    InventSumDateDim inventSumDateDim;
    InventDim inventDim = InventDim::find('AllBlank');
    InventQty inventQty;
    ;

    // Set the dimensions
    inventDimParmCrit.initFromInventDim(inventDim);
    inventSumDateDim = inventSumDateDim::newParameters(today(), '010410003', inventDim, inventDimParmCrit);
    inventQty =  inventSumDateDim.receivedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty();

    info(strFmt("%1",inventQty));
}

source : http://daxdude.blogspot.com/2012/11/ax-2012-find-on-hand-inventory-by-date.html

error : The DefaultValue expression for the report parameter ‘AX_CompanyName’ contains an error: Request for the permission of type System.Security.Permissions.EnvironmentPermission

this error occur when I run the SSRS report. Here are to solution :

1. on your windows server that contains SQL Server, go to C:\Program Files\Microsoft SQL Server, then do find the MRS for your environment.
Capture2
 
2. find rssrvpolicy.config
Capture
 
3. replace PermissionSetName=”Execution” on class=”UnionCodeGroup” with PermissionSetName=”FullTrust”
capture3

source : https://msdn.microsoft.com/en-us/library/ms155866.aspx

error : For views relation fields in query can not come from base or derived tables.

This error occurs at on standar AX View because i add new relation on Derived table.

Capture

example base table :
Capture

how to fix it :
1. At the example views QUERY HcmBenPolWkrPosDtlInfo, find OMOperatingUnit datasource, and find the relation on the table.
Capture

2. Set the RELATIONS properties to NO on query datasource table , then right click on the relations, add new relations and manually configure the tables relations, do this for the all base table in datasource too
Capture

3. Then restore the query views, do restore to on the views, then re-compile the view
Capture

refresh DB Sync error log

i found error log that make no sense after DB sync on AX 2012 R2 CU7. the table that the error pointed to already deleted, but the error still occurred.

here is the solution :
“If you are getting synchronization errors that do no make sense or that you think you should have already resolved there may be old records left in the SQLsyncinfo table or have old or incorrect object ID references. If you suspect this make a database backup, shutdown the AOS, in SQL run truncate table SQLSYNCINFO, start the AOS and run a new Synchronize from the AOT.”

source : http://blogs.msdn.com/b/axsupport/archive/2012/09/19/troubleshooting-aot-synchronize-errors.aspx

Kernel version and application version number are different on AX 2012 R3 CU8

i installed AX 2012 R3, then i installed R3 CU8 update with offline package.
the problem is after installed, the version number is different.
1432569397994

although it was running well on the first heavy test, i’m afraid the R2 crash issue because different build number will raise again at R3.
to make it same , install AX R3 with slipstream CU8 package update, here is the full guide from MSDN blog :
http://blogs.msdn.com/b/axsupport/archive/2015/04/21/how-to-slip-stream-ax-2012-r3-cu-8.aspx

error : Failed to create a session, confirm that the user has the proper privileges to log on to Microsoft Dynamics

I just recently installed new fresh AX 2012 R3 and importing data using Test Data Tools :
https://technet.microsoft.com/en-us/library/dn756356.aspx .

but somehow when i start sync db after full compilation and full CIL, i got following error :
“Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics”.

i logged in using domain administrator.

here is the solution :
on the user mapping at SQL Server , make sure the db owner for administrator is checked. if you are using another user, just make sure db.datareader and db.datawriter is checked.
Untitled

voila !! , the ax db sync successfully

more info :
https://community.dynamics.com/ax/f/33/t/48661

Add new custom webparts (Data Grid) to Enterprise Portal Quick Launch sidebar tutorial

This is step by step to create custom webparts and added it to Enterprise Portal (EP)
1. at Visual Studio, add new EP VS Project
Capture

2. Right click at the solution project, and click add new item, select Microsoft Dynamics Ax -> EP User Control with form and grid.
Capture

3. Go to AOT, add new data set , then add datasource, link it to your table , do compile and do INCREMENTAL CIL.
Capture

4. Go back to VS, refresh schema for the datasource, and select your newly created datasource
Capture

5. Make sure your column etc are inside axForm, fill the properties DataKeyNames,DataMember and DataSourcesID
Capture

6. at gridview, select datasource, and edit colomn, select field you want to show on the grid
Capture

7. right click on VS solution project, and choose “Add your_webParts to AOT”
Capture

8. go back to AOT, on Web -> Web Content -> Managed , find your webcontrol, right click on it, and Deploy to EP, then choose which module this webcontrol belongs to. After you deploy it , AX will create an URL Menu Item automatically at (Web -> Web Menu Items -> URLs) and Page Definition (Web -> Web Files -> Page Definition
Capture

9. If your menu is on submenu of the module, example Home\EmployeeServices\ManageTime , just change it on properties “Web Module” at Page Definition. and change the url on on web url properties
Capture
Capture

10. The next step is we want to add newly web parts to EP Quick Launch. Find the page you want to customize on “Web Modules” on AOT, on the properties, look at value of properties “Quick Launch”
Capture

10. Go to Web -> Web Menus , find the Quick Launch, and drag and drop the newly add url menu item to Quick Launch Menu, then deploy the EP from System Admin -> Setup -> Enterprise Portal -> Deployment
Capture

11. go to Web -> Web Modules . just right click and deploy on the module you customize.
Capture

12. Last, restart IIS for refreshing the EP with newest customize, with command prompt see post :
https://hellodax.com/2015/05/22/restart-iis-command-prompt/
Capture

13. Preview
Capture

Restart IIS command prompt

To restart IIS using the IISReset command-line utility
1.From the Start menu, click Run.
2.In the Open box, type cmd, and click OK.
3.At the command prompt, type iisreset /noforce computername, and press ENTER.
4.IIS attempts to stop all services before restarting. The IISReset command-line utility waits up to one minute for all services to stop. If the services cannot be stopped within one minute, all IIS services are terminated, and IIS restarts.

example :
Capture

source :
https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/003ed2fe-6339-4919-b577-6aa965994a9b.mspx?mfr=true

get LedgerDimension combination RecId example code

source : http://kiwiaxguy.blogspot.com/2014/07/setting-ledgerdimension-field-on.html

i modify some code from the source because the dimension name is hardcoded,

on class global :

public static container getDimensionNameTID()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &amp;&amp;
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

getLedgerDimension example code :

public static RecId getLedgerDimensionCombination(MainAccountNum _ledgerAccount, container dimValue)
{
    DimensionServiceProvider DimensionServiceProvider = new DimensionServiceProvider();
    LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
    DimensionAttributeValueContract   ValueContract;
    List ListValueContract = new List(Types::Class);
    dimensionAttributeValueCombination dimensionAttributeValueCombination;
    DimensionStorage          dimStorage;
    container dimensionName = Global::getDimensionNameTID();
    int       loopDimValue = 1;

    if(conLen(dimValue) > 0)
    {
        while(loopDimValue <= conLen(dimValue))
        {
            ValueContract = new DimensionAttributeValueContract();
            ValueContract.parmName(conPeek(dimensionName,loopDimValue)) ;
            ValueContract.parmValue(conPeek(dimValue,loopDimValue));
            ListValueContract.addEnd(ValueContract);
            loopDimValue++;
        }

        LedgerAccountContract.parmMainAccount(_ledgerAccount);
        LedgerAccountContract.parmValues(ListValueContract);
        dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
        dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
        return dimensionAttributeValueCombination.RecId;
    }
    else
    {
        return 0;
    }

}

example code on how to use it :

static void TIDF_getLedgerDim(Args _args)
{
    DimensionValue       dim1,dim2;
    container conDimValue;
    MainAccountNum       mainAccountNum;

    mainAccountNum = "6100-1001";
    dim1           = "5102";
    dim2           = "OPERATIO";
    conDimValue = [dim1,dim2];

    info(strFmt("%1",getLedgerDimensionCombination(mainAccountNum,conDimValue)));


}

Init field value from datasource , show on Enterprise Portal AXBoundField

on AOT dataset method init() :
Capture

X++ code on init method example:

public void init()
{
    super();
    DNLLeaveRequest.DNLEmplRecId  = DNLEmployeeTable::findByUserId().RecId;
    DNLLeaveRequest.LeaveDateFrom = today();
    DNLLeaveRequest.LeaveDateTo   = today();
}

or you can code it on Tables\initvalue()

XML code on visual studio for EP pages :

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="TIDDNLLeaveRequestEP.ascx.cs" Inherits="TIDDNLLeaveRequestEP" %>
<dynamics:AxDataSource ID="AxDataSource1" runat="server" 
    DataSetName="TIDLeaveRequestDS" ProviderView="DNLLeaveRequest" 
    oninitializeddatasetrun="AxDataSource1_InitializedDataSetRun">
</dynamics:AxDataSource>
<dynamics:AxForm ID="AxForm1" runat="server" DataSourceID="AxDataSource1" 
    DataKeyNames="RecId,Added,AdvanceUsed,BlankLeave,carryused,ContactPhone,createdBy,createdDateTime,dataAreaId,DNLEmplRecId,DNLLeaveTypeRecId,IdApproval,IdPersonSubstitute,LeaveDateFrom,LeaveDateTo,modifiedBy,modifiedDateTime,Remarks,RequestDate,State,StatusFlag,SubmittedBy,SubmittedDateTime,TableId,TotalLeaveDays,TravellingLocation,getEmplName**,GetEntitlement**,getExpiration**,getLeaveDesc**,mostRecentComment**,requiredActionDueDate**,titleFields**" 
    DataMember="DNLLeaveRequest_Current" AutoGenerateInsertButton="True" 
    DefaultMode="Insert" UpdateOnPostBack="true" oninit="AxForm1_Init"  >
    <dynamics:AxMultiColumn ID="AxMultiColumn1" runat="server">
    
 
    
 
        <dynamics:AxColumn ID="AxColumn2" runat="server">
            <dynamics:AxGroup ID="groupLeaveRequest" runat="server" FormID="AxForm1" 
                Caption="Leave Request" CaptionAlign="Top" HorizontalAlign="Left" 
                Width="300px">
                <Fields>
                    <dynamics:AxReferenceBoundField DataField="DNLEmplRecId" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="DNLEmplRecId" Mandatory="True">
                    </dynamics:AxReferenceBoundField>
                    <dynamics:AxReferenceBoundField AutoPostBack="True" 
                        DataField="DNLLeaveTypeRecId" DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="DNLLeaveTypeRecId" 
                        Mandatory="True">
                    </dynamics:AxReferenceBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateFrom" 
                        DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="LeaveDateFrom">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateTo" 
                        DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="LeaveDateTo" 
                        Mandatory="True" >
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="Remarks" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="Remarks">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="TotalLeaveDays" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="TotalLeaveDays">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField DataField="GetEntitlement**" DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" ReadOnly="True">
                    </dynamics:AxBoundField>
                </Fields>
            </dynamics:AxGroup>
        </dynamics:AxColumn>
    </dynamics:AxMultiColumn>
</dynamics:AxForm>

C# code on visual studio for EP pages

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Microsoft.Dynamics.AX.Framework.Services.Client;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;
using Microsoft.Dynamics.Framework.Portal;
using Microsoft.Dynamics.Framework.BusinessConnector.Proxy;

public partial class TIDDNLLeaveRequestEP : System.Web.UI.UserControl
{
    DateTime dateFrom;
    DateTime dateTo;
    Int64 recIdLeaveType;
   
    

    #region customMethod
    private ISession AxSession
    {
        get 
        {
            AxBaseWebPart webPart = AxBaseWebPart.GetWebpart(this);
            return webPart == null ? null : webPart.Session;
        }
    }
    static AxBoundField GetField(DataControlFieldCollection fields, string name)
    {
        foreach (DataControlField field in fields)
        {
            // Is this the field being searched for?
            AxBoundField boundField = field as AxBoundField;
            if (boundField != null && String.Compare(boundField.DataField, name, true) == 0)
            {
                return boundField;
            }

        }
        // Nothing found, so return null.
        return null;
    }

    private DataSetViewRow CurrentRow
    {
        get
        {
            try
            {
                DataSetView dataSetView;
                dataSetView = this.AxDataSource1.GetDataSet().DataSetViews["DNLLeaveRequest"];
                return (dataSetView == null) ? null : dataSetView.GetCurrent();
            }
            catch (System.Exception)
            {
                return null;
            }
        }
    }

    #endregion customMethod

    protected void AxForm1_Init(object sender, EventArgs e)
    {
        //this.loadFirst();
        this.AxForm1.DefaultMode = DetailsViewMode.Insert;
        this.AxForm1.AutoGenerateInsertButton = true;

        AxBoundField axbfNoNPK;
        axbfNoNPK = GetField(groupLeaveRequest.Fields, "DNLEmplRecId");
        axbfNoNPK.ReadOnly = true; 
    }


    protected void Page_Load(object sender, EventArgs e)
    {

    }
 
   
    protected void AxDataSource1_InitializedDataSetRun(object sender, AxDataSourceDataSetEventArgs e)
    {
        e.DataSet.Init();
    }
}

preview :
Capture

error: The data source is not embedded within a (parent) data source.

This error will occur when you add new dataSource with function “addDataSource(tableNum(MyTable))” but not specify which it’s parent datasource

so example code before :

qbsInventDim = _query.addDataSource(tableNum(WMSLocation));

code after :

qbsInventDim = _query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(WMSLocation))

Print Query-Based SSRS via X++ example

code example :

public void PrintPenarikanBarang(TECTradeInId tradeInCode)
{
    SrsReportRunController                  reportRunController;
    Map                                     queryContracts;
    MapEnumerator                           mapEnum;
    Query                                   query;
    QueryBuildRange                         range;

    SrsReportDataContract                   contract;
    TECTradeInPenarikanBarangReportContract     rdpContract;

    if(TECTradeInTable.TECTradeInCode)
    {
        reportRunController = new SrsReportRunController();
        reportRunController.parmReportName(ssrsReportStr(TECPenarikanBarangTradeIn ,Report));
        reportRunController.parmLoadFromSysLastValue(false);

        // Set printer settings (print to file, format, filename, etc).
        contract    = reportRunController.parmReportContract();

        rdpContract = contract.parmRdpContract() as TECTradeInPenarikanBarangReportContract;

        queryContracts = contract.parmQueryContracts();
        mapEnum = queryContracts.getEnumerator();
        while(mapEnum.moveNext())
        {
            query = mapEnum.currentValue();
            range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(TECTradeInTable)),fieldNum(TECTradeInTable, TECTradeInCode));
            range.value(queryValue(tradeInCode));
        }

        reportRunController.runReport();

    }

}

Modify Query on Report DP Class example

[SysEntryPointAttribute]
public void processReport()
{
    //query class
    QueryRun            qr;
    Query               q;
    QueryBuildDataSource qbds;
    //table object
    InventSum           inventSum;
    WMSLocation         wmsLocation;
    RetailStoreTable    retailStoreTable;
    TIDLocType          tidLocType;
    //
    str                 headerName;

    contract = this.parmDataContract() as TIDStoreDisplayReportContract;
    parmShowQty = contract.parmShowQty();
    parmFilterItem = contract.parmStoreDisplayFilterItem();

    q = this.parmQuery();

    q.dataSourceTable(tableNum(WMSLocation)).addRange(fieldNum(WMSLocation,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
    switch(parmFilterItem)
    {
        case TIDStoreDisplayFilterItem::OnlyDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::Yes));
            headerName = "Only Display Item";
            break;
        case TIDStoreDisplayFilterItem::NoDisplay :
            qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::NoExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "No Display Item";
            break;
        case TIDStoreDisplayFilterItem::NotInDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::No));
            headerName = "Not in Display Item";
            break;
        case TIDStoreDisplayFilterItem::HaveDisplay :
            qbds =  q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::ExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "Have Display Item";
            break;
        default :
            headerName = "Store Display Item";
            break;
    }

    // only display > 0
    q.dataSourceTable(tableNum(InventSum)).addRange(fieldNum(InventSum,AvailPhysical)).value(queryValue(">0"));

    qr = new QueryRun(q);

    while(qr.next())
    {
        inventSum   = qr.get(tableNum(inventSum));
        wmsLocation = qr.get(tableNum(wmsLocation));
        tidLocType  = qr.get(tableNum(TIDLocType));

        tempTable.clear();
        tempTable.headerName        = headerName;
        tempTable.ItemId            = inventSum.ItemId;
        tempTable.EcoResDescription = TIDItemDisplayCustom::getItemDescription(inventSum.ItemId);
        tempTable.EcoResProductName = TIDItemDisplayCustom::getItemName(inventSum.ItemId);
        tempTable.NameAlias         = TIDItemDisplayCustom::getItemModel(inventSum.ItemId);
        tempTable.AvailPhysical     = inventSum.AvailPhysical;
        select firstOnly retailStoreTable where retailStoreTable.inventLocation == wmsLocation.inventLocationId;
        tempTable.Store             = retailStoreTable.StoreNumber;
        tempTable.WMSLocationId     = wmsLocation.wMSLocationId;
        tempTable.IsDisplay         = tidLocType.TIDIsDisplay;
        tempTable.insert();

    }

}

Boosting ax compile process with axbuild.exe

AX single compilation from AOT can take hours. around 4-10 hours.
depends on your server hardware specs.

but with ax build, you can boost up the compilation process because its using pararel compilation.

on MSDN : https://msdn.microsoft.com/en-us/library/dn528954.aspx

and this feature only works on AX 2012 R2 CU7 and above version.

example on command prompt (run as admin) :
cd C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin

axbuild.exe xppcompileall /aos=01

Capture

Capture2

Capture3

get currency exchange rate using AX Class

static CurrencyExchangeRate getExchRate(CurrencyCode fromCurrency, CurrencyCode toCurrency, TransDate exchangeDate, RefRecId exchangeRateTypeRecId)
{
    ExchangeRateHelper exchangeRateHelper;
    ;

    exchangeRateHelper = ExchangeRateHelper::construct();
    exchangeRateHelper.parmLedgerRecId(Ledger::current());
    exchangeRateHelper.parmFromCurrency(fromCurrency);
    exchangeRateHelper.parmToCurrency(toCurrency);
    exchangeRateHelper.parmExchangeDate(exchangeDate);
    exchangeRateHelper.parmExchangeRateTypeRecId(exchangeRateTypeRecId);

    return ExchangeRateHelper::displayStoredExchangeRate_Static(exchangeRateHelper.getExchangeRate1(),
           ExchangeRateCurrencyPair::getExchangeRateDisplayFactor(fromCurrency, toCurrency, exchangeRateTypeRecId, true));
}

SQL Query Search all DB

i found nice query for searching all db. this query is useful when i need to trace some data.

/* Reto Egeter, fullparam.wordpress.com */

DECLARE	@SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM 	INFORMATION_SCHEMA.TABLES
		WHERE 		TABLE_TYPE = 'BASE TABLE'
			AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
			AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND	OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
	)
	IF @TableName IS NOT NULL
	BEGIN
		DECLARE @sql VARCHAR(MAX)
		SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(''' + @TableName + ''', 2)
				AND	TABLE_NAME	= PARSENAME(''' + @TableName + ''', 1)
				AND	DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
				AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
		INSERT INTO @ColumnNameTable
		EXEC (@sql)
		WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
		BEGIN
			PRINT @ColumnName
			SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
			SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
			WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
			ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
					WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
					ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
			INSERT INTO #Results
			EXEC(@sql)
			IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
			BEGIN
				SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
					' FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
					WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
					ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
				EXEC(@sql)
			END
			DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
		END	
	END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

source : https://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

Export License Key using job

static void OutputLicenseToText(Args _args)
{
    #define.licenseVersion(2)
    #define.KeywordLen(20)
    #define.keywordLicense('License')
    #define.keywordProperties('Properties')
    #define.keywordCodes('Codes')
    #define.keywordCodeLine('CodeLine')
    #define.keywordDate('Date')
    #define.keywordSerial('Serial')
    #define.keywordValue('Value')
    #define.blank('')
    #define.space1(' ')
    #define.space2('  ')
    #define.space3('   ')
    #define.spaceHash(' #')
    #define.OutputFilename(@'C:\OutputLicenseKeys.txt')

    #define.keywordInfo(1)
    #define.keywordWarning(2)

    SysConfig           sysConfig;
    SysLicenseCodeSort  sysLicenseCodeSort;
    container           fileOut;
    int                 i;
    System.IO.StreamWriter  sw;
    InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);
    ;

    fileOut += "LicenseVersion "    + strfmt("%1", #licenseVersion);
    fileOut += #blank;
    fileOut += #keywordLicense      + #spaceHash + xSysConfig::find(ConfigType::LicenseName,0).Value;
    fileOut += #blank;
    fileOut += #space1  + #keywordProperties;
    fileOut += #space2  + "Name"            + #spaceHash    + xSysConfig::find(ConfigType::LicenseName,0).Value;
    fileOut += #space2  + #keywordSerial    + #spaceHash    + xSysConfig::find(ConfigType::SerialNo,0).Value;
    fileOut += #space2  + #keywordDate      + #spaceHash    + xSysConfig::find(ConfigType::LicenseName,1).Value;
    fileOut += #space1  + "EndProperties";
    fileOut += #blank;
    fileOut += #space1  + #keywordCodes;

    // Build CodeLines
    while select sysConfig
        where sysConfig.configType  == ConfigType::AccessCodes   &&
              sysConfig.value       != #blank
        join sysLicenseCodeSort
        order by SortIdx
        where sysLicenseCodeSort.Id == sysConfig.id
    {
        fileOut += #space2  + #keywordCodeLine  + #spaceHash    + int2str(sysConfig.id + 1);
        fileOut += #space3  + #keywordValue     + #spaceHash    + sysConfig.value;
        fileOut += #space2  + "EndCodeLine";
        fileOut += #blank;
    }

    fileOut += #blank;
    fileOut += #space2  + "EndCodes";
    fileOut += #space1  + "EndLicense";

    // Begin file output
    perm.assert();

    sw = new System.IO.StreamWriter(#OutputFilename);

    for (i=1; i<=conLen(fileOut); i++)
    {
        sw.WriteLine(conPeek(fileOut, i));
    }

    sw.Flush();
    sw.Close();
    sw.Dispose();

    CodeAccessPermission::revertAssert();

    info("License successfully output to " + #OutputFilename);
}

source : http://alexondax.blogspot.com/2011/01/how-to-export-license-keys-that-are.html

get exchange rate example code

static void TIDF_ExchangeRate(Args _args)
{
    ExchangeRate     exchangeRate;
    ExchangeRateType ExchangeRateType; 
    ExchangeRateCurrencyPair exchangeRateCurrencyPair;
    real             exchRate;
    
    CurrencyCode fromCurrency  = "USD";
    CurrencyCode toCurrency    = "IDR";
    TransDate    transDate     = today();
    
    
    select firstonly exchangeRateCurrencyPair 
    where 
        exchangeRateCurrencyPair.ExchangeRateType == Ledger::find(Ledger::current()).TECFakturPajakAdjustExchangeRateType
    &&  exchangeRateCurrencyPair.FromCurrencyCode == fromCurrency 
    &&  exchangeRateCurrencyPair.ToCurrencyCode   == toCurrency;
    exchRate = exchangeRate::findByDate(exchangeRateCurrencyPair.RecId,transDate).ExchangeRate;
    info(strFmt("%1",exchRate/100));
    
}

Upgrade SSRS from AX 2012R2 to AX2012R3 error

when deploying SSRS report from ax 2012 R2 to R3 i got following error :

Capture

and i did some search for this error , here is the solution :

1. http://mafsarkhan.blogspot.com/2014/04/upgrade-ssrs-reports-from-dynamics-ax.html

2. https://technet.microsoft.com/en-us/library/hh389762.aspx

so i did export all the R2 reports in one project, replace all the version from 6.2.0.0 to 6.3.0.0 with notepad++, and reimport again, and done :d
Capture2

Change caption form via code example

public void init()
{
    super();

    element.updateDesign(InventDimFormDesignUpdate::Init);

    //add by fanddy
    if(element.args().record().TableId == tableNum(TECCustInOutClaimTable))
    {
        TECCustInOutClaimTable = element.args().record();
        element.design().caption(strFmt("Customer Claim Line : %1, Receipt Number : %2",TECCustInOutClaimTable.TECCustInOutClaimId,TECCustInOutClaimTable.RetailReceiptId));
    }

    element.disableNewButton();


}

fix budget exceeded still can submit Ledger Journal WorkFlow , ledgerJournalCheckPost

on class LedgerJournalWFApprSubmitToWF \ main

//add by fanddy, fix Bugs exceed budget still can submit on 30/03/2015
    LedgerJournalCheckPost ledgerJournalCheckPost;
	FormDataSource fds;
    //end add by fanddy


    Debug::assert(tableId == tableNum(LedgerJournalTable));
    Debug::assert(recId != 0);

    // The method has not been called correctly.
    if (tableId != tableNum(LedgerJournalTable) ||
        recId == 0)
    {
        throw error(strFmt("@SYS19306", funcName()));
    }
    //add by fanddy, fix Bugs exceed budget still can submit on 30/03/2015
    else
    {
        ledgerJournalCheckPost= ledgerJournalCheckPost::newLedgerJournalTable(ledgerJournalTable,NoYes::No,NoYes::Yes);
        ledgerJournalCheckPost.run();

        if(!ledgerJournalCheckPost.getParmCheckJournalTID())
        {
            throw error("Can't submit this journal");
        }
		//refresh form dataSource , fix error when submit must restore form dataSource 
		fds = args.record().dataSource();
		fds.research(true);
		fds.refresh();
    }
    //end add by fanddy

i did some modify on class LedgerJournalCheckPost for returning checkJournal result.
on LedgerJournalCheckPost\run method

//edited by fanddy
checkResultTID = this.checkJournal();

set default dimension value

public static RecId getNewDefaultDimension(RecId defaultDimension, Name dimName, str 255 dimValue)
{
    container c;
    RecId newdefaultDimension;
    int i;
    ;

    c = AxdDimensionUtil::getDimensionAttributeValueSetValue(defaultDimension);
    i = conFind(c, dimName);
    if (!i && !dimValue)
        return defaultDimension;
    if (i)
    {
        c = conDel(c, i+1, 1);
        c = conDel(c, i, 1);
    }
    if (dimValue)
    {
        c += dimName;
        c += dimValue;
    }

    c = conDel(c, 1, 1);
    c = conIns(c, 1, conLen(c) / 2);
    newdefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(c);

    return newdefaultDimension;
}

get dimension name , return container

public static container getDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &amp;&amp;
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

Custom Number Sequence Reference Example

1. Create new field as picture below

Capture1

2. Go to the form, right click and choose restore to reload new field we added before. On the field method, add code below
 

public Common lookupReference(FormReferenceControl _formReferenceControl)
{
    return NumberSequenceTable::lookupReference(_formReferenceControl);
}

 
Capture2

3. Add reference group for the field at the form as picture below , and you are done.

Capture3

4. Just run the form for testing.

Capture4

Custom lookup example 2

public void lookup()
{
   // super();

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(SalesTable), this);
    Query query = new Query();
    QueryBuildDataSource qbds,qbds2,qbds3;

    qbds = query.addDataSource(tablenum(SalesTable));
    qbds.addOrderByField(fieldNum(SalesTable,SalesId));
    qbds.addOrderByField(fieldNum(SalesTable,SalesName));
    qbds.addOrderByField(fieldNum(SalesTable,SalesType));
    qbds.addOrderByField(fieldNum(SalesTable,CustAccount));

    qbds.addGroupByField(fieldNum(SalesTable,SalesId));
    qbds.addGroupByField(fieldNum(SalesTable,SalesName));
    qbds.addGroupByField(fieldNum(SalesTable,SalesType));
    qbds.addGroupByField(fieldNum(SalesTable,CustAccount));
    
    qbds2 = qbds.addDataSource(tableNum(TECDeliveryOrderTable));
    qbds2.joinMode(JoinMode::NoExistsJoin);
    qbds2.relations(false);
    qbds2.addLink(fieldNum(SalesTable,SalesId),fieldNum(TECDeliveryOrderTable,TIDUnlockSalesId));

    qbds3 = qbds.addDataSource(tableNum(SalesLine));
    qbds3.joinMode(JoinMode::InnerJoin);
    qbds3.relations(false);
    qbds3.addLink(fieldNum(SalesTable,SalesId),fieldNum(SalesLine,SalesId));
    qbds3.addRange(fieldNum(SalesLine,ItemId)).value(InventParameters::find().TIDItemIdServices);


    sysTableLookup.addLookupfield(fieldnum(SalesTable, SalesId),true);
    sysTableLookup.addLookupfield(fieldNum(SalesTable,SalesName));
    sysTableLookup.addLookupfield(fieldNum(SalesTable,SalesType));
    sysTableLookup.addLookupfield(fieldNum(SalesTable,CustAccount));
    sysTableLookup.addLookupMethod("customerName");
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}

Modify AOT items properties via code x++

TreeNode    objTreeNode;
 
objTreeNode = TreeNode::findNode(@"\Menu Items\Display\");
 
if (objTreeNode)
{
    objTreeNode = objTreeNode.AOTfirstChild();
    /*
    change objTreeNode object properties
    */
    while (objTreeNode)
    {            
        objTreeNode.AOTsetProperties("PROPERTIES\n ViewUserLicense  #" + 'TASK' + "\n  MaintainUserLicense  #" + 'TASK' + "\n ENDPROPERTIES\n");
        objTreeNode.AOTsave();
        objTreeNode = objTreeNode.AOTnextSibling();
    }
}

source : http://dotnetxperience.blogspot.com/2012/10/modify-aot-items-from-code-ax-2012.html

Job example using COMVariant

static void TIDF_UploadUnitConversion(Args _args)
{
    //coded by fanddy
    // for upload Unit Conversion Purpose
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    COMVariantType          type;
    int                     row;

    UnitOfMeasureConversion UnitOfMeasureConversion;
    //table's field data
    ItemId                  itemid;
    UnitOfMeasureSymbol     unitTo;
    unitOfMeasureSymbol     unitFrom;
    Factor                  factor;



    Filename                fileName = 'C:\\#TECTURA#\\Master Data\\UploadFormula\\UnitConversionrev4.xlsx';

    int                     loop;
    int                     lastSerial = 1;


    application = SysExcelApplication::construct();
    workbooks   = application.workbooks();

    try
    {
        workbooks.open(fileName);
    }
    catch
    {
        throw error(strFmt("File: %1 cannot be opened", fileName));
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();

    row         = 2;
    type        = cells.item(row, 1).value().variantType();

    ttsBegin;
    while (type != COMVariantType::VT_EMPTY)
    {
        itemid      = cells.item(row, 1).value().bStr();
        unitFrom    = cells.item(row, 2).value().bStr();
        unitTo      = cells.item(row, 3).value().bStr();
        factor      = cells.item(row, 4).value().double();

        UnitOfMeasureConversion.clear();
        UnitOfMeasureConversion.Product             = EcoResProduct::findByProductNumber(itemid).RecId;
        UnitOfMeasureConversion.FromUnitOfMeasure   = UnitOfMeasure::findBySymbol(unitFrom).RecId;
        UnitOfMeasureConversion.ToUnitOfMeasure     = UnitOfMeasure::findBySymbol(unitTo).RecId;
        UnitOfMeasureConversion.Factor              = factor;
        UnitOfMeasureConversion.Numerator           = 1;
        UnitOfMeasureConversion.Denominator         = 1;

        UnitOfMeasureConversion.insert();
        row++;
        type    = cells.item(row, 1).value().variantType();
    }
    ttsCommit;
    application.quit();

    info(strFmt('Upload Success'));
}

Create Loyalty Point Customer via X++


static void TIDF_TestCreateLoyaltyPointCustomer(Args _args)
{
    //table
    RetailLoyaltyCustTable      retailLoyaltyCustTable;
    RetailLoyaltyMSRCardTable   retailLoyaltyMSRCardTable;
    RetailLoyaltyMSRCardTrans   retailLoyaltyMSRCardTrans;
    AxRetailLoyaltyMSRCardTable AxRetailLoyaltyMSRCardTable;
    RetailLoyaltySchemesTable   RetailLoyaltySchemesTable;
    
    //field
    RetailLoyaltyCustId     retailLoyaltyCustId;
    CustAccount             custAccount;
    RetailLoyaltySchemeId   retailLoyaltySchemeId;
    RetailLoyaltyCardId     retailLoyaltyCardId;
    RetailLoyaltyPoints     retailLoyaltyPoints;
    
    retailLoyaltyCustId = "XXTESTXX03";
    custAccount         = "010203";
    retailLoyaltySchemeId = "2";
    retailLoyaltyCardId = "XXTESTXX10";
    retailLoyaltyPoints = 50;

    //save header
    retailLoyaltyCustTable.clear();
    retailLoyaltyCustTable.initValue();
    retailLoyaltyCustTable.accountNum = custAccount;
    retailLoyaltyCustTable.initFromCustTable(CustTable::find(custAccount));
    retailLoyaltyCustTable.loyaltyCustId = retailLoyaltyCustId ;
    retailLoyaltyCustTable.insert();

    //save lines
    AxRetailLoyaltyMSRCardTable =  new AxRetailLoyaltyMSRCardTable();
    AxRetailLoyaltyMSRCardTable.parmLoyaltyCustId(retailLoyaltyCustTable.loyaltyCustId);
    AxRetailLoyaltyMSRCardTable.parmLinkId(retailLoyaltyCustTable.loyaltyCustId);

    RetailLoyaltySchemesTable = RetailLoyaltySchemesTable::find(retailLoyaltySchemeId);
    if(RetailLoyaltySchemesTable)
    {
        AxRetailLoyaltyMSRCardTable.parmLoyaltySchemeId(RetailLoyaltySchemesTable.loyaltySchemeId);
    }

    AxRetailLoyaltyMSRCardTable.parmLoyaltyTender(RetailLoyaltyTenderTypeBase::AsCardTender);
    AxRetailLoyaltyMSRCardTable.parmCardNumber(retailLoyaltyCardId);
    AxRetailLoyaltyMSRCardTable.save();
    
    //upload points.
    retailLoyaltyMSRCardTrans.clear();
    retailLoyaltyMSRCardTrans.loyaltySchemeId = RetailLoyaltySchemesTable.loyaltySchemeId;
    retailLoyaltyMSRCardTrans.loyaltyCustId   = retailLoyaltyCustId;
    retailLoyaltyMSRCardTrans.cardNumber      = AxRetailLoyaltyMSRCardTable.retailLoyaltyMSRCardTable().cardNumber;
    retailLoyaltyMSRCardTrans.entryType       = RetailLoyaltyEntryTypeBase::Sale;
    retailLoyaltyMSRCardTrans.expirationDate  = maxDate();
    retailLoyaltyMSRCardTrans.points          = retailLoyaltyPoints;
    //include store or staff or any
    //retailLoyaltyMSRCardTrans.storeId   
    
    retailLoyaltyMSRCardTrans.insert();


}

lookup using view example x++

 
Capture
 

public void lookup()
{

     QueryBuildDataSource queryBuildDataSource;
    //super();
    //coded by fanddy, 
     SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(TIDInventLookup), this);

     Query query = new Query();

     queryBuildDataSource = query.addDataSource(tableNum(TIDInventLookup));
     sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,NameAlias));
     sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,DisplayProductNumber));
     sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,Name));
     sysTableLookup.parmQuery(query);
     sysTableLookup.performFormLookup();

}

error : “”Object reference not set to an instance of an object” when open SSRS Report

try one or all of this solutions below :
1. Generate Incremental CIL
2. Compile on the customized report including report class (Contract, controller, DP). If you have modified TMP table , please do synchronize on that table.
3. Clean User Usage data on Tools-> Option -> Usages Data.
4. Restarting reporting services.
5. Refresh all cache on AOT, on AOT-> Tools -> Caches
5. Re-Deploy the SSRS report.
6. Restarting AOS. << sometimes it works like magic -_-“

Fixed Asset Projection Depreciation Expense example code

private void runWithAssetGroupId(NoYes parmendOfYearOnly,AssetGroupId assetGroupId)
{
    TmpDateSum      tmpDateSum;
    AssetTable      assetTable;
    AssetBook       assetBook;
    AssetBookTable  assetBookTable;
    //main account
    AssetLedgerAccounts  assetLedgerAccounts;
    MainAccountNum       mainAccountNum;


    while select assetTable
    join assetBook
    join assetBookTable
    where
        assetTable.AssetGroup                   == assetGroupId
    &&  assetTable.AssetId                      == assetBook.AssetId
    &&  assetBook.BookId                        == assetBookTable.BookId
    &&  assetBook.Depreciation                  == NoYes::Yes
    &&  assetBookTable.CurrentOperationsTax     == CurrentOperationsTax
    &&  assetBook.Status                        == AssetStatus::Open
    {

        lineTMP.clear();
        assetLedgerAccounts = AssetLedgerAccounts::find(assetBook.BookId,AssetParameters::find().PostingProfile,enum2int(AssetTransType::Depreciation),TableGroupAll::GroupId,assetTable.AssetGroup);
        mainAccountNum =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.OffsetLedgerDimension).MainAccount).MainAccountId;

        //Method calculation provided by AX
        tmpDateSum.setTmpData(assetBook.initDepreciationProfile());
        //set header line
        lineTMP.AssetGroupId = assetTable.AssetGroup;
        lineTMP.AssetId      = assetTable.AssetId;
        lineTMP.MainAccount  = mainAccountNum;
        lineTMP.rGrossFa     = assetBook.AcquisitionPrice;

        //cut off date
        while select tmpDateSum
        where
            tmpDateSum.TmpNumerals01 != 0
        &&  tmpDateSum.TransDate <= endYearDate
        {
            lineTMP.Period       = tmpDateSum.TransDate;
            lineTMP.rAccDepr     = tmpDateSum.TmpNumerals01;
            lineTMP.rDeprExp     = tmpDateSum.Total;
            lineTMP.rNetBookvalue = tmpDateSum.TmpNumerals02;
            lineTMP.insert();
        }

    }


}

Speeding up Sales Agreement Release Order process

When we create SO from sales agreement, we will face a problem that occur on standard version of AX 2012 if number of sales agreement record are thousands and we only want to choose let’s say 2 or 3 line from sales agreement line to Sales Line.
 
we can choose records from agreement line by fill the quantity, and the problem is standard AX still will loop all records no matter the quantity is fill or not. and all thosands will going through process that not needed.
 
Class : SalesAutoCreate\Create

void  create()
{
    #OCCRetryCount

    try
    {
        setprefix("@SYS55110");

        ttsbegin;

        while (this.recordExist())
        {

            this.setCust();

            setprefix(#PreFixField(CustTable,AccountNum));

            this.setSalesTable();

            this.setSalesLine();

            setprefix(#PreFixField(SalesLine,ItemId));

            this.nextRecord();
        }

        this.endUpdate();

        ttscommit;
    }

    catch (Exception::Deadlock)
    {
        retry;
    }

    catch (Exception::UpdateConflict)
    {
        if (appl.ttsLevel() == 0)
        {
            if (xSession::currentRetryCount() >= #RetryNum)
            {
                throw Exception::UpdateConflictNotRecovered;
            }
            else
            {
                retry;
            }
        }
        else
        {
            throw Exception::UpdateConflict;
        }
    }

}

i tried debug and i found the issue that made sales agreement release order process become so slow. and made a modification as below.
Class : SalesAutoCreate_ReleaseFromAgreement\New

protected void new(Common  _releaseOrderLine,
                   Object  _callBackClass     = null,
                   Common  _releaseOrderTable = null)
{
    //modify by fanddy
    //purpose : speeding up release agreement from BMP
    SalesCreateReleaseOrderLineTmp  TIDSalesCreateReleaseOrderLineTmp;

    releaseOrderLine     = _releaseOrderLine  as SalesCreateReleaseOrderLineTmp;
    delete_from releaseOrderLine where releaseOrderLine.SalesQty < 1;
    //end modify fanddy

    releaseOrderTable    = _releaseOrderTable as SalesCreateReleaseOrderTableTmp;
    this.firstRecord();

    createFromAgreementLine     = AgreementLineQuantityCommitment::find(releaseOrderLine.AgreementLineQuantityCommitment, false);
    createFromSalesAgreement    = SalesAgreementHeader::find(createFromAgreementLine.Agreement ? createFromAgreementLine.Agreement : releaseOrderTable.AgreementHeader);

    currentSalesId       = '';
    firstRecord          = true;

    super(_releaseOrderLine,_callBackClass);
    // <GEERU>
    countryRegion_RU = SysCountryRegionCode::isLegalEntityInCountryRegion([#isoRU]);
    // </GEERU>

 

Voila !! Now your when you create an SO with 2-3 SO lines originated from thousands line data of sales agreement, the process has been boosted !!

print report via x++ AX 2012

SrsReportRunController          controller = new SrsReportRunController();
SysUserLicenseCountRDPContract  rdpContract = new SysUserLicenseCountRDPContract();
SRSPrintDestinationSettings     settings;
 
// Define report and report design to use
controller.parmReportName(ssrsReportStr(SysUserLicenseCountReport, Report));
// Use execution mode appropriate to your situation
controller.parmExecutionMode(SysOperationExecutionMode::ScheduledBatch);
// Suppress report dialog
controller.parmShowDialog(false);
 
// Explicitly provide all required parameters
rdpContract.parmReportStateDate(systemDateGet());
controller.parmReportContract().parmRdpContract(rdpContract);
 
// Change print settings as needed
settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::File);
settings.fileFormat(SRSReportFileFormat::Excel);
settings.fileName(@'\\share\UserLicenseCount.xlsx');
 
// Execute the report
controller.startOperation();

Upload a LedgerJournalTrans Fixed Asset type without insert to LedgerJournalTrans_Asset table

an error will occured when you try validate ledgerJournalTrans.

how to fix this :

static void TIDF_fixBugsUploadFixedAssets(Args _args)
{
    ledgerjournaltrans ledgerJournalTrans;
    LedgerJournalTrans_Asset    cekExist;
    LedgerJournalTrans_Asset    LedgerJournalTrans_Asset;
    
    while select  ledgerJournalTrans 
    where 
        ledgerJournalTrans.JournalNum == 'GEN-000007'
    {
        select cekExist where cekExist.RefRecId == ledgerJournalTrans.RecId;
        
         if (ledgerJournalTrans.isFixedAssetsTransaction() && !cekExist)
            {
                ledgerJournalTrans_Asset.clear();
                ledgerJournalTrans_Asset.initValue();

                ledgerJournalTrans_Asset.RefRecId  = ledgerJournalTrans.RecId;
                ledgerJournalTrans_Asset.AssetId   = ledgerJournalTrans.getAssetId();
                ledgerJournalTrans_Asset.Company   = ledgerJournalTrans.getAssetCompany();
                ledgerJournalTrans_Asset.TransType = AssetTransTypeJournal::Acquisition;
                ledgerJournalTrans_Asset.BookId    = "ALL";

                if (!ledgerJournalTrans_Asset.validateWrite())
                    throw Exception::Error;

                ledgerJournalTrans_Asset.insert();
            }
    }


}

source : http://www.cnblogs.com/Fandyx/p/3343688.html

Modify Query Ranges on Report Controller Class

Create a enum and set it on menu item properties as example below.
 
Capture
 
override method preRunModifyContract on report controller class as example code below.

protected void preRunModifyContract()
{
    //modify the parameter value of the contract
   // if( element.args().parmEnumType() == EnumNum( NoYes ) )
    if(this.parmArgs().parmEnumType() == enumNum(TIDSalesDailyRecon))
    {
       switch(this.parmArgs().parmEnum())
       {
           case  TIDSalesDailyRecon::SalesInvoice :
               SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(5637149251));
               break;
           case  TIDSalesDailyRecon::SalesReturn :
               SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(this.parmArgs().record().RecId));
               break;
           case TIDSalesDailyRecon::SalesExIndent :
               break;
       }
    }
}

 
this.parmArgs().record().RecId value is come from :

 SrsReportRunController  controller;
 Args = new Args()

 controller = new SrsReportRunController();
 args.record(TABLES_record);
 controller.parmReportName(ssrsReportStr(REPORT_NAME, REPORT_DESIGN_NAME));

 controller.startOperation();

 
source :
1. http://www.dynamics101.com/2014/01/using-controller-class-developing-ssrs-reports-microsoft-dynamics-ax-2012/
2. http://dynamics.folio3.com/x-tips-and-tricks-passing-parameters-microsoft-dynamics-ax/

add mainAccount to LedgerJournalReport

add this code below to LedgerJournalDP\InsertLedgerJournalTmp

 if(_accountOffsetAccount ==  AccountOffsetaccount::Account)
    {
        switch(_ledgerJournalTrans.AccountType)
        {
            case LedgerJournalACType::Ledger :
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
                    break;
            case LedgerJournalACType::Bank :
                    select * from dimAttrValueCombo
                    join Name from bankAccountTable
                    where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
                    && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
                    break;
            case LedgerJournalACType::Vend :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    VendLedgerAccounts   = VendLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::Cust :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    CustLedgerAccounts = CustLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::FixedAssets :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
                    assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,_ledgerJournalTrans.PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
                    break;
        }
    }
    else
    {
        switch(_ledgerJournalTrans.AccountType)
        {
            case LedgerJournalACType::Ledger :
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
                    break;
            case LedgerJournalACType::Bank :
                    select * from dimAttrValueCombo
                    join Name from bankAccountTable
                    where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
                    && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
                    break;
            case LedgerJournalACType::Vend :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    VendLedgerAccounts   = VendLedgerAccounts::find(VendParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::Cust :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    CustLedgerAccounts = CustLedgerAccounts::find(CustParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::FixedAssets :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
                    assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,AssetParameters::find().PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
                    break;
        }
    }

Filter Query Form DataSource using DynaLink

example code on form VendPurchaseOrderJournal , datasource VendPurchOrderJournal method init()

public void init()
{
    QueryBuildDataSource  queryDataSourceLink;
    super();

    if (element.args().dataset() == tablenum(PurchReqLine))
    {
        this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks();

        queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour));
        queryDataSourceLink.relations(true);
        queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, PurchId),
                                        element.args().record(),
                                        fieldnum(PurchReqLine, PurchId));
    }

    //add by fanddy , for inqueries confirm PO
    if (element.args().dataset() == tablenum(TECPOGroupHeader))
    {
        this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks();

        queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour));
        queryDataSourceLink.relations(true);
        queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, TIDPOGroupId),
                                        element.args().record(),
                                        fieldnum(TECPOGroupHeader, POGroupId));
    }
    //END
}

Found a BudgetPlanningProcessTmp record instead of an expected BudgetPlanningProcess record.

Version : AX 2012 R2 CU7
&nbsp
Found this error this morning when select a record in budgetplanning lookup.
 
Error detail :
 
“Found a BudgetPlanningProcessTmp record instead of an expected BudgetPlanningProcess record. Check to see if an override of the resolveReference method is returning a record from the correct table.”
 
Capture
 
Solution :
 
Revert static method BudgetPlanGenerateBudgetTrnsForm::lookupBudgetPlanningProcess back to SYS layer using compare.

add filter to form datasource method:ExecuteQuery example code

public void executeQuery()
{
    ;
    TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).clearRanges();
    if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Draft))
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Draft));
    if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Posted))
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Posted));
    //add by fanddy
    if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Confirmed))
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Confirmed));
    //END add by fanddy
    if(CreatorFilter.value() == true)
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,CreatedBy)).value(sysquery::value(curUserId()));
    super();
}

get Dimension Value from DefaultDimension

public static str 255 getDimensionValueTID(RecId defaultDimension, Name dimName)
{
    DimensionAttributeValueSetStorage dimStorage;
    ;

    dimStorage = DimensionAttributeValueSetStorage::find(defaultDimension);

    return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(dimName).RecId);
}

 
how to use :
 
getDimensionValueTID(_ledgerJournalTrans.DefaultDimension, 'BusinessUnit')

Global Variable in X++ AX 2012

some circumtances i must use global variable.

read :
http://msdn.microsoft.com/en-us/library/aa891830.aspx

more example how to use :

SysGlobalCache globalCache;
globalCache = ClassFactory.globalCache();
globalCache.set(strFmt("%1%2",curUserId(),"WhatEver"), 0, "ANY VALUE");

on other class.

SysGlobalCache globalCache;
globalCache = ClassFactory.globalCache();
info(strFmt("%1",globalCache.get(strFmt("%1%2",curUserId(),"WhatEver"), 0)));

Auto Convert field from Excel to X++

public static 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 '';
}

how to use example :
YourClass::COMVariant2Str(cells.item(row,17).value());

Create Journal Upload with Batch Form

Capture

just duplicate class tutorial_runBaseBatch, then modify the following class :

on class declaration :

public class JournalUploadExcextends RunBaseBatch
{
    DialogField         dfFileName;
    FileName            fileName  ;

    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        fileName
    #endmacro
}

on dialog() :

public Object dialog()
{
    #File
    DialogRunbase       dialog = super();
    #resAppl
    ;
    dfFileName = dialog.addFieldValue(extendedTypeStr(FileNameOpen),fileName);
    dialog.filenameLookupFilter(["All files", #AllFiles]);

    return dialog;
}

on method getFromDialog() :

public boolean getFromDialog()
{
    //do what ever you want in this class. i call upload journal method with PathFile as parameter
    JournalUploadExc::JournalUpload(dfFileName.value());
    return super();
}

voila.. !! done.. !!

Job Upload General Journal from excel

public static boolean JournalUpload(str _filePath)
{
    container            accEntryPattern;
    container            offSetEntryPattern;

    LedgerJournalACType LedgerJournalACType;
    ledgerJournalName ledgerJournalName;
    journalID JournalID;
    axLedgerJournalTable axLedgerJournalTable;
    axledgerJournalTrans axLedgerJournalTrans;
    LedgerJournalType JournalType = ledgerJournalType::Daily;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;

    boolean         _return = true;
    ;


    startLengthyOperation();
    //filename = "C:\\GJCashBank30April2013.xlsx";
    filename = _filePath;

    try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();

            row = 5;

            ttsBegin;
            axLedgerJournalTable = new axLedgerJournalTable();
            axLedgerJournalTable.parmJournalName("GenJrn");
            axLedgerJournalTable.parmJournalType(JournalType);
            axLedgerJournalTable.parmName("General Journal");
            axLedgerJournalTable.save();
            JournalID = axLedgerJournalTable.parmJournalNum();

            while (cells.item(row,2).value().bStr() != "")
            {
                //Create Trans ...
                axLedgerJournalTrans = new axLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(JournalID);

                //start from A
                axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
                axLedgerJournalTrans.parmAccountType(  str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));

                if(cells.item(row,2).value().bStr() == "Ledger")
                {

                    accEntryPattern =
                    [   cells.item(row, 3).value().bStr() + "-" + "Disp",
                        cells.item(row, 3).value().bStr(),
                        4,
                        "BusinessUnit",
                        cells.item(row,17).value().bStr(),
                        "Department",
                        cells.item(row,18).value().bStr(),
                        "ProductCode",
                        cells.item(row,19).value().bStr(),
                        "ProjectCode",
                        cells.item(row,20).value().bStr()
                    ];
                    axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
                }

                if(cells.item(row,5).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
                }

                if(cells.item(row,6).value().double() != 0)
                {
                        axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
                }

                axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
                axLedgerJournalTrans.parmOffsetAccountType(  str2Enum(LedgerJournalACType,cells.item(row,6).value().bStr()));

                if(cells.item(row,8).value().bStr() == "Ledger")
                {
                    offSetEntryPattern =
                    [   cells.item(row, 9).value().bStr() + "-" + "Disp",
                        cells.item(row, 9).value().bStr(),
                        4,
                        "BusinessUnit",
                        cells.item(row,17).value().bStr(),
                        "Department",
                        cells.item(row,18).value().bStr(),
                        "ProductCode",
                        cells.item(row,19).value().bStr(),
                        "ProjectCode",
                        cells.item(row,20).value().bStr()
                    ];
                    axLedgerJournalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
                }
                //
                axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
                axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
                axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
                axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
                axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
                axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
                axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
                //

                axLedgerJournalTrans.save();
                row++;

                ttsCommit;
            }

            info('Imported  Items');
        }
    }
    catch(Exception::Error)
    {
        _return = false;
    }

    return _return;
}

File Path X++ AX 2012

static void  main(Args  _args)
{
    FileName            fileName  ;
    container           c;
    FileIOPermission    permission;
    TextBuffer          textBuffer;
    str                 readText;
    str                 readLine;
    int                 etgline;
    int                 i = 0;
    Dialog              dialog = new Dialog("Journal Upload Excel");
    DialogField         dfFileName;
    str 100            filename2;
    container       con;
    ;
    #File

    dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
    dialog.filenameLookupFilter(["All files", #AllFiles]);

    if (dialog.run())
    {
        fileName = dfFileName.value();
        info(filename);

   }
}

Create Purchase Order via X++

void clicked()
{
    PurchTable  purchTable;
    PurchLine   purchLine;
    InventDim   inventDim;
    NumberSeq   numberSeq;

    //add by fanddy
    AxPurchTable    axPurchTable;
    AxPurchLine     axPurchLine;
    PurchFormLetter purchformLetter;
    //END

   //create PO Header
   purchTable.clear();
   purchTable.initFromVendTable(VendTable::find(TECPOGroupHeader.VendAccount));
   purchTable.DefaultDimension = InventTable::find(POGroupLine.ItemId).DefaultDimension;

   axPurchTable = axPurchTable::newPurchTable(purchTable);
   axPurchTable.parmPurchId(NumberSeq::newGetNum(PurchParameters::numRefPurchId()).num());
   axPurchTable.parmPurchaseType(PurchaseType::Purch);
   axPurchTable.parmDocumentStatus(DocumentStatus::PurchaseOrder);
   axPurchTable.parmAccountingDate(systemDateGet());
   axPurchTable.parmDeliveryDate(systemDateGet());
   axPurchTable.parmPurchStatus(PurchStatus::Backorder);
   axPurchTable.save();

   info(strFmt('Create PO %1', Purchtable.PurchId));

   //create PO Line
   purchLine.clear();
   purchLine.initValue();
   purchLine.initFromPurchTable(purchTable);
   purchLine.initFromInventTable(InventTable::find(POGroupLine2.ItemId));
   axPurchLine = AxPurchLine::newPurchLine(purchLine);
   axpurchLine.parmItemId('YOUR ITEMID');
   axPurchLine.parmPurchQty(YOUR QTY);
   axPurchLine.parmPurchPrice(YOUR PRICE);
   axPurchLine.parmInventDimId('YOUR INVENTDIMID');
   axPurchLine.save();

}

Auto Allocate Charges before auto Confirm Purchase Order

static void AutoAllocateCharges_beforeConfirmPO(Args _args)
{
    PurchTable purchTable;
    PurchFormLetter purchFormLetter;
    Args args = new Args();
    MarkupAllocation markupAllocation;

    select firstOnly purchTable where purchTable.PurchId=='1016-PO-14-000191';
    args.record(purchTable);
    //auto allocate charges
    markupAllocation = MarkupAllocation::newMarkupAllocation(args.record());
    markupAllocation.run(args.parmObject());
    
    purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
    purchFormLetter.update(purchTable, purchTable.PurchId);
}

Modify Controller Class for Query Based Report SSRS

public void prePromptModifyContract()
{
    //add a range in the report query  
      SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(SSRSReportDemo),fieldNum(SSRSReportDemo, RecId),SysQuery::value(this.parmArgs().record().RecId));
}

source : https://community.dynamics.com/ax/b/dynamics101trainingcenterax/archive/2014/01/30/using-controller-class-in-developing-ssrs-reports-in-microsoft-dynamics-ax-2012.aspx