Finding two people with specific roles (sales and client) on one transaction

Discussion created by on Dec 11, 2013
Latest reply on Dec 11, 2013 by Datagrace
  1. Transaction: TransID, PropertyAddress, Price, Closedate, etc
  2. Join Trans_Person_Role: ID, Role, TransID, PersonID
  3. Persons: PersonID, Name, Email, etc.



From a layout based on table 1, I have a list of transactions plus a portal from table 2 that lists role and person joined on TransID and PersonID

This works fine for setting up the initial transaction one at a time


What I need

From what table should I build the layout and how can I get:

  • PropertyAddress, Price, CloseDate, Role and Name Person1, Role and Name Person2


I've been working from the join table (thinking SQL), but I'm thinking that in FmPro I need to approach again from the transaction table with TWO filtered portals, each with one record from people that match the filter criteria in the join table.


Seems clunky. Is there a better way?