Update admin user in Dev box

Old is gold,

MS removed admin user provising tool, but there is an old way where you can still add your user as admin, 

Go to the VM and open SQL management Studio, 

Step 1: Find admin user rec id in USERINFO table, 

select  * from USERINFO

Step 2: Get SID from earlier Database for your user

select  * from USERINFO

where RECID = 53755554576

Step 3: Update new Database with below query 

update USERINFO

set NETWORKALIAS = 'deepak.agarwal@theaxapta.com',

sid = 'S-1-19-18-####-####-####5192'

where RECID = 53755554576

Make sur eof choosing right Database of each query and use the right recid and other values. 

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

QuickFix: Stop script for service model: AOSService on machine:

If you are getting errors while upgrading your tier-1 environment, trust me you are not alone, many of us facing this error say, 

Error 1: 

Stop script for service model: AOSService on machine

If you remember this is the same issue we had with the earlier version, and to fix this you have to update the VC++ runtime. You can download this from the below link and run it on our tier-1 environment (s).

New VC++ runtime requirement for cloud-hosted environments and VHD images running version 10.0.36 or later

Error 2:

GlobalUpdate script for service model: RetailServer on machine:(VM Name)

Let's understand why, (Or let's see what the fix is :) )

Whenever you Apply any update from LCS, it will create a package on VM local disk normally at the below location 

ServiceVolume\DeployablePackages  and you have to select the most recent folder (in case you already tried a few times with this error).

Navigate to RetailServer\Scripts and find the file name 'DropAllRetialChannelDbObjects'. 

Here is the entire path in case you need it (ServiceVolume\DeployablePackages\RetailServer\Scripts\DropAllRetialChannelDbObjects), run this SQL query on AXDB on VM and try again to apply the update. 

Tip: This SQL script sometimes takes hours and hours to finish, you can try to stop this execution and select one query at a time from the script. 

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

How to get tableId of tables in D365FO

Hi Folks,

Now when we don't have the leverage of Jobs any more and if you need quick result when you are dealing with multiple tables, try below to get table Id

Option 1: D365FO browser

http://<Environment link>/?cmp=<LegalEntity>&mi=SysTableBrowser&TableName=SysTableIdView

Option 2: SQL server management studio

Run below query

select * from SysTableIdView

Of course, you can filter your queries based on your requirements. 

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

How to enable maintenance mode

Hi Folks,

Now when most of the project management stuff needs to handle via LCS for D365FO project implementation. In each cloud-hosted environment, we use to have the option to enable maintenance mode for an environment. But for DevTest/Demo environments we don’t have that option on LCS. So, here are some steps to enable maintenance mode,

1. Open SQL server management studio (SSMS) in the dev box.

2. Go to AXDB and use the below queries as per your requirement,

a. To check current state

select Value from sqlsystemvariables a where a.PARM = 'configurationmode'

b. To enable maintenance mode

update sqlsystemvariables set VALUE = 1 where PARM = 'configurationmode'

c.  Now you need to restart word wide web service. Sometimes it doesn't work and you need to just restart this service for more than once.

d. Once you done with your activities run below query to disable maintenance mode

update sqlsystemvariables set VALUE = 0 where PARM = 'configurationmode'

3. Whenever you made a change to it.

Cheers!!!

Harry

Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

This post is referenced from MS Docs.

[LogicApps/Flow] Odata operators for Odata query

[Updated on September 8, 2020]

Hi Folks,

While working on Logic apps and Flow we need to apply a different filter on Odata query to get the desired data from an Odata entity. Below are the most used operators to use in an Odata query with example. 

The operators supported in the expression language are shown in the following table.

Operator	Description	Example
Logical Operators
Eq	Equal	/Suppliers?$filter=Address/City eq 'Redmond'
Ne	Not equal	/Suppliers?$filter=Address/City ne 'London'
Gt	Greater than	/Products?$filter=Price gt 20
Ge	Greater than or equal	/Products?$filter=Price ge 10
Lt	Less than	/Products?$filter=Price lt 20
Le	Less than or equal	/Products?$filter=Price le 100
And	Logical and	/Products?$filter=Price le 200 and Price gt 3.5
Or	Logical or	/Products?$filter=Price le 3.5 or Price gt 200
Not	Logical negation	/Products?$filter=not endswith(Description,'milk')
Arithmetic Operators
Add	Addition	/Products?$filter=Price add 5 gt 10
Sub	Subtraction	/Products?$filter=Price sub 5 gt 10
Mul	Multiplication	/Products?$filter=Price mul 2 gt 2000
Div	Division	/Products?$filter=Price div 2 gt 4
Mod	Modulo	/Products?$filter=Price mod 2 eq 0
Grouping Operators
( )	Precedence grouping	/Products?$filter=(Price sub 5) gt 10

For details refer below link 

Open Data Protocol (OData)

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

This post is referring to https://www.odata.org/.

Give method name on a range of AOT Query instead of value

Hi Guys,

Here is a quick tip, how to add methods in an AOT query ranges instead of values. Refer standard query 'CaseListPages_MyOpenCases'

https://www.theaxapta.com/

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

