Getting second related value

[cinolas]

In a calculation field :

If a certain condition is true, I want to get the value of the first portal row (or first related record). That's pretty easy. If the condition is false, I want to get the value of the second related record (or second portal row).

How do I specify "Take the second related record" ? Without having to run a script.

Thanks

Nicolas

[Chuck]

You're probably going to need two relationships. One is your normal relationship that you view through a portal and the other links somehow to the second record instead.

The problem is how do you get that second relationship working? How you do it depends on how you create related records, so I'm going to give one example that might get you going.

Let's say you create a related record with a script. The related record has a field called "CreationOrder". The first related record gets this field set to 1, the second to 2. You do this by using the Max function to get the highest value so far in the related records and add 1 to it.

In your case, you want the first record most of the time but you want to second on occasion if a certain flag is set. In the related file you create a new calculation field set to something like:

CreationOrder & ParentID

In the parent file you create a similar field, but make it set to:

"2" & ParentID

Then you create a relationship between the files using these two fields as the match field. Now if you want the first record, you use your normal relationship and if you want the second you use this new relationship.

Chuck