Join table portal within portal

[ehwalker]

On November 20, 2015 at 1:01:41 AM, comment said:

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

Ah, okay that makes sense! Thanks so much for all your advice.

Okay, my next issue is as follows....

So I have a layout for 'Tracking Sessions' which contains details of each time someone went out to find a particular individual/group of cheetahs. Within this layout, I have a Tab that contains different portals to different tables of information that could be (but not always) collected during a specific tracking session (e.g. kills found, supplemental feeding, and 'Tracking Notes'). Now, because of the Join table how can I set these portals up to allow for record creation in these child tables while also creating the necessary records for the join tables? I've included a screenshot of the layout. Everything under 'Kill Details' is contained within a single portal row, as there is usually only a single kill during a single tracking session (though I guess in theory there could be more than one at some point in the future). From my experience and what I've read, you can't place a portal within a portal so how do I solve this issue while maintaining the current format (or if there is a better way of doing this, I'm all ears!) I've also included an updated screenshot of my relationship table, so you can see where tracking sessions lies in relation to kills and cheetahs.

 

[ehwalker]

If anyone has any advice on this, I'd really appreciate it. Thanks!

[LaRetta]

On November 20, 2015 at 6:27:23 AM, Eli Walker said:

Okay, my next issue is as follows....

Hi Eli,

Each new issue should be a separate thread.  Otherwise it seems the person who responds to your first issue is captured to assist on the next question and they may not have the time, inclination, interest or desire to assist on the next one.  Further, by having different issues as different threads, you can name the subject more closely to the problem at hand and then others with same issues can search and find them much easier.

I suggest that you start a new thread for this new issue and not burden the person who helped you, with helping you further with multiple different issues.  Once you have started your new thread, ask a moderator if they can remove these last three posts to clean this up.  Make sense?  

Edited November 25, 2015 by LaRetta

[ehwalker]

1 hour ago, LaRetta said:

Hi Eli,

Each new issue should be a separate thread.  Otherwise it seems the person who responds to your first issue is captured to assist on the next question and they may not have the time, inclination, interest or desire to assist on the next one.  Further, by having different issues as different threads, you can name the subject more closely to the problem at hand and then others with same issues can search and find them much easier.

I suggest that you start a new thread for this new issue and not burden the person who helped you, with helping you further with multiple different issues.  Once you have started your new thread, ask a moderator if they can remove these last three posts to clean this up.  Make sense?  

Yes of course, my apologies.

[ehwalker]

<bump> I understand that generally adding a portal within another portal isn't possible. I've thought of a few ways around this, but I think they are all rather clunky and I had hoped that someone had a streamline/more sophisticated way of handling this. Thanks again!!!

[Wim Decorte]

I don't think you explained the issue very well.   I understand that in your screenshot everything under "Kill details" is actually a portal row, and that you'd ideally want to put a portal within that area.   What I don't get from your description is what data would be in that new portal?  And how many records related to the kill would be typically in that portal if you could have it?

[Charity]

You might think about a CheetahSightings table.  Since not all Cheetahs that are spotted during a Trip engage in the kill but you still want to enter information about all Cheetahs you see such as supplemental feed, Cheetah condition, Cheetah injured.  From CheetahSightings which is the child of multiple to parent of a single Tracking session then a record for each Cheetah spotted during that session holds all that fields.

So your top layout should be either Tracking with CheetahSigntings in portal or filtered because you do not want to have to duplicate the Tracker, Farm etc on every record.  What I was told once in this kind of thing is that my perspective was incorrect and I think yours might be.   So CheetahSightings would be a tab panel  with portal.  A Kill record is created if the Cheetah in that session was involved in the kill.  I wrote up something similar for test while back to try and figure relationships.  A Kill seems a child of Tracking as a Tracking Session might have multiple Kills.  You will not create circular reference because from Kills you can use checkbox of Cheetahs which belong to the Group and it can be single field as multiple line I think.  Maybe table is best but you do not need to track how much of the kill was eaten by specific Cheetah, right?.

One thing which is not clear.  You said you go out on a Tracking of "individual/group".  When I read up on Cheetahs it says a Group is temporary and shifting and usually only happens among bachelors.  Do you mean group as in family?  I wonder because you mention parturitions which upon Googling is giving birth or something so that should be related to CheetahSightings and not necessarily Tracking because you might spot the same Cheetah in a single Tracking.  But would same Cheetah parturition multiple times with a single Tracking session?

Doh. I almost missed my point/s.  Assigning a Cheetah to a Group is okay but only so Trackers can select the Cheetahs in the Kill.  And what if they take down 2 bullalo in a single kill? That should be two records in the Kill table to enter data about the animal attacked.  My other point is that you can not enter specific data about a Cheetah in a Tracking Session because .  well I lost myself but I hope that helps.  And also what Wim says .

