web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Filter on a grid, but ...
Microsoft Dynamics AX (Archived)

Filter on a grid, but with a value from another table

(0) ShareShare
ReportReport
Posted on by Ashein 275

Hi

I am trying to filter my grid, but with a value from another.

fx.

I am trying to filter on salesline form, but i only want to see records which are not "invoiced".

How can i create that filter on salesline form? i only know how to filter on a combobox on the form, something like that.

Please help.

*This post is locked for comments

I have the same question (0)
  • Ashein Profile Picture
    Ashein 275 on at

    The value should not invoiced. So it should be something like:

    ---executeQuery()---

    criteriaStatus.value(salesLine.salesStatus != salesTable::find(salesLine.salesId).salesStatus)

    ?

  • Ashein Profile Picture
    Ashein 275 on at

    Don't quite understand query range can help my problem?

  • Martin Dráb Profile Picture
    Martin Dráb 238,149 Most Valuable Professional on at

    Can you confirm that this question is about coding, as others seem to assume? Maybe you just want to filter it in user interface?

    Also, what are table are you talking about? Can't you simply use sales status field on the line itself?

    By the way, you can do many complicated things (including adding other tables to queries) in the Advanced Filter/Sort dialog (Ctrl+F3).

  • Ashein Profile Picture
    Ashein 275 on at

    Martin: i will tag it next time it is about coding. And the reason i can't use salesStatus on my solution, using salesTable/salesLine is an example.

    Crispin: How do i ensure all the records are sorted by the salesTable not being "invoiced"?

  • Martin Dráb Profile Picture
    Martin Dráb 238,149 Most Valuable Professional on at

    All right, I've changed the category from Other to Development / Customization / SDK.

    It's pretty simple - add the other table to the query, add a range on it and then use valueNot() when specifying the value.

    For example:

    ds.addRange(fieldNum(Table1, Field1)).value(valueNot(Status::Invoiced));
  • Ashein Profile Picture
    Ashein 275 on at

    Thanks Martin, but the field one does not contain the status::invoiced. But contain the salesId. I am trying to use the salesid to get the status from the table. But is it possible?

  • Martin Dráb Profile Picture
    Martin Dráb 238,149 Most Valuable Professional on at

    Aha, so you don't know how to join two tables, right?

    Get a reference to the QueryBuildDataSource to which you want to join the table. Call addDataSource(tableNum(YourTable)) to add the other table.

    Consider which join type to use - one option is using ExistJoin with a negative condition, another using NotExistJoin with a positive condition.

    Then you have to define which fields should be used for joining. If there is a relation, call relations(true) on the new datasource. If not, consider adding one, or use addLink().

    When you have that, add a range to the new datasource as explained in my previous reply.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#2
TAHER Mehdi Profile Picture

TAHER Mehdi 3

#3
Nakul Profile Picture

Nakul 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans