-->

AX 2012 X++: Add N working days to a date

maqk® by MAQ Khan | 5:04 PM

AX 2012 X++: Add N working days to a date

This post provides a few tips, specially focusing the working calendar and working days


Audience: Dynamics AX 2012 (FP, R2 R3) X++ Developers, Functional and Technical consultants and advanced users


Date operations are every day common tasks for any developer working on a business application. Any good programming language, platform or framework would provide well designed library(ies) with sufficient functionality abstracted in various helper classes and methods to achieve the common and extended date operations. After all, what we want to achieve is re-usability and not reinventing the wheel every now and then.

AX also has alot of such reusable method to save time and keep consistency. A few are listed below;


  • DateTimeUtil: A date time manipulation utility class. Some of its methods are referenced below too
  • DateTimeUtil::getSystemDateTime(): Works like a Now() method
  • DateTimeUtil::addMonths(): Add n months to the provided date
  • dayOfMth(date): Calculates the number of the day in the month for the specified date.
  • mkDate(int month, day, year)Creates a date based on three integers, which indicate the day, month, and year, respectively.
  • prevYr(date) - Retrieves the date in the previous year that corresponds most closely to the specified date. Similar functions include prevMth() and prevQtr()
  • dateMthFwd(date, int qty): Adds the specified number of months to a date.

These all methods are so useful and handy while there are much more to mention. See links section for more.

We are more interested in the working calendar. I recently got a scenario in which I had to validate the date from calendar. Then I had to decide the ending date (the famous 'To' date). The resulting date must be a valid date on a calendar. So should I write some algo for it. Experience says such a thing should be available in the library / framework somewhere. Yes this is what is to be shared with all.


WorkCalendarSched.schedDate Method

This sweet method in short takes from date, no of days to add, scheduling direction, calendar id to process against and calendar days flag to schedule the resulting date, awesome. Exactly what I was looking for. Hence using this method, we can add N working days (excluding holidays, weekEnds, etc) to any date. The resulting date is guaranteed to be a working date. Just keep your calendar tight and updated

Code sample


static void job_testSchedDate(Args _args)
{
    WorkCalendarSched       workCalendarSched;
    SchedDate               schedDateFrom, resultantTODate;
    Days                    noOfDays;
    CalendarId              primCalendar, secCalendar;

    workCalendarSched = new workCalendarSched();

    noOfDays            = 30;     
    primCalendar        = "Default";    secCalendar         = "Default";
    schedDateFrom       = str2Date("21.6.2016", 123);    // dd.mm.yyyy

    info(strfmt("From date: %1", 
        date2str(schedDateFrom, 123, DateDay::Digits2, DateSeparator::Slash, DateMonth::Short, DateSeparator::Slash, DateYear::Digits4)    )); 
    // sequence (int) 123 means 1=dd 2=mm 3=yyyy
    
    info(strFmt("Adding %1 days [Working days]", noOfDays)); 
    resultantTODate = workCalendarSched.schedDate(  SchedDirection::Forward,    // Selecting backward will minus the noOfDays
                                                    schedDateFrom,  
                                                    noOfDays,                   // number of days to add
                                                    NoYes::Yes,                 // Yes mean bring WorkDays (exclude weekEnds and other configured holidays in between)
                                                    primCalendar, secCalendar); // which calendar to be used for date calculation and verification
     info(strfmt("Resultant TO date: %1", 
        date2str(resultantTODate, 123, DateDay::Digits2, DateSeparator::Slash, DateMonth::Short, DateSeparator::Slash, DateYear::Digits4)    ));  
}


Output is shared as follows


 Links

ProjUnpostedTransView-Field WorkerName is not compatible with field WorkerName

Subject


This post provides insight about a synchronize error received during full AOT for AOT view 'ProjUnpostedTransView' and its resolution.

Error

ProjUnpostedTransView: Synchronize database There is a field mismatch in the union query. Field WorkerName is not compatible with field WorkerName.

Audience:  Dynamics AX 2012 (FP, R2 R3) X++ Developers, Functional and Technical consultants and advanced users

Background

We have been facing this issue since day one of our installation of AX 2012 R3 (RTM). Build numbers are as follows;

Microsoft Dynamics AX 2012 R3 (RTM)
Kernel version: 6.3.164.0
Application version: 6.3.164.0

Problem

When synchronizing the full AOT, the following errors come






followed by...

Solution(s)

As per mentioned in the synchronize database error (1), all the errors have come in the View, 'ProjUnpostedTransView'. Exploring the same yields the composition of the view on following sub views.


  1. ProjLedgerJournalTransUnpostedView
  2. TrvExpTransDistributionsUnposted
  3. ProjJournalTransFeeUnpostedView
  4. projCustInvoiceLineDistsUnpostedView
  5. ProjJournalTransHourUnpostedView
  6. ProjProdJournalRouteUnpostedView
  7. InventJournalTransUnpostedView
  8. ProjProdJournalBOMUnpostedView
  9. VendInvoiceInfoLineDistributionsUnposted
  10. ProjOnAccTransUnpostedView
  11. ProjIntercompanyUnpostedExpView
  12. TSUnpostedTimesheetLinesAllView
  13. ProjIntercompanyUnpostedTSView
  14. ProjIntercompanyUnpostedVendInvLineView
Data sources (Views) with mismatching EDTs in their field, WorkerName