Oh I do not really know but this is what seems mostly clear.

My point about Groups is that you need to allow additional cheetahs because the groups can change so entering the group name into Cheetahs released might be too hard coded but if CheetahsReleased Group field is on the layout then if a cheetah is spotted that has changed groups you can just change it there.  if you mean groups as in families maybe forget all this part because that is hard coded for sure.

I have class tomorrow so if you have questions about what I said it will have to wait until tomorrow night.

[ehwalker]

3 hours ago, Wim Decorte said:

I don't think you explained the issue very well.   I understand that in your screenshot everything under "Kill details" is actually a portal row, and that you'd ideally want to put a portal within that area.   What I don't get from your description is what data would be in that new portal?  And how many records related to the kill would be typically in that portal if you could have it?

Ok, let me try to clarify. So under 'Kill Details' within the 'Tracking Session' layout, I would like to add a portal to the Cheetahs to Kills_Join table so that I can assign the cheetahs that participated to the kill, as not all cheetahs tracked during that given session would be in the kill. Does this make sense? I have attached a file that will (hopefully) illustrate what I need. There are also other tables that I will recreate this for (supplemental feedings, notes, etc..) but I thought that only one example was necessary. Let me know if something needs clarifying.

One thought that I had was just to have a button within each portal row that would open a new window to a layout with the relevant join table, but this seemed a bit clunky.

 

Charity, thanks for your advice! I hadn't added it in at the time of the screenshot, but the Tracking Session layout now has a portal to 'Cheetahs' so that I can assign the relevant cheetahs to each tracking session. Within a given tracking session, only one group would be tracked and I've added that field to reduce the number of options when adding records to the join table portal (e.g. so rather than scrolling through 100 cheetahs, FM limits the value list to the individuals within that group. Does this make sense? Also, at the moment I have it set up for only one potential kill per tracking session, but your example of a group killing two animals at once is technically possible so I may add another join table between tracking sessions and kills, but my issue is still the same. I would like to have a single layout in which I can enter all (or at least most of) the relevant data for a given tracking session.

CCF Releases Demo File.fmp12

[ehwalker]

<bump>

Any thoughts on this?

[Wim Decorte]

Still does not make sense to me.

The layout in your demo is based on "tracking session".  On the left you have a portal that shows the animals tracked.

Then on the right you have a 1-row portal for "kills".  And inside that portal you of a kill you want to see what animals were killed. That's the part that does not make sense.  Why the 1-row portal?  That would mean only one kill but there could be many animals killed so I would expect multiple kill records, one per animal.  So what I am missing is the significance of the kill record, and wanting to have only one per session.

Since you are already tracking the animals for the session, why not just add a check mark on the left portal to say "killed"?

[LaRetta]

On December 4, 2015 at 7:07 AM, Eli Walker said:

I would like to have a single layout in which I can enter all (or at least most of) the relevant data for a given tracking session.

Hi Eli,

I have two questions:

1) Regarding a single cheetah if it IS involved in a kill ... what do you need to track on that cheetah about that specific kill?  What parts of the kill it ate?  How aggressive it was during the kill?  Was it the lead?  How much did it consume?

2) Reporting:  Will you ever want to see a report of kills by Cheetah name, for example, during a year period, such as:

Mambulu
  Gazelles - 6
  Impala - 2
  Zebra - 1

Gall
  Gazelles - 1
  Hippo - 2

 

[LaRetta]

If you do not need to track anything specific about the cheetah/kill interaction then you can simply have a KillID in your CheetahTracking record (please see attached).

Forgive the looks of the file.  I prepared in in v11 so others can view it but it caused me grief because El Capitan does not like v11 at all so the file is quite rough.  Notice that the Cheetah tracking portal holds a KillID (text) multiline.  This is so multiple kills can take place on a single Tracking Session.  If your answer to either of the questions above is yes then you may wish to include a Cheetah Kills join table instead of using a multiline checkbox for kills.

As has been explained by Comment in your prior thread http://fmforums.com/topic/98460-what-to-do-when-a-child-record-has-multiple-parent-records-of-same-table/?do=findComment&comment=447640, the decision to include a join table versus a checkbox depends upon your needs; in this case, it is depending upon the answers to the two questions above.  If you go with checkbox for kills then it isn't very difficult to change later.  I believe Comment recommended inserting the CheetahID into the Kills table whereas I inserted the KillIDs into the Cheetah Tracking join table.  I had not seen his recommendation when I put this file together (which was end of November and I'm just finishing it now).  I would tend to go with Comment's recommendation but its location might depend more on the type of reporting required about the kills.

