- Transaction: TransID, PropertyAddress, Price, Closedate, etc
- Join Trans_Person_Role: ID, Role, TransID, PersonID
- 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?