Apply filters to multiple tables together in a query

(0) Share

Report

Posted on by Ariba Mehdi

160

Hi everyone,

I have a query made with the following tables; Price List Header, Price List Line, Customer and Item. I want to filter customer and item on the basis of unit price. Currently, the query outputs same combination of Item and Customer with different unit price. However, I want to show unique combination of Item and Customer. For the records having different unit prices for same Item-Customer combination, I want to filter such record based on lowest unit price.

For example:
Right now, it outputs:

Customer	Item	Unit Price
Customer1	Item1	4
Customer1	Item1	5
Customer2	Item1	6

The result I want:

Customer	Item	Unit Price
Customer1	Item1	4
Customer2	Item1	6

I'd appreciate any help or suggestion. Thank you in advance.

Categories:

Development

All responses (2)

Answers (0)

Ariba Mehdi 160 on at

Like (0)

Report

Apply filters to multiple tables together in a query

Hi YUN ZHU,

Thank you for the response.

The unit price field exists in Price List Line Table, not Item table. The hierarchy of the dataitems follow:
Price List Header

Price List Line

Customer

Item

Thus, this does not work to filter the unique Item-Customer Combination.
Suggested answer

YUN ZHU 69,639 Super User 2024 Season 2 on at

Like (0)

Report

Apply filters to multiple tables together in a query

Hi, you can try Min method: Retrieves the lowest value of fields in the column within a group.

More details: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/properties/devenv-method-property?wt.mc_id=DX-MVP-5004336

For example,

Hope this helps.

Thanks.

ZHU