Filter blank value on field in outer join

(0) Share

Report

Posted on by Martín Flores

Hi guys!

I'm facing this problem. I have 2 data sources in a form linked with an outer join.

I've added fields from both data sources in a grid.

When the user tries to filter a blank field from the outer join record (in this example the Invoice proposal id) the query gives no results.

I guess this is because, in fact, the field either has a value or is null. The problem is this is counterintuitive to the end user.

What could I do to avoid this behaviour?

Take into account that my real requirement involves a lot more data sources, and potentially the end user could apply a filter on any field of the query (using an advanced filter).

Thanks in advance!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Martín Flores on at

Like (0)

Report

RE: Filter blank value on field in outer join

Hi Douglas,

That was intersting reading, I wasn't aware of QueryFilters.

Creating a view and adding computed columns indeed solves my problem, the issue is that I have a lot of fields to add and I kinda want to avoid creating a method each time a field needs to be added.

I was thinking I could override some method on my form datasource so I could add a queryFilter instead of a QueryBuildRange when the user tries to make a filter. Unfortunately I don't know wich method I should override. I tried overriding filter method (either at field and datasource levels) but it never got triggered. There is I method I could override to achieve this?

Thank you for your answers!

Kind regards

Martín

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

RE: Filter blank value on field in outer join

Hi

seems to be the 'good old' outer join behaviour (starting years ago) using Ranges

take a look here: axaptadevmallik.blogspot.de/.../difference-between-querybuildrange-and.html

Maybe you can try (available since Ax2012) a QueryFilter instead of a "standard" QueryBuildRange.

And of course you can try a view (based on an outer joined query) and "de-NULL" some colums using computed colums in the view using ISNULL(column,'') (for nvarchars) in the computed column definition. Then you will have a NON-NULL value in all cases and can set a Range to ""

only some ideas

regards

Douglas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filter blank value on field in outer join

Hi Martín Flores,

Please find below Images Doc, it may useful for you.

Thanks,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filter blank value on field in outer join

Hi Martin Flores,

What your expecting the records, why you need to give outer join in that form.

What is the relation from parent table to child table?

and why don't you get output when you create View. Please try with the view and that to in that query you should create a relation from Parent table to child table, then only you will get the data with View.

Note: It may happen that if AOS gets stopped suddenly or the client loses connection with the AOS, some records stay in specific tables that causes these kind of situations

Thanks,

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

Like (0)

Report

RE: Filter blank value on field in outer join

HI Martin,

You can indeed try to return an empty string in that case using the computed column. I haven't done this myself before. It was just a suggestion to try out.

Was this reply helpful? Yes No
Martín Flores on at

Like (0)

Report

RE: Filter blank value on field in outer join

Hi André,

I forgot to tell, I already tried to create a view and had the same result.

Regarding to define columns via T-SQL, are you talking about computed columns? What would you do? Return an empty string if there is a null value?

Kind regards,

Martín

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

Like (0)

Report

RE: Filter blank value on field in outer join

HI Martin,

You can try to create a view with the datasources and specify which fields are needed. You can also use T-SQL to define some columns. Then use this view as datasource on the form.

Was this reply helpful? Yes No