Find Minimum?

IrishGiri

Hi,

I have a form in which I have 4 columns called QuoteA, QuoteB, QuoteC and QuoteD. For each product in inventory, this information shows the cost of producing the item quoted by a vendor. Multiple vendors may supply us with quote information.

I would like to find the minimum non-zero quotation for all vendors. The minimum quote for each vendor is the minimum of its Quote A, B C and D. I want to show the minimum quote for all vendors in a text box under the form.

What is the most efficient way of finding this minimum form these four columns of information? CALCFIELD?.....

Thx, Paul.

stryk

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.

David_Singleton

IrishGiri wrote:

Hi,

I have a form in which I have 4 columns called QuoteA, QuoteB, QuoteC and QuoteD. For each product in inventory, this information shows the cost of producing the item quoted by a vendor. Multiple vendors may supply us with quote information.

I would like to find the minimum non-zero quotation for all vendors. The minimum quote for each vendor is the minimum of its Quote A, B C and D. I want to show the minimum quote for all vendors in a text box under the form.

What is the most efficient way of finding this minimum form these four columns of information? CALCFIELD?.....

Thx, Paul.

Paul, your problem is that the design from the beginning is ... can I say ... wrong.

You need these values stored in a separate table. Preferably in Table 7012 Purchase Price. Even if you need to make minor changes to that table, its much better than having 5 separate fields in the item card. What happens when they decide they need 6 Vendor Quotes?

ufuk

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.

David_Singleton

to Jorg and ufuk

IrishGiri wrote:

... I have 4 columns called QuoteA, QuoteB, QuoteC and QuoteD. ...

COLUMNS !!!!

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

ufuk

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

stryk

ufuk wrote:

ooops

ooooops, too

David_Singleton

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

ufuk

In fact we can use flowfields in rows.

Create 4 variable, filter the flowfield by vendor and quote type then assign value to flowfield. Continue the filtering for the other variables.
(You have to add a quote type filter in the flowfield)

David_Cox

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

David_Singleton

David Cox wrote:

.. I would use Purchase Prices ...

Hmm that sounds like a good idea

David Singleton wrote:

...You need these values stored in a separate table. Preferably in Table 7012 Purchase Price. ...

I don't think many people have rad the entire thread here.

ufuk

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.

David_Cox

ufuk wrote:

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

ufuk

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.

David_Singleton

Irishgiri has left the building... 8)



No point in discussing this unless he wants to provide more information.

David_Cox

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!

ufuk

Irishgiri has left the building...

No point in discussing this unless he wants to provide more information.

Without enough info this discussion seems fruitless for me too