Validation on Empty space through X++

 Tables-->InventBatch-->ValidateField

 case fieldNum (InventBatch,InventBatchId):

if(strContains(this.InventBatchId," "))

{

      ret = checkFailed("Empty space is not Allowed");

}

SSRS Expression

Add total on Group by :

=SUM(MAX(Fields!RoundOff.Value,"LedgerVoucher"))

here LedgerVoucher is group name

Serial number on Group by :

syntax:

=RunningValue(<Grouped field>,CountDistinct,”<DataSet>”)

Example:

=RunningValue(Fields!LedgerVoucher.Value,CountDistinct,"SendingBillsDS")

Date Format : Monthname - year 

=FORMAT(Fields!ExpDate.Value, "MMM-yy")

Ex: Jan-23

call table level lookup method in form

EmplTable-->Method
 public client static void lookupActiveEmpId(FormStringControl _ctrl)
{
    SysTableLookup          sysTableLookup =      SysTableLookup::newParameters(tableNum(EmplTable),_ctrl);
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource = query.addDataSource(tableNum(EmplTable));

    queryBuildDataSource.addRange(fieldNum(EmplTable,Active)).value(enum2str(NoYes::Yes));
    sysTableLookup.addLookupfield(fieldNum(EmplTable,EmplId));
    sysTableLookup.addLookupfield(fieldNum(EmplTable, EmplName));
    sysTableLookup.addLookupfield(fieldNum(EmplTable,Active));
    sysTableLookup.addSelectionField(fieldNum(EmplTable,EmplId));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}
Form-->Design String Control-->method

public void lookup()
{   
    EmplTable::lookupActiveEmpId(this);
}

Passing ranges in Query through X++

Not equal to Enum Value :

qbr = qbds1.addRange(fieldNum(InventTransferTable,       TransferStatus)).value(SysQuery::valueNot(InventTransferStatus::Created));
Passing NOT equal multiple Enum values:

this.query().dataSourceTable(tablenum(PurchTable)).addRange(fieldnum(PurchTable, PurchStatus)).value(strfmt('!%1,!%2',enum2str(PurchStatus::Canceled),enum2str(PurchStatus::Invoiced)));

qbds.addRange(fieldNum(InventSite, SiteId)).value(strFmt("%1,%2",strFmt('!%1*','*_VF*'),strFmt('!%1*','*_VP*')));

      

LessThan:

qbr3 = qbds.addRange(fieldNum(CustTrans,TransDate)).value(strFmt('<%1',queryValue(asondate)));

Not Equal to 0 (Zero) :


qbds.addRange(fieldNum(CustTrans,AmountCur)).value('!0');

LessThan zero:

 qbr5 = qbds2.addRange(fieldNum(VendInvoiceJour, InvoiceAmount));
 qbr5.value(strFmt('<%1',queryValue(0)));

Not Equal to Null Values:

qbdsBankAccountTrans.addRange(fieldNum(BankAccountTrans,ChequeNum)).value(sysquery::valueNotEmptyString());
    
AND operator

qbds1.addRange(fieldNum(GeneralJournalEntry,JournalTransactionType)).value(strFmt('%1,%2',enum2str(JournalTransactionType::StockTransfer),enum2str(JournalTransactionType::StockTransferOut)));

Not equal 100:

qbds.addRange(fieldnum(VendTable,accountnum)).value(sysquery::valueNot(“100”));

pass Between Ranges:

qbds.addRange(fieldnum(VendTable,accountnum)).value(queryrange(“1″,”100”));

between two fields :

 qbr1 = queryBuildDataSource.addRange(FieldNum(CustTrans,RecId));

    qbr1.value(strFmt('((%1 < %2))',fieldStr(CustTrans, AmountCur),fieldStr(CustTrans, AmountMST)));

 Enum Values

qbds.addRange(fieldNum(InventSite, RunningOn)).value(strFmt('((%1 == %2) || (%1 == %3))', fieldStr(InventSite,RunningOn),any2int(SiteRunningOn::AX),any2int(SiteRunningOn::GOLD)));

Create Dialog in D365

class simpledialog extends RunBaseBatch
{
    DialogField     fieldfromDate;
    DialogField     fieldtoDate;

    TransDate       fromDate,toDate;   

    #DEFINE.CurrentVersion(1)
    #LOCALMACRO.CurrentList
        fromDate,toDate
    #ENDMACRO



