Microsoft Dynamics AX (Archived)

Filtering Form Datasource Results

(0) Share

Report

Posted on by Community Member

Hi Everyone,

I have 2 datasources "A"(parent) and "B"(Child) on Fom.

on datasource "B" -> joinSource ist set to "A", and linkType = InnerJoin.

I want to filter the results of datasource "B" . I have tried executeQuery method on "B" but its not working.

The problem is due to the inner join some records on "B" are displayed that i do not want to be shown.

Can anyone please help.

Thanks in Advance.

Bests

Shah

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (0)

Sort by

AX 2012 r3 2,426 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Try Writing your Unnecessary B Records before super call of B Datasource ExecuteQuery()

public void executeQuery()

{

;

GroupHeader_ds.query().dataSourceTable(tableNum(GroupHeader)).clearRanges();

if(statusFilter.valueStr() == enum2str(AllDraftPosted::Draft))

{ GroupHeader_ds.query().dataSourceTable(tableNum(GroupHeader)).addRange(fieldnum(GroupHeader,Status)).value(sysquery::value(GroupStatus::Draft));

}

super();

}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi,

Thanks for your reply, but there is a inner join on Datasource B. and using executeQuey does not get called when the data is called and shown on the listpage.

I have tried using query range before and after super but nothing happens.

Was this reply helpful? Yes No
Martin Dráb 239,213 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

List pages are based on AOT queries. Can't you simply add your range there?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

No, setting range on the query will not give the appropriate results.

In datasource "A" (temporary table) records are written then it is linked to "B" where inner join is performed on ID , but there are some other conditions that i need to check, and only those records should be shown that fullfill the condition in B.

Was this reply helpful? Yes No
Martin Dráb 239,213 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You filter data by setting ranges to the query. If you say that your requirement can't be done by that, you have to change the design.

Or did you mean that you can't add the ranges to AOT but you can add them at runtime?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Well, From datasource B i want to get the highest count record example

Datasource A , DatasourceB ( joineSource = A, linkType = innerJoin)

A -> Id = 1 B -> Id = 1 , Count = 2

B -> Id = 1 , Count = 3

B -> Id = 1 , Count = 1

so i want to get the row from B , where Count is higest(meaning = 3)

so only B-> Id = 1, Count = 3 (record) should be displayed on list page.

Was this reply helpful? Yes No
Martin Dráb 239,213 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Okay, I see. Please make sure next time that you explain your problem properly, saying "I want to filter the results of datasource "B" . I have tried executeQuery method on "B" but its not working." is clearly something else.

If your intention is getting only B-> Id = 1, Count = 3, the right query should be this:

select count(RecId) from B group by Id

Model it as an AOT query and use it in your form.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

No, Sorry i think you got it wrong :) .

Id and Count are fields in Datasource B. Now Count can be any number from 1 to N . so want to show only that record from B where Id = 1 and Count = MAX . so there can be many records with id = 1 in B and all have some "Count" value. so i need to display only that record from B where Id = 1 but Count = MAX (Max value from Count Field) . ( 1, 2 ,3 ... 10) so Count = MAX, which is (10)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

And also execute method is not working on Datasource B. maybe cuz of Innerjoin.

So how will it work.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,213 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Aha, so "count record" means "the highest value in the Count field" (ignoring the fact that it can't be called Count, because that's a keyword in X++), you actually wanted to ask how to join a single record only (selected by some rule, in your case the one with the highest count) and the whole discussion about filtering and executeQuery() is not relevant to your problem at all.

You're lucky, because I have a blog post describing exactly this scenario: Join first line in AX 2012. Just don't forget to take into account Partition and DataAreaId; I've omitted them to keep things simple and some readers were confused by that.

executeQuery() works exactly as it should. You can't expect it to execute twice, once for each datasource, because there is just a single query to run in your case.

Was this reply helpful? Yes No