Dynamic Filtering on data entity

(0) Share

Report

Posted on by Community Member

Hi All,

Please help me out.

Needs to check if we can include a parameter value which is configurable in the data entities range to get the desired results.

Example: If we want to configure the Vendors entity to fetch the vendors which are modified/created before 'X' days. X here will be a parameter which we will create in D365 and user can update this number based on their need.

For this I have created a Data entity and add Parameter table which includes the Field X, which user can update from front end.

So I have added range in createdDateTime of Data entity Datasource and add value DayRange(-30,0)), so now here I want to replace 30 with X in the range

So Is this feasible ?? If yes, how can I achieve this.

Regards,

Aimon Fatima

I have the same question (0)

All responses (12)

Answers (3)

Suggested answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report
Copy link

Link copied!

No, unfortunately it's not possible.

But there's a very simple solution: filter the entity when you read it. This way you will achieve the exact same result.

How exactly to filter it in runtime depends on how you query the data entity.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Could you please explain more.

Where to do filtering?

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report
Copy link

Link copied!

Hi Aimon,

Technically it's possible if you create your own range function. You can copy dayRange function from class SysQueryRangeUtil and instead of using "from parameter", you can select it from the parameter table.

How often are you planning to change parameter? You should keep in mind that in a multiuser environment several users can change parameters and during the export own of the users can get unexpected results because another user changed the parameter already.

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report
Copy link

Link copied!

"Where to do filtering"?

You would do it in the place where you request the entity data.

- If you do an OData request, here's doc page that explains how you can do filtering: docs.microsoft.com/.../odata

- If you read the entity in x++ code you can filter it the same way than when you read tables or views with a select statement.

- If you export via Data management, you can specify the filter in the data project settings

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Sergei Minozhenko,

Thank you for the response. So should I create our own function in Data entity level?? or in some other place??

Yes, user will change the parameter. Will see this thing after achieving this functionality.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thank you Nikolaos Mäenpää.

I'll check with 2nd and 3rd point.

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (1)

Report
Copy link

Link copied!
Hi Aimon Fatima,

The range function is a global function that you can use in any query-based objects (data entity, forms, query dialog).

You need to create a new class (or make an extension for class SysQueryRangeUtil) with method

[QueryRangeFunctionAttribute()] public static str myDayRange() { utcdatetime currentDateTime; ; currentDateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getUserPreferredTimeZone()); return SysQueryRangeUtil::dateRange(DateTimeUtil::date(DateTimeUtil::addDays(currentDateTime, MyParameters::find().MyParameter)), currentDateTime); }

Then you can use it in ranges (myDayRange())

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!
Hi Sergei Minozhenko,

1. As suggested by you I have created a custom myDayRange() in the SysQueryRangeUtil class and add the logic.
[ExtensionOf(classStr(SysQueryRangeUtil))] final class XYZSysQueryRangeUtil_Extension { [QueryRangeFunctionAttribute()] public static str myDayRange() { utcdatetime currentDateTime; Int noOfdays = MyParameters::find().MyParameter; currentDateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getUserPreferredTimeZone()); return SysQueryRangeUtil::dateRange(DateTimeUtil::date(DateTimeUtil::addDays(currentDateTime,-noOfdays)), DateTimeUtil::date(DateTimeUtil::addDays(currentDateTime,0))); } }

2. While synchronization I'm getting the below error.

3. I don't know why this error is coming while I have assigned the company in project properties as well.

4. In other case, I have used custParameter::find().AccountPosition field to check whether the method is working or not. But its also not worked.

What can be the reason if you have any suggestions on it??

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report
Copy link

Link copied!

Hi Aimon Fatima,

I guess it's because during synchronization advanced range function is compiled to static range in SQL view. So you should not put it in range on entity level in x++, but you can add an advanced range function as a filter in a data project.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi,

You want me to add here.?? I found that advanced range functions were not working here like (dayRange(-28,0))

Is there is any other way to use advance ranges in Data project settings??

Was this reply helpful? Yes No