Go to Next Portal Row in a Loop

[mattpalmgren]

I have a DB called Quotes. When I receive a quote from a supplier, I create a record and enter a Quote Number (unique), a Part Number, and the Quoted Price. Let's say I receive 4 quotes from several suppliers for the same part, each quote with a different price. I want to be able to take the 4 quotes for that part number, take the highest quoted price, and enter it in a Highest Price field in each of the four records. Likewise, I want to take the lowest quoted price and enter it into a Lowest Price field for each of the four records.

I created a self-joined relationship based on the Part Number field, and created a portal. The record shows the part number, and the portal shows the four quotes that were received for that part number. The Quote number and the Quoted Price fields are displayed on each portal row.

I wrote a script that sorts the 4 records by Quoted Price with the highest first, goes to the first portal row and then its related record, grabs the price (which is the highest) and places it in a Highest Price global field. It then goes to the last portal row, and then to its related record, grabs the price (which is the lowest), and puts it in a Lowest Price global field. It then goes back to the first portal row and related record, and takes the Highest Price global field value and places it into the first record's High Price field, and does the same with the Lowest Price.

All of that works, but now I need to do the same for the second and third records. I thought I could create a loop that went to the next portal row and enter the values, go to the next portal row, enter the values, and continue until it gets to the last row. However, inside the loop, the go to next portal row command puts it in the first portal row and never into the next portal row. So I get an endless loop.

Does go to next portal row work within a loop?

[Chris Cain]

First off, yes, go to portal row definitely works within a loop. You could post your script here, perhaps we could figure out why you're getting stuck on the first row.

On the other hand, you might consider using calculations to get what you want, rather than scripting.

From Parts...

Max ( Quotes::price ) will get you the highest quoted price

Min ( Quotes::price ) will get you the lowest quoted price

GetNthRecord ( Quotes::price ; 2 ) will get you the second price in the (sorted) portal

HTH

Chris Cain

Extensitech, Inc.

[mattpalmgren]

Will Max and Min only look at the portal row values and not the found set of records? I simplified the example, in reality, I will have many records, with many different part numbers, and each record will have zero to many "duplicates" based on part numbers. So the script will have to sort by part number, go to the first record, see if the High/Low fields are empty, and if they are, look at the number of portal rows there are and populate them. Then it sill need to skip the next record(s) to find the next record with blank values, and run the Max/Min loop again.

Max/Min sounds like it can help me determine the Max/Min values, but I am not sure if the Max/Min will help navigating to the next row to populate to the portal rows between the max and min.

Script attached

Quotes_In.pdf

[Chris Cain]

If defined in Parts, min and max of a Quotes value will give you the highest and lowest values across the related Quotes.

Quote A for Part 11 is $5

Quote B for Part 11 is $3

Quote C for Part 11 is $15

Quote D for Part 11 is $11

On Part 11...

Max ( Quotes::Price ) = 15

Min ( Quotes::Price ) = 3

GetNthRecord ( Quotes::Price ; 2 ) (where the Quotes relationship is sorted by price, ascending) = 5

These calculations won't help you navigate around in the portals; they'll (potentially ) make doing so entirely unnecessary.

HTH

Cain

[Chris Cain]

Perhaps this will help...

Cain

MinMax.zip

[Chris Cain]

Actually, while I'm at it, here's a version with a script that loops through portal rows.

HTH

Cain

MinMax.zip

[mattpalmgren]

It just clicked... That is exactly what I needed, no need for the script or Go To Next Portal Row. Thanks so much!