Help creating efficient data entry layout in a many to many relationship

[gjowilson]

Hello,

 

I'm a relative newbie to databases and Filemaker - so please excuse me if there is a really easy answer to this that I am not aware of.

 

So... here is my scenario:

 

Purpose of database: to link together people, organisations, evidence (i.e., pieces of information relating to people, organisations, or the relationships between people-org, people-people, org-org). 

 

I won't bring all the tables into this discussion as one the problem has been solved in one area, it is solved everywhere. So, taking this particular instance:

 

My  tables are:

People:      __pkPersonID, title, firstName, surname, AKA, fullName, DOB, DOD, information, notes

Organisation:      __pkOrgID, orgName, founded, terminated, notes

JOIN_people_organisation:     __pkJOINtableID, _fkPersonID, _fkOrgID, relationshipDescription, notes

 

Note - the JOIN table is there because there will be many-to-many relationships

 

My situation is this. I will have a large number of people, a large number of organisations and the need to continually add new people and new organisations - plus the ability to add either/or during the creation of a new relationship between them.

 

Here's some fake data (not relevant to my database)

 

People:

1, John Lennon

2, Paul McCartney

3, Ringo Starr

4, Mick Jagger

5, Kurt Cobain

6, Courtney Love

7, Alan Klein

8, Bill Geffen

 

Organisations

1, The Beatles

2, The Rolling Stones

 

I create a layout based on the JOIN table with the aim of creating relationships. This is where my problem starts.

 

I add my first relationship, an easy one - John Lennon, The Beatles

 

PROBLEM 1: how do I remember the _fkPersonID that relates to John Lennon without leaving the layout and noting it down? Is there a way of creating a live 'search' field within the layout that lets me search for a person to see if (a) they already exist and ( if they do, to then populate the relevant fields.

 

PROBLEM 2: I'm looking at this from the reverse. I decide to add George Harrison to my database. So, I assume an easy way to do this would be to go to the JOIN table layout, search for The Beatles, populate one field with the relevant _fkOrgID (associated with The Beatles) and then add, presumably in another set of related fields or a portal, George Harrison as a new record in the People table that relates to this?

 

PROBLEM3: For example. Say I was looking at a new type of relationship. Still an organisation, but say this was 'association of 60's legendary musicians'. So, I add this to the Organisation table. I then go to the JOIN layout and 'search' for this, populate the field with the relevant foreign key. Then I want to start adding people to this relationship.

... search for Paul McCartney, add him as a relationship

... search for Mick Jagger, add him..

... search for Dave Crosby.. he's not there, so ADD Dave Crosby

... ADD Bob Dylan

... search for Ringo Starr, add him.

 

Etc. You can see why I am looking for this solution as it makes the data entry side of things much more useful. Especially if other people add to the database to build more records and more relationships.

 

Any help would be gratefully appreciated. I've tried all sorts of key words to find an answer on google. Looked through Lynda.com, missing manuals etc. 

 

Thanks in advance