Help with creating a join table -- many-to-many relationships

[cdefeciani]

It has been a long time since I've done any Filemaker development so am asking for some assistance.

 

I have a "People" table

I have an "Accounts" table

The people in the people table can be assigned many accounts

Accounts can have multiple people affiliated with them

 

I decided I needed a join table so I created a "JOIN_People_Accounts" table

 

Ultimately when a person is placing an order, I will need a pop up list of accounts from which they can choose. Each person can have a different set of accounts.

 

I think I understand I need a portal to do this, however I'm not sure how to get the data to populate the portal.

 

People table has "Unique_ID_#" field

Account table has "Account #" field

JOIN table has both

 

Not sure if I've created the relationships properly.

 

I am stuck here.  Help anyone?

[LaRetta]

Your structure should look like this:

 

If you would like the simple sample file, I can attach it as well.

[eos]

I am stuck here.  Help anyone?

 

For the start, you simply need to “Allow creation of related records” for JOIN in the relationship between People and JOIN. This will display a spare row at the end of the portal; adding data to it will automatically create a related record with the correct key(s) set (the personID, in this case).

 

Create a value list of Accounts, defined as 1. field: accountID (the primary key), 2. field: accountNo, “Show only data from second field“). Put the accountID field from JOIN into the portal and format it with the value list. Selecting an accountNo from this list will actually insert the associated accountID.

 

Note that you need to select control type “popup” for the field for this to work properly; a drop-down list would show the actual accountID instead of the accountNo.

 

There are more sophisticated, usually scripted methods to add accounts (which also allow for better plausibility and other checks), but this should get you started.

 

Speaking of which …

 

People table has "Unique_ID_#" field

Account table has "Account #" field

JOIN table has both

 

In case you haven't done so: make sure that you define an actual auto-entered, meaningless, “artificial” serial ID (an auto-incrementing number, or a UUID) as the primary key in all tables, and use that to define relationships. Do not use the account numbers that are used and displayed in your official transactions and documents; these are business, “natural” data; they are beyond your control and should therefore not be used as a key.

[cdefeciani]

Your structure should look like this:

 

If you would like the simple sample file, I can attach it as well.

 

Thank you! I constructed my tables in this way.

 

Now I am confused about creating the portal. I inserted a portal into my People table with Acct Id # Acct # and Acct Name fields but no data is populating. Not sure what I am doing incorrectly.

 

 

For the start, you simply need to “Allow creation of related records” for JOIN in the relationship between People and JOIN. This will display a spare row at the end of the portal; adding data to it will automatically create a related record with the correct key(s) set (the personID, in this case).

 

Create a value list of Accounts, defined as 1. field: accountID (the primary key), 2. field: accountNo, “Show only data from second field“). Put the accountID field from JOIN into the portal and format it with the value list. Selecting an accountNo from this list will actually insert the associated accountID.

 

Note that you need to select control type “popup” for the field for this to work properly; a drop-down list would show the actual accountID instead of the accountNo.

 

There are more sophisticated, usually scripted methods to add accounts (which also allow for better plausibility and other checks), but this should get you started.

 

Speaking of which …

 

 

In case you haven't done so: make sure that you define an actual auto-entered, meaningless, “artificial” serial ID (an auto-incrementing number, or a UUID) as the primary key in all tables, and use that to define relationships. Do not use the account numbers that are used and displayed in your official transactions and documents; these are business, “natural” data; they are beyond your control and should therefore not be used as a key.

 

Thank you for the reply.  I'm very rusty with Filemaker and am not exactly following you.  I have been trying to insert a 5-line portal into the people table with the fields account # and account name.  Are you saying I also need to inset the account unique ID field (which I did create after I read your reply suggesting not using the actual account numbers for the key).

[eos]

Are you saying I also need to inset the account unique ID field

 

Yes; the foreign personID is set automatically into JOIN (People__Accounts), because that's what the People --< JOIN relationship is based on; but you need to specify the account that the new portal record represents. 

 

PS: Please don't quote the entire post(s) you're referring to.

 

EDIT: See attached file for a (very) simple example.

PeopleAccounts_eos.fmp12.zip

[LaRetta]

Hi cdefeciani,

 

When you have 'allow creation of related' checked between two relationships in the graph, FM will automatically insert the parent key into the child record when you type into any child field (in the portal) except that key.  Do not put the PersonID from the People_Accounts in the portal at all.  It will be handled for you.

 

I've expanded the example a bit, showing how you can add records into the join table but also, how you can add records into two tables simultaneously with NO added complexity, taking advantage of FileMaker's need to resolve the relationship.

 

So when you are on a parent record (in this case People) and you realise you need to add an Account FIRST because you want to add that account onto your People_Accounts portal and the account does not yet exist, you can do it simply without script.  This is one of my favourite techniques because of its simplicity.  It uses FM's standard backfill behaviour which has existed in FM now since version 7 so although it is little-documented, it is a solid technique.

 

corrected validation

PeopleAccounts_MOD2.fmp12.zip

Edited December 19, 2014 by LaRetta

[cdefeciani]

Thank you all for the input. I've been away from this project for a few weeks and now am back to it.  

 

I'm not sure I explained myself correctly.

 

I have this working the way you both instructed me, but it is not exactly how I need it to be.

 

When a person places an order, they select their name from a drop down list.  

 

They then need to select their account but the only accounts they should see to select from are those "owned" by their supervisor - not all accounts.

The way it's working now, the account selection they have to choose from included ALL accounts.  I need them to only have a selection of the accounts affiliated with their particular supervisor.

 

Any additional assistance greatly appreciated.

 

Happy New Year!

[eos]

Happy New Year!

 

Same to you!

 

I have this working the way you both instructed me, but it is not exactly how I need it to be.

[…]

they should see to select from are those "owned" by their supervisor - not all accounts

 

Small wonder it doesn't work as required – you never mentioned a “supervisor” … 

 

Anyway, for this you work you obviously need to assign a supervisor to each person (or at least each person who needs to use an account).

 

Add a foreign key field to the People table (like id_supervisor, fk_supervisorID, or whatever your notation of choice is). Simplest way then (not necessarily the optimal one) is to create a value list of people (1. field: ID, 2. field: name, show second only) and format the foreign key field as popup with that value list. 

 

Now create a new relationship

 

People::supervisorID = PeopleAccounts_forSupervisor::personID (a new TO of PeopleAccounts)

 

and define your VL as using fields from PeopleAccounts_forSupervisor, and starting from People

 

Same principle as shown before, just using a different relationship.