DAXSPOT

Sunday, December 4, 2022

X++ function date2Str()

 Hi,

This post is about the X++ function date2Str(). This Function Converts the specified date to a string.

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


str date2Str(

    date date,

    int sequence,

    int day,

    int separator1,

    int month,

    int separator2,

    int year

    [, int flags = DateFlags::None])

Parameters

Parameter

Description

date

The date to convert.

sequence

A three digit number that indicates the sequence for the components of the date, 1 for day, 2 for month, and 3 for year.

day

A DateDay enumeration value that indicates the format for the day component of the date.

separator1

A DateSeparator enumeration value that indicates the separator to use between the first two components of the date.

month

A DateMonth enumeration value that indicates the format for the month component of the date.

separator2

A DateSeparator enumeration value that indicates the separator to use between the last two components of the date.

year

A DateYear enumeration value that indicates the format for the year component of the date.

flags

A DateFlags enumeration value that indicates whether the language settings on the local computer should be used to calculate the proper left-to-right or right-to-left sequence in the returned string.


Return Value

A string that represents the specified date.


X++ Example:

static void Job2(Args _args)

{

    date currentDate = today();

    str s;

    int iEnum;

    ;

    s = date2Str

        (currentDate,

        321,

        DateDay::Digits2,


        DateSeparator::Hyphen, // separator1

        DateMonth::Digits2,

        DateSeparator::Hyphen, // separator2


        DateYear::Digits4

        );

    info("Today is:  " + s);

}

/** Example Infolog output

Message (12:36:21 pm)

Today is:  2009-01-13

**/

Thank you,

Thursday, December 1, 2022

Calculate Days and Month of Date - X++

Hi,

In this post we will be related to days and month of Date.

In below sample of code you to get example for following point:

    1. Start date of the month.

    2. End date of the month.

    3. Total number of days in the month.

    4. Remaining number of days in the month.


static void days_Example(Args    _args)

{

    int FromDays, ToDays;


    info(strFmt("Today date is %1", today()));    

    

    // Displays start date of the month for the given date.

    info(strFmt("Start date is %1 for the month of %2", date2str(DateStartMth(Today()),213,2,4,2,4,4), mthName(mthOfYr(today()))));


    // Displays end date of the month for the given date.

    info(strFmt("End date is %1 for the month of %2", date2str(endmth(Today()),213,2,4,2,4,4), mthName(mthOfYr(today()))));


    // Displays total number of days in the month for the given date

    FromDays = date2num(DateStartMth(today()));

    todays = date2num(endmth(today()));

    info(strFmt("There are total %1 days in month %2", (ToDays+1) - FromDays, mthName(mthOfYr(today()))));


    // Displays remaining number of days in the month for the given date

    FromDays = date2num(DateStartMth(today()));

    todays = date2num(today());

    info(strFmt("%1 days are remaining in the month of %2", (ToDays+1) - FromDays, mthName(mthOfYr(today()))));

}


Thank you,

Tuesday, November 15, 2022

Accessing Data of other companies/Legal entities using X++

Hi,

In Microsoft Dynamics AX/D365 Finance and operation, users can setup multiple legal entities. By default in Microsoft Dynamics AX/D365 FO, all tables store data per company unless SaveDataPerCompany property is not changed to NO. By default value of property SaveDataPerCompany  = Yes.

Using crossCompany & changeCompany keyword, you can construct a query to retrieve all records, regardless of the company you are currently logged into. You can fetch the all the company records using keyword - crossCompany and function – changeCompany.


Change-Company:

static void main()

{

  CustTable custTable;

  changeCompany('INMF') 

  {

    custTable = null;

    while select custTable

    {

       //custTable of company 'INMF'.

    }

  }

}


Cross-Company:

static void main()

{

  CustTable custTable;

  while select crosscompany custTable

  {

      //custTable of all the companies

  }

}


Thank you,

Saturday, November 12, 2022

D365F&O / AX7 URL parameters

Hi All,

This post is regarding different type of D365F&O URL parameters.


1. Calling a Display – MenuItem directly via the ''mi'' Parameter

With the ''mi'' parameter you can call a (Display) MenuItem directly. The example shows how to call the ''SalesTableListPage'' directly.

Call: ?cmp=[Company]&mi=[MenuItemName]

Example: MenuItemName -> SalesTableListPage


2. Open a Form via an URL Parameter

If you want to call a Form directly, use the parameter "f".

Call: ?cmp=[Company]&f=[FormName]

Example: MenuItemName -> ProdParameters


3. See your Data in the Built-In Table Browser

The very convenient built-in table browser can be called from the address bar as well. Just use the "mi" parameter from above and add the parameter "tablename", afterwards add the name of the table you want to see.

Call: ?cmp=[Company]&mi=SysTableBrowser&tablename=[TableName]

Example: TableName -> SalesTable


4. Run a Class directly from your Browser

You can also call a runnable class. To do so, add the "SysClassRunner" menu item and the parameter "cls" with the class you want to call.

Call: ?cmp=[Company]&mi=SysClassRunner&cls=[SysClassRunnerExample]


class SysClassRunnerExample

{        

    /// <summary>

    /// Runs the class with the specified arguments.

    /// </summary>

    /// <param name = "_args">The specified arguments.</param>

    public static void main(Args _args)

    {        

        info("From Classrunner!");

    }

}


5. Change the language on the fly

Changing the language is also an option that we can change from the address bar, just add the parameter "lng" and pass the language you want to change AX to.

Call: ?cmp=[Company]&lng=[en-us]


Thank you

Confirmation dialog box/pop up box, X++

Hello,


Below code will help you to show a pop up confirmation dialog.  


boolean    confirmButton;

confirmButton = Box::confirm("Are you sure want to confirm?", "Confirmation", "Once Confirmed, record can not be edited!");

if(confirmButton == true)

{

     info("Confirmed");

}

else

{

    info("Not Confirmed");

}


Thank you,

Tuesday, November 8, 2022

Calculate no of months between two dates, X++ Updated

Hello,

Recently I got a small problem to calculate no of months between two dates.

Dynamics Ax 2012 provides a default function which calculates no of intervals between two dates based on enumeration.


Function looks like:

intvNo(date input_date, date ref_date, int func)


You can use it as;

noOfIntervals = intvNo(refDate, inputDate, intvScale::Month);

 

intvScale enumeration have two values for months

 1. Month

 2. YearMonth


If we provide the intvScale::Month then X++ ignores the year and assumes that month is calculated within one year.

If we provide the intvScale::YearMonth then X++ calculate the number of months between different years. Consider following example:

static void NumberofMonthsbetweenTwodates(Args _args)

{

date inputDate = str2Date("1/1/2007", 213);

date refDate = str2Date("3/1/2009", 213);

int noOfMonths, noOfMonthsBetweenYears;

;

noOfMonths = intvNo(refDate, inputDate, intvScale::Month);

noOfMonthsBetweenYears = intvNo(refDate, inputDate, intvScale::YearMonth);

//noOfMonths= intvNo(firstdate,seconddate,IntvScale::Month);

print("noOfMonths  :"+int2str(noOfMonths) + "  ,noOfMonthsBetweenYears :"+int2str(noOfMonthsBetweenYears));

}

Info box will be return like:

noOfMonths  :2  ,noOfMonthsBetweenYears  :26

-----------------------------------------------------------------------------------------------

Some cases above code did not works then used following:

 InfAdjValidation_MX::monthDifference(FromDate _fromDate, ToDate _toDate)


Example is as follow.

FromDate _fromDate = mkDate(1,1,2018);

ToDate _toDate =mkDate(31,1,2018);

info(int2str( InfAdjValidation_MX::monthDifference( fromDate, toDate)));


Thank you,

Saturday, November 5, 2022

Clear value of all the controls in form, D365FO - X++

Hello,


