Max Text field

[kerrin]

Hiya,

I am trying to create a calculated field that will return a Text value from a related record based on the highest quantity.

EG

I have 3 fields Product, Qty and Price for two records as follows:

Cake 1 $10

Sugar 2 $5

If i do max(SelfRelatedbyInvoiceNo::Qty) in my field definition then each record will display a 2

But how would i display the word sugar instead?

Any help would be fantastic!

Regards,

Kerrin

[kerrin]

Ok, so i fixed it, but for anyone that was curious this is what i did:

I created a calc field name cMaxQty as follows:

max(SelfRelatedbyInvoiceNo::Qty)

Then i made a relationship(called MaxProd) From cMaxQty to SelfRelatedbyInvoiceNo::Qty

Finally i can put the related Product field on my form to display the product name or create another calc field like this.

MaxProd::Product

This works but if anyone can suggest better "programming practice" I would appreciate it.

Regards,

Kerrin

[PaulJ]

Cool solution kerrin!

I have done something a bit like this, but simply used a calculating constant field on both sides of the relationship. Then I specified to sort in descending order by the quantity.

If I use QuantitySort::Product I get the product with the highest quantity, but I can use the same relationship to place a portal on another layout with products listed in descending order by quantity. Plus I also use the same constant calc for several other relationships.

The outcome is the same as with your method, though.

[kerrin]

So simple yet ingenious.

I skinned my cat again.

My records already had the same invoice number so i made a relationship, sorted by qty descending.

Works great and now i can get rid of those extra field definitions....

Thanks

Akart