How to let a manager to choose a vendor with best prices?

Good afternoon!

I'm trying to create a helper for purchase order creation which allows a manager to choose where to buy items. I have imported price lists for several vendors and some items are cheaper in one company while others are cheaper in another. The delivery is free starting from some amount of the order.

So, after the user enters items and quantities in this document, and clicks Fill vendors, the Vendor column of the document's tabular section should be filled with vendors having cheapest price for that item. After that I need to allow a user to choose a vendor from a choice list which should contain values with presentations like "vendor (price)".

In the footer of that document the summary of the document should be displayed as:
vendor | amount | delivery cost

I know how to implement the summary, problems are:
The query to get best prices with vendors.
How to fill choice lists of vendor column fields.

The Vendor prices information register has Daily periodicity, Item and Vendor dimensions, and Price resource.

May be it can help you..

You can fill choise list in OnChange event handler of some field, in my example it is column of tabular section.

Thank you, Ivan! That is a good start, but I get prices of all vendors in that query...

And maybe you could help also: I need to set Price and Vendor column values at a time when a user selects an item in the choice list.

Yes, and in query you can make different filters, like filter on item: Item IN (&Items)
in query

after query


you can process value of selection in OnChange event handler

If I could know the Vendor I would not need to search for a better price... Thus the filter for Vendor is not what I can use.

Here is an example for both tasks in attachment (works on latest Small Business).

Thank you very much, Dmitrij! Works perfect for me!