Query Range more than one Value in x++ Query

static void queryMultipleValue(Args _args)
{
    Query                   query;
    QueryRun                queryrun;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr, qbr1;
    SalesTable              salesTable;
    str                     rangevalue;
    ;

    query = new Query();

    qbds = query.addDataSource(tableNum(SalesTable));
    qbds.addSortField(fieldNum(SalesTable, CustAccount), SortOrder::Ascending);
    qbr     = qbds.addRange(fieldNum(SalesTable, CustAccount));

   
    qbr.value(strFmt("%1,%2,%3", queryValue("US-4000"), queryValue("US-027"), queryValue("US-028")));
 

    queryrun = new QueryRun(query);


    info(strFmt(queryrun.toString()));
    while(queryrun.next())
    {
        salesTable = queryrun.get(tableNum(SalesTable));

        info(strFmt("%1", salesTable.CustAccount));
    }
}

Simple x++ query with enum value range

static void queryEnumValue(Args _args)
{
    Query                   query;
    QueryRun                queryrun;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    PurchTable              purchTable;
    ;
   
    query = new Query();
   
    qbds = query.addDataSource(tableNum(PurchTable));
    qbds.addSortField(fieldNum(PurchTable, PurchId), SortOrder::Ascending);
    qbr     = qbds.addRange(fieldNum(PurchTable, PurchStatus));
   
    qbr.value(enum2str(PurchStatus::Received));
   
    queryrun = new QueryRun(query);
   
    while(queryrun.next())
    {
        purchTable = queryrun.get(tableNum(PurchTable));
       
        info(strFmt("%1", purchTable.PurchId));
    }
}

Date Range filter in x++ query

static void QueryDateRange(Args _args)
{
    Query                   query;
    QueryRun                queryrun;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    PurchLine               purchLine;
    TransDate               fromDate, Todate;
    ;
   
    fromDate = mkDate(19,11,2016);
    Todate   = mkDate(20,12,2016);
   
    query = new Query();
   
    qbds = query.addDataSource(tableNum(PurchLine));
   
    qbds.addSortField(fieldNum(PurchTable, PurchId), SortOrder::Ascending);
   
    qbr     = qbds.addRange(fieldNum(PurchLine, deliveryDate));
   
    qbr.value(queryRange(fromDate, Todate));
   
    queryrun = new QueryRun(query);
   
    while(queryrun.next())
    {
        purchLine = queryrun.get(tableNum(purchLine));
       
        info(strFmt("%1", purchLine.PurchId));
    }
}

Simple Custom Lookup Ax 2012 R3

In this post we'll see how easily we can create a custom lookup.

Suppose I have two fields 

1. Country/Region

2. StateId

Now I want to create a lookup so that If I will select any country then state lookup must show states which belongs to my selected country

for example If I have selected India in country field then it must show only indian state in state field

Lets see how can we achieve it.

Step 1: Create a table and then create two fields

           1. CountryRegionId

           2. StateId

Step 2: Create a form

Step 3: For custom lookup we will use State master table i.e. LogisticsAddressState

here we can see there is a method lookupStateId

public client static void lookupStateId(

    FormStringControl               _ctrl,

    LogisticsAddressCountryRegionId _countryRegionId)

