Jump to content

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


This topic is 2037 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 (B) 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

 

 

Link to post
Share on other sites

This topic is 2037 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Tony Diaz
      The tables.
      Items::ID
      Items::Item
      Items::Publisher
      Items::Date
       
      The Genre data is already arranged like this:
      Genre::ID (Unique Key)
      Genre::Name
      Genre::Category_ID
      Genre::Category

      The Platform data is 'simple'. ID and Name. But each item could be multiple platforms.  (Example #1 vs. Example #2)
      Platforms::ID
      Platforms::Name
      An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples.

      It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selection from. Most have 4-6 of them.

      Example 1: Items::Table on the left, Genre::Table on the right, with some Genre Categories (Genre, Perspective, Pacing, Gameplay, Interface, Setting) and their sub-options.
      This one has just one sub-option per category.

      Example 2:  The Gameplay Genre Category has two sub-options related to it.



      Example Genre Table content:

       
      Just cracking the surface on One to One and One to Many relationships, I don't think this scenario is quite covered this way.

      Would each of those Genre Categories be portals showing only their related category ID?

      I presume that I would add fields to the Items::Table so I can pull related records:
      Items::Platform_IDfk
      Items::Genre_IDfk
      Items::Category_IDfk
      But those only support one relationship.
      Would I make value lists from those Genre Categories and Platforms and set them as tick box fields?
       
    • By stan111
      Gents,
      I use self portal on my Clients layout to serve two things:
      1. display all the records and
      2. quick navigate among them. 
      This portal is not displayed in Webdirect. 
      Is it possible to make it work?
    • By Guy_Smith
      This should be easy, but my brain just won't cooperate!
      I'm moving a bunch of stuff and want to inventory what is in each moving container.  I have a very simple parent-child relationship with the parent record having the container number and category of parts/equipment/supplies and the child records describe each piece of equipment in the container.  I have a portal with the child records showing for each container, but want to put anatto-entered item number for each piece that has the container number followed by a dash and then followed by a serialized number for each item in that particular container.  For example, I would have container 1 labeled "Glassware" and Container 2 labeled "Chemicals".  In Container 1's portal I'd like to see:
      Item    Description          Qty
      1-1      Beakers, 500ml    4
      1-2      Beakers, 250 ml   2
      and in Container 2's portal I'd like to see:
      Item    Description          Qty
      2-1      Potassium           4 g
      2-2      Sodium                26 g
      I'd like to auto-fill the item numbers, but can't figure out how to reset the serialization for each parent record.
      Any help is greatly appreciated.
      Thanks and keep yourselves safe!
       
    • By Answers
      We have a layout that has multiple tab panels, I want to add a field in the same place in all the tab panels at once without clicking on each panel. It's probably simple but I can't seem to figure it out.
      Thank you for your help in advance!
      Caroline
    • By Calvin Tomm
      In list view I want to be able to "grab" an object so I can move the record up or down in the found set. Is there a way to do this? It would be just like how an item on an list in an iPhone gets moved up or down. For example, on a play list in Music, I can change the order of songs to be played.
      Also, I would like to have this capability when I move my solution over to FileMaker Go
      Thanks!
  • Who Viewed the Topic

    5 members have viewed this topic:
    fileman922  millmaine  3DSteve  TomEkberg  moriya0238 

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.