Find records with date range in Model driven app using Advance Find

When you work in Model driven app – especially around date field, you may need to search record based on certain conditions.

Some of the options are shown in below image

The most often used filter criterias are “On”, “On or after” and “On or before”.

If we want to search records based on two date fields such as by using between operator, we need to use Advance search.

Before that, let see how “On or after” and “On or before” filter works. This is required to build our between filteration criteria.

On or after :

Consider the below records and see the published date in these records.

To get records after 1st of Aug 2021, the filteration shows three records

On or before:

To get records on or before 1st of Aug 2021, the filteration shows only one record

If we need to get record between 1st Aug to 7th Aug 2021, we need to use advanced search or using advance filteration.

Advance Search:

Click on the advance search icon on the right hand corner . By using both “On or after” and “One or before”, we can achieve the between date range functionality.

Add the date range as shown below

Result shows records within the selected range of dates.

Advance Filter:

Click on the “Advance filter” option

Select the same filteration that we have discussed above

Records will be filtered out with the date range

Looking for a video demo? I have explained the same in the video below.

Fix to change Dynamics 365 – database to multi user mode

Sometimes when you try to restore the AX database, it will be converted into a Single User mode. And it will now allow you to perform any task against the AX database.

b1

To rectify that, please try to execute the below query.

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you have any deadlock on the database, you will receive the below error.

2

To rectify this issue, we need to kill the dependent process on the database. To identify the process please use the below sql script.

  1. Get the database id

(select dbid from sysdatabases where [name] =’AxDB’)

3

  1. Find all the process on the database id

select spID from sysprocesses where dbid IN 5

Once, you find the spID of the process, you should kill the process by using below code

Kill <spID>

Now execute alter table to change the database to multi user mode using below query.

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

Alternatively, you can use the below script to find lock processes

4

SELECT * FROM   sys.dm_tran_locks WHERE  resource_database_id = DB_ID(‘AxDB’)

And kill the process with the request_session_id as shown below code

kill 53

kill 51

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

How to consume Dynamics AX Product Images in PowerApps

Step 1: Create a sample data entity as shown below. It uses EcoResProductImage table which holds the image container of the items.

1

Step 2: Check the odata feed in the browser. You can see the Base64 images are exposed in “MediumSize” field as shown below.

2

Step 3: Create a sample PowerApps application and add a gallery. Assign the data source to the gallery.

4.png

Step 4: Assigning the “MediumSize” to the image will not shows up the image in PowerApp. So add the below code in the image source to specify the Base64.

“data:image/jpeg;base64,” & ThisItem.MediumSize

3

 

Read Query String in Dynamics 365 Operations – AX 7

Challenge: To read query string parameter in form init() method

Solution: URLUtility class is used to extract the query string parameters. To explain this, I have created a simple form designed to extract query string in the init method and display in a text control.

  • A display menu item is attached with the form “Form1”

d1

  • A string control is attached and the “AutoDeclaration” property is set to “Yes”. Assign custom pattern to the form.

d2

  • Below code in the form init() method retrieves the query string information and set to the string control.

D3

  • Execute the menu-item linked with the Form and attach the following line in the url link

&queryParm=Hello,world

D4

  • The query string value is displayed in the text control.