How to execute Date Time range in X++ query

Here is the example for datetime range in Query. We are trying to achive as on date data here based on AOT and an additional range of From Date. This will help to understand the query base. 

 Query     query = new query(querystr(#AOTQueryName)); // Getting Query From AOT , 

 QueryBuildDataSource qbds = query.dataSourceTable(tablenum(#TableName)); // Adding More DB

 queryBuildRange            qbr = qbds.addRange(fieldNum(#TableName,#FieldValidFrom)); // Add Range 

qbr.value(strFmt('(%1.%2 <= DateTimeUtil::date("%3"))',tableStr(HcmEmployment),fieldStr(HcmEmployment, ValidFrom),asonDate)); // Passing Value 

Database Sync error in D365-F&O with ISV Solution

Recently I faced an issue where the Data Base synchronization for D365 F&O was throwing the error where as all the Build was successful even after move all the code from another environment. 

Here I would to mention one important thing which I was completely ignoring  was "We were using ISV solution from another party ".

Error - 

Although it was a long error log but from it you could find the below main part which will try to expose error detail. 

An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch

Solution - 

As I mentioned we were using ISV solution so it was the only reason for this Sync Failed. I had to follow the below process to make a successful Sync.

1- Go to the Local directory location "K:\AosService\PackagesLocalDirectory"

2- Got to the package folder in directory ""K:\AosService\PackagesLocalDirectory\###Package (ISV Package)"

3- Find the DLL file here .

4- Right click and open "Properties" 

5- here you will find one Unlock check box. we need to check this box and run the Sync for DB again. 

Hope this will Work for you.... :) 

 

 

Admin provisioning tool can't stop DynamicsAXBatch service

 Admin User Provisioning Tool Error:

1. If the Admin User Provisioning Tool gives error that can’t stop DynamicsAxBatch.
2. Go to the Services,
3. Find out the service with the name: Microsoft Dynamics 365 Unified Operations: Batch Management Service
4. Stop it.
5. Now register with the provisioning tool
6. If you are unable to stop the DynamicsAxBatch Service. Then do the following steps in it:

• Click the Start menu
• Click Run or in the search bar type services.msc
• Press Enter
• Look for the service and check the Properties and identify its service name. Service name would be DynamicsAxBatch
• Once found, open a command prompt. Type: sc queryex [servicename].
• Press Enter
• Identify the PID
• In the same command prompt type: taskkill /f /pid [pid number]
• Press Enter

For reference, see the following picture:

Now start the service and and register yourself in the Admin Provisioning Tool.

D365 F&O call ACTIVE Method through extension

As we can't modify any standard method in D365 F&O , so we need to pass all of it's component in Code Of Command (COC). Below is the syntex for  "Active" Methos in vend Table form where we are enabling a field based on #Checkbox selection. Hope it'll help ... At least me in Future 😆 

[ExtensionOf(formdatasourcestr(VendTable, VendTable))] // Extension of Standard Obejct

final class VendTable_Extension

{

  

    public int Active()

    {

        int             ret;

        VendTable       vendTable           =  element.Vendtable; // Direct Table buffet for value 

        FormDataSource  vendTable_ds        = element.vendtable_ds; // Data Source Buffer

        ret = next Active(); // Calling standard Active Logic

    // adding Addition logic to check 

        vendTable_ds.object(fieldNum(VendTable, #FieldName)).enabled(VendTable.#CheckBox == NoYes::Yes);

        return ret;

        vendTable_ds.research(true); // This is useful to stop record refresh in Active 

    }

}

Make any field mandatory through user interface. No development required

Dear All

In D365 now you can make any field mandatory through user interface. No customization required for that. Please note that as it's possible through personalization so the field will be mandatory  for the selected user only although it'll create a form view version which can be publish for all and other user can also use the same. 

This is a very common one user miss some really important information in to the form every time this user can make field mandatory for themselves as well as can share his personalization with other users also. 

Here we go .

To use this feature, from feature management "Designate fields as required using personalization" feature needs to be enabled from - System Administration - Feature management

for more information about feature management, follow https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/feature-management/feature-management-overview . 

After enabling the above feature, user can have the privilege to use the nice functionality to make ANY field mandatory, lets go through.

We will see how a field can be made mandatory while creating customer (example Sales Tax Group to be mandatory while creating customer)

Step 1: Determine which field needs to be mandatory to fill in and right click on that field (sales tax group in our case).

Step 2: Click "Personalize" 

Step 3: "Personalize" form will open and enable "Required" field.

Step 4: Now Sales Tax group will be mandatory and can not be created record unless the same is filled in.

Microsoft has introduced this nice feature in 10.0.11 version as a preview and this will be available on next releases for general availability.

Note that this feature is dependent on the saved views feature.

I hope this feature will add value to you and if you have any concern kindly connect in or comment on the comment box

Unable to install AX 2012 R3 Reporting Extensions. AOS cannot be reached.

The AOS '<AOS Server>@2712' cannot be reached. Setup cannot continue. Verify that you entered the correct server information and that the AOS service is running.

We can face this error during the installation of when we try to install the components which need the AOS permission like Reporting services IIS Web Config Analysis services.

if this occurs then we must check the service account in AX under system administration. That account should be the same for Services and domain should be the same.  

The internal time zone version number stored in the database is higher than the version supported by the kernel (9/7). Use a newer Microsoft Dynamics AX kernel.

Windows could not start the Microsoft Dynamics Ax Object Server on Local Computer, refer to service- specific error code 100.

The above error is not sufficient to tell us what the root cause of the failure is. Please check the event viewer log for the exact error.

Error Message found was Event viewer:

Object Server 01:  Fatal SQL condition during login. Error message: “The internal time zone version number stored in the database is higher than the version supported by the kernel (6/4). Use a newer Microsoft Dynamics AX kernel.”

This is because we have two different kernel versions between Development and the test environment. So, workaround to resolve this is:

We need to update the SYSTIMEZONESVERSION Parmater in SQLSystemVariables table with the right value. In this case, expected is 4 whereas actual is 6.

SQLSystemVariables is available in Application database.

USE [ApplicationDB] SELECT * FROM SQLSYSTEMVARIABLES where parm =’SYSTIMEZONESVERSION’; –Result is 6 Update SQLSYSTEMVARIABLES set value=4 where parm=’SYSTIMEZONESVERSION’;–update it to 4

Once updated, we were able to turn on the AOS. Eventually, the right solution would be to make the environment in sync.

Get selected records in a grid on a form AX - X++

To get selected record(s) in a grid in AX, we use the MultiSelectionHelper class. To achieve that, follow the steps below:

1.  Create a command button on the form and override the clicked method.

2.  Insert the code below:

 

void clicked()

{

    MultiSelectionHelper          selectionHelper = MultiSelectionHelper::construct();//Create instance of class

    Set                           selectedRecords = new Set(Types::Record);//Declare a set of type record

    MyLinesTable                  tableLines; //Your table buffer

    super();

 

    selectionHelper.parmDataSource(MyLinesTable_DS); //Set the datasource

    tableLines  = selectionHelper.getFirst(); //assign to table buffer the reference to selected record(s)

 

    if (tableLines.RecId)

    {

        while (tableLines)

        {

            selectedRecords.add(tableLines);

            info(strFmt('Selected record.. %1',tableLines.myField));//Display selected record

            tableLines = selectionHelper.getNext();

        }

    }

}

 

3. Select record(s) on the grid then click on the command button to view the selected records.

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports (Part I)

Header/Footer:
There is only one header, one body and one footer available per report. It is no longer possible to use a second header or footer (like Prolog/Epilog or Programmable Section) in a report.

This means it is no longer possible to use a secondary footer for an invoice summary with single positions one below the other. To make it worse: Hiding header or footer on a page only means that the content of the header and footer is not displayed. But: The space which would be consumed by the non-hidden header/footer (by setting the height) cannot be used in report and is displayed as a white space in the report.

If header/footer is only displayed on the first or last page of report the size should be set as small as possible to prevent waste of space on the pages where header/footer are not displayed.
Note: Behavior of header/footer vary between running report in Visual Studio or in Dynamics AX 2012 because in Visual Studio a hidden header/footer by Property “Show on first/last page” is not displayed at all and wastes no space.

Missing property “LabelTabLeader” (for automatic setting of colons after labels):
The property “LabelTabLeader” was an AX-standard till Dynamics AX 2009 to set dots and colons automatically after a label like this: “….:”. In SSRS reporting engine for Dynamics AX 2012 this property is no longer available. Even "better": According to Best Practices for designing SSRS reports colons are no longer officially supported.

Workaround suggested by Microsoft Support: Set colons manually with an expression like =":" after every label. Problem: Every default report has to be changed.

We asked for a change request at Microsoft’s SSRS/AX 2012-Team, because we do not have the time to edit every used default report manually. According to Microsoft support re-implementing the “LabelTabLeader”-property is not a simple job and still discussed at Microsoft.

Setting an element in a fixed position at the bottom of the report:
As mentioned above: There is only one report-footer available and because of technical restrictions you cannot size it as big as sometimes needed for example for an invoice summary where the single positions are one below the other. Common requirement is that the invoice summary is based on the bottom of the summary independently of the used space above the summary.

With Dynamics AX 2009 it was rather simple to achieve this requirement: You just used a programmable section only on the last page.

Because there is no second footer available we tried several other things to fix the sales summary at the bottom of the invoice-report but outside the footer. But we always failed.

Microsoft Support confirmed that there is no option in SSRS reporting engine available to fix an element on a specific space on report if it is not possible to place the element in header or footer.
This issue is also topic of a change request, which is also still discussed at Microsoft.

Paper Size of default Dynamics AX 2012 reports: 
According to Microsoft Best Practices an AX 2012 report has to be designed in a way which allows the report to be printed in US letter and also in DIN A4.

Unfortunately most of the default reports are designed in a way which uses US letter paper size in its complete width without proper margins. Because the width of DIN A4 is 210mm and the width of US letter is 216mm a lot of white pages are printed in DIN A4.

This means that a lot of the default reports have to be redesigned to be used proper by users outside USA.

Update 06.02.2012:
Starr informed me that my statement, that all the reports are optimized for US Letter is wrong. He checked some more reports which were not optimized for DIN A4 or US Letter at all. Indeed he is right:

I have checked some of more reports and some paper-sizes I discovered were not neither US Letter nor DIN A4. It seems that when many columns needed to be displayed width was set to fit all columns even the report was not really printable anymore.

He also asked what we do to make the reports printable: Our solution is simple but also time-intensive: Together with our client we identified the most important reports which need to be printed and all these reports will be redesigned. Not because of the wring paper-sizes but also because of the “beautiful” design of the standard-reports.

This was the first part of “What’s no longer possible in AX-reports”. Part 2 will follow soon and there are still some more nasty restrictions of SSRS for Dynamics AX 2012 which were unknown in Morph X-reports for Dynamics AX 2009 and prior.

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports (Part II)

This is the second part of “What’s no longer possible in AX-reports”. After finishing writing the first part I thought, that I just present some nasty, but not so important restrictions. Unfortunately Microsoft support just confirmed a restriction which could affect report design in a very bad way:

254 column limit in SalesInvoiceTmp (maybe every temporary table filled by a RDP class):
Some days before Christmas I was still designing Sales Invoice-report: After adding 20 new columns to SalesInvoiceTmp-table and implementing these columns in Sales Invoice-report no report-data were shown in the report any longer.

Only label-data were displayed in the report. The behavior was the same when running report in Visual Studio or direct in Dynamics AX. First I thought that this behavior was caused by a programming-mistake by me but I found no reason why X++-code should have caused this. Then I checked SalesInvoiceTmp-table while running the report. SalesInvoiceTmp-table was filled correctly and all needed data were available. Because I had no clue what may have caused this I deleted the before added 20 new columns. Result: Sales Invoice-report worked correctly.

I started adding the columns one by one and after reaching 255 columns in SalesInvoiceTmp-table report just showed label-data again. After deleting one column report just worked fine.

I opened a support case at Microsoft support and just yesterday support confirmed that this behavior is caused by SalesInvoiceTmp-table with more than 254 columns. Support directly opened a change request because this restriction seems to be by design.

If one asks why I need more than 254 columns in SalesInvoiceTmp: Default SalesInvoiceTmp has about 200 columns when delivered with Dynamics AX, because there is just one dataset for header- and detail-data. In the end a programmer just can add approximately 50 columns for customization. 50 columns sound much, but if a client has just some special wishes to be implemented in Sales Invoice-report you may need all of these 50 columns and maybe some more.

SalesInvoiceTmp-table & default AOS settings:
Default maximum buffer-size in AOS settings is 24KB. Recommendation by Microsoft is not to raise this size. When starting customizing Sales Invoice report I had to add start some columns. But after adding some columns with an overall size below 1000 Bytes an error-message appeared, that maximum size of SalesInvoiceTmp-table was reached and columns need to
be deleted.

The answer of Microsoft was fast and simple: Raise maximum buffer-size in AOS settings to 48 KB. This is the only solution to solve this problem and the only possible solution when additional columns need to be added. Because SalesInvoiceTmp-table with about 200 columns by default is very big and so it reaches the 24KB limit very fast.

Till now we found no negative impacts of raising the buffer-size. But according to Microsoft support another raise above 48KB may cause problems.

So the big question is: Why is Sales Invoice report just using SalesInvoiceTmp-table as its only dataset if this solution causes that much trouble? The question gets even more interesting when you check the other reports: Most of them have two datasets. One for header-data and another one for detail –data.
It took me some time to discover the reason, which is simple but also shows another problem of SSRS reporting engine:

Paper-size limits space for report-elements:
When starting redesigning Sales Invoice report I have just deleted the default report because it had a very ugly layout. I was setting paper-size to DIN A4 paper which has a height of 297 millimeters. When starting design there was plenty of space but later in the design-process and after adding more and more design-elements (which were printed one by another as unique elements printed only on the first page, continuing with reoccurring elements for invoice details in the middle section of the report and ending with unique elements only printed on last page) space went short.

So I reexamined the default Sales Invoice report. The solution used in default report is simple: One big table with only one column is used as a container-element. The table has some rows. In every row one report-element is embedded, mostly tables. With this solution it is also possible to print the elements one by another.

The reason why a table is used as container-element is that the table which is used as container element can be bigger than the paper size. When printing the report the paper size which was set in the report properties is used correctly and report-layout is not negatively affected by not wanted page breaks.

The downside of this solution is also the explanation why only one dataset is used: You have to assign a dataset to the table used as container element. But for the elements inside this table you cannot assign other datasets than that one used in the container element table.

This is the reason why Sales Invoice report just as one dataset. To my mind this is a very bad limitation, which makes report designers work a lot of harder.

Enough for today. It seems that a third part of “What’s no longer possible in AX-reports” is needed. 

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports (Part III)

This is the third and last part of “What’s no longer possible in AX-reports”. Let’s start with some updates of topics I presented in Part I & II.

Update: Setting an element in a fixed position at the bottom of the report
As mentioned in Part I this bug was topic of a change request. Some days ago we became answer by Microsoft support. The change request was rejected. Reason: It’s a known issue of Reporting Services.

Update: 254 column limit in SalesInvoiceTmp (maybe every temporary table filled by a RDP class)
This bug seems to be a bigger one. The bug is confirmed. But why it occurs is still subject of an investigation by Microsoft support-technicians. Here it seems we will have a good chance that this bug will be fixed when it is finally found.
And here a new one:

Differences of displayed data in SSRS Reports in Visual Studio and Dynamics AX
I mentioned in a tweet before. But I think it is too important. So I raise this topic again. Data displayed in a SSRS report started in Visual Studio und a SSRS report started from Dynamics AX 2012 client may show different data.

The differences could be:
– numbers of rows displayed
– (sub)sets of rows displayed

Reasons are that on the one hand Visual Studio does not use the Reporting Engine of Reporting Services but its own. On the other hand each report, which is called via Dynamics AX 2012 client, gets its own unique id, which allows Dynamics AX to identify different reports which were called at the same time / while another report still was processed. This is of significant importance to show only the own data in a report and not data of different reports. If a report is started inside Visual Studio this unique id is ignored and datasets of different reports may be displayed in one report.

Conclusion:
Report Services itself are well known and part of SQL Server since version 2000 (as add on) and on a regular basis since version 2005. Of cause there were some issues when using Reporting Services for typical SQL Server tasks. But in general most time it was fun working with Reporting Services and SQL Server as main data source.

Working with Reporting Services and Dynamics AX 2012 one the other hand could be a little bit (ore some bit more) frustrating. Why? Quite simple! The field of operation is a different one: E.g. Designing a sales invoice with fixed positions of report elements was never subject of my work when working directly with SQL Server data but it is a common need when designing an invoice-template for Dynamics AX.

And that is exactly problem: The “old” Dynamics AX MorphX reporting engine was designed to fit this and most of the other typical Dynamics AX tasks like generation Invoices or other bills and receipts.

SQL Server Reporting Services (as delivered together with SQL Server) is designed to fit most of typical tasks like processing and displaying mass data in a clear and structured way.

Reporting Services delivered with Dynamics AX 2012 is still (let’s say over 80 percent) SQL Server Reporting Services. Lots of the old MorphX report features are missing. In the end this might cause some extra work when building a report which tries to fit the Dynamics AX users’ needs when generating an SSRS-report in Dynamics AX 2012.

My hope is that Microsoft realizes that for many common tasks in Dynamics AX 2012 Reporting Services in its current version is not adequate and a massive increase of functionality and different, more accurate behavior (e.g. when placing report elements) is needed.

AX 2012 Compile Application using axbuild command

he new way to compile AX application through command prompt is introduced in AX 2012 CU7. This is much faster than the traditional way of compiling. It takes approx.. 1-2 hours to compile depends on your server

1. Run command prompt by Run As Administrator
2. go to  C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\
3. Use command to run compilation. axbuild.exe  xppcompileall /s=01 /altbin="C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin"

Notes: -

If you are facing any issue running the command ie: App can't run in this system or Window coming and close immediately then just copy and paste AXBuild.exe files from any working environment.

Microsoft Dynamics AX Inventory Tables & Classes Details

The inventDim table is table that holds the combinations of inventory attributes assigned to items at they progress through inventory transactions. For example, an item can be located in many different warehouse, at may different sites, and stored in certain locations. Imagine the number of combinations that can be associated with an items its endless.  Below are the inventory dimensions AX uses to segregate items as they process through the system

InventColorId Item table contains information about colors.
InventSizeId Item table contains information about item size.
ConfigId  table contains information about available configuration of the various items.
InventBatchId table contains information about batches.
InventSerialId table contains information about item serial numbers.
InventLocationId table contains information about warehouses.
InventSiteId table contains information about sites.
wmsLocationId table contains information about locations.
wmsPalletId table contains information about pallets.

The inventTrans table is what I like to call the parent table of all transactions. NO matter what is performed a record will be written to this table when posting occurs. The inventTrans table represents a flow of inventory transactions as data leaves and enters a company for example. If a product is ordered a status field will be set to ordered. If a item is transferred from one warehouse to another warehouse the status will then be set to transfer. The inventory transactions table is typically used for very detailed inventory reports.

InventSum classes are used to find the on-hand information of a certain item at a certain date. The InventOnHand class is used to find the current on-hand information.

InventMovement class validates and prepares data that will be used to generate inventory transactions. All the classes related to the inventMovement class will be prefixed with InvMov

For instance the InvMove_Transfer preps data for inventory transfer journals

The InventUpdate classes are used to insert and update inventory transactions. Whenever a transaction should be posted, the updateNow()method in the correct InventUpdate subclass will execute. The super class in the hierarchy is the InventUpdate class, and the other classes in the hierarchy are prefixed ‘InventUpd_’. For example, InventUpd_Estimated is used whenever an item line is entered in the system that will most likely generate a physical transaction in the future. A good example can be a sales order line that has the on-order sales status. When a line like this is entered or generated in AX, the InventUpd_Estimated is triggered so that the inventory transactions will reflect that the item is on-order can is taken into consideration when the same item is ordered.