Add link/hyerlink through drillthrough on SSRS report in D365 F&O

Today I will discussing about one of the requirement which I came through while working on sales order confirmation report. 

The requirement was this that i need to add a hyperlink on Itemid field within existing sales order confirmation report. So here are couple of steps which any one can follow on in order to add a hyper link for itemid field within SSRS report.

Step 1) Open the design of your report. Right click on text box properties.

 

Step 2) Go to action tab and click the URL radio button and add a expression then Click on ok button.

Explanation of expression :

=Microsoft.Dynamics.Framework.Reports.BuiltInMethods.GenerateDrillThroughLink(

Parameters!AX_ReportContext.Value,

Parameters!AX_UserContext.Value,

"Menuitem name to be called",                    

"The type of the meun item which is to be called for instance : "Display"",

"Table name associated with Menu item or referred by it",

"Field name within the restricted table ",

"The value of field within dataset")

Output : 

When you will click on Item id field in SSRS report then it will navigate  you to release products form.

Get worker name against current user in D365 F&O

Today , I will be sharing a small code snippet which can be used to get worker which is attached with the current system user.

Here is the code snippet.

UserId currentUserId;
Name workerName;

currentUserId = curUserId();

if (currentUserId)
{
       workerName =  HcmWorker::findByPerson(DirPersonuser::find(currentUserId).PersonParty).name();

}

Note : curUserId() is system based method which can be used to get current user which is logged in right now.

Create batch job using SysOperationFramework

Today, I will discussing about how to create a batch job using SysOperationFramework in Dynamics 365 Finance And Operations.

Let's suppose we need  get all customer open transaction with negative amount whose payment method is null and update their fields on basis of some logic. Also, user will have option to select number of customers on run time.

In order to achieve our target we need create 3 new class.

1- Contract
2- Service
3- Controller

Step 1- Create a query in my case i have taken join between CustTrans and CustTransOpen tables and added ranges on AccountNum, AmountCur and PaymMode fields.

Step 2- Create a new Contract class and add query in it.

Step 3- Create a new Service class and add method in it for executing the logic and get query from data contract. Execute query by using QueryRun. Add some logic in executeMode method as per your requirement like in my case I need to update some records.

Step 4 - Create a new controller class.

Step - 5 Create a new action menu item and set the controller class name as object and set the
processing method name from service class in parameter field.

Step - 6 Final output of the batch job.

Clear usage data in D365 Finance and Operations

Today I will be discussing about one of the common issue which you might have encountered at a time of executing batch job for second time.

For instance I was executing a job for first time and  have selected few parameter. At a code level I have set by default the value of batch job processing field to Yes.
However, before executing for second time I did modification in code and setting the batch processing field value to No . So now I was executing same batch job for second time but it batch processing field value was still Yes and due to this reason it was added in batch.

So, in order to reset or get latest code impact after build I need to reset usage data of that particular user.

Following are the steps required to reset usage data.

1- Open Chrome/IE browser.

2-  Click on setting in right most corner and select User options.

3- Click on Usage data button.

4-  Click on Reset button available in bottom. It will clear the usage data of specific or currently login user.

That's it and your usage data will be reset :)

D365 F&O - Open table in a browser (IE/Chrome)

Today, I will be discussing about two different approaches which can be used for opening tables in chrome/IE in order to view data.

First approach

1 - Go to visual studio.
2-  Right click on the table.

Note : Default company for table browser will be picked from its user which is set as administrator.

Second approach

1- Go to IE or Chrome.
2- Paste the following URL

https://<URL>/?mi=SysTableBrowser&prt=initial&cmp=<Company>&tablename=<TableName>&limitednav=true

Sample : https://ABC.cloudax.dynamics.com/?mi=SysTableBrowser&prt=initial&cmp=USMF&tablename=BankStatementTable&limitednav=true

Cmp             : It is company whose data you want to view.
TableName  : The name of table whom you want to browse.
URL              : https://ABC.cloudax.dynamics.com

Download cloud Dev VM's from LCS