    // pack() and unpack() methods are used to load the last value from user
    public container pack()
    {
    return [#CurrentVersion,#CurrentList];
    }

    public boolean unpack(container _packedClass)
    {
    int version = conpeek(_packedClass, 1);

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

    return true;
}

    public Object dialog()
    {
        Dialog dialog;
        dialog = super();

        // Set a title for dialog
        dialog.caption( 'Purchase Order creation for Out Grower');

        // Add a new field to Dialog
        fieldfromDate = dialog.addField(extendedTypeStr(TransDate), 'From Date');
        fieldtoDate   = dialog.addField(extendedTypeStr(TransDate), 'To Date');

        return dialog;
    }

    boolean validate(Object _calledFrom = null)
    {
        boolean ret;

        ret = super(_calledFrom);
     
        if(fromDate > toDate)
         {
            ret = checkFailed('To Date should be greater than From Date') && ret;
         }
     
        return ret;
    }

    public TransDate parmfromDate(TransDate _fromdate = fromDate)
    {
    fromDate = _fromdate;

    return fromDate;
    }

    public TransDate parmtoDate(TransDate _todate = toDate)
    {
    toDate = _todate;

    return toDate;
    }

    public boolean getFromDialog()
    {
        fromDate = fieldfromDate.value();
        toDate = fieldtoDate.value();     

        return super();
    }

    public void run()
    {
       //your Logic 
    }

    public static void main(Args _args)
    {
        simpledialog   simple = new  simpledialog ();

        // Prompt the dialog, if user clicks OK it returns true
        if (simple .prompt())
        {
            simple .run();
        }
    }

   }

Individual financial dimension value through X++

display DimensionValue displayLocDimension()
{ 
    VendTable                                          vendTable;
    DimensionAttributeValueSet             dimensionAttributeValueSet;
    DimensionAttributeValueSetItem      dimensionAttributeValueSetItem;
    DimensionAttributeValue                  dimensionAttributeValue;
    DimensionAttribute                          dimensionAttribute;
   VendTrans                                          vendtrans
    DimensionValue                               location;

      select * from vendTable
                           where vendTable.AccountNum == "XXXX"
            join RecId from dimensionAttributeValueSet
                          where dimensionAttributeValueSet.RecId == vendtrans.DefaultDimension
            join RecId, DisplayValue from dimensionAttributeValueSetItem
                          where dimensionAttributeValueSetItem.DimensionAttributeValueSet == dimensionAttributeValueSet.RecId
            join RecId from dimensionAttributeValue
                        where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue
            join RecId, Name from DimensionAttribute
                       where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute
                       && dimensionAttribute.Name == "LOCATION";
            {
                location = DimensionAttributeValueSetItem.DisplayValue;
            }
                return location;
  }

userwise security roles

static void userSecurityExcelDocumentnew(Args _args)
{
    SysExcelWorksheetHelper     worksheetHelper;
    SysExcelHelper              sysExcelHelper;
    SysExcelWorkSheet           xlsWorkSheet;
    dialogField                 dialogFilename;
    Dialog                      dialog;
    SecurityUserRole            _SecurityUserRole;
    SecurityRoleTaskGrant       _SecurityRoleTaskGrant;
    SecurityTask                _SecurityTask;
    SecurityRole                _SecurityRole;
    UserInfo                    _UserInfo;
    SysUserInfo                 _sysUserInfo;
    FilenameOpen                filename;
    str                         worksheetName;
    int                         row = 1;
    SysOperationProgress Progressbar = new SysOperationProgress();
    #Excel
    ;
    startLengthyOperation();
    dialog              =   new Dialog("Excel Upoad");
   dialogFilename      =   dialog.addField(extendedTypeStr(FilenameOpen));
   dialog.filenameLookupFilter(["@SYS28576",#XLS,"@SYS28576",#XLSX]);
   dialog.filenameLookupTitle("Export to Excel");
   dialog.caption("User Security details");
   dialogFilename.value(filename);
   if(!dialog.run())
   return;
   filename            =   dialogFilename.value();

    worksheetName = strfmt("%1", date2str(systemdateget(),321,DateDay::Digits2,DateSeparator::None,DateMonth::Digits2,DateSeparator::None,DateYear::Digits4, DateFlags::None));
    sysExcelHelper = SysExcelHelper::construct();
    sysExcelHelper.initialize();
    xlsWorkSheet = sysExcelHelper.addWorksheet(worksheetName);

    worksheetHelper = SysExcelWorksheetHelper::construct(xlsWorkSheet);

     worksheetHelper.addColumn(1, 'User Id', Types::String);
     worksheetHelper.addColumn(2, 'User Name', Types::String);
     worksheetHelper.addColumn(3, 'User Role', Types::String);
     worksheetHelper.addColumn(4, 'Department', Types::String);
     worksheetHelper.addColumn(5, 'Active', Types::String);
     worksheetHelper.addColumn(6, 'Legal Entity', Types::String);
     worksheetHelper.addColumn(7, 'Created Date Time', Types::Date);
     worksheetHelper.addColumn(8, 'Modified Date Time', Types::Date);

     worksheetHelper.setCellValue(1, row,'User Id');
     worksheetHelper.setCellValue(2, row,'User Name');
     worksheetHelper.setCellValue(3, row,'User Role');
     worksheetHelper.setCellValue(4, row,'Department');
     worksheetHelper.setCellValue(5, row,'Active');
     worksheetHelper.setCellValue(6, row,'Legal Entity');
     worksheetHelper.setCellValue(7, row,'Created Date Time');
     worksheetHelper.setCellValue(8, row,'Modified Date Time');

    row++;
    while select _UserInfo
    {
        select _sysUserInfo where _sysUserInfo.Id == _UserInfo.id;
        while select _SecurityUserRole where _SecurityUserRole.User==_UserInfo.id
        {
                while select _SecurityRole where _SecurityRole.RecId==_SecurityUserRole.SecurityRole
                {
                    worksheetHelper.setCellValue(1, row,_UserInfo.id);
                    worksheetHelper.setCellValue(2, row,_UserInfo.name);
                    worksheetHelper.setCellValue(3, row,_SecurityRole.name);
                    worksheetHelper.setCellValue(4, row,_sysUserInfo.departmentName());
                    worksheetHelper.setCellValue(5, row,_UserInfo.enable);
                    worksheetHelper.setCellValue(6, row,_UserInfo.company);
                    worksheetHelper.setCellValue(7, row,_sysUserInfo.createdDateTime);
                    worksheetHelper.setCellValue(8, row,_sysUserInfo.modifiedDateTime);

                row++;
                }
        }
    }

    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);

    sysExcelHelper.save(filename);
    sysExcelHelper.close();

    info(strFmt("Created file: %1", fileName));
}