Below code sample will help to clear value of all the controls in form. Code tested in D365FO X++.


   void clearFormControls(Object o)

    {

        FormBuildControl    formBuildControl;

        FormStringControl   formStringControl;

        FormInt64Control    formInt64Control ;

        FormIntControl      formIntControl ;

        FormRealControl     formRealControl;

        FormTimeControl     formTimeControl;

        FormCheckBoxControl formCheckBoxControl;

        FormDateControl     formDateControl;

        Date                emptyDate;

        int                 i;

        ;

        for (i=1;i<=o.controlCount(); i++)

        {

            formBuildControl = o.controlNum(i);                                       

            switch (ClassId2Name(classIdGet( element.control(formBuildControl.id()))))

            {

                case 'FormStringControl':

                    formStringControl = element.control(formBuildControl.id());

                    formStringControl.text('');

                    break;

                case 'FormIntControl':

                    formIntControl = element.control(formBuildControl.id());

                    formIntControl.value(0);

                    break;

                case 'FormInt64Control':

                    formInt64Control = element.control(formBuildControl.id());

                    formInt64Control.value(0);

                    break;

                case 'FormRealControl':

                    formRealControl = element.control(formBuildControl.id());

                    formRealControl.realValue(0);

                    break;

                case 'FormTimeControl':

                    formTimeControl = element.control(formBuildControl.id());

                    formTimeControl.value(0);

                    break;

                case 'FormCheckBoxControl':

                    formCheckBoxControl = element.control(formBuildControl.id());

                    formCheckBoxControl.value(0);

                    break;

                case 'FormDateControl':

                    formDateControl = element.control(formBuildControl.id());

                    formDateControl.dateValue(emptyDate);

                    break;

            }

            if (formBuildControl.isContainer())

                clearFormControls(formBuildControl);

        }

    }

Wednesday, October 19, 2022

How to drop SQL Server database currently in use and in Single user mode, MS SQL.

Hi All,


Working in MS SQL, you must have faced below error at least once. 


Issue: How to drop SQL Server database currently in use and in Single user mode.


Error:

"changes to the state or options of database 'AxDB' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."


Solution:

This built-in stored procedure shows all connections to a database

exec sp_who2


And this query kills a connection to the database, for example connection SPID #53

kill 53


Thank you!

Tuesday, October 18, 2022

Avoiding "Divided by zero" error, X++

Hi,


Two ways to avoid the error "Divided by Zero".


int    a,b,c;

a = 2;

b = 0;

if(b)

    c = a / b;

else

    print "Cannot divide by zero"

//Output is "Cannot divide by zero" because "b" is zero so else statement will run.


(OR)


c  = a / minOne(b); 

//Output is C = 2, reason given below.


Function minOne() returns non zero values. If "b" is zero then it returns one.


Thank you,


Tuesday, October 11, 2022

Add or Minus Days in DateTime or Date Datatype, X++

Hi,

below you will find ways to add/minus days in "DateTime" or "Date" Datatype.


Example 1: Days add/minus in "DateTime" Datatype.

{

 utcDateTime         todayLessOneDay;

// Get the actual UTCDateTime based on the current system

todaysDateTime = DateTimeUtil::utcNow();

// Convert it to a string, just to show in on the info log

info(DateTimeUtil::toStr(todaysDateTime));

// Now less a day

todayLessOneDay = DateTimeUtil::addDays(todaysDateTime, -1);

// And Info it out again

info(DateTimeUtil::toStr(todayLessOneDay));

}


Example 2: Days add/minus in "Date" Datatype.

{

  TransDate transDate = today();

  ;

  transDate++;

  print transDate - 2;

  pause;

}

Sunday, October 9, 2022

Add multiple Query Value to one Query Range - X++

 Hello,

In this post you will find a code sample to run multiple Query Value to one Query Range.


You have two options:


Option 1: Add multiple ranges.

QueryBuildDataSource qbds = q.dataSourceTable(BOMTable);

QueryBuildRange qbr;

while (...)

{

    qbr = qbds.addRange(fieldNum(BOMTable, BOMId));

    qbr.value(queryValue(BOMVersion.BOMId));

}


Option 2: Add multiple values to one range separated by comma.

QueryBuildRange qbr = q.dataSourceTable(BOMTable).addRange(fieldNum(BOMTable, BOMId));

container c;

while (...)

{

    c+= queryValue(BOMVersion.BOMId);

}

qbr.value(con2str(c));

Saturday, September 3, 2022

SSRS Date time Expression, AX/D365FO

SSRS Date Time Expressions

Date Time Functions of SSRS:

Below are Data time functions which are used in SSRS expressions:

To get current date and time according to system: 


Time:

=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy hh:mm.ss") 


Date:

=FORMAT(Cdate(today), "dd-MM-yyyy")


Today() - Returns or sets a Date value containing the current date according to your system:

=Today()


="Tomorrow is " & DateAdd("d",1,Today())

="Tomorrow is " & DateAdd(DateInterval.Day,1,Today())


Now() - Returns or sets a Date value containing the current date according to your system.

=Now()


="This time tomorrow is " & DateAdd("d",1,Now())

