Grid Filtering for outer joined items.

(0) Share

Report

Posted on by KCDeVoe

1,450

I have a grid for product management and we are wanting to add the RouteId to the grid. I added it as a view since there are some calculations done to make sure it's the Active Route. I also did the view because you can't sort/filter by display methods.

Now I can sort and filter. However, I can only filter on items that exist. We're wanting to basically see all the "blank" fields(ie, still need a Route). This would be anything that 1) doesn't have an active route and 2) has no route period. This is becoming an issue because filtering for nothing gives you nothing.

I've created a manual filter that combines a a NotExist join with a Not Approved query. However, I'd prefer something more fluid and works better with the built in filtering system. Does anyone have any suggestions for me?

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Community Member on at

Like (0)

Report

Hi,

Try adding both tables as datasources in the form and set the following properties on the route table:

JoinSource: your primary table;

LinkType: OuterJoin;

The datasource of the grid should be your primary table, but you can add fields from the route table.

Regards,

Was this reply helpful? Yes No
KCDeVoe 1,450 on at

Like (0)

Report

I've done this. There's actually several data sources on the grid because the route is just one part of it. However, since there are certain items that need to be checked(Active/Approved/FromDate/ToDate etc...) to see if the route is active I wasn't able to just join the tables. That's why I ultimately did a view for the route. Now I do have the route on the grid how I want it, but the issue is it won't filter for items that are blank (ie one's without a route or no active route). This is likely because there's no related information the query would end up being "SELECT * FROM InventItemPurchSetup where JOIN...." Since there's no record in the route table to join then there's nothing it can search for.

I've created a solution with a manual filter where I do a non exist join and union that with a list of not active routes. However I was hoping there was a more elegant solution to this issue.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Yes, it seems AX still doesn't support outer joins for views.

I achieved displaying data in this way by creating a view with only the route table with ranges to filter out inactive records. Then I added the view as a datasource and outer joined it to the main table. In the init() of the view datasource, I added a link:

public void init()

{

super();

this.queryBuildDataSource().addLink(fieldNum(CustTable, Party), fieldNum(ContactPersonView, ContactForParty));

}

My code uses different tables because I already had a test form with those, but it's the same situation if you can filter out inactive routes with ranges on the view.

This brings all customers, with or without an "active" contact (for you it would be the route). The only problem I found was when I filter (in the form) for the value empty ("") on the routeId, it returns no records. Filtering for a non empty value works fine. I think it is pretty simple, only 1 line of code using outer join. Hope this can help you in some way.

Was this reply helpful? Yes No
KCDeVoe 1,450 on at

Like (0)

Report

Sounds like you got to the same point as me. I can see all the items with no route, but when it comes to filtering for these empty items it shows a blank record set. Thanks for your help, hopefully Microsoft will address gaps like this in future releases.

Was this reply helpful? Yes No
Ghetz 3,013 on at

Like (0)

Report
Sorry for opening this post again.

I'm using AX 2009. My I ask how did you get the option to filter?

I created a View and added as a second datasource in my form. I put the JoinSource and Outerjoin in the properties.

I created the link in the Init method of my View DataSource:

public void init() { super(); this.query().dataSourceName('MyView').addLink(fieldNum(InventTable, ItemID), fieldNum(MyView, ItemID)); }

However, I can only order the grid, but I can't filter by the fields in the View datasource empty or not empty.

Was this reply helpful? Yes No
KCDeVoe 1,450 on at

Like (0)

Report

I don't know about 2009 (I just started with AX in 2012), but in 2012 if you're in a Grid you just have to hit Ctrl + g and a filter row appears. There's also command buttons you can add that will give you a filter dialog (and I believe ctrl + f5 will give you the same).

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

You can add computed column to view which would be set by SysComputedColumn::if() condition instead of directly dragging field from view datasource.

In the following example we are setting '0.00' if VendInvoiceTrans.Qty is null else value of VendInvoiceTrans.Qty

eg : SysComputedColumn::if(

SysComputedColumn::isNullExpression(SysComputedColumn::returnField(viewName,identifierStr(VendInvoiceTrans_1_1), fieldStr(VendInvoiceTrans, Qty))),

"0.00",

SysComputedColumn::returnField(viewName,identifierStr(VendInvoiceTrans_1_1), fieldStr(VendInvoiceTrans, Qty))

);

Was this reply helpful? Yes No

Community site session details

Grid Filtering for outer joined items.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Grid Filtering for outer joined items.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates