Find Minimum?

Check out the function GETRANGEMIN:

GETRANGEMIN (Record)
Use this function to return the minimum value in a range for a field. Compare with GETRANGEMAX.

Value := Record.GETRANGEMIN(Field)
Value

Data type: text constant or code

Contains the minimum value of the range set for Field. The data type of value must match the type of Field.

Record

Data type: record

The record that contains the field.

Field

Data type: field

The field you want to find the minimum value for. The current filter on Field can only be a single range filter, otherwise a run-time error occurs.

Example
These examples show how to use the GETRANGEMIN function.

With a filter which is a single range:

Customer.SETFILTER("No.",'100..200');
Val := Customer.GETRANGEMIN("No.");
MESSAGE(Text000', Val);

The message window shows:

The minimum value is: 100

With a filter which is single value:

Customer.SETFILTER("No.",'100');
Val := Customer.GETRANGEMIN("No.");
MESSAGE(Text000, Val);

Create the following text constant in the C/AL Globals window:

Text Constant
ENU Value

Text000
'The minimum value is: %1'


The message window shows:

The minimum value is: 100

With a filter which is not a single range:

Customer.SETFILTER("No.",'200|300');
Val := Customer.GETRANGEMIN("No.");

This code causes a run-time error to occur.

Getrangemin could be used if there is a filter applied. So I think in your case you have to prefer flowfield.

You can create a flowfield in the Item table. Put a vendorfilter in this flowfield, use a constant of '>0' and select "min" method. In your form, filter the variable in your textbox with the related vendor and by this way you can show 4 result with one table field.

Regards.

COLUMNS !!!!

You can not use flowfields on columns in Navision, only on ROWS!

ooops

Then we could try a form design in which the lines and columns are reversed:D

I have been making the same mistake a lot lately, its easy to do. :oops:

I was going to write a comment/warning about the problems with MIN flowfields in SQL on 5.00 when realized it was not the question.

stryk wrote:

ufuk wrote:

ooops

ooooops, too

The whole post does not make sense, as it says about multiple Vendors

I would use Purchase Prices, and on a card style form maybe add a subform with these prices, but if you wanted them sorted by the lowest cost price, you would need to add a key to the Purchase Prices, this would also help in returning the lowest price on a report or in code.

Look at the Item Price code units for the code to return the Cost Price and copy and change this for your own function to get the lowest, by returning a record, where you have the Vendor No. and the cost Price.

David

The whole post does not make sense, as it says about multiple Vendors

I don't think so. If you do not filter by vendor then the flowfield will give a result of all vendors. If you want to filter by a certain vendor or vendor group, then it again works.

The point is you have 4 static fields, this is to limited and narrows options, Quotation1 to Quotation4, how do you know which Vendor is which, Item to Item, as David S said what happens if you have a 6th Vendor for that item?

What happens if Vendor 1 sells only in cases of 24 and Vendor 2 quotes price per unit?

Now David S pointed out, that you can use Purchase Prices ("I did read the post B.T.W.") and I pointed out one way to use them, card and subform, as many prices as you want, with the Unit of Measure, multiple pack sizes are no problem, and quantity breaks can be filled in.

Upgrades are easier, as you are only adding a key.

Some work with a new key, to sort by the Unit Cost

If I didn't understand wrong, here we are talking about 4 type of quote, not vendor. I understood something like that:

Lines: Item
Columns: Quote types
Header: Vendor Filter, Date filter etc.

So using a subform or anything else will not change the case. In all cases there will be 4 column which shows the minimum quote price received from all the vendors.

As I have already said, The whole post does not make sense, as it says about using multiple Vendors.

Now it could be a new table with Item No., Vendor No., Quote 1, Quote 2,Quote 3 and Quote 4.
Or it could be 4 fields on the Item Record, which makes it hard to have multiple Vendors, what about currencies, this has not been made clear.

All we can do is look at the post, and how the system would best deal with this, as we understand the problem.
Also when replying to the post we should take into consideration how the rest of the system works, there is no point having these fields in a new table or additional fields, if there is a way the standard system can deal with it.

If this is in a new table most Customers would want this price to default, if you added that Item on a Purchase Order for that Vendor, so you have to create your new fields or tables, add code to return the cost to the purchase line.

This is just "re-inventing the wheel" as they say, when there is a table and code that in this case is about a 90% fit as far as I can see, then I would point the poster in that direction, as best practice.

A good way to work with Navision is to ask first, how could I do this, what is the best approach, what is already there, if the customer changed the way they worked could my code restrict them?

I have heard the phrase "Its just a value I want it on the Sales Order", then it is required on the Shipment, Return Receipt, Invoice, Credit Memo tables, all the forms, oh and Customer Ledger etc:, then on a visit 6 months later find that the field is not used, as they did not realise there was a field there as standard that they could use.

The best one is "new field LCY only we don't use Currencies", weeks later "But we do have one large supplier who does!" #-o

So what seems a simple change has an impact on other area's, both in development time and additional cost to the customer.

8) Developers rush in where other fear to tread :shock:

Phew!