4 Replies Latest reply on Jul 16, 2013 9:37 AM by philmodjunk

    Creating records in join table through portal

    JonKinney

      Title

      Creating records in join table through portal

      Post

           Hi there!

           I'm an experienced web application developer (Ruby on Rails, PHP, and .NET), but am setting up my first Filemaker system so a friend can hopefully more easily support it herself for her music therapy business. She needs to have clients with many objectives. Objectives belong to a goal. Sessions and objectives are joined through sessions_objectives as each session needs to note whether or not the objective was met during that session.

           What I currently have working:

           1) Create a therapist

           2) Create a client

               a) Add multiple objectives to the client record

               b) Select the goal when adding an objective

           3) Create a session

               a) Select a therapist from a drop down

               b) Select a client from the drop down list

               c) date/time auto-populated

               d) Type in location

               e) Type in notes

               f) Select multiple treatments (checkboxes)

           What I don't have working yet is the next section on the sessions form (shown below the relationship graph below). I need to add a portal for the sessions_objectives join table and add new records to each session that pull in all the client's objective and lets her type in a field whether or not the objective was met. She has special syntax for this so it's just a text field/string, not a checkbox/boolean.

           I was able to get the objective_id_fk from the sessions_objectives join table to populate the drop down with only that patient's objectives (so she can pick one or more objectives to work on in that session). But I'd like the session_id_fk to be auto-populated in the join table by the current session's id, and then then she has to type in the 'met' field in the join table. I tried for 4-5 hours to try to get this all working, but I'm stumped. Any help would be greatly appreciated. 

           Here's the relationship graph:

            

           And below is the form I'm trying to get to work. You can see I'd like to pick the objective from the drop down and then have the description and goal populated in each portal. Each of these sessions_objectives records needs to be unique to the session. If we create a new session and select a patient that has already had a session previously, no records should show in the join table portal because they're per-session with a 'met' field that needs to be entered for every objective each session (hence the sessions_objectives join table). I was having some issues with this too.

            

           By the way, I do have allow creation of records in this table for the sessions_objectives table on both sides of the relationship. Here's the screen for the objectives -> sessions_objectives side

            

           So what I'm asking is how to do what I have in the annotated form above since it doesn't work how I think it should.

            

           Thank you!!!

        • 1. Re: Creating records in join table through portal
          philmodjunk

               Your Sessions_Objectives is related to the Sessions table occurrence only through a very round about "chain" of table occurrences. It is directly related to the Sessions 2 table occurrence. That lack of a direct link to the join table is probably what is keeping this from working for you.

               You might try setting up a layout that specifies Sessions 2 in Layout Setup | "Show Records From". You'll probably need to link in a new occurrence of Therapists directly to Sessions 2 in order to be able to successfully select a therapist for a given session on such a layout, but your portal to Sessions_Objectives should now work once you enable the "allow creation..." option in the sessions 2 to Sessions_Objectives relationship.

          • 2. Re: Creating records in join table through portal
            JonKinney

                 Well it took some playing around with, but I finally got what I needed!

                  

                 Then the key for getting the objectives dropdown to scope to the current client was this:

                  

                  

                 The whole having to specify the table multiple times in the relationship graph is still very very weird/confusing to me, but I think I'm starting to get it a little. If you have any pointers on a good primer for how that all works I'd love to hear it!

                 Thanks again for nudging me in the right direciton.

                 Jon

            • 3. Re: Creating records in join table through portal
              JonKinney

                   Yea, even looking back at my relationship graph again, it'd be sooooo much less confusing if you could just link a table's primary key to multiple other tables. Having to duplicate them doesn't make any sense to me.

                   For instance, being able to do this seems like it'd be way easier to manage

                   Any idea why that's not possible?

                   Thanks again,

                   Jon

              • 4. Re: Creating records in join table through portal
                philmodjunk

                     You can link one table's primary key to the foreign key to multiple tables.

                     But you can't link them in such a way that you get mutliple relationship lines that link to the same table occurrence. Note the "loop" that your red relationship line creates such that there are two relationship "paths" from clients to Sessions_Objectives. That creates a situation where FileMaker is unable to evaluate a reference to that join table from the context of the clients occurrence and thus it prevents you from creating such a relationship.

                     You may also want to consider the Anchor-Bouy method of organizing your relationships: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                     And with FileMaker 12, wev'e gained a limited way around this issue in the ExecuteSQL function as it can define relationships that are independent of anything found in the relationships graph--but I doubt I'd use that to try to get a portal to work.