Thursday, July 31, 2014

Loading NumberSequence for a module

static void loadNumSeq(Args _args)
{
    NumberSeqModuleHRM numhrm=new NumberSeqModuleHRM();
    ;
    numhrm.load();
    info("Done");
}

to generate number sequence

info(NumberSeq::newGetNum(Purchparameters::numRefPurchInvoiceId(),true).num());

Friday, July 18, 2014

Save File As CSV From Excel through X++

static void SaveAsCSVFileFromExcel(Args _args)
{
    #Excel
      SysExcelApplication excel;
      SysExcelWorkBooks books;
      Filename excelFileName;
      Filename csvFileName;
      ;
    /*
      csvFileName = 'C:\\LedgerData.csv';
      excelFileName = ' C:\\LedgerData.xls';
      excel = SysExcelApplication::construct( );
      excel.displayAlerts (false);
      books = excel.workbooks( );
      books.open(csvFileName,0,false,2,"","",false,#xlWindows,",",false,false,1,false,false,1,false);

      books.item(1).saveAs(excelFileName);
      excel.quit();*/

      csvFileName = 'D:\\LedgerData.csv';
      excelFileName = 'C:\\LedgerData.xlsx';
      excel = SysExcelApplication::construct();
      excel.displayAlerts (false);
      books = excel.workbooks( );
      books.open(excelFileName,0,false,2,"","",false,#xlWindows,",",false,false,1,false,false,1,false);

      books.item(1).saveAs(csvFileName);
      excel.quit();
}

Thursday, July 17, 2014

Cross company in X++

static void crossCompanyUse(Args _args)
{
    AddressCountryRegion addressCountryRegion,_addressCountryRegion;
    Dialog      dialog;
    DialogField dialogField;
    ;
    dialog = new Dialog();
    dialogField=dialog.addField(typeId(DataareaId));
    dialog.run();
    select * from _addressCountryRegion;

    If(!_addressCountryRegion)
    {
         while Select crossCompany CountryRegionId,Name,Type,AddrFormat,ISOcode
         from addressCountryRegion where addressCountryRegion.dataAreaId == dialogField.value()
         {
            _addressCountryRegion.clear();
            _addressCountryRegion.initValue();
            _addressCountryRegion.CountryRegionId = addressCountryRegion.CountryRegionId ;
            _addressCountryRegion.Name = addressCountryRegion.Name ;
            _addressCountryRegion.Type = addressCountryRegion.Type;
            _addressCountryRegion.AddrFormat = addressCountryRegion.AddrFormat;
            _addressCountryRegion.ISOcode = addressCountryRegion.ISOcode;
            _addressCountryRegion.insert();
         }
         info("Records Imported");
         // insert_recordset _addressCountryRegion(CountryRegionId) Select crossCompany  CountryRegionId  from addressCountryRegion where addressCountryRegion.dataAreaId == dialogField.value();
   }
   else
   {
        info("Records alredy exists");
   }

}


static void crossCompanyUseModify(Args _args)
{
    AddressCountryRegion addressCountryRegion,_addressCountryRegion;
    Dialog      dialog;
    DialogField dialogField;
    CompanyId   currentCompany,newCompany;
    ;
    dialog = new Dialog();
    dialogField=dialog.addField(typeId(DataareaId));

    if(dialog.closedOk())
    {
        dialog.run();
        currentCompany = curext();
        newCompany = dialogField.value();
        select * from _addressCountryRegion;
        If(!_addressCountryRegion)
        {
            select * from _addressCountryRegion;
             while Select crossCompany CountryRegionId,Name,Type,AddrFormat,ISOcode
             from addressCountryRegion where addressCountryRegion.dataAreaId == dialogField.value()
             {
                _addressCountryRegion.clear();
                _addressCountryRegion.initValue();
                _addressCountryRegion.CountryRegionId = addressCountryRegion.CountryRegionId ;
                _addressCountryRegion.Name = addressCountryRegion.Name ;
                _addressCountryRegion.Type = addressCountryRegion.Type;
                _addressCountryRegion.AddrFormat = addressCountryRegion.AddrFormat;
                _addressCountryRegion.ISOcode = addressCountryRegion.ISOcode;
                _addressCountryRegion.insert();
             }
             info("Records Imported");
             // insert_recordset _addressCountryRegion(CountryRegionId) Select crossCompany  CountryRegionId  from addressCountryRegion where addressCountryRegion.dataAreaId == dialogField.value();
       }
       else
       {
            info("Records alredy exists");
       }
    }
}

xml creation

// Changed on 09 Dec 2013 at 16:47:30 by malle
void AsnXmlCreation()
{
    XmlDocument xmlDoc;
    XmlElement xmlRoot;
    XmlElement xmlField;
    XmlElement xmlRecord,xmlRecord1,xmlRecord2;
    XMLWriter xmlWriter;
    CustPackingSlipTrans custPackingSlipTrans;
    SalesLine salesLine_1;
    CustPackingSlipJour custPackingSlipJour;
    DictTable dictTable_1 = new DictTable(tablenum(SalesLine));
    DictTable dictTable_2 = new DictTable(tablenum(CustPackingSlipJour));
    DictTable dictTable_3 = new DictTable(tablenum(CustPackingSlipTrans));
    DictField dField;
    int i, fieldId,fieldid_1;
    str value;
    FileIoPermission _perm;
    PackingSlipId _packingSlipId;
    ;

    xmlDoc = XmlDocument::newBlank();
    xmlRoot = xmlDoc.createElement("CustPackingSlip");
    _packingSlipId = "PS-101729";

    select custPackingSlipJour where  custPackingSlipJour.PackingSlipId == _packingSlipId;

    while select salesline_1 where salesLine_1.SalesId == custPackingSlipJour.SalesId
    {
        select CustPackingSlipTrans
            where CustPackingSlipTrans.PackingSlipId == custPackingSlipJour.PackingSlipId && CustPackingSlipTrans.SalesId == salesLine_1.SalesId && CustPackingSlipTrans.InventTransId == salesLine_1.InventTransId;

        xmlRecord1 = xmlDoc.createElement("SalesLine");
            for (i=1; i<=5; i++)
            {
                switch(i)
                {
                    case 1:
                        fieldId = fieldname2id(salesLine_1.TableId,"LineNum");
                        break;
                    case 2:
                        fieldId = fieldname2id(salesLine_1.TableId,"ItemId");
                        break;
                    case 3:
                        fieldId = fieldname2id(salesLine_1.TableId,"SalesQty");
                        break;
                    case 4:
                        fieldId = fieldname2id(salesLine_1.TableId,"SalesUnit");
                        break;
                    case 5:
                        fieldId = fieldname2id(salesLine_1.TableId,"RemainSalesPhysical");
                        break;

                }
                dField = dictTable_1.fieldObject(fieldId);
                xmlField = xmlDoc.createElement(dField.name());
                value = salesLine_1.(fieldId);
                xmlField.innerText(value);
                xmlRecord1.appendChild(xmlField);
            }
       xmlRecord2 = xmlDoc.createElement("CustPackingSlipJour");
       xmlRecord = xmlDoc.createElement("CustPackingSlipTrans");
            for (i=1; i<=3; i++)
            {
                switch(i)
                {
                    case 1:
                        fieldId = fieldname2id(CustPackingSlipTrans.TableId,"PackingSlipId");
                        break;
                    case 2:
                        fieldId = fieldname2id(CustPackingSlipTrans.TableId,"Remain");
                        break;
                    case 3:
                        fieldId = fieldname2id(CustPackingSlipTrans.TableId,"Qty");
                        break;
                }

            dField = dictTable_3.fieldObject(fieldId);
            xmlField = xmlDoc.createElement(dField.name());
            switch (dField.baseType())
            {
                case Types::Int64 :
                    value = int642str(CustPackingSlipTrans.(fieldId));
                    break;
                case Types::Integer :
                    value = int2str(CustPackingSlipTrans.(fieldId));
                    break;
                default :
                    value = CustPackingSlipTrans.(fieldId);
                break;
            }
            xmlField.innerText(value);
            xmlRecord.appendChild(xmlField);

            if(value != "" && fieldId == fieldname2id(CustPackingSlipTrans.TableId,"PackingSlipId"))
            {
                fieldId_1 = fieldname2id(custPackingSlipJour.TableId,"Qty");
                dField = dictTable_2.fieldObject(fieldId_1);
                xmlField = xmlDoc.createElement(dField.name());
                value = custPackingSlipJour.(fieldId_1);
                xmlField.innerText(value);
                xmlRecord2.appendChild(xmlField);

            }
            else if((value == "" && fieldId == fieldname2id(CustPackingSlipTrans.TableId,"PackingSlipId")))
            {
                fieldId_1 = fieldname2id(custPackingSlipJour.TableId,"Qty");
                dField = dictTable_2.fieldObject(fieldId_1);
                xmlField = xmlDoc.createElement(dField.name());
                value = custPackingSlipJour.(fieldId_1);
                xmlRecord2.appendChild(xmlField);

            }
            }
        xmlrecord2.appendChild(xmlRecord);
        xmlrecord1.appendChild(xmlRecord2);
        xmlRoot.appendChild(xmlRecord1);
    }
    xmlDoc.appendChild(xmlRoot);
    info(xmlDoc.toString());
   // _perm = new FileIoPermission(@"D:\"+custPackingSlipJour.PackingSlipId".xml",'W');
    xmlWriter = XMLWriter::newFile(@"D:\Malleswaran_Data\"+custPackingSlipJour.PackingSlipId+".xml");
    xmlDoc.writeTo(xmlWriter);
   // _perm.assert();
}

AIF Document services operations

static void AifSample_CustomerService(Args _args)
{

    /*
        All code used below is meant for illustration purposes only and not intended for use in production. The following disclaimer applied to all code used in this blog:

        Copyright (c) Microsoft Corporation. All rights reserved.  THIS CODE IS MADE AVAILABLE AS IS, WITHOUT WARRANTY OF ANY KIND. THE ENTIRE RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE USER.
        USE AND REDISTRIBUTION OF THIS CODE, WITH OR WITHOUT MODIFICATION, IS HEREBY PERMITTED.
    */


    /*
        This job demonstrates how to use AIF data objects to call an AIF service from X++.
        The CustCustomerService service class is used to call the CRUD + find service operations on customer data.
        The new service class delegates to the underlying Axd<Document> class for all operations.
    */

    // TODO: Before creating a customer, make sure that the Number sequence for the reference Directory ID is setup at the following location:
    //       Basic => Setup => Directory => Parameters => Number Sequences tab => Directory Id

    // TODO: The delete code has been commented out so that the created customer can actually be viewed from the CustTable form.  Uncomment this code to test the delete functionality.

    CustCustomerService     customerService;            // Customer Service class
    CustCustomer            customer;                   // Customer Document object
    CustCustomer            customerRead;               // Customer Document object
    CustCustomer_CustTable  custTable;                  // CustTable data object
    CustCustomer_CustTable  custTableRead;              // CustTable data object
    AifEntityKeyList        entityKeyList;              // Entity key list
    AifEntityKeyList        entityKeyListFind;          // Entity key list
    AifQueryCriteria        queryCriteria;
    AifCriteriaElement      criteriaElement;
    AccountNum              accountNum;
    Name                    name;
    ;

    // Create the service instance
    customerService =  CustCustomerService::construct();

    // Create the Customer document object
    customer = new CustCustomer();
    customer.createCustTable();                            // Create the CustTable list
    custTable = customer.parmCustTable().addNew();         // Add CustTable instance to CustTable list

    // Initialize the CustTable instance
   // custTable.parmName("Cust01");
    custTable.parmAccountNum("22220002");
    custTable.parmCustGroup("10");
    custTable.parmCurrency("USD");
    custTable.parmPartyType(DirPartyType::Organization);

    // Create Customer
    entityKeyList = customerService.create(customer);
    accountNum = entityKeyList.getEntityKey(1).parmKeyDataMap().lookup(fieldnum(CustTable, accountnum));
    info(strfmt("Created customer:  Account Number: %1.", accountNum));

    //Creating another Customer
    entityKeyList = customerService.create(customer);
    accountNum = entityKeyList.getEntityKey(2).parmKeyDataMap().lookup(FieldNum(Custtable,accountNum));
    info(strfmt("Created another Customer :  Account Number : %1. ",accountNum));


    // Read Customer using returned entity key
    customerRead = customerService.read(entityKeyList);
    custTableRead = customerRead.parmCustTable().get_Item(0);
    info(strfmt("Read customer: Account Number: %1, Name: %2.", custTableRead.parmAccountNum(), custTableRead.parmName()));

    // Update Customer
    custTableRead.parmName(custTableRead.parmName() + ": Updated Name");
    customerService.update(entityKeyList, customerRead);
    info (strfmt("Updated Customer: Account Number: %1.", custTableRead.parmAccountNum()));

    // Call Read to check update
    customer = customerService.read(entityKeyList);
    custTable = customerRead.parmCustTable().get_Item(0);
    info(strfmt("Read updated customer: Account Number: %1, Name: %2.", custTable.parmAccountNum(), custTable.parmName()));

    // Call FindKeys to find entity keys of matching customer entity keys
    queryCriteria = new AifQueryCriteria();
    criteriaElement = AifCriteriaElement::newCriteriaElement('CustTable', 'CustGroup', AifCriteriaOperator::Equal, '10');
    queryCriteria.addCriteriaElement(criteriaElement);
    entityKeyListFind = customerService.findKeys(queryCriteria);
    info(strfmt("Find customer keys: Result count: %1", entityKeyListFind.getEntityKeyCount()));

    // Call Find to find matching customers
    queryCriteria = new AifQueryCriteria();
    criteriaElement = AifCriteriaElement::newCriteriaElement('CustTable', 'CustGroup', AifCriteriaOperator::Equal, '10');
    queryCriteria.addCriteriaElement(criteriaElement);
    customerRead = customerService.find(queryCriteria);
    info(strfmt("Find customer: Result count: %1", customerRead.existsCustTable()?customerRead.parmCustTable().get_Count():0));

    info("TO DO: To test delete, uncomment delete code in the job.");
    // TODO: UNCOMMENT TO DELETE CUSTOMER
    /*
    // calling deleting customer
    customerService.delete(entityKeyList);
    info(strfmt("Deleted customer:  Account Number: %1.", accountNum));
    */
    pause;
}

Adding OR condition to queryrange

static void AddORRangeToQuery(Args _args)
{
    Query q = new Query();  // Create a new query.
    QueryRun qr;
    CustTable ct;
    QueryBuildDataSource qbr1;
    str strTemp;
    ;

    // Add a single datasource.
    qbr1 = q.addDataSource(tablenum(CustTable));
    // Name the datasource 'Customer'.
    qbr1.name("Customer");

    // Create a range value that designates an "OR" query like:
    // customer.AccountNum == "4000" || Customer.CreditMax > 2500.

    // Add the range to the query data source.
    qbr1.addRange(fieldNum(CustTable, AccountNum)).value(
    strFmt('((%1.%2 == "4000") || (%1.%3 > 2500))',
        qbr1.name(),
        fieldStr(CustTable, AccountNum),
        fieldStr(CustTable, CreditMax)));

    // Print the data source.
    print qbr1.toString();
    info(qbr1.toString());

    // Run the query and print the results.
    qr = new QueryRun(q);

    while (qr.next())
    {
        if (qr.changedNo(1))
        {
            ct = qr.getNo(1);
            strTemp = strFmt("%1 , %2", ct.AccountNum, ct.CreditMax);
            print strTemp;
            info(strTemp);
        }
    }
    pause;
}

Enable Paging in Ax 2009

static void ABTest(Args _args)
{
 Query query = new Query(Querystr('ABTest'));
 salestable salestable;

//query query = new Query();
QueryBuildDataSource qbds;
QueryRun qr;
tableid tableid;



qbds = query.dataSourceTable(tablenum(salestable));
//Order by item id
qbds.addOrderByField(fieldnum(salestable,salesid));
//Instantiate a Queryrun class
qr = new QueryRun(Query);
//Enable Ordering
qr.literals(true);
// Enable position paging for the queryrun object
qr.enablePositionPaging(true);
//Add a range by providing the parameters as starting record number and number of records
qr.addPageRange(10,20);
while(qr.next())
{
salestable = qr.get(tablenum(salestable));
info(salestable.SalesId);
}

}

Monday, July 14, 2014

Document handling Edit Notes

public void docHandlingEditNotes(RefRecId _recId, Notes _notes,Description _strDescription)
{
DocuRef docuref;
;
ttsbegin;
select forupdate firstonly docuref where docuref.RecId == _recId;
docuref.Notes = _notes;
docuref.Name = _strDescription;
docuref.update();
ttscommit;
}

Generic job for testing AIF Read operation

static void GenericReadServiceCall(Args _args)
{
    DictClass dictClass;
    anytype objEntityValue;
    anytype objValue;
    Object object;
    ExecutePermission permission;
    str strClassName="KT_ActivitiesViewService";
    str strTableName="smmActivities";
    str strRecordValue="S450247";

    str strTableMethodName="find";
    str strServiceMethodName="read";
    aifcriteriaElement criteriaElements;
    aifQueryCriteria objqueryCriteria;
    container objContainer;
    SysDictTable objSysDictTable;

    AifEntityKey           entityKey     = new AifEntityKey();
    AifEntityKeyList      entityKeyList = new AifEntityKeyList();



    objSysDictTable=new SysDictTable(tableName2Id(strTableName));
    objEntityValue=objSysDictTable.callStatic(strTableMethodName,strRecordValue); //,"CANONIMAGERUNNER");

     entityKey.parmKeyDataMap(SysDictTable::getKeyData(objEntityValue));
        entityKeyList.addEntityKey(entityKey);
    // Grants permission to execute the DictClass.callObject method.
    // DictClass.callObject runs under code access security.
    permission = new ExecutePermission();
    permission.assert();

    dictClass = new DictClass(className2Id(strClassName));
    object = dictClass.makeObject();

    if (dictClass != null)
    {
    //If it is static method
    //dictClass.callStatic(methodName);
     objValue = dictClass.callObject(strServiceMethodName, object,entityKeyList);
    }

    // Closes the code access permission scope.
    CodeAccessPermission::revertAssert();
}

Generic Job for testing AIF Find operation with criteria

static void GenericFindServiceCall(Args _args)
{
    DictClass dictClass;
    anytype objValue;
    Object object;
    ExecutePermission permission;
    str strClassName="KT_ActivitiesListService";
    str strTableName="smmActivities";
    str strMethodName="find";
    aifcriteriaElement criteriaElements,criteriaElement1,criteriaElement2;
    aifQueryCriteria objqueryCriteria;
    container objContainer;
    aifQueryCriteria queryCriteria;

    objContainer=[1,strTableName,"KTPAGING",1,1,10];

    /* = conins(c,1,"SalesTable");
    c = conins(c,2,"SalesId");
    c = conins(c,3,salesIdValue);*/
    //info(tableid2name(2303));
    //info(tableid2name(8152));
    //pause;

//    criteriaElements = aifCriteriaElement::create(objContainer)


criteriaElements = AifCriteriaElement::newCriteriaElement(tablestr(smmActivities),
                                                                                          "KTPAGING",
                                                                                          AifCriteriaOperator::Range,
                                                                                          "1","10");

    /*criteriaElements[0] = new CriteriaElement();
    criteriaElements[0].DataSourceName = "SalesTable";
    criteriaElements[0].FieldName = "SalesId";
    criteriaElements[0].Value1 = salesIdValue;*/

    objqueryCriteria = new aifQueryCriteria();


    criteriaElement1 = AifCriteriaElement::newCriteriaElement(tablestr(smmActivities),
                                                                                          fieldstr(smmActivities, Closed),
                                                                                         AifCriteriaOperator::Equal,
                                                                                          enum2str(noyes::No));
/*    criteriaElement2 = AifCriteriaElement::newCriteriaElement(tablestr(smmActivities),
    fieldstr(docuref, RefRecId),
    AifCriteriaOperator::Equal,
    "5637666290");
*/
    objqueryCriteria.addCriteriaElement(criteriaElements);
       //objqueryCriteria.addCriteriaElement(criteriaElement1);
             //   objqueryCriteria.addCriteriaElement(criteriaElement2);
    // Grants permission to execute the DictClass.callObject method.
    // DictClass.callObject runs under code access security.
    permission = new ExecutePermission();
    permission.assert();

    dictClass = new DictClass(className2Id(strClassName));
    object = dictClass.makeObject();


    if (dictClass != null)
    {
    //If it is static method
    //dictClass.callStatic(methodName);
     objValue = dictClass.callObject(strMethodName, object, objqueryCriteria);
    }

    // Closes the code access permission scope.
    CodeAccessPermission::revertAssert();

}

Generic job for testing AIF Find operation

static void GenericFindServiceCal(Args _args)
{
    DictClass dictClass;
    anytype objValue;
    Object object;
    ExecutePermission permission;
  //  str strTableName;
    str strClassName="KT_ActivitiesListService";
   // str strClassName="SalesViewQService";
    str strTableName="smmActivities";
//str strTableName="SalesLine";
    str strMethodName="find";
    aifcriteriaElement criteriaElements;
    aifQueryCriteria objqueryCriteria;
    container objContainer;
    aifQueryCriteria queryCriteria;

    objContainer=[1,strTableName,"KTPAGING",1,1,10];
   // objContainer=[1,strTableName,"Salesid",1,1,10];

    /* = conins(c,1,"SalesTable");
    c = conins(c,2,"SalesId");
    c = conins(c,3,salesIdValue);*/
    //info(tableid2name(2303));
    //info(tableid2name(8152));
    //pause;

    criteriaElements = aifCriteriaElement::create(objContainer);

    /*criteriaElements[0] = new CriteriaElement();
    criteriaElements[0].DataSourceName = "SalesTable";
    criteriaElements[0].FieldName = "SalesId";
    criteriaElements[0].Value1 = salesIdValue;*/

    objqueryCriteria = new aifQueryCriteria();
///
    objqueryCriteria.addCriteriaElement(criteriaElements);

    // Grants permission to execute the DictClass.callObject method.
    // DictClass.callObject runs under code access security.
    permission = new ExecutePermission();
    permission.assert();

    dictClass = new DictClass(className2Id(strClassName));
    object = dictClass.makeObject();


    if (dictClass != null)
    {
    //If it is static method
    //dictClass.callStatic(methodName);
     objValue = dictClass.callObject(strMethodName, object, objqueryCriteria);
    }

    // Closes the code access permission scope.
    CodeAccessPermission::revertAssert();

}

Getting document path in document handling

public str getDocPath(str type)
{
    docutype doc= docutype::find(type);
    return doc.ArchivePath;
}

Adding notes to record in document handling

public void docHandlingAddNotes(notes _notes='testNotes',RefTableId objRefTableId,RefRecId objRefRecId,Description strDescription='',str strDateAreId)
{

    docuref                         docuref;
    form                            form2;

    filename                        file, docuFilename;
    recid                           docuvaluerecid;
    docuvalue                       docuvalue;
    int                             hwnd;//wfs ctodd 6/21/2012

    docutype                        docutype;
    DocuActionArchive archive;
    System.IO.FileInfo fileInfo;
    int size;

    Filename    onlyFilename;
    Filename    curFileType;
    ;


       if(_notes)
        {
            ttsbegin;
            docuref.TypeId = "Note";
            docuref.Name   = strDescription;
            docuref.Notes  = _notes;
            docuRef.Restriction  = DocuRestriction::External;
            docuref.RefCompanyId = strDateAreId;

            docuref.RefTableId   = objRefTableId;
            docuref.RefRecId     = objRefRecId;
            docuref.insert();

/*           docuvalue.initValue();
           docuvalue.insert();
           docuref.ValueRecId = docuvalue.RecId;
           docuref.update();
           docuvaluerecid = docuvalue.RecId;
  */
           //select forupdate docuvalue where docuvalue.recid == docuvaluerecid;
           //docuvalue =  docuvalue::writeDocuValue(docuref,file);

            /*fileInfo = new System.IO.FileInfo(file);
            size = fileInfo.get_Length();
            info(strfmt("%1",size));*/

           //archive = new DocuActionArchive();
           //archive.add(docuRef, file);
           /*
           docutype = docutype::find(docuref.TypeId);
           numSeq = NumberSeq::newGetNum(DocuParameters::numRefDocuNumber(),true);
           [onlyFilename,curFileType]  = Docu::splitFilename(file);
            if(docuType.filePlace == DocuFilePlace::Archive)
            {
                docuFilename = docuRef.path()
                                + smmDocuments::getNonExistingFileName(numSeq,docuRef.path(),curFileType)
                                + '.'
                                + curFileType; //new Filename
                                //uses curFileType
                WinAPI::copyFile(filename, docuFilename);
            }
            else
            {
                docuFilename = filename;
            }

            this.insertDocuValue(docuRef,docuFilename);*/

           ttscommit;

      }
}

Using the above method
------------------------
//adding notes
    AIF_DOCHandCustomService_add.docHandlingAddNotes("Testing data......",8152,5637144709,"Testing data","ceu");

Getting file content of a record from document handling and return to device / base64 code

public notes ArchiveBlob(str strFilePath="C:\\Users\\hemamalinib\\Downloads\\T&L-TDD-V1.2.docx")
{
    Set permissionSet;
    //Hold the instance of table....
//    docuValue objdocuRef;
    //This class is used to process the blob data....
    BinData bin = new BinData();

    //Hold the instance of the encoded string format...

   System.IO.FileStream outFile;
   System.Byte[] arrByte;
   str content;
   System.Int32 j;
   container temp;
    ;
    // Assert permission.
    permissionSet =  new Set(Types::Class);
    permissionSet.add(new FileIOPermission(strFilePath ,"rw"));
    permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
   //FileIOPermission fioPermission;
   //InteropPermission permission = new InteropPermission(InteropKind::ClrInterop);
   //permission.assert();
    //getting file
   //objdocuRef= docuValue::find(_valueRecId); //5637145107);

// Assert permission.
    //fioPermission = new FileIOPermission(strFilePath ,"RW");
    //fioPermission.assert();

    //arrByte= System.IO.File::ReadAllBytes(strFilePath);

    //content =System.Convert::ToBase64String(arrByte);
     //  j =  outFile.Read(arrByte, 0,outFile.
    //CodeAccessPermission::revertAssert();


    //return content;
    // outFile.Close();
  //creating BinData object from Container in the CompanyImage object
    //bin.setData(objdocuRef.File);

    CodeAccessPermission::assertMultiple(permissionSet);
    //temp = this.Job_File_IO_TextIo_Write_Read(strFilePath);
    content = this.Job_File_IO_TextIo_Write_Read(strFilePath);//con2str(temp);
    //temp = WINAPI::findFirstFile(strFilePath);
    //bin.setData(temp);
    //bin.setData(tobjdocuRef.File);
    //encoding the image to base64
    //content = bin.base64Encode();
    // Create the base64 encoded string

    //This is for testing purpose
    //arrByte =System.Convert::FromBase64String(content);
    //outFile = new System.IO.FileStream(strFilePath,System.IO.FileMode::Create,
                             //System.IO.FileAccess::Read);
    //outFile.Read(arrByte, 0, arrByte.get_Length());
    //outFile.Close();
    return content;
}

using the above method
------------------------
//getting file content and return to device
notes filedet;
   filedet = AIF_DOCHandCustomService_add.ArchiveBlob(file);


Another Way
-------------
public notes Blob(RefRecId _valueRecId)
{
    //Hold the instance of table....
    docuValue objdocuRef;
    //This class is used to process the blob data....
    BinData bin = new BinData();
    //Hold the instance of the encoded string format...
    str content;
   System.IO.FileStream outFile;
   System.Byte[] arrByte;
    //getting file
   objdocuRef= docuValue::find(_valueRecId); //5637145107);

  //creating BinData object from Container in the CompanyImage object
    bin.setData(objdocuRef.File);
    //encoding the image to base64
    content = bin.base64Encode();
    // Create the base64 encoded string
    /*
    //This is for testing purpose
    arrByte =System.Convert::FromBase64String(content);
      outFile = new System.IO.FileStream(@"D:\DocHandling\test1.docx",System.IO.FileMode::Create,
                                 System.IO.FileAccess::Write);
      outFile.Write(arrByte, 0, arrByte.get_Length());
      outFile.Close();
    */
    return content;
}

Adding file/document to record

public void docHandlingAdd(filename filename)
{
    Args                            args = new args();
    formrun                         FormRun;
    //filenameSave                    filename;
    Args                            args1 = new args();
    //wfsEMSalesOrderDocumentation    wfsEMSalesOrderDocumentation;
    docuref                         docuref;
    form                            form2;
    FilenameFilter                  _conFilter;
    filename                        file, docuFilename;
    recid                           docuvaluerecid;
    docuvalue                       docuvalue;
    int                             hwnd;//wfs ctodd 6/21/2012
    salestable                      salestable = salestable::find('SO-101342');
    docutype                        docutype;
    DocuActionArchive archive;
    System.IO.FileInfo fileInfo;
    int size;

    Filename    onlyFilename;
    Filename    curFileType;
    ;

    if(SalesTable)
    {
        _conFilter = ['All FIles','*.*'];
         //hWnd = this.hWnd();
        //file = WinAPI::getOpenFileName(infoLog.hWnd(),_conFilter,'','Select Document Path');
        file = filename;

        if(file)
        {
            ttsbegin;
            docuref.TypeId = "Test";
            docuref.Name   = "T&L-TDD-V1.2";
            docuref.Notes  = docuRef.Notes;
            docuRef.Restriction  = DocuRestriction::External;
            docuref.RefCompanyId = SalesTable.dataAreaId;
            //docuref.WfsExportType = wfsexporttype::CommercialInv;
            docuref.RefTableId   = tablenum(SalesTable);
            docuref.RefRecId     = SalesTable.RecId;
            docuref.insert();

           docuvalue.initValue();
           docuvalue.insert();
           docuref.ValueRecId = docuvalue.RecId;
           docuref.update();
           docuvaluerecid = docuvalue.RecId;

           //select forupdate docuvalue where docuvalue.recid == docuvaluerecid;
           //docuvalue =  docuvalue::writeDocuValue(docuref,file);

            /*fileInfo = new System.IO.FileInfo(file);
            size = fileInfo.get_Length();
            info(strfmt("%1",size));*/

           archive = new DocuActionArchive();
           archive.add(docuRef, file);
           /*
           docutype = docutype::find(docuref.TypeId);
           numSeq = NumberSeq::newGetNum(DocuParameters::numRefDocuNumber(),true);
           [onlyFilename,curFileType]  = Docu::splitFilename(file);
            if(docuType.filePlace == DocuFilePlace::Archive)
            {
                docuFilename = docuRef.path()
                                + smmDocuments::getNonExistingFileName(numSeq,docuRef.path(),curFileType)
                                + '.'
                                + curFileType; //new Filename
                                //uses curFileType
                WinAPI::copyFile(filename, docuFilename);
            }
            else
            {
                docuFilename = filename;
            }

            this.insertDocuValue(docuRef,docuFilename);*/

           ttscommit;

           //docuFilename = docutype.ArchivePath + "\\";
           //WinAPI::copyFile(file, docuFilename);
      }
      }
}

Using the above method
--------------------------
filename file = "C:\\Users\\hemamalinib\\Downloads\\Tips_for_Creating_Services_in_Microsoft_Dynamics_AX_2009.pdf";
    KT_DocuHandlingService AIF_DOCHandCustomService_add = new KT_DocuHandlingService();
    //adding new file/document to record
    AIF_DOCHandCustomService_add.docHandlingAdd(file);

Sunday, May 4, 2014

Convert String to Barcode

To convert the sting to barcode, you just pass the string type and get the barcodestr 128 

Convert2Code128(str _str)
{
        BarCode barCode1 = BarCode::construct(barCodeType::Code128); 
        ; 
        barCode1.string(true, strUpr(_str)); 
        barCode1.encode(); 
        return barCode1.barcodeStr();
}

then setup the properties as below:

Friday, April 25, 2014

Getting financial dimension values from DefaultDimension RecId

static void DEV_Dimension(Args _args)
{
     SalesTable salesTable;
     DimensionAttributeValueSetStorage dimStorage;
     Counter i;
     salesTable = salesTable::find("SO-101282");
     dimStorage = DimensionAttributeValueSetStorage::find(salesTable.DefaultDimension); //recid of 1101
     for (i=1 ; i<= dimStorage.elements() ; i++)
    {
         info(strFmt("%1 = %2", DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name,
         dimStorage.getDisplayValueByIndex(i)));
     }
}

To get description of dimension value

public Description getDimensionAttributeDesc(DimensionDefault _defaultDimension)
{
    Description dimValue='';
    DEFAULTDIMENSIONVIEW dimensionView;
    DimensionAttribute dimensionAttribute;
   
    select firstOnly dimensionView
    where dimensionView.DefaultDimension == _defaultDimension            
    join dimensionAttribute
    where dimensionView.Name == dimensionAttribute.Name
    && dimensionView.Name == "GSECProject";
   
    dimvalue = DimensionFinancialTag::findByFinancialTagCategoryAndValue(dimensionAttribute.financialTagCategory(), dimensionView.DisplayValue).Description;
   
    return dimValue;
}

Wednesday, April 23, 2014

Finding current, previous and next week period through X++

static void Example_date(Args _args)
{
    TransDate   cwkstartDate,cwkEndDate;
    TransDate   lwkstartDate,lwkEndDate;
    TransDate   nwkstartDate,nwkEndDate;
    TransDate   d;
    ;
    d = systemDateget();
    cwkstartDate = Global::dateStartWk(d);
    cwkEndDate   = Global::dateEndWk(d) - 1;
    info(strfmt("Current StartDate: %1 and EndDate: %2",cwkstartDate,cwkEndDate));
    lwkstartDate = (Global::dateStartWk(d) - 7);
    lwkEndDate   = (Global::dateEndWk(lwkstartDate)) - 1;
    info(strfmt("Lastweek StartDate: %1 and EndDate: %2",lwkstartDate,lwkEndDate));
    nwkstartDate = (Global::dateStartWk(d) + 7);
    nwkEndDate   = (Global::dateEndWk(nwkstartDate)) - 1;
    info(strfmt("Nextweek StartDate: %1 and EndDate: %2",nwkstartDate,nwkEndDate));
}

Copy the existing route details using class through coding X++ in Ax 2012


static void BOMRouteCopyJob(Args _args)
{
    BOMRouteCopyJob      bOMRouteCopyJob;
    RouteVersion         routeVersion,newRouteVersion;
    RouteId              fromId,newId;
    InventSiteId         fromSiteId;
    EcoResItemConfigurationName             configId;
    RouteTable           routeTable;
    #define.item("1101")
    InventTable         inventTable= InventTable::find(#item);
    InventDim           inventDimVersion;
    ;

    Select routeversion where
        routeversion.ItemId == #item &&
        (routeversion.Approved == Noyes::Yes &&
        routeversion.Active == Noyes::Yes);

        fromId = routeversion.RouteId;
        configId = routeVersion.configId();
        fromSiteId = routeversion.inventSiteId();
       

    if (routeversion)
    {
        //Create a new route
        newId =  fromId + "/333" ;
        routeTable.RouteId = newId;
        routeTable.ItemGroupId = inventTable.itemGroupId();
        routeTable.insert();

       //Create a route version
        newRouteVersion.initFromRouteTable(routeTable);
        newrouteversion.initFromInventTable(inventTable);
        inventDimVersion.InventSiteId = fromSiteId;
        newRouteVersion.InventDimId = InventDim::findOrCreate(inventDimVersion).inventDimId;
        newrouteversion.insert();
    }

    //Copy the route details from another route  
    bOMRouteCopyJob = BOMRouteCopyJob::construct();
    bOMRouteCopyJob.parmFromRouteId(fromId);
    bOMRouteCopyJob.parmToRouteId(newId);
    bOMRouteCopyJob.checkBaseData();
    bOMRouteCopyJob.parmFromItemId(#item);
    bOMRouteCopyJob.parmToItemId(#item);
    bOMRouteCopyJob.parmToSiteId(fromSiteId);
    bOMRouteCopyJob.parmFromSiteId(fromSiteId);
    bOMRouteCopyJob.parmCopyRoute(true);
    bOMRouteCopyJob.run();
}

Tuesday, April 15, 2014

Using Currency Exchange Helper class

static void SR_CEH_Example1(Args _args)
{
    CurrencyExchangeHelper currencyExchangeHelper;
    CurrencyCode transCurrency = 'EUR';
    AmountCur amountCur = 500.00;
    AmountMst amountMST;

    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());
    amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, amountCur ,true);
    info(strFmt('%1',amountMST));
}

Friday, April 4, 2014

Using Temp Table

1. Create temp table with property In Memory or TempDB

2. public void init()
{
    HRMAbsenceTable  hrmAbsenceTable;
    HRMAbsenceTrans hrmAbsenceTrans;
    A_AbsenceBalances _a_AbsenceBalances; //temp table (In Memory)

     super();

    while select sum(Hours),PostedDate,TransDate,
                    HRMAbsenceCodeId,                  
                    hrmAbsenceCodeGroupId
        from hrmAbsenceTrans
        group by PostedDate,hrmAbsenceTrans.TransDate,
                    HRMAbsenceCodeId,                  
                    hrmAbsenceCodeGroupId
        join hrmAbsenceTable      
        where hrmAbsenceTable.hrmAbsenceTableId==hrmAbsenceTrans.hrmAbsenceTableId
    {
        _a_AbsenceBalances.Approve=HcmWorker::find(HRMAbsenceTable::find(hrmAbsenceTrans.hrmAbsenceTableId).Worker_ApprovedBy).name();
        _a_AbsenceBalances.status=hrmAbsenceTable.status;
        _a_AbsenceBalances.PostedDate=hrmAbsenceTrans.PostedDate;
        _a_AbsenceBalances.transDate=hrmAbsenceTrans.transDate;
        _a_AbsenceBalances.TransferredDate=hrmAbsenceTrans.TransferredDate;
        _a_AbsenceBalances.TransferToPay=hrmAbsenceTrans.TransferToPay;
        _a_AbsenceBalances.transText=hrmAbsenceTrans.transText;
        _a_AbsenceBalances.hrmAbsenceCodeGroupId=hrmAbsenceTrans.hrmAbsenceCodeGroupId;
        _a_AbsenceBalances.hrmAbsenceCodeId=hrmAbsenceTrans.hrmAbsenceCodeId;
        _a_AbsenceBalances.hrmAbsenceTableId=hrmAbsenceTrans.hrmAbsenceTableId;
        _a_AbsenceBalances.IsTransToPay=hrmAbsenceTrans.IsTransToPay;
        _a_AbsenceBalances.Name=hrmAbsenceTrans.Name;
        _a_AbsenceBalances.hours=hrmAbsenceTrans.hours;
        _a_AbsenceBalances.PayType=hrmAbsenceTrans.PayType;
        _a_AbsenceBalances.Worker=hrmAbsenceTable.worker;
        _a_AbsenceBalances.doInsert();
    }
    A_AbsenceBalances.setTmp();
    A_AbsenceBalances.setTmpData(_a_AbsenceBalances);
    A_AbsenceBalances_ds.executeQuery();
}


Thursday, April 3, 2014

OR condition in X++ Query Range

Adding OR condition in X++ query range

public void executeQuery()
{
    list list = new list(types::String);
    listenumerator listenumerator;
    query q;
    querybuilddatasource qbds;
    ;
    list.addEnd(SalesStatus::Delivered);
    list.addEnd(SalesStatus::Invoiced);
   
    qbds = this.query().dataSourceNo(1);
    listenumerator = list.getEnumerator();
    while(listenumerator.moveNext())
    {
        qbds.addRange(fieldnum(SalesTable,SalesStatus)).value(queryvalue(listenumerator.current()));
    }
    info(this.query().dataSourceNo(1).toString());
    super();
}

Monday, February 24, 2014

Creating Custom AIF Service in Ax 2009 / 2012

Creating the below custom service to print the available physical quantity of particular item.

1. Creating a class 'InventOnHandService'

2. Create a method under this class 'itemOnHandPhysical'

//[SysEntryPointAttribute(true)]
public InventQty itemOnHandPhysical(ItemId  _itemId = '')
{
    SalesLine       salesLine;
    InventOnhand    inventOnHand;
    InventMovement  movement;
    InventQty       qty = 0;

    select firstOnly salesLine
        where salesLine.ItemId == _itemId;

    if (salesLine)
    {
        movement     = InventMovement::construct(salesLine);
        inventOnhand = InventOnhand::newPhysicalUpdate(movement,
                                                       movement.inventdim());

        qty          = inventOnHand.availPhysical();
    }

    return qty;
}

3. Create new service 'InventOnHandService'. Change the Class property to 'InventOnHandService'

4. Add Operation 'itemOnHandPhysical'.

5. To test the above custom service, create a job 'InventOnHandService_CustomService'

static void InventOnHandService_CustomService(Args _args)
{
    // Customer Service class
    InventOnHandService     inventService;
    inventQty inventQty;
    ;
    inventService = new InventOnHandService();
    inventQty = inventService.itemOnHandPhysical('1001');
    info(num2str(inventQty,0,2,1,1));
}

Monday, February 17, 2014

Run Batch Job automatically in Ax 4.0

In Class 'SysStartupCmdBatchRun', add the code in infoRun method.

void infoRun()
{
    BatchRun batchRun = new BatchRun();
;

    batchRun.parmUseForm(true);
    batchRun.parmGroupId(parm); //added
    batchRun.run();
}
in the client configuration, mention the batchgroup name like startupcmd=batch_invoice

Batch Job

Creating sample batch job

class AV_RunbaseBatchDemo extends RunbaseBatch
{
    NoYes           displayMessage;
    Description     message;

    DialogField     fieldDisplayMessage;
    DialogField     fieldMessage;

    #define.CurrentVersion(1)

    #localmacro.CurrentList
        displayMessage,message
    #endmacro
}
protected Object dialog()
{
    DialogRunbase dlg;
    ;

    dlg                 = super();
    fieldDisplayMessage = dlg.addFieldValue(typeId(NoYes), displayMessage, "Display message", "Indicates whether to display a message or not.");
    fieldMessage        = dlg.addFieldValue(typeId(Description), message, "Message", "The message to display");

    return dlg;
}
public boolean getFromDialog()
{
    boolean ret;
    ;

    ret             = super();
    displayMessage  = fieldDisplayMessage.value();
    message         = fieldMessage.value();

    return ret;
}
public void initParmDefault()
{
    super();
    displayMessage  = NoYes::Yes;
    message         = "Hello World!";
}
public container pack()
{
    return [#CurrentVersion, #CurrentList];
}
public void run()
{
    if (displayMessage)
    {
        info(message);
    }
}
public boolean unpack(container _packedClass)
{
    Integer version = RunBase::getVersion(_packedClass);
    ;

    switch (version)
    {
        case #CurrentVersion:
            [version, #CurrentList] = _packedClass;
            break;

        default:
            return false;
    }

    return true;
}
public static AV_RunbaseBatchDemo construct()
{
    return new AV_RunbaseBatchDemo();
}
public static ClassDescription description()
{
    return "Runbase batch demo";
}
public static void main(Args    _args)
{
    AV_RunbaseBatchDemo runbaseBatchDemo = AV_RunbaseBatchDemo::construct();
    ;
    if (runbaseBatchDemo.prompt())
    {
        runbaseBatchDemo.run();
    }
}

Dependent lookup in dialog class

class dialogDepLookup extends runbase
{
    DialogRunbase       dlg;
    DialogField     fieldCustAccount;
    DialogField     fieldCustTransDate;

    CustAccount     custAccount;
    TransDate       transDate;

    #define.CurrentVersion(1)

    #localmacro.CurrentList
        custAccount,transDate

    #endmacro
}
protected Object dialog()
{
    dlg = new DialogRunbase("test",this);


    //dlg                 = super();
    fieldCustAccount = dlg.addFieldValue(typeId(CustAccount),'', "Customer Account");
    fieldCustTransDate = dlg.addFieldValue(typeId(TransDate), datenull(), "Transaction Date");

    return dlg;
}
public void dialogPostRun(DialogRunbase _dialog)
{
    super(_dialog);

    _dialog.dialogForm().formRun().controlMethodOverload(true);
    _dialog.dialogForm().formRun().controlMethodOverloadObject(this);
}
void Fld2_1_lookup()
{
    Object control = dlg.formRun().controlCallingMethod();
    ;

    this.transDate_Lookup(control, fieldCustAccount.value());
}
public boolean getFromDialog()
{
    boolean ret;
    ;

    ret             = super();
    custAccount  = fieldCustAccount.value();
    transDate         = fieldCustTransDate.value();

    return ret;
}
public container pack()
{
    return [#CurrentVersion, #CurrentList];
}
void run()
{
    //#OCCRetryCount
    //setprefix("@SYS3761");

    try
    {
        info(custAccount);
        info(date2strxpp(transDate));
    }
    catch (Exception::Deadlock)
    {
        retry;
    }   
}
public void transDate_Lookup(FormStringControl _ctrl,CustAccount _custAccount)
{
    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(CustTrans),_ctrl);
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource = query.addDataSource(tablenum(CustTrans));
    ;

    sysTableLookup.addLookupfield(fieldnum(CustTrans,TransDate));
    queryBuildDataSource.addRange(fieldnum(CustTrans,AccountNum)).value(_custAccount);
    queryBuildDataSource.orderMode(orderMode::GroupBy);
    queryBuildDataSource.addSortField(fieldnum(CustTrans,TransDate));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}
public boolean unpack(container _packedClass)
{
    Integer version = RunBase::getVersion(_packedClass);
    ;

    switch (version)
    {
        case #CurrentVersion:
            [version, #CurrentList] = _packedClass;
            break;

        default:
            return false;
    }

    return true;
}
public static void main(Args    _args)
{
    dialogDepLookup dialogDepLookup = new dialogDepLookup();
    ;
    if (dialogDepLookup.prompt())
    {
        dialogDepLookup.run();
    }
}

Create new project through X++

static void createProject(Args _args)
{
    sysprojectfilterrunbase upgradeproject;
    utilelements            theelements;
    ;

    upgradeproject = new sysprojectfilterrunbase();
    upgradeproject.parmProjectNode(systreenode::createProject('test'));
    upgradeproject.grouping(sysprojectgrouping::AOT);

    while select name, utilLevel, RecordType, ParentId from theelements
    where theelements.utilLevel == UtilEntryLevel::usr
    {
        try
        {
            theelements.reread();
            upgradeproject.doUtilElements(theelements);
        }
        catch (exception::Error)
        {
            throw error('error');
        }
    }

    upgradeproject.write();
    info('finish');
}

Creating context (Right-click) menu for display field

Creating right click filter options for display field disCustName (Customer Name)

Add the override method 'Context' for the name field

public void context()
{
    int             selectedMenu;
    formrun         fr;
    Args            ag;
    Name            strtext;
    querybuilddataSource qb1;
    queryrun    qr;
    query       q;
    PopupMenu menu = new PopupMenu(element.hWnd());
    int a = menu.insertItem('Filter By Field'); //2
    int b = menu.insertItem('Filter By Selection'); //3
    int c = menu.insertItem('Remove Filter'); //4
    ;

    selectedMenu = menu.draw();
    print selectedMenu;pause;
    switch (selectedMenu)
    {
    case -1: //Filter by field
            break;
    case 2:
            ag = new args('SysformSearch');
            fr = new formrun(ag);
            fr.run();
            fr.wait();
//Reading User entered value for filter process
            strtext = '';
            strtext = fr.design().controlName('FindEdit').valueStr();

            if(strtext)
            {
//Creating a query for filter
                q   = smmBusRelTable_ds.query();
                qb1 = q.dataSourceTable(tablenum(smmBusRelTable));
                qb1 = qb1.addDataSource(TableNum(CustTable));
                qb1.addLink(FieldNum(smmBusRelTable,CustAccount),FieldNum(CustTable,AccountNum));
                //strtext = strreplace(disCustName.text(),',','*');
                info(strtext);
                qb1.addRange(FieldNum(CustTable,Name)).value(strtext);
                smmBusRelTable_ds.query(Q);
                info(qb1.toString());
                smmBusRelTable_ds.executeQuery();
            }
            break;

    case 3:   // Filter By Selection
         
            q   = smmBusRelTable_ds.query();
            qb1 = q.dataSourceTable(tablenum(smmBusRelTable));
            //qb1.clearRanges();
            qb1 = qb1.addDataSource(TableNum(CustTable));
            qb1.addLink(FieldNum(smmBusRelTable,CustAccount),FieldNum(CustTable,AccountNum));
            //info(disCustName.text());
            strtext = strreplace(disCustName.text(),',','*');
            qb1.addRange(FieldNum(CustTable,Name)).value(strtext);
            smmBusRelTable_ds.query(Q);
            info(qb1.toString());
            //qr = new queryrun(smmBusRelTable_ds.query());
            smmBusRelTable_ds.executeQuery();
            break;
    case 4 :   // Remove Filter
            q   = new Query();
            qb1 = q.addDataSource(tablenum(smmBusRelTable));
            qb1.clearLinks();
            qb1.clearRanges();
            smmBusRelTable_ds.query(Q);
            smmBusRelTable_ds.removeFilter();
            break;

    Default:
            break;
    }

}

Using DictTable

Truncate table using DictTable object

class SqlPermissionJobCls
{
}
static void main(args _args = null)
{
    DictTable dictTable = new DictTable(tablenum(TestTable));
    str sqlTableName;
    SqlDataDictionary sqlTable;

    if (dictTable && dictTable.enabled())
    {
        sqlTableName = dictTable.name(DbBackend::Sql);
        sqlTable = new SqlDataDictionary();
        // Try to truncate only if the table does exist
        // in the SQL database.
        if (sqlTable.tableExist(sqlTableName))
        {
            new SqlDataDictionaryPermission(
                methodstr(SqlDataDictionary, tableTruncate)).assert();
            sqlTable.tableTruncate(tablenum(TestTable));
            CodeAccessPermission::revertAssert();
        }
    }
}

Using sub-query through X++

static void SelectAsExpression3Job(Args _args)
{
    int64 nRecId,
        nCount;
    str sAccountNum,
        sName;
    ;
    // Test_1.a
    sAccountNum = (select firstonly AccountNum from CustTable
        order by AccountNum desc
        where 0 == 0 // 'where' must occur after 'order by'.
        ).AccountNum;
    info(strFmt("Test_1.a: %1", sAccountNum));

    // Test_1.b
    sAccountNum = (select maxof(AccountNum) from CustTable).AccountNum;
    Global::info(strFmt("Test_1.b: %1", sAccountNum));

    // Test_2.c
    nRecId = (select maxof(RecId) from CustTable
        where CustTable.Blocked == CustVendorBlocked::No).RecId;
    info(strFmt("Test_2.c: %1", nRecId));

    // Test_2.d
    nRecId = (select count(RecId) from CustTable
        where CustTable.Blocked == CustVendorBlocked::No).RecId;
    info(strFmt("Test_2.d: %1", nRecId));

    // Test_3
    sName = (select Name from AssetTable
        where AssetTable.AssetId ==
            (select AssetId from AssetTrans
                where AssetTrans.AssetId == 'VEHC-005' //"CNC-01"
            ).AssetId).Name;
    info(strFmt("Test_3: %1", sName));
}

Populating Data source Using Temp Table

Form init method

public void init()
{
    custtable        nut; // regular table in AOT
    custtable        tmp; // instead of creating in AOT a temporary table
                                   // we can create a temporary instance of the preivous regular table
    ;
     super();

    // here we make it temporary;
    // it will disappear from memory when loses the scope
    // in other words, when we close the form
    tmp.setTmp();

    // simply selecting some of records from the regular table
    while select nut
        where nut.City=='Toronto'
    {
        // and putting them in the temporary one
        tmp.data(nut);
        tmp.doInsert();

    }
    // finally to show them on the form
    // we set the form data source to the temporary table
    custtable.setTmp();
    custtable.setTmpData(tmp);
}

Using external websevice

class CampaignClients
{
}
public static server void getClient()
{
    COM     myXmlHttp;
    url     webServiceUrl;
    str     method;
    str     userName;
    str     password;
    str     strXML;
    str     strTxt;
    str     result;
    str     fileName;
    str     listValue;
    int     listLength;
    int     i;


    System.Exception netExcepn;
    InteropPermission permission;
    FileIOPermission    fileIOPerm;
    COM             receiveCom;
    XMLDocument     xmlDocu;
    XMLDocument     doc = XMLDOcument::newBlank();
    XMLElement      node;
    XMLNodeList     elemlist;
    str s_Json;
    ;

    try {

    permission = new InteropPermission(InteropKind::ComInterop);
    permission.assert();

    myXmlHttp = new COM('Microsoft.XMLHTTP'); // used ms xml http com object to communicate with the web service
    webServiceUrl   = strfmt("https://api.createsend.com/api/v3/clients.json");
    method          = "GET";
    fileName = "c:\\temp\\getRateFile.xml";
    xmlDocu = XmlDocument::newBlank('utf-8');

    CodeAccessPermission::revertAssert();
    fileIOPerm = new FileIOPermission(fileName, 'r');
    fileIOPerm.assert();

    xmlDocu.load(fileName);

    s_Json ='{"CompanyName": "Karya","Country": "India","TimeZone": "(GMT+10:00) Canberra, Melbourne, Sydney"}';

    CodeAccessPermission::revertAssert();
    permission = new InteropPermission(InteropKind::ComInterop);
    permission.assert();

    myXmlHttp.open(method,webServiceUrl,false,"2b4c0d6d781cbb43983e88674154cd39",password);
    myXmlHttp.setRequestHeader("content-type","text/xml"); //application/x-www-form-urlencoded
    myXmlHttp.send(s_Json);

    strTxt =strrtrim(myXMLHttp.responseText());
    info(strTxt);

    receiveCom  = myXMLHttp.responseXML();

    doc.loadXml(receiveCom.xml());
    elemList = doc.GetElementsByTagName("v3:Amount");
    listLength = elemList.length();

    for (i=0; i < elemList.length(); i++)
    {

        info(elemList.item(i).innerXml());
    }

    }
    catch (exception::CLRError)
    {
     netExcepn = CLRInterop::getLastException();
     info(netExcepn.ToString());
    }
    catch(exception::Error)
    {

     netExcepn = CLRInterop::getLastException();
     info(netExcepn.ToString());
    }
}

Create XML through X++


class ExportToXML
{
}
public static void main(Args _args)
{
    #AviFiles
    smmCampaignSelection smmCampaignSelection;
    SysOperationProgress    progress = new SysOperationProgress();
    int                     total, updateCount, i;
    ContactPerson           contactPerson;
    XmlDocument doc;
    XmlElement nodeXml;
    XmlElement nodeSubs;
    XmlElement nodeSub;
    XmlElement nodeTable;
    XmlElement nodeEmail;
    XmlElement nodeName;
    XmlElement nodeCustomFields;
    XmlElement nodeCustomField;
    XmlElement nodeKey;
    XmlElement nodeValue;
    XmlElement nodeReSub;
    XmlElement nodeQueueSub;
    XmlElement nodeRestartSub;

    boolean _includeInvalid = false;
    smmCampaignTable _smmCampaignTable;

    #define.filename('D:\\campaignMultipleSubs.xml')
    /*#define.filename(strfmt("%1\%2_%3_%4_%5.csv", @"\\gs1melb17\axenvironments$\Prod\Campaigns\",
                                          curUserId(),
                                          _smmCampaignTable.CampaignId,
                                          strkeep(date2str(systemdateget(),321,2,1,2,1,4),"0123456789"),
                                          strkeep(time2str(timenow(),1,1),"0123456789"))) //TODO: To parameterise
*/
    ;
    _smmCampaignTable = smmCampaignTable::find('8012');

    if (!_includeInvalid)
        select count(Recid) from smmCampaignSelection where
            smmCampaignSelection.CampaignId == _smmCampaignTable.CampaignId
            //&& smmCampaignSelection.GS1_CampaignStatus != GS1_CampaignStatus::Invalid
            ;
    else
        select count(Recid) from smmCampaignSelection
            where smmCampaignSelection.CampaignId == _smmCampaignTable.CampaignId
            ;

    total = smmCampaignSelection.RecId;

    progress.setCaption("Export Communication List to Web");
    progress.setAnimation(#AviUpdate);
    progress.setTotal(total);

    ttsbegin;
    doc = XmlDocument::newBlank();
    nodeXml = doc.createElement('xml');

    doc.appendChild(nodeXml);
    nodeTable = doc.createElement('AddSubscribers');
    nodeXml.appendChild(nodeTable);
    nodeSubs = doc.createElement('Subscribers');
    nodeTable.appendChild(nodeSubs);
    i = 0;
    while select forupdate smmCampaignSelection
        where smmCampaignSelection.CampaignId == _smmCampaignTable.CampaignId
    {
        i++;
        //if (!_includeInvalid && smmCampaignSelection.GS1_CampaignStatus == GS1_CampaignStatus::Invalid)
            //continue;

        progress.setText(strfmt("Processing %1 of %2..", updateCount, total));
        progress.setCount(updateCount, 1);

        //smmCampaignSelection.gs1_ValidateLine();

        if (smmCampaignSelection.ContactPersonId == "274227")
            debug::assert(true);

        contactPerson = ContactPerson::find(smmCampaignSelection.ContactPersonId);

        if(contactPerson)
        {
            nodeSub = doc.createElement('Subscriber');
            nodeSubs.appendChild(nodeSub);

            /*apiKey,
                                                   _smmCampaignTable.GS1_CampaignListId,
                                                   contactPerson.Email,
                                                   contactPerson.Name,
                                                   contactPerson.ContactPersonId,
                                                   smmCampaignSelection.CampaignId,
                                                   _smmCampaignTable.CampaignGroupId);
                                                   */

            //ContactPerson Email
            nodeEmail = doc.createElement('EmailAddress'); //fieldstr(ContactPerson, Email));
            nodeEmail.appendChild(doc.createTextNode(contactPerson.Email));
            nodeSub.appendChild(nodeEmail);
            //ContactPerson Name
            nodeName = doc.createElement('Name'); //fieldstr(ContactPerson, Name));
            nodeName.appendChild(doc.createTextNode(contactPerson.Name));
            nodeSub.appendChild(nodeName);
            //CustomeFields
            //if(i==1)
            {
                nodeCustomFields = doc.createElement('CustomFields');
                nodeSub.appendChild(nodeCustomFields);
            }
            //CustomField
            nodeCustomField = doc.createElement('CustomField');
            nodeCustomFields.appendChild(nodeCustomField);
            //smmCampaignSelection CampaignId
            //nodeKey = doc.createElement(fieldstr(smmCampaignSelection, CampaignId));
            nodeKey = doc.createElement('Key');
            nodeKey.appendChild(doc.createTextNode(smmCampaignSelection.CampaignId));
            nodeCustomField.appendChild(nodeKey);
            //smmCampaignTable CampaignGroupId
            //nodeValue = doc.createElement(fieldstr(smmCampaignTable, CampaignGroupId));
            nodeValue = doc.createElement('Value');
            nodeValue.appendChild(doc.createTextNode(_smmCampaignTable.CampaignGroupId));
            nodeCustomField.appendChild(nodeValue);

            updateCount++;
        }
    }
    nodeReSub = doc.createElement('Resubscribe');
    nodeReSub.appendChild(doc.createTextNode('true'));
    nodeTable.appendChild(nodeReSub);

    nodeQueueSub = doc.createElement('QueueSubscriptionBasedAutoResponders');
    nodeQueueSub.appendChild(doc.createTextNode('false'));
    nodeTable.appendChild(nodeQueueSub);

    nodeRestartSub = doc.createElement('RestartSubscriptionBasedAutoresponders');
    nodeRestartSub.appendChild(doc.createTextNode('true'));
    nodeTable.appendChild(nodeRestartSub);

    doc.save(#filename);
    //return updateCount ? true : false;
    info(updatecount ? 'true' : 'false');
}