{

    SysTableLookup       sysTableLookup = SysTableLookup::newParameters(tableNum(LogisticsAddressState), _ctrl);

    Query                query = new Query();

    QueryBuildDataSource queryBuildDataSource;

    sysTableLookup.addLookupfield(fieldNum(LogisticsAddressState, StateId));

    // <GEERU>

    if (isCountryRegionRU(_countryRegionId))

    {

        sysTableLookup.addLookupMethod(tableMethodStr(LogisticsAddressState, fullName_RU));

        sysTableLookup.addLookupMethod(tableMethodStr(LogisticsAddressState, propertiesZipCode_RU));

        sysTableLookup.addLookupMethod(tableMethodStr(LogisticsAddressState, propertiesGniCode_RU));

    }

    else

    {

    // </GEERU>

        sysTableLookup.addLookupfield(fieldNum(LogisticsAddressState, CountryRegionId));

        sysTableLookup.addLookupfield(fieldNum(LogisticsAddressState, Name));

    // <GEERU>

    }

    // </GEERU>

    queryBuildDataSource = query.addDataSource(tableNum(LogisticsAddressState));

    queryBuildDataSource.addRange(fieldNum(LogisticsAddressState, CountryRegionId)).value(queryValue(_countryRegionId));

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

Step 4 : Go to Design and select CountryRegionId control then go to properties and set AutoDeclaration property "YES"

Step 5:

Now override lookup method on Form > Datasource > Fields >StateId > methods > lookup

and customize lookup method

public void lookup(FormControl _formControl, str _filterStr)

{

    

    LogisticsAddressState::lookupStateId(_formControl, S3_countryStateLookup_CountryRegionId.valueStr());

}

Step 5: Now select India in country field and then you can see lookup it will show only Indian state 

Get Current Record for different data source x++

By this we can use same MenuItem at different place and for different data source.

---------------------------------------------------------------------------
Public static void main(Args _args)
{
    RetailStatementTable    retailStatementTable;
    RetailStatementLine     retailStatementLine;
    RetailStatementId         StatementID;
    RetailCountedAmountUpdate  RetailCountedAmountUpdate = new RetailCountedAmountUpdate();
    ;

     switch (_args.dataset())
    {
            case tablenum(RetailStatementTable) :
            retailStatementTable =  _args.record();
            StatementID = retailStatementTable.statementId;
            break;
           
            case tablenum(RetailStatementLine) :
            retailStatementLine =  _args.record();
            StatementID = retailStatementLine.statementId;
            break;
    }
 

    RetailCountedAmountUpdate.run(StatementID);

}

Automatic Create Folder x++

static void CreateFolder(Args _args)
{
    str  path = 'C:\\2017\\Jan';
    ;
    if (!WinApi::pathExists(path))
    {
        WinApi::createDirectoryPath(path);
    }
}

Post product receipt for registered quantity x++, Ax 2012

public void Run()
{

    PurchFormLetter             purchFormLetter;
    PurchFormletterParmData     purchFormLetterParmData;
    PurchParmUpdate             purchParmUpdate;
    PurchParmTable              purchParmTable;
    PurchParmLine               purchParmLine;
    PurchTable                  purchTable,purchTable1;
    PurchLine                   purchLine;
    InventTrans                 inventTrans;
    InventTransOrigin           inventTransOrigin;
    PurchId                     purchId;
    Num                         packingSlipId;

    purchId       = "INMF-000886";
     purchTable    = PurchTable::find(purchId);
    packingSlipId = "Challan_"+int2str(timeNow());
    ttsBegin;
    // Create PurchParamUpdate table
    purchFormLetterParmData = PurchFormletterParmData::newData(
        DocumentStatus::PackingSlip,
        VersioningUpdateType::Initial);

    purchFormLetterParmData.parmOnlyCreateParmUpdate(true);
    purchFormLetterParmData.createData(false);
    purchParmUpdate = purchFormLetterParmData.parmParmUpdate();

    // Set PurchParmTable table
    purchParmTable.clear();
    purchParmTable.TransDate                = SystemDateGet();
    purchParmTable.Ordering                 = DocumentStatus::PackingSlip;
    purchParmTable.ParmJobStatus            = ParmJobStatus::Waiting;
    purchParmTable.Num                      = packingSlipId;
    purchParmTable.PurchId                  = purchTable.PurchId;
    purchParmTable.PurchName                = purchTable.PurchName;
    purchParmTable.DeliveryName             = purchTable.DeliveryName;
    purchParmTable.DeliveryPostalAddress    = purchTable.DeliveryPostalAddress;
    purchParmTable.OrderAccount             = purchTable.OrderAccount;
    purchParmTable.CurrencyCode             = purchTable.CurrencyCode;
    purchParmTable.InvoiceAccount           = purchTable.InvoiceAccount;
    purchParmTable.ParmId                   = purchParmUpdate.ParmId;
    purchParmTable.insert();

    // Set PurchParmLine table
    while select purchLine join InventTrans Join inventTransOrigin
        where purchLine.PurchId == purchTable.purchId
             && inventTransOrigin.InventTransId == purchLine.InventTransId
             && inventTrans.InventTransOrigin == inventTransOrigin.RecId
             && inventTrans.StatusReceipt   == StatusReceipt::Registered
    {
        purchParmLine.InitFromPurchLine(purchLine);

        purchParmLine.ReceiveNow    = inventTrans.Qty;
        purchParmLine.ParmId        = purchParmTable.ParmId;
        purchParmLine.TableRefId    = purchParmTable.TableRefId;
        purchParmLine.setQty(DocumentStatus::PackingSlip, false, true);
        purchParmLine.setLineAmount();
        purchParmLine.insert();

    }

    purchFormLetter = PurchFormLetter::construct(DocumentStatus::PackingSlip);
    purchFormLetter.transDate(systemDateGet());
    purchFormLetter.proforma(false);
    purchFormLetter.specQty(PurchUpdate::Recorded);
    purchFormLetter.purchTable(purchTable);

 
    purchFormLetter.parmParmTableNum(purchParmTable.ParmId);
    purchFormLetter.parmId(purchParmTable.ParmId);
    purchFormLetter.purchParmUpdate(purchFormLetterParmData.parmParmUpdate());
    purchFormLetter.run();
    ttsCommit;

 

    info("Done...!!!");
}

Save SSRS report in PDF and send email attachemnt x++

public void SaveSSRStoPDF()
{
    SrsReportRunController              ssrsController = new SrsReportRunController();
    PayslipContract                 Contract = new PayslipContract();
    SRSPrintDestinationSettings         printerSettings;
    Filename                            ReportPath;
 
    while select HcmWorker
    {
         
            ReportPath = "C:\\"+ int2str(yearName) +"\\"+ int2str(monthName) +"\\"+ HcmWorker.PersonnelNumber +".pdf";
            ssrsController.parmReportName(ssrsReportStr(WorkerPayslip, Report));
            ssrsController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
            ssrsController.parmShowDialog(false);
            Contract.parmPayGrpCode(PayGrpCode);
            Contract.parmYearName(yearName);
            Contract.parmHcmPersonnelNumberId(HcmWorker.PersonnelNumber);
            Contract.parmMonthName(monthName);
            ssrsController.parmReportContract().parmRdpContract(Contract);

            //link the printer settings to the controller
            printerSettings = ssrsController.parmReportContract().parmPrintSettings();
            //print to pdf and always overwrite if the file exists
            printerSettings.printMediumType(SRSPrintMediumType::File);
            printerSettings.fileFormat(SRSReportFileFormat::PDF);
            printerSettings.overwriteFile(true);
            printerSettings.fileName(ReportPath);


            //run & save the report
            ssrsController.runReport();
         // code to send email to employee with attached salary slip
            this.email(ReportPath);
            }
    }
}
For Email click here

Send Email with Attachment x++ for port 465 or 587

This code is also useful for below error.

Method 'send' in COM object of class 'CDO.Message' returned error code 0x8004020E (<unknown>) which means: <unknown>.

 For SSL enabled mail servers. Like: gmail, or office365 smtp.gmail.com with port 465 or 587 you can use below code

public void email(filename ReportPath)
{
    System.Net.Mail.MailMessage             mailMessage;
    System.Net.Mail.SmtpClient              myMail;
    System.Net.Mail.MailAddressCollection   mailcoll;
    System.Net.Mail.MailAddress             mailFrom;
    System.Net.Mail.MailAddress             mailTo;
    System.Net.Mail.Attachment              attachment;
    System.Net.Mail.AttachmentCollection    attachementCollection;

    str                                     receiverMailAddress;
    str                                     mailBody;
    str                                     smtpServer;
    str                                     mailSubject;
    int                                     SMTPPort;


    #File
    str                 mail;
    userinfo            userInfo;
    str pwd;
    SysEmailParameters parameters = SysEmailParameters::find();
    ;
    new InteropPermission(InteropKind::ClrInterop).assert();

    receiverMailAddress = HcmWorker.email();

    if(receiverMailAddress != "") // check if email id exists for employee then send a mail
    {

            mailSubject         = "Pay slip for "+ mthName(Monthname) +"-"+int2str(YearName);
            mailFrom            = new  System.Net.Mail.MailAddress(parameters.SMTPUserName ,"DAX Info");
            mailTo              = new  System.Net.Mail.MailAddress(receiverMailAddress);
            mailcoll            = new  System.Net.Mail.MailAddressCollection();
            mailBody            = "Hello "+HcmWorker.name()+", Kindly Find the attachment salary slip"; // Body Should be in HTML Format;


            try
            {
            smtpServer          = SysEmaiLParameters::find(false).SMTPRelayServerName;// using the SMTP server ip //setup in email Parameters
            mailMessage         = new System.Net.Mail.MailMessage(mailFrom,mailTo);
            mailmessage.set_Subject(mailSubject);
            mailmessage.set_Body(mailBody);

            attachementCollection = mailMessage.get_Attachments();
            attachment = new System.Net.Mail.Attachment(ReportPath);
            attachementCollection.Add(attachment);


            SMTPPort            = SysEmaiLParameters::find(false).SMTPPortNumber;
            myMail              = new System.Net.Mail.SmtpClient(smtpServer, SMTPPort);

            myMail.set_EnableSsl(true); // For SSL enabled mail servers. Ex: gmail, smtp.gmail.com, port 465 or 587

            pwd = SysEmaiLParameters::password();

            mymail.set_Credentials(New System.Net.NetworkCredential(parameters.SMTPUserName, pwd));

            mymail.Send(mailmessage);
            }
            catch(Exception::CLRError)
            {
               throw Exception::CLRError;
            }

            mailMessage.Dispose();
            CodeAccessPermission::revertAssert();
    }
}