Jump to content
Sign in to follow this  
gjowilson

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

Recommended Posts

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

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • 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!
    • By amerioca
      Hi everybody,
       
      I recently amended a db from an old Lynda course, controlling shore excursions for river cruise ships. For my limited expertise (I am still a rookie at the very best) the construction is somewhat complex as there is many vessels, different routes, different charterers and consequently different shore excursion programs and prices. In some cases the shore excursion program of different vessels and operators is similar but not the same and so on.
      I have 7 different tables partly related (pls see graph attached):
       
      For every journey ::customers and ::trip are assigned a tour code and date (“tour code” is not unique as journeys repeat themselves during the season, “Tour Code” in conjunction with “Date” is unique)
      Every tour operator/vessel has many trips on every journey. All table occurences and fields are uniquely defined by unique id.
      Now I want to create an opening layout reflecting all relevant cruise information such as passenger list from ::customer and ::trip (shore excursions) for the specific journey, as well as other information in text fields.
      But I seem to be unable to get the right relationship and layout setups together. I tried all possible relations and layout setup combination but nothing worked so far.  
      On the same layout I was able to either retrieve the passenger list from ::customer for each journey or the offered shore excursion (trip) list from ::trip  but not both together on one layout.
       
      What is it, I am doing wrong? Any ideas are appreciated

    • By Randy Bennett
      Yes, I did say iPhone 5 -
      I have a small database i created that has a few portals on different layouts - Everything works fine on my Mac, fine on my iPhone X, even fine on my iPad mini
      However, I need it to also work on an old iPhone 5 and the portal data is just blank - nothing. In fact, I really ONLY need it to work on this old iPhone 5 (This particular database will go to a music festival to be used by several other people and I can't give them my iPhone) 
      I have another layout with different portals that are working on the iPhone 5 - but not this main screen. 
      I have made sure that all of my fields are small and inside the portal. - So, I am stuck as to why? BTW - It is Filemaker Go 16.0.4.456 (The latest version that I can install on an iPhone 5)

    • By Gismo12
      Hi All,
      I need to copy one field only from one layout (+- 200 records) and past all those records from that field into another layout.
      I have tried a few commands but does not produce the required result.
      Please could you advise on a script?
    • By cat traveller
      Hi everyone,
      on a file accessed via filemaker Go 17, I am using a scripted navigation to move records up and down in a portal that is inside a popover.
      This has worked in filemaker 12 like a charm and also works on a filemaker client. The records smoothly move up and down the list. 
      However in Filemaker Go, the portal follows every move of the script as if it is not aware of the freeze portal.
      EG the down button is to move the record one record further down inside the filtered and sorted portal (like so)
      This is the script.

      - freeze window and go to portal
      - go to first line of portal and loop through the portal until I hit the record to move
      - go to next line and give the position field the value of my record to be moved
      - go to line above and give the position field the value of my record +1
      - commit record and refresh portal via an x join 
      - go to last line of the portal 
      - go to the line of position of my record to be moved. 
       
      Would anyone be aware of this issue or know a workaround to stop the portal from flickering?
       
      Thanks so much
       
×
×
  • Create New...

Important Information

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