="This time tomorrow is " & DateAdd(DateInterval.Day,1,Now())


MonthName - Returns a String value containing the name of the specified month.

=MonthName(10,True)

=MonthName(Month(Fields!BirthDate.Value),False)

="The month of your birthday is "&MonthName(Month(Fields!BirthDate.Value))


WeekDayName - Returns a String value containing the name of the specified weekday.

=WeekdayName(2,True,0)

=WeekDayName(DatePart("w",Fields!BirthDate.Value),True,0)

=WeekDayName(DatePart(DateInterval.Weekday,Fields!BirthDate.Value),True,FirstDayOfWeek.System)


Year  - Returns an Integer value from 1 through 9999 representing the year.

=Year(Fields!BirthDate.Value)


To format Date values through expressions:

=Format(Today(),"dd-MM-yyyy") --- 23-10-2015

=Format(today(),"dd/MM/yyyy") --- 23/10/2015

=Format(today(),"MMM-dd-yyyy") --- Oct-23-2015

=Format(today(),"MMM-dd-yy") --- Oct-23-15

=FORMAT(Today(),"M/d/yy") ---10/23/15

=FORMAT(Today(),"MM-dd-yyyy") ---10-23-2015

=FORMAT(Today(),"MMM-dd-yyyy") Oct-23-2015 

=FORMAT(Today(),"MMMM dd, yyyy") ---October 23, 2015 =FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") ---Oct 23, 2015 01:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") ---Oct 23, 2015 13:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") ---Oct 23, 2015 13:43:33.587

=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") ---Oct 23, 2015 01:43:33 PM


To add/substract date or time(day, month, year, sec etc.) with given date field we can use DateADD function in SSRS expression, which returns a Date value containing a date and time value to which a specified time interval has been added:

=DateAdd(DateInterval.Month, 6, Today())

=DateAdd("d",3,Fields!BirthDate.Value)

=DateAdd(DateInterval.Day,3,Fields!BirthDate.Value)


DateDiff - function returns a Long value specifying the number of time intervals between two Date values.

=DateDiff("yyyy",Fields!BirthDate.Value,"1/1/2010")

=DateDiff(DateInterval.Year,Fields!BirthDate.Value,"1/1/2010")


DatePart - Returns an Integer value containing the specified component of a given Date value.

=DatePart("q",Fields!BirthDate.Value,0,0)

=DatePart(DateInterval.Quarter,Fields!BirthDate.Value,FirstDayOfWeek.System,FirstWeekOfYear.System)


To get first day of current Week:

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)


To get first day of current Month:

=DateAdd("d",-(Day(today)-1), Today)

=DateSerial( year(today()), month(today()), 1)

Sunday, August 21, 2022

Error: The report server isn’t configured properly, SSRS report deployment error.

Error:

The service is not available.

The report server isn’t configured properly. Contact your system administrator to resolve the issue. 

System administrators: The report server can’t connect to its database because it doesn’t have permission to do so. 


Solution:

Use Reporting Services Configuration Manager to update the report server database credentials.

This issue "The report server can’t connect to its database because it doesn’t have permission" might occur if the account that the report server uses to connect to the report server database is:

  • Disabled or Locked.
  • Its password has changed recently.
  • It doesn't have owner permission on the Report Server databases (its permission has revoked).
  • This Report Server account can be one of the below types:
    • Windows domain user account.
    • SQL Server account.
    • The same service account that used to run the Power BI Report Server service.

And, you can find the current report server account login type at

Report Server Configuration Manager > Database > current report server database credential:

Update your current credential or add admin credential.

Thursday, August 18, 2022

Row Number or Serial Number for grid - SSRS Report expression

Below expression will give you the serial number to the grid:


=RunningValue(CountDistinct("YourTableName"),Count,"YourTableName")

or

=RowNumber(Nothing)

Thursday, August 11, 2022

Error - cannot create record in DMFEntity in D365FO.

Hello,

Sometimes after developing a entity, user face "record already exist in Entity" error. This can be solved by simply deleting all records in DMFEntity table.

Once you refresh entity list. you will get all the entities back.

Either you can run the below job or just go to the DMFEntity Table and delete that one selected record which is causing the error.

There are a lot of other related error:

Severity    Code    Description    Project    File    Line    Suppression State

