Working on a solution, with in a repeating field i got the solution to give the lowest cost. now i need it to give me the vendor of the lowest cost.
i'm stumped. any help would greatly appreciated.
An ideal setup would have product pricing as it's own separate table with a sorted relationship to the parent product. That way you can have a portal with the records that always displays the cheapest product at the top.
Also, you are using dual currency, but unless you account for the conversion rate, then this will not be accurate when two numbers are close together. EG today $1 USD = $1.31 CAD, so $2 CAD would be cheaper than $1.75 USD.
To do what you want to do with repeating fields is a lot of work, you essentially have to walk through the repetitions of the repeating field, then get the corresponding repetition from your "vendor names"
does return the $2.00 in your example from a repeating field, but it doesn't return the position, so you would need a custom function like this one to find out which repetition matches your value:
That would turn into:
ValuePosition( List(Table::Cost) ; Min(Table::Cost) )
for returning which repetition matches your minimum value you are seeking.
And only then, would you be able to use GetRepetition() to get the corresponding value from your Vendor Names field:
GetRepetition( Table::Vendor Names ; ValuePosition( List(Table::Cost) ; Min(Table::Cost) ) )
One last issue you'll need to address is what to do if two vendors have the same price for the same product. In that case, what gets priority?
Retrieving data ...