1 Reply Latest reply on Dec 11, 2013 8:01 PM by Datagrace

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

      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?




        • 1. Re: Finding two people with specific roles (sales and client) on one transaction

          I'm not following what you are trying to find, i.e, what would be the WHERE clause if this were a SQL statement. I think you want the fields you list (PropertyAddress, Price, etc.) for Mary Brown as client and Jack Smith as agent, but I'm not sure. Could you write a SQL statement, using pseudo code if that would make it clearer, that does what you want?


          Portals are really graphical representations, not schematic objects, and may not be your friend here. A filtered portal does not filter the relationship, but only what is displayed in the portal (and it can be be pretty slow against a fair sized data set). So actions using a filtered portal may not return the results you expect.


          John Weinshel