4 Replies Latest reply on Sep 17, 2014 9:52 AM by philmodjunk

    Dwindling many-to-many structural issues...

    timc

      Title

      Dwindling many-to-many structural issues...

      Post

      Hi there,

      I'm working on creating a lesson planning resource which, apart from retaining a complete lesson plan history in one db (as opposed to far too many XL spreads), it prints an individual lesson plan map...

      To explain a little of the detail, each lesson consists of a set of objectives and a set of steps. The steps include a length of time and a referencing number to the start PPT slide and this step's end slide along with ref dropdowns and text boxes.

      Given a many to many scenario, I opted for record creation via two portals in the join table while printing is handled from the Lectures layout where I can slide fields...

      While this works for umpteen lecture records and their multiple objectives and steps, the problem is slide numbering...

      I wanted a dwindling dropdown to reference the starting slide and another for the ending slide. To do this I've employed a Brian Dunning custom numrange function to fill the missing between-values (cSlideRange), converting this to a list (cMySlides) and making this list a "selected" valuelist. This valuelist is then captured (cMySlideVL) to form a 'not equal to' relationship, providing a valuelist of "unselected" slide numbers...

      All good except that adding a second record at Join level doesn't 'reset' the slide numbering drop-down. So if I've used say 20 slides in lecture 1, lecture 2 should offer slide 1 rather than slide 21 as it currently does...

      Needless to say, this is an 'organic' build and not properly thought-through! Not illustrated are all my flailing/failed attempts to add a match-field 'f_id' to the slide table, the increasing relational complexity of which has led me to wonder whether adding so many records purely to offer a range of numbers makes any sense... Just to stress, nothing goes in these slide records, in theory I'm just using the id number (if it weren't for the fact that the second slide's serials would begin at 81).

      I'd appreciate any directional pointers anybody can offer. Thanks for looking.

       

      lecture.jpg

        • 1. Re: Dwindling many-to-many structural issues...
          philmodjunk

          Take a look at the relationships I've uploaded. This is from the Dwindling Value List example in "Adventures In FileMaking #1" that you can download and examine. Note the additional field included in the relationship to Values4DVL, the TO that servers as a value source for the Dwindling Value List. That extra field is used to force the value list to update automatically each time that a value is selected from the value list.

          The OnObjectModify trigger on the value list formatted field performs this simple script to force the update:

          Commit Records"
          Set Field [MainDVL::gRefreshTrigger ; 1]

          The value set to this field is not important. Any value can be specified. It's the act of modifying the value of this global along with the Cartesian join operator (x) that makes it work.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Dwindling many-to-many structural issues...
            philmodjunk

            Ooops. Had to re-edit the post and re-submit as I forgot to upload an image. That will leave the image out of any email that you see from this forum....

            • 3. Re: Dwindling many-to-many structural issues...
              timc

              Thanks Phil,

              Your CVL Adventures file is an Aladdin's cave; much appreciated...

              It's taken me an age to standalone replicate what your DVL is doing! The need for a trigger to refresh the list wasn't lost on me and you've demoed the perfect way to apply it.

              I've yet to apply any of this to my existing file largely because I'm uncertain how to address the issue of multiple records allied to different Join Table records – sorry, I can't explain this succinctly, no vocab!

              But: the portal (for lecture steps, say) lives in a record in the join table. So one join table record (u_id)[f_id] is parent to multiple portal-created lecture records (id_lct). To print a specific lectures layout requires an f_id search to filter out the records from non-applicable lectures. All good, except for the slides which currently lists ALL (cExclusionList) records regardless of f_id.

              Can I use f_id as a conditional filter and get the exclusion list to be a little more reactive? Will that work?

              Thank you again...

              • 4. Re: Dwindling many-to-many structural issues...
                philmodjunk

                Join tables aren't parents they're children and each table on either side of the many to many relationship is a parent to the records in that join table. So your vocab challenged description is a bit confusing here.

                And if you still have Lectures_Objective... configured as shown in your first post, it's not correctly set up as a join table. (sorry for not noticing that detail sooner.) It needs two match fields not one. One match field should match to Lectures and one to Objectives. The same match field cannot be used for both.

                Ironically, AIF #3 is all about many to many relationships, but it's going to be a while before that one is available as I have several other more urgent projects taking priority over it.