Each of these views comprise of one or more tables. And each of these views as a data source has the HCMWorker data source with in further relating to DirPartyTable to fetch the worker name. Closely monitoring each of them reveals that the following four data sources were returning null for the worker name. Obviously the logic implemented would have required the field (since a union query) to have a consistent schema returned. Further to returning null, the fields were provided the extended type, "Name" which was different from what is returned from DirPartyTable Name field which extends "DirPartyName" extended type.  

Data sources with inconsistent extended types

  1. projCustInvoiceLineDistsUnpostedView
  2. ProjProdJournalBOMUnpostedView
  3. ProjInventJournalTransUnpostedView
  4. ProjOnAccTransUnpostedView

Due to this EDT mismatch, the AOT framework was giving errors.

Providing the correct extended type of DirPartyName to the WorkerName fields for each of these views made the schema fully consistent, hence error removed.

NOTE: These corrections are to be made in each view used as a datsource and in the field section

I am unable to find if its a R3 RTM original issue or what, cause i have not been able to find any corresponding hotfix. If any of the readers have any idea, kindly share it with me.

Thanks :)

AX 2012: FormRun.createRecord Method with example

maqk® by MAQ Khan | 10:24 AM

AX 2012: FormRun.createRecord Method with example

This post provides a tutorial about the FormRun.createRecord Method which is override able at form level for custom implementation

AudienceDynamics AX 2012 (FP, R2 R3) X++ Developers, Functional and Technical consultants and advanced users

Background

Recently I came through a scenario where I had to use a base table (supporting inheritance) as a Form data source. I needed to ensure which child table's instance is being created. Due to not being totally aware of the table inheritance implementation (and sorry for Microsoft documentation, such things are not specified anywhere) I was relying on my intelligent (or semi intelligent perhaps :D ) guesses. 

Question: Where is the InstanceRelationType field set to identify the record belonging to a child table.

Thanks to Nasheet Siddiqui | blog a close friend of mine, who guided me in the right direction. As per him, such a thing should be decided by the system at record creation method. And that is the FormRun class method: createRecord().

Example

One of the example is the PurchAgreement form, where the system decides at runtime if the agreement line is quantity based or volume based (depending on Comitment type field inrespective helper class instance). This is implemented in PurchAgreementr Form's CreateRecord method. 

createRecord() implementation at PurchAgreement form

There they have used the FormDataSource's method createType to specify exactly which child table is to be instantiated. Evaluating the commitment type member, a map is instantiated with a value pair of data source name and child table id are set. The map is then sent as parameter to 
FormDataSource > createTypes() method which takes a map in params. This method internally decides the fate of the record i.e which child table the record will belong too. Setting the instancerelationtype members are probably internal to this method.

For even better understanding of concepts, I am attaching this valuable excerpt

An excerpt from Inside Dynamics AX 2012

Traditionally, to create a record in X++ code you only had to call the FormDataSource.create method. However, that method does not let you specify the type. To support the polymorphic creation scenario, use the following method: FormRun.createRecord(str _formDataSourceName [, boolean _append = false]) All create actions performed by the kernel are routed through this method. You should use this method as well instead of the create method. The first parameter specifies the name of the form data source in which to create the record, and the second parameter contains the same append value that is passed to the create method. You can override this to put in conditional code that depends on the type being created. The call to the super of the method executes the correct logic depending on the type.

Links

SQL Server 2012: Rename a database with datafiles

maqk® by MAQ Khan | 4:12 PM

SQL Server 2012: Rename a database with data files (MDF, LDF)

This post provides the scripts that will rename a database along with its data files.

Audience

DB Admins, T-SQL developers, Technical Consultants, AX Developers

Background

In so many scenarios, you may need to rename a database. One general scenario in AX implementation would be when maintaining multiple AX environments at one server like Staging, TEST, DEV and Production. This is also very likely in development and RnD scenarios where you keep restoring LIVE instances backups on a central backstage HUB for data availability.

Problem

For hosting multiple and different AX servers on a single machine for local availability, you need to have multiple AX server databases hosted with different names. But for that, you need to rename the database since each backup may result in the exact same database name. But you face the problem as you already have a database with exactly same name, 'MicrosoftDynamicsAX' attached to your local server. This can cause problems and you may start thinking how to restore both databases with different names cleanly without disturbing the existing database.


Solution(s)

Properly renaming existing database(s) would let you restore any database coming under the name of MicrosoftDynamicsAX. How I have resolved this issue at my local server is, I rename the existing MicrosoftDynamicsAX database (along with data file names, yes, renaming data files :) ) and the restoring the new database under the same MicrosoftDynamicsAX (or whatever) name. To restore more such instances, I can always rename databases as I like. This I achieve using the attached script. It lets me achieve the following in one go

Script process
  • Set desired database to single user mode
  • Rename database to new provided name
  • Takes the database (with new name) to offline mode
  • Suspends further execution for 5 minutes 
    • This is done so that the user is expected to rename the files manually at physical file level.
  • Renames the data files logically
  • Brings the database back to online
  • Sets the database again to multi user mode.


The script is at the moment hard coded since I was not able to provide variables to a few alter database commands due to command's nature. For this reason, the script is not plug and playable as I had to use symbols to specify the replacing database names. when using, carefully replace sample values with yours and test and master execution on test databases :)

This mess would be resolved by dynamically generating SQL and providing user provided database names and other variables to SQL. Therefore as improvement, I am writing a small utility that would enable user to select a database, provide its new name and click and all is done automatically. So stay tuned.

Script can be downloaded from the link: Database renaming script


top