We have reset all users FileMaker related profile fields. Please take the opportunity to update your information,  this will provide background to members whom read your posts. Click here.

Jump to content
Eli Walker

What to do when a child record has multiple parent records of same table

Recommended Posts

Eli Walker    0

I'm working on a database to store/organise data pertaining to Radio/GPS collared cheetahs (e.g. movements, kills they've made, etc.). Within the database, my main table is 'Released Cheetahs' which contains all the information about an individual cheetah and their local ID number, which is called 'AJU#'. AJU# is the field relating 'Released Cheetahs' to the other main tables within the database (Fix Data, Kills, Tracking Sessions, Parturitions, Releases, Captures, etc.).

Some of these cheetahs (and thus the records within 'Released Cheetahs') are in groups of a few individuals, which is delineated by 'Group Name'. My problem is that when I make a entry into one of the other tables, let's say 'Kills' (which contains info about the prey they have successfully hunted), with my current design I would need to duplicate this record for every cheetah within the group, but I would rather be able to just list all of the cheetahs (i.e. AJU#s) within a single field of the 'Kills' table so that there is only one entry per kill.

So, my question: What would be the best way of tying each kill record (single 'child' record) back to each of the cheetahs (multiple 'parent' records) it pertains to? I know I could just look at this from cheetah group rather than individual, but even though there is a group each kill record does not always pertain to each cheetah within a given group. I have tried playing around with repeating fields (e.g. using a repeating AJU# field within the Kills table) but I can't quite seem to get this to work as hoped. Any advice is most welcome!

I've attached an image of my relationship graph in case that helps.

Screen Shot 2015-11-07 at 5.16.45 PM.png

Share this post


Link to post
Share on other sites
comment    1,356

By your description, the relationship between Cheetahs and Kills is many-to-many. The "proper" way to resolve a many-to-many relationship is by using a third join table. However, Filemaker provides an alternative in the form of a multi-key field. This is very easy implement (all you need is a checkbox field in Kills to select the IDs of the cheetahs participating in the kill) - but has some limitations when producing detailed reports.

Share this post


Link to post
Share on other sites
Eli Walker    0

Hi, thanks for the info. Could you explain the 'proper' way to which you refer? If there is a better way of handling this, then I would definitely like to do it that way. 

Also, I have the multi-key field working fine but unfortunately have a couple of lookups/calculations that run off of that field. These lookups/calculations only return a value for the first value in the multi-key field but I need the lookup to run for every value in the multi-key field. Any advice on how to accomplish this? I've been scouring the forums but haven't really found anything helpful.

Share this post


Link to post
Share on other sites
LaRetta    470
On November 7, 2015 at 10:58:45 AM, comment said:

By your description, the relationship between Cheetahs and Kills is many-to-many. The "proper" way to resolve a many-to-many relationship is by using a third join table

Hi Eli,

A join table would sit between Cheetahs and Kills.  It would hold the primary ID from Cheetahs AND the primary ID from Kills.  This resolves the many-to-many issue.  

Here is an example of join - just change Invoices to Cheetahs and change Products to Kills.

Join tables

The 'line items' table is what would be your new join table.  :-)

  • Like 1

Share this post


Link to post
Share on other sites
comment    1,356
8 hours ago, Eli Walker said:

I have the multi-key field working fine but unfortunately have a couple of lookups/calculations that run off of that field.

Can you explain in more detail the "lookups/calculations" thing?

Share this post


Link to post
Share on other sites
Eli Walker    0

In the Kills table (and most other tables outside of Released Cheetahs) the AJU# for a new record is a lookup based on the relationship to Released Cheetahs. So upon data entry, the AJU# is populated automatically based upon the cheetah name that is entered in the Name field (each cheetah has a unique AJU#). So, if I have multiple cheetah names within the Name field of a single record (by using a checkbox), how do I get the AJU#'s for each of those names to auto-enter/calculate within the same single record? 

Edited by Eli Walker

Share this post


Link to post
Share on other sites
Eli Walker    0