Today, I will be discussing about how to download cloud Dev VM's from LCS.
Below are the few steps required to perform this task.

Note : You should have access to login on LCS and then access to project on which you need to work on.

Step 1) Login to LCS.

Step 2) Click on Project.

Step 3) Click on three parallel lines and select cloud hosted environments option.

Step 4) Click on your cloud hosted environment and click on its full details option.

Step 5) Click on VM Name and it will download your VM.

Few common functions related to Query and dates

Today, I will be discussing about few of the common methods related to query and dates.

Let's begin with it.

Date  related methods 

 -  Year(date  _date) : Method will be used to get year from specified date.
-   mkdate(int _day, int _month, int _year) : Method will be used to make and return a new date
-   mthOfYr(date _date) : Method will be used to get month from the the specified date parameter.
-   dayOfMth(date _date) : Method will be used to get day of the month from the specified date parameter.


Query  related methods 

QueryBuildRange         startAndEndDateRange ;

startAndEndDateRange = this.query().dataSourceTable(tableNum(EmplSickLeaveEncashment)).addRange(FieldNum(EmplSickLeaveEncashment, NextEncashDate));
startAndEndDateRange.value(SysQueryRangeUtil::dateRange(startDate,toDate));
startAndEndDateRange.status(RangeStatus::Hidden);

Here for adding date range we can using dateRange method from SysQueryRangeUtil class.
In order to hide range we can set its status to Hidden by using method of status.

Table browser - You are not authorized to login with your current credentials error in D365 F&O

Today, I will discussing about one of the common issue which you might have faced while opening table browser in D365 finance and operations. Eventhough you are logged in successfully as administrator and have full rights.

The error will be :
You are not authorized to login with your current credentials.

A quick work around for it.
Go to on following path : C:\AOSService\PackagesLocalDirectory\Bin
Here you will find a file DynamicsDevConfig.xml

Open this file as administrator in notepad. Here at the end you will find following line of code
<OfflineAuthenticationAdminEmail>test@domain.com</OfflineAuthenticationAdminEmail>

The email id which is associated with it is not authorized so replace it with your authorized email address and you will be able to access it.

Join multiples tables with same table using query build data source

Today, I will be discussing about one of the common issue related to query creation using  query build data source. 

Let's suppose we have following tables.
 - Leave Assignment  table - (Base Table) - Namely Parent for demo purpose
 - HcmEmployment table   - Namely Child 1 for demo purpose
 - Leave Plan table    - Namely Child 1 for demo purpose

We want to link base table with both hcm employment (child1) and leave plan table (child2)  using query build data source. Below is the code used for performing this operation.

public QueryRun   fetchMultipleTableQueryDemo()
{
    QueryBuildDataSource hcmEmploymentds;
    QueryBuildDataSource qbds;
    Query query = new Query();
       
    // Adding parent  as a datasource
    QueryBuildDataSource qbds = query.addDataSource(tableNum(Parent));

    // Adding child 1 as a datasource
    hcmEmploymentds= qbds.addDataSource(tableNum(Child1));
    hcmEmploymentds.joinMode(JoinMode::InnerJoin);
    hcmEmploymentds.addLink(fieldNum(Parent, Worker),fieldNum(Child1, Worker));
    hcmEmploymentds.fetchMode(QueryFetchMode::One2One);

   // Adding child 2 as a datasource
   qbds = qbds.addDataSource(tableNum(Child2));
   qbds.joinMode(JoinMode::InnerJoin);
   qbds.addLink(fieldNum(Parent, LeaveDetail), fieldNum(Child1, RecId));
   qbds.fetchmode(QueryFetchMode::One2One);
       
    return new QueryRun(query);
}

SysMultiTableLookup - A way to show fields from multiple tables within custom lookup

Today , I will be discussing about one of the common issue which we might face while showing fields from multiple tables in lookup . As we all know SysTableLookup only pick up fields from first data source. However, in few cases we might need to show fields from multiple tables for instance I need to show operating unit number but as well as organization hierarchy Name which is field of 4th data source within my custom. In order to achieve this target I will be using SysMultiTableLookup class.

