2 separate filters on the same field

(0) Share

Report

Posted on by Guillaume Cantin

689

Hi,

I'm making a customization in which I need to have 2 different filters on a single field (AccountNum). I apply my first range in the override of the FormDataSource.executeQuery() method (The method of a child data source, not the parent FormDataSource object) for my form's main data source. That rangeworks properly but shows up in the grid's filters. In this specific case, we can't have this show up as something that can be modified as it would give some users access to data that isn't theirs to manipulate.

We need the users to be able to filter on that field because they will have 100-150 records in the grid at once. Since I'm using the X++ Querying classes, I was wondering if there's any way to hide the QueryBuildRange I'm creating in X++ from the user. The user input in the grid needs to be calculated against the existing filter.

Ex: I have Accounts 1, 2 and 3 in the X++ filter, those are the only values that can be returned even if the user's filter contain Accounts 4, 5 and 6

Is there any way to do this in AX 2012 R2?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (2)

Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

You can change the status of your range (range.status(RangeStatus::Hidden)).

Was this reply helpful? Yes No
Guillaume Cantin 689 on at

Like (0)

Report

If I do this, users will not be able to filter in the grid. I need users to be able to filter on the already filtered data.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,030 Super User 2025 Season 2 on at

Like (0)

Report

Hi Guillaume,

I wonder the purpose of the first range. Is it related to secure data? Probably you can use an eXtensible Data Security policy to restrict the data? Then users can only view data defined in the XDS query, but cannot override this.

Was this reply helpful? Yes No
Verified answer

Ivan (Vanya) Kashperuk on at

Like (1)

Report

If there is a locked range/filter on a field, that field is not available for filtering (through grid filtering or thru SysQueryForm). That's by design and confirmed by Microsoft kernel team.

In earlier version of AX, you could "AND" ranges on the same field by addressing the field through fieldId2Ext() and applying the filter on the 1st "imaginary" value of the field.

That however does not seem to work any longer in my 6.3 installation.

You could try doing the hidden filter on another field, that is calculated based on the data you need to select (even RecIds, for example).

This depends, of course, on how much data is being shown.

Was this reply helpful? Yes No
Guillaume Cantin 689 on at

Like (0)

Report

Andre : The first filter is based on a container that is generated by querying the packed queries from the Customer Collection Pool table. I have yet to use XDS, would it be possible to use either the queries or the result in an XDS policy?

Ivan : We're currently working on optimizing this specific process because we're seeing performance issues. Also, for future upgrade purposes, I would rather avoid using something that is not supported in later versions. A quick (yet very dirty) fix I had in mind would be to duplicate the field in the table and have my X++ query filter on the duplicate, hide that field both in the table and data source, and have the user filter on the original field. I tried that solution out of curiosity and it worked and doesn't seem to have a big impact on either record creation or selection, but isn't the cleanest way of dealing with this problem in my opinion.

Thanks

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report

You can't meet both requirements on the same field. Nevertheless you might be able to redesign your query, e.g. having one data source with the open range and another joined data source with the locked/hidden range.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,030 Super User 2025 Season 2 on at

Like (0)

Report

Hi Guillaume,

Can you explain the business requirement? If the filter is based on selected records, it is not suitable for XDS. If it is a security which reads allowed values from a several table and is applicable to the user for the complete AX session time, it would be possible to use XDS.

Was this reply helpful? Yes No
Verified answer

Guillaume Cantin 689 on at

Like (0)

Report

I believe I've found a better way of dealing with this. I already retrieve all the Account Numbers prior to initializing/refreshing the data in the grid. I think I could try using Ivan's idea of filtering on a different/hidden field (RecId in my case). I'll see if there is a big performance hit. We should only have to refresh the list of RecId in special cases, so the performance overhead probably is acceptable.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

If you have a list of IDs that you want to filter by, a more optimal option may be putting them into a TempDB table and joining with the other data.

It also resolves your issue, because there is no range involved.

Was this reply helpful? Yes No