Well I think I understand the join table concept well now and I believe it will work for me really well (I'll just have to figure out how it will work when importing data). My question however is how do reports function across this sort of join? I've looked everywhere for information about reporting with join tables but I can't find anything... Any help on this?

Share this post


Link to post
Share on other sites
comment    1,356
4 hours ago, Eli Walker said:

So, if I have multiple cheetah names within the Name field of a single record (by using a checkbox), how do I get the AJU#'s for each of those names to auto-enter/calculate within the same single record? 

I would use a value list that uses values from the AJU# field (in the Cheetahs table), also displaying the Name field. You can set this up so than only the Name field is shown. That way users think they are selecting names, but the field actually contains the IDs.

You should consider using the same for selecting the cheetah in the other child tables as well.

 

2 hours ago, Eli Walker said:

My question however is how do reports function across this sort of join?

Normally you would find the records you want to report upon (e.g. within some date range), sort them the way you want to report them and present them in a layout with sub-summary part/s. For example, sort the join records by the AJU# field, and use a summary field counting the records to display the number of kills for each cheetah.

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites
Eli Walker    0
On November 17, 2015 at 1:36:15 PM, comment said:

I would use a value list that uses values from the AJU# field (in the Cheetahs table), also displaying the Name field. You can set this up so than only the Name field is shown. That way users think they are selecting names, but the field actually contains the IDs.

You should consider using the same for selecting the cheetah in the other child tables as well.

 

Okay, I've done this before so I know how it works. But are you suggesting that I don't use the Name field at all in any child tables but rather just the ID? If so I guess it makes sense, as long as Name is the value shown

 

On November 17, 2015 at 1:36:15 PM, comment said:

Normally you would find the records you want to report upon (e.g. within some date range), sort them the way you want to report them and present them in a layout with sub-summary part/s. For example, sort the join records by the AJU# field, and use a summary field counting the records to display the number of kills for each cheetah.

Okay, so just using the records within the join table to build reports? Rather than the table that has the actual data?

Share this post


Link to post
Share on other sites
comment    1,356
1 hour ago, Eli Walker said:

But are you suggesting that I don't use the Name field at all in any child tables but rather just the ID?

Yes. This will allow you to change the name in one place only, without breaking existing links.

 

1 hour ago, Eli Walker said:

Okay, so just using the records within the join table to build reports? Rather than the table that has the actual data?

Yes. Although in many cases the join table will have some data too.

 

 

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites

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 123
      Hey,
      I started working with FileMaker a while ago and so far it's been pretty fun and fairly easy to learn because I worked with MySQL in the past. However, there are still a few questions I have. I'm used to having complete freedom when it comes to databases, because in Python or C++ for instance I would simply write my own queries and get whatever data I need, no matter if a database is related to another or not. Anyway, the flexibility is still what causes me some problems. I have the following problem.
      I'm working on an ERP (Enterprise Resource Planning) software for the owner of several restaurants. I need to have the following tables to start with:
      Restaurants Employees Jobs Visa Health Insurances Meetings Now, the person using the solution needs to be able to create as many restaurants, employees, jobs and meetings as they want and assign them to each other. For instance, Restaurant A is going to have 5 employees, Employee A, B, C, D, E and so on, and all these employees will have one or multiple (this is really important) jobs assigned to them. This process has to be flexible, so I can assign an employee to whichever restaurant and job I choose. You also need to be able to assign one or more visa to a job (and the assigned employee), the same goes for one or more health insurances. The next problem is about meetings. Each restaurant needs to be able to have meetings every once in while, which need the ability to keep track of employees that attend the meeting. So you should be able to select employees form a portal or card window (in FM16 for instance) and they will be added to another table MeetingAttendees or something similar. Again, this needs to be flexible so every restaurant can create meetings and check which employee is present. (I need to be able to create a layout to display the meetings and who has been present, if possible also to see who wasn't).
      Normally I would simply take the IDs and create tables that join these iDs together, however I'm not able to recreate this in FileMaker, if one relationship works, the other breaks for some reason. If anyone could give me a simple layout of what my database needs to look like, or if possible and not too much to ask an example in FileMaker. Your help is much appreciated.
      Thanks in advance,
      Mike
    • By mrmacmusic
      I would really appreciate some input on this, as I think I've got lost along the way trying to figure this out and whilst I have a working solution it seems somewhat convoluted (and is a bit sluggish to update)... 
      I needed to create a report from the context of contacts so that this can display sub-summary data (sorted by contact::area and contact::category) of related survey data for each contact from the single most recent survey record (within a specified report date range) as well as count how many survey events took place in that date range. Because of the context, I concluded – perhaps wrongly – that I couldn't simply put the required summary fields in the surveys table and instead needed to pull the appropriate survey data for each contact into the parent record, then add the summary fields in that table.
      To do this I've added a bunch of un-stored calculation fields, namely:
      1) wasOpen – since I'm working with all contacts records, this evaluates whether the contact was added after the reportEnd date or closed before the reportStart data and should therefore be included (1) or excluded (0)
      2) mostRecentSurvey – calculated using ExecuteSQL( "SELECT ''||surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate<=? ORDER by surveyDate DESC FETCH FIRST ROW ONLY" ; "¶" ; "" ; contacts::contact_ID ; globals::reportEnd ) and used to create a relationship to a single survey record – contactID = fk_contactID AND mostRecentSurvey = surveys::surveyDate
      3) survey_Q1 – answer to first question in related survey multiplied by the value of wasOpen so will only have a value if the contact is being included in the report
      4) surveys_carried_out – calculated using ExecuteSQL( "SELECT COUNT surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate>=? AND surveyDate<=?" ; "¶" ; "" ; contacts::contact_ID ; globals::reportStart ; globals::reportEnd  ) as this needs a figure for each contact whether it is included or excluded from the report.
      5) summary field, total of survey_Q1
      6) summary field, total of surveys_carried_out
      etc (There are obviously other survey questions/dates being pulled in to the parent and summarised in the same way)
      So when I set the global reportStart/reportEnd dates, goto the report layout, find all contacts and sort by contacts::area and contacts::category I get the required sub-summarised results, but I just feel I'm missing something and there is perhaps a better, more efficient approach... 
      Thoughts?
    • By DreadDamsel
      Hi. I'm using FMPro 12 Advanced.
      Creating a database for our community centre and I need to add a membership number to contacts - but not all of them.
      I've created a separate table for Membership numbers (as not all contacts will have one, so serial numbers aren't appropriate).
      Each membership has a number of details fields.
      It's probably a REALLY logical and stupid problem I'm batting against - but I need to:
      Select a contact>go to their details>add a membership number (go to a floating window layout, enter the details to the serial-number-derived membership) and have the details including the number show up in a tab on the contact's details layout.
      I've created a relationship between my contacts file and my membership file via the Contact_ID>Contact_ID_fk and have played around with other relationships. 
      I've also created a simple script from a button on the Contacts membership tab to go to the Membership Layout window and create a new record. But it keeps creating a new record in the Contacts, not Membership - even though the relationship only has to create new records in the memberships side.
      I'm now running around in circles - please can anyone help me out here?????
    • By Darg
      Hello guys! I wish to everyone a happy new year.
      I am new to FM. I'm trying to create a Diary. I have the following tables: 
      Diary --< StudyLog >-- StudyPart >-- StudyContent
      In my diary Layout, I have a portal. Through this portal I want to:
      Create new records to StudyLog, StudyPart, StudyContent IF there are existing records to StudyPart or StudyContent I want to keep those the same! Example: I create Day: "1/1/17". Through the Day's portal, I create
      StudyLog: "Log #: 1, Time: 9:00, Duration: 1hr"
      StudyPart: "StudyPart: Chapter 1, Chapter Title: Coulomb's Law" 
      StudyContent: "Title: Quantum Physics, Author: ..., Type: Book"
      In the same Day, through the portal I want to create:
      New StudyLog
      Same StudyPart
      Same StudyContent
      When I write the StudyPart and it exists, I want it to auto complete. Same for the StudyContent.
       
      I tried to make it as simple and compact as possible. If you need pictures or anything else, please tell me. I would appreciate it if anyone could help.
      Thank you so much!
      Again, happy new year!


×