1 Reply Latest reply on Nov 16, 2012 8:35 AM by philmodjunk

    Some assistance with relationships and portals?



      Some assistance with relationships and portals?


           This may be very simple, so apologies if the answer is staring me in the face.

           I have 2 tables:  one consists of account names, numbers and account owners.

           The other table is a table of people.  Some of the People records are account owners of multiple accounts (dept. heads)to which they can charge purchases,  and other people are simply account users (members of the dept) assigned to purchase on only one account number.

           I am trying to relate the 2 tables and created a field in my People table called Supervisor and related on that field to the Account Owner field in the Accounts table.

           My People table consists of  a handfull of records that are One- to-Many relationships (the dept heads) who have access to multiple purchasing accounts, and a larger number of One-to-One relationships (the department  members) who are generally 99% of the time are allowed to purchase only on one particular account.  Also, those only allowed to purchase on one account have expiration dates whereas the dept heads have no exipiration dates.

           I am trying to develop a purchasing interface where when they type in their name ( or select from a drop down list that comes from the People table), their associated purchasing account or multiple accounts come up depending on their status as dept head or dept member. (Ultimately I would also like to be able for those with multiple accounts to split the cost of the purchase among multiple accounts by percentages.)

           I tried using a portal In People but it brings in ALL accounts associated with a particular dept. for every record regardless of designation as dept head or dept member. Would some kind portal filter with a calculation make this work? Or perhaps using a portal is the wrong approach.

           Can anyone point me in the right direction?

           Many thanks.


        • 1. Re: Some assistance with relationships and portals?

               What you have sounds like a many to many relationship. A given account can be used by many people and some people can use many accounts.

               This is typically implemented in FileMaker or other relational database by using a third table to serve as a "join" table to link the two:


               People::__pkPeopleID = People_Account::_fkPeopleID
               Accounts::__AccountID = People_Account::_fkAccountID

               See this thread if the above notation is unfamiliar: Common Forum Relationship and Field Notations Explained

               With this set up, a portal to People_Account can be placed on a People layout to list all accounts to which that person is permitted to make charges. Fields from Accounts can be added to the portal row to fill in things like the account name. In similar manner, a portal to People_Account on an Accounts layout can be used to list all People assigned to each account and fields from People can be included in that portal.

               Here's a demo file illustrating the basic design set up for many to many relationships plus a few added layouts that illustrate more sophisticated approaches that are possible: