pulling related values based on other related vals

[Gabe]

Does anyone know how I should set up a relationship that will allow me to display only specific related data? I have a many to many relationship with a join table linking to master tables. Within the join table I have a field describing the type of relationship, and a sequence field which sets which records have priority over the others in the same relationship.

On the main display, I want to show two records with the two different possible relationship types. For example, in my system, I have a lawsuits table and a parties table. Linking them is a table which contains which parties belong to which suits, along with whether they are a plaintiff or a defendant.

On the lawsuits layout, I want to display the name of the first plaintiff and the first defendant, but only one of each, even though there can be multiple plaintiffs and defendants.

Right now I have a sequence field which prioritizes the parties, with a value of 1 signifying the primary plaintiff and defendant.

I can't figure out how to use those two fields, the type and the sequence, to reference the particular records I am looking for.

can anyone help?

[-Queue-]

Are you using a portal? If so, define your portal to sort by the priority field, ascending, and to display only two rows, with no scroll bar.

[Ender]

I'd go with two Table Occurences of the Join table. One for the plaintiffs, one for the defendants. If the relationship is sorted (ascending) by that Sequence field, then just putting the related fields on the layout should work.

So the relationships would look like this:

Lawsuit<->Defendant =

Lawsuit::LawsuitID = Defendant::LawsuitID

Lawsuit::gDefendant = Defendant::Relationship Type

Lawsuit<->Plaintiff =

Lawsuit::LawsuitID = Plaintiff::LawsuitID

Lawsuit::gPlaintiff = Plaintiff::Relationship Type

So here we need two additional globals text fields in Lawsuit to act as parent keys for the filtered relationships. Just insert "Plaintiff" and "Defendant" (or whatever you're using to distinguish these in your join table,) into these fields.

[Gabe]

thanks for your suggestions. I didnt want to use a portal for the display, but the idea from Q led me to the idea to use two single row, single field portals set up and sorted to display the values I was looking for. It was a lot easier than defining new relationships.