Below is the code snippet.

Note: In below line the number 4 is showing the number of datasource n in my case it is 4th datasource.
 sysMultiTableLookup.addLookupField(fieldNum(OMHierarchyType, Name),4);

Final output

Create a model driven app - Part 8

As we all know that in previous section we learned how to create dashboard for model driven application. Now its time to view this newly created app.

So, let's begin with it.

- Sign in PowerApps.

- Go to Apps menu and select your IT projects demo application. Then click on Play button.

- Here is the first navigation menu IT projects dashboard with both chart and list.

- Go to IT Project Demo menu to see your views.

- Create a new project record by clicking on new button.

List updated after creating new project.

Note : I just used duration for demo purpose you can use another field to show long duration.

Create a model driven app - Part 7

As we all know that in last post we learned how to create charts for model driven app. Now we will discussing about how to create a dashboard for this app and link it with site map.

So, let's begin with it.

- Sign in PowerApps.

- Open your model driven app and click on Edit.

Steps required for creating a dashboard.

Step 1) Click on Dashboards and it will show you its components.  Then click on Create new button to create new dashboard. Select classic dashboard option.

Step 2) Choose a layout. In my case I have selected 2 column layout and click on create button. It will open a new window.

Step 3) Enter name for your dashboard. Select Insert chart option for first column and insert list for second column.

Step 4) Insert a chart. Select your record type and view. Then click on add button.

Step 5) Insert a list. Select your record type and view. Then click on add button.

Step 6) Select Save then close. Go to Site map designer and select newly created dashboard.

Steps for linking dashboard with SiteMap. In order to show it as a first screen on app.

Step 1) Click on Site map edit button.

Step 2) Click on Add button and add sub area component.

Step 3) Rename it and set following properties on it. In order link it with newly created dashboard. I moved it up so that it should be first area for user to look around.

In last Save and Publish your application.

In the next part we will see what we have created. Stay tuned.

Create a model driven app - Part 6

As we all know that in previous post we learned how to create views for model driven app. Now we will discussing about how to create a charts for this app.

So, let's begin with it. I will be adding two charts one for showing showing cost of resource allocation per department and another for showing projects count per department.

- Sign in PowerApps.

- Open your model driven app and click on Edit.

Steps required for creating bar chart. In this we will showing cost of resource allocation per department.

Step 1) Click on charts and it will show you its components.  Then click on Create new button to create new chart. It will open new window.

Step 2) Click on charts then bar chart.

Step 3) Enter the name for chart and select series and category columns.
In my case I have selected Series to resource allocation cost and category to department.

Step 4) Select Save and close button.


Steps required for creating pie chart. In this we will showing project count per department.

Step 1) Click on charts and it will show you its components.  Then click on Create new button to create new chart. It will open new window.

Step 2) Click on charts then pie chart.

Step 3)Step 3) Enter the name for chart and select series and category columns.
In my case I have selected Series to project Id and category to department.

Step 4) Select Save and close button.

Finally go back to Site map designer and select your newly created charts.

In next part we will learn how to create dashboards.

Create a model driven app - Part 5

As we all know that in previous blog we discussed about how to create a new form for model driven application. Now  we will discussing about views creation for this app.

So, let's begin with it. I will be adding two views one for showing projects which are in completed status and another with list of all projects.

Step 1) Sign in PowerApps.

Step 2) Open your model driven app and click on Edit.

Step 3) Click on views and it will show you its components.  Then click on Create new button to create filtered view. It will open new window.

Step 4) Click on filter criteria and add a filter for showing only completed projects.

Step 5) Add columns by choosing from column attributes.

Step 6) Rename the form by clicking on edit button and save your filtered view.

Create All projects view.

Step 1) Click on views and it will show you its components.  Then click on Create new button to create filtered view. It will open new window.

Step 2) Add all columns  by choosing from column attributes and rename view.

Step 3) Save and close the view.

In final step you need to select those view from Site map designer screen. Save and close the application.

In next part we will learn how to create charts.