Error        Infolog diagnostic message: 'Error synching entity metadata for entity - KRTProductEntity. Message -    at Microsoft.Dynamics.Ax.MSIL.Interop.throwException(Int32 ExceptionValue, interpret* ip)            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityUpdate(DMFEntity dmfEntity, DictDataEntity dictDataEntity)            0    

Error        at Dynamics.AX.Application.DMFEntity.insert() in xppSource://Source/ApplicationFoundation\AxTable_DMFEntity.xpp:line 433            0    

Error        Infolog diagnostic message: 'Cannot create a record in Entity (DMFEntity). Entity: Items, KRTProductStaging.            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityUpdate(DMFEntity dmfEntity, DictDataEntity dictDataEntity, Boolean useTargetEntityName, Boolean @useTargetEntityName_IsDefaultSet) in xppSource://Source/ApplicationFoundation\AxClass_DmfDataPopulation.xpp:line 947            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityMetadata(StringCollection dataEntityViewCollection, StringCollection compositeEntityList) in xppSource://Source/ApplicationFoundation\AxClass_DmfDataPopulation.xpp:line 794.' on category 'Error'.            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityCreate(DictDataEntity dictDataEntity, Boolean useTargetEntityName, Boolean @useTargetEntityName_IsDefaultSet) in xppSource://Source/ApplicationFoundation\AxClass_DmfDataPopulation.xpp:line 899            0    

Error        at Dynamics.AX.Application.DmfDataPopulation.syncEntityCreate(DictDataEntity dictDataEntity, Boolean useTargetEntityName)            0    

Error        at Microsoft.Dynamics.Ax.Xpp.Common.Insert()            0    

Error        at Microsoft.Dynamics.Ax.MSIL.cqlCursorIL.insert(IntPtr table)            0    

Error        at Microsoft.Dynamics.Ax.Xpp.NativeCommonImplementation.Insert()            0    

Error        Database synchronization failed. You may have to do a full build of the package 'K3Retail' and all of its dependent packages.            0    

Error        The record already exists.' on category 'Error'.            0    


JOB Sample:

class DeleteDMFEntity

{        

    public static void main(Args _args)

    {      

        DMFEntity dmfEntity;

        DMFTargetXML dmfTargetXML;

 

         //ttsbegin;

        delete_from dmfEntity;

        delete_from dmfTargetXML;

 

         /*while select forupdate dmfEntity

        {

            dmfEntity.delete();

        }*/

        //ttscommit;

        info('Done');

    }

}

Thursday, August 4, 2022

Get invent onHand as per site/warehouse or InventDimId itself, using x++ code.

Hello,

This post will help you to get invent onHand as per site/warehouse or InventDimId itself.


InventDim inventDim;

InventDimParm inventDimParm;

InventOnHand inventOnHand;

ItemId itemId;

InventDimId inventDimId;


itemId  = "XYZ";

inventDim.InventSiteId = "XYZ";

inventDim.InventLocationId = "XYZ";

inventDimParm.initFromInventDim(inventDim);

inventOnHand = InventOnhand::newParameters(itemId, inventDim, inventDimParm);

inventOnHand.availPhysical();

//or 

inventDimId = "XYZ";

inventDim = InventDim::find(inventDimId); //you can use as SalesLine.InventDimId

inventDimParm.initFromInventDim(inventDim);

inventOnHand = InventOnhand::newParameters(itemId, inventDim, inventDimParm);

inventOnHand.availPhysical();


Happy Daxing!

Wednesday, August 3, 2022

Department for the current user in X++ select statement

Hi All,

Below you will find a snippet to get department for the current user.


select OMOperatingUnit 

            join HcmPositionDetail

                where OMOperatingUnit.RecId == HcmPositionDetail.Department

            join HcmPosition 

                where HcmPositionDetail.Position == HcmPosition.RecId

            join HcmPositionWorkerAssignment

                where HcmPosition.RecId == HcmPositionWorkerAssignment.Position

            join HcmWorker 

                where HcmPositionWorkerAssignment.Worker == HcmWorker.RecId

            join DirPerson 

                where HcmWorker.Person == DirPerson.RecId

            join DirPersonUser 

                where DirPersonUser.PersonParty == DirPerson.RecId 

                && DirPersonUser.User == curUserId();

How to execute SQL directly form Dynamics AX X++

How to execute Sql directly form Dynamics AX X++ Reference by : alirazazaidi Dynamics Ax provide many other ways to communicate with databas...