If you wish to report only on Cheetahs who were involved in Kills, you could still use the CheetahTracking table by simply searching for * in CheetahTracking::KillIDs before switching to your report.  And you will notice I agree completely with Wim and Charity ... it is easier to allow for multiple kills now rather than later so I included that option.  I can see the cheetahs taking down a weak momma gazelle and then eating its baby for snack later.

There is a difference between an entity diagram and a FileMaker graph.  You are quite right to establish an entity diagram ... it helps you get clarity on your needs, just as you are currently experiencing and working through  But if the functionality for data-entry feels off, you can add additional table occurrences to your graph and connect them as you wish to provide the needed UI and rarely do you get the structure exactly as you need it without further adjustments.

Anyway, I hope this moves you forward.  

Cheetahs.fp7.zip

[ehwalker]

On December 12, 2015 at 4:50 PM, Wim Decorte said:

Then on the right you have a 1-row portal for "kills".  And inside that portal you of a kill you want to see what animals were killed. That's the part that does not make sense.  Why the 1-row portal?  That would mean only one kill but there could be many animals killed so I would expect multiple kill records, one per animal.  So what I am missing is the significance of the kill record, and wanting to have only one per session.

Since you are already tracking the animals for the session, why not just add a check mark on the left portal to say "killed"?

Hi Wim, thanks for your response. It's not the cheetahs that are being killed. A 'Kill' is anytime a cheetah successfully hunts and kills a prey animal. That's why within the portal with the kill details, I'd like to have a portal to the cheetahs to kills join table so that I could create a new join record for every cheetah that participated in the hunt (in order to individually link the kill to each relevant cheetah). Does this make sense? And yes, at the moment since it's a single row I can only enter one kill per tracking session but I'm going to change that to allow more rows.

 

On December 15, 2015 at 9:26 AM, LaRetta said:

1) Regarding a single cheetah if it IS involved in a kill ... what do you need to track on that cheetah about that specific kill?  What parts of the kill it ate?  How aggressive it was during the kill?  Was it the lead?  How much did it consume?

No, this information is not tracked here. All of the necessary information for each kill is contained within that 1-row portal. All I'm needing to do is indicate which cheetahs participated in the kill (and if I'm correct I think it's best to do this with a join table?).

On December 15, 2015 at 9:26 AM, LaRetta said:

2) Reporting:  Will you ever want to see a report of kills by Cheetah name, for example, during a year period, such as:

Yes! Most definitely, that's the main reason I had set it up using a join table. I want to be able to easily report on kills by individual cheetah

On December 15, 2015 at 9:55 AM, LaRetta said:

If your answer to either of the questions above is yes then you may wish to include a Cheetah Kills join table instead of using a multiline checkbox for kills.

Yes, I have a join table between kills and cheetahs because each cheetah has multiple kills and each kill has multiple cheetahs. From your advice and comment's advice, I think I have the joins set up properly. I just want to be able to access them within a single layout... which requires the portal within portal (as a checkbox will not create the join records that I need). Thanks for putting the example file together it definitely helps me understand what you are suggesting.

Does this clarify much? Thanks so much for everyones help on all of this and sorry it's not making much sense. 

[Wim Decorte]

6 hours ago, Eli Walker said:

Hi Wim, thanks for your response. It's not the cheetahs that are being killed. A 'Kill' is anytime a cheetah successfully hunts and kills a prey animal. That's why within the portal with the kill details,

Got it.  Missed that part.

So back to the basics: FM does not support a portal within a portal. But you can still visualize the data to look like it does.  If you are not familiar with Virtual Tables / Virtual Lists then this is the time to brush up on that.  In essence you go collect the necessary data in the background (native FM finds, ExecuteSQL() calls,...) and the present that back to the user.

[ehwalker]

13 hours ago, Wim Decorte said:

So back to the basics: FM does not support a portal within a portal. But you can still visualize the data to look like it does.  If you are not familiar with Virtual Tables / Virtual Lists then this is the time to brush up on that.  In essence you go collect the necessary data in the background (native FM finds, ExecuteSQL() calls,...) and the present that back to the user.

Thanks for the response Wim. Yeah, I've seen it done but I'm not yet versed in the practice. However, isn't that for displaying/reporting on data? With this layout I'm mainly interested in data entry...

[Wim Decorte]

The two are not mutually exclusive... just a UX challenge more than anything else.  You could give the user a popover for instance with global fields to enter and when they click done it you write the proper data where it needs to go and update the virtual table.

[ehwalker]

17 hours ago, Wim Decorte said:

You could give the user a popover for instance with global fields to enter and when they click done it you write the proper data where it needs to go and update the virtual table.

Hmm.. I didn't think of using global fields like that but I'll look into it. That may do what I need! Thanks Wim