How to get QueryRange from Query object X++

Hi Folks,

This post is about how to get QueryRange from a query object in x++ code. This is very useful when you have to pass a query on another object and perform a certain operation based on this query and its ranges.

To understand this code sample better, let's take a scenario. On FormA I have to build a query (OR use AOT query) on run time based on certain ranges. When the user clicks on a button system will pass this query to FormB and on FormB we have to use this query and its ranges to perform other sets of operations.

Now let's see the code, which may need to write on Form B to take a query as a parameter and fetch the ranges and its values in code.



I hope this will help.

-Cheers!!!
Harry
PS: My VM is down for some reason and I couldn't prove the code and have to write it manually so you might get some syntax issues in the above code.

Extensible Data Security Framework in MS Dynamics AX 7

This feature replaces the record level security feature available in earlier releases of AX. The extensible data security framework or XDS policy framework enables developers and administrators to secure data in shared tables such that users have access to only the part of the table that is allowed by the enforced policy.

Features:

1. Improved filters for data security

2. Data security based on effective date

3. Data security policy concept

Concept:

1. Constrained table

2. Primary table

3. Policy query

4. Context

Process:

Step 1: Create a query

Step 2: Create a policy

Step 3: Add constrained table or view

Step 4: Configure the context

This post referred from here

-Harry Follow us on Facebook to keep in rhythm with us. @Facebook

Use one table for creating multiple relation

Hi Folks,
After a long time I’m writing this new post.
Requirement: I need to use the single table to add multiple relation on a table. For eg. I have a parent table Table1 with field ItemId and ProcessItem. Now I need to add relation to InventTable to both fields. Hence I have to create two relation with InventTable.

How to do: Add two new relation in your table as shown below.

‘
You might get an error message after this,
'RelatedTableRole' conflicts with another 'RelatedTableRole' on relation InventTable_Item on table ProcessedItemTable.

Solution to this error:
Set below two properties to each relation
1. UseDefaultRoleNames- set it to “NO”
2. Role: Give any logical name here, for eg. “Item”



Now compile your table, error must gone.
Write your any query/feedback in comment box and help us to improve. Thanks..!!!
-Harry

Date manipulation through X++ code [AX 2012]

Hi Folks,

I come though a requirement where I need to manipulate on “FromDate” and “ToDate”. When user enter these value through a dialog box, user can select any day from month for eg.

From Date: June 6, 2015
To Date: August 20, 2015



And to perform some action on Financial stuff we may need to consider the whole month like
from June 1, 2015 to August 31, 2015

You can fetch this month range in your x++ code, here is a sample code, try it…
static void theAxapta_MonthRange(Args _args)
{
    DialogField                     dialogStartDate, dialogEndDate;
    Dialog                          dialog = new Dialog("Month range");
    TransDate                       fromDate, todate;
    dialogStartDate = dialog.addField(extendedTypeStr(TransDate));
    dialogEndDate   = dialog.addField(extendedTypeStr(TransDate));
   
    dialogStartDate.label("From Date");
    dialogEndDate.label("To Date");
   
    if(dialog.run())
    {
        fromDate = mkDate(1, mthOfYr(dialogStartDate.value()), year(dialogStartDate.value()));
        toDate   = (mkDate(1, (mthOfYr(dialogEndDate.value()) + 1), year(dialogEndDate.value())) - 1);
       
       
        info(strFmt("Start Month %1", fromDate));
        info(strFmt("End Month %1", todate));
    }
}

Output:



In case of any suggestion and query, feel free to drop as a comment.

-Harry

Restore the DB, showing DB in use

Hi Folks,

Recently I was facing some issue while restoring AX database, whenever I try to restore the Transactional DB its show error "DB is use" while actually this DB is not used anywhere. Than I try to take take the database offline, But again there was one other issue.
Same issue again DB offline taking very long time.
I tried below steps and this worked for me. :)
Step 1: Try to run below two queries in same given sequence.

i. ALTER DATABASE <DBNAME> SET OFFLINE WITH ROLLBACK IMMEDIATE

ii. ALTER DATABASE <DBNAME> SET Offline

Step 2: Now try to take offline manually.

Step 3: Restore the DB.
Step 4: Some time after doing restoration/backup DB will not work.
Than u need to roll back above query operation
ALTER DATABASE <DBNAME> SET online WITH ROLLBACK IMMEDIATE
ALTER DATABASE <DBNAME> SET online.
Related Post:

AX DB restoration: Database goes into “Restoring” state for long

-Harry

AX DB restoration: Databsae goes into “Restoring” state for long

While restoring the AX DB , after completion of this process respective DB stuck into “Restoring” state


You need to run below query to fix this.
RESTORE DATABASE [DataBase Name] WITH RECOVERY

-Harry

Error: When opening AX client

“The Microsoft Dynamics AX client cannot be started because more users are connected to the system than are allowed by the license.” 

Error: While opening the AX client system showing below error message.
“The Microsoft Dynamics AX client cannot be started because more users are connected to the system than are allowed by the license.”
However there is clearly mention in error that its an license issue, 
But the actual issue is we are not able to open the AX and without opening we can not change the license. Upppsss:).



This error should not come While there is only one user login the server and RDP.

SO to resolve this issue Try this:

1. Open SQL management studio
Run below query on AX data base

select * from sysclientsessions where STATUS = 1
there must be some records in this table

2. Stop AOS services
3. Now delete are records from this table
4. Now restart the AOS service and open the AX client.
5. You may need to change the license as well (If expired).

-Harry

How to use Complex Query Ranges in Dynamics AX

Use of Complex Query Ranges in Dynamics AX

     1.  Adding a query with a datasource.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable));
// Add our range
queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

    2.  Simple criteria

Lets find the record where the value of ItemId field is Item1. Take note of the single quotes and parenthesis surrounding the entire expression.

queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("Item1")));
Find records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));

Find records where the ItemType is Service or the ItemId is Item1. Note the nesting of the parenthesis in this example.

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))', any2int(ItemType::Service), queryValue("Item1")));

Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.

queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));

    3.  Complex criteria with combined AND and OR clauses

We need to find those records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard QueryRange syntax.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',fieldStr(InventTable, ItemType),any2int(ItemType::Service),any2int(ItemType::Item),fieldStr(InventTable, ProjCategoryId),queryValue("Spares")));

-Harry

Dynamics AX 2012 Certification Exams Details- Part II

MB6-869: Microsoft Dynamics AX 2012 Development Introduction

Preparation materials

·      80303A: Development I in Microsoft Dynamics AX 2012 (2 Days)

·      80304A: Development II in Microsoft Dynamics AX 2012 (2 Days)

This Exam contains following topics

Understanding Dynamics AX 2012 Architecture (13 percent)

·      Identify key development features and functionality.

·         This topic may include: development workspace; IntelliMorph; MorphX; object-oriented design; navigation

·      Demonstrate understanding of the data architecture.

·         This topic may include: working with data in forms; sorting records; filtering records; finding records

·      Demonstrate understanding of architecture components.

·         This topic may include: layers; models; labels; Help system; reporting

·      Work with customization tools.

·         This topic may include: using MorphX to customize the user interface; using the X++ editor to develop customizations; identifying best practices; using the Type Hierarchy Browser and Type Hierarchy Context tools; using the reverse engineering tool

Managing the Data Dictionary (13 percent)

·      Work with MorphX, the Application Object Tree (AOT), and projects.

·         This topic may include: working with development projects; features of the AOT; Microsoft Visual Studio projects node; objects in the data dictionary; navigating the AOT and data dictionary

·      Work with tables and relations.

·         This topic may include: table structure and components; fields; field groups; indexes; delete actions; creating tables; creating relations; primary keys; foreign keys; surrogate keys

·      Work with data types and base enumerations.

·         This topic may include: primitive types; extended types; creating data types; using data types; creating base enumerations; using base enumerations

·      Work with maps and views.

·         This topic may include: map functionality; map advantages; view functionality; view advantages

Managing the User Interface (13 percent)

·      Work with menus and menu items.

·         This topic may include: creating and using menu items; menu functionality; creating menus

·      Manage forms.

·         This topic may include: data sources; design; document view; editing data in a form

·      Work with forms.

·         This topic may include: form types; list pages and list page metadata; working with the action pane; form parts

Managing Security (11 percent)

·      Work with role and task based security.

·         This topic may include: identifying key concepts, terms, and benefits; working with roles, process cycles, and duties; working with privileges, entry points, and permissions

·      Understand security concepts and settings.

·         This topic may include: default security settings; sample security settings

·      Work with XDS and server enforcement of security.

·         This topic may include: server-based code authentication; data security filters; org model; effective date

Working with X++ Control Statements (13 percent)

·      Work with variables.

·         This topic may include: declaration; simple data types; composite data types; arrays; containers

·      Work with operators.

·         This topic may include: assignment operators; arithmetic operators; relational operators; operator precedence

·      Work with conditional statements and loops.

·         This topic may include: if…else; ternary; switch; while loops; do…while loops; for loops

·      Work with communication tools.

·         This topic may include: print; boxes; infolog; dialog

Managing Objects and Classes (12 percent)

·      Work with classes, objects, and inheritance.

·         This topic may include: defining key attributes; method access control; extending a class; expression operators for inheritance; referencing object methods; method types; inheritance among tables

·      Work with scoping, events, and parameters in X++.

·         This topic may include: scope of objects within a class; events in X++

Accessing the Database (15 percent)

·      Retrieve data.

·         This topic may include: table buffers; select statements; field lists; while select statements; sorting; joins; cross-company data access

·      Manipulate data.

·         This topic may include: insert; update; insert_recordset; update_recordset; delete; delete_from; transaction tracking system

·      Work with queries.

·         This topic may include: executing a query; building a query; QueryBuildDataSource; QueryBuildRange

Managing Exception Handling (10 percent)

·      Work with exceptions and optimistic concurrency exceptions.

·         This topic may include: handling errors

·      Work with throw and try/catch commands.

·         This topic may include: handling errors

Previous Post:

Dynamics AX 2012 Certification Exams Details- Part I

-Harry