Keeping track of sequence numbers

[Gabe]

I have a problem. I have sequence numbers that don't span the entire table.

I have a join table that joins parties to lawsuit file. Each party has a sequence number in the join table.

For example

say Suit number 1-0001-0030 has 4 party menbers

sequence number - party type - partyid

1 - plaintiff - 2454

1 - defendant - 1080

2 - defendant - 3456

As you can see, I have to keep track of the sequence of each plaintiff and defendant within the suit. They are independent for each suit number and party type. I cannot figure out how to set up a relationship that will allow me to access the dominant party (as in plaintiff with seq number 1).

Has anyone come across a problem like this?

[comment]

You could define a calculation field, let's call it cDominantID =

Case ( SequenceNumber = 1 ; SuitID )

Then a relationship from Suits to Parties based on

Suits::SuitID = Parties::cDominantID

will filter out all parties but the dominant ones.

Depending on what you need this for, you could also sort your existing relationship by SequenceNumber, and show a portal of 2 rows only (assuming there are always 2, and only 2, dominant parties).