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/

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

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.");
        }
    }
}

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'));
}