4 Replies Latest reply on May 30, 2014 10:15 AM by wheelodj

    Display single child record data on parent record form.

    wheelodj

      From one beginner to another,

       

      I'm quite sure that this tip will be entirely mundane to the experienced developer and largely irrelevant to most everyone else.  Still, I thought I'd share what I've been learning as a first attempt to give back to the community that I regularly come to for insight.

       

      Goal:

      Display data from a single child record, namely an image and a description, on its parent layout without the use of a portal.  With First, Previous, Next, and Last buttons, the user should be able to navigate through all the images associated with the parent record in a more familiar presentation than a scrolling portal.

       

      Initial thoughts:

      Somehow set up a slide control object with a projected maximum number of slides necessary to display each child record.

      --This clearly wasn't the right approach, because the number of slides would limit the number of child records to be displayed.  More importantly, it didn't answer how to designate which child record would go on each slide.

       

      First attempt:

      Create a "MediaNumber" field and global "placeholder" fields in the parent table.  MediaNumber should designate the record number within the related found set (not its ID number) for which child data should be displayed.  Thus, MediaNumber should always be between 0 (if no related records) and the found count of related child records.  (Sadly, at this point I was still navigating to the related records just to get a found count.  I have since learned the beauty of an unstored Get(FoundCount) calculation field in the child table.)  Next, whenever MediaNumber is modified, call a script which navigates to the child records, goes to the record number defined by MediaNumber, and stores the desired data into local variables.  (For the container field, I copied the filepath to the where the image was stored in the variable.)  Then, navigate back to the parent form and insert the data into the placeholder fields.

      --This essentially accomplished my goal.  However, it was overloaded with unnecessary script steps (I was certain), required open storage of container data, and seemed to have terrible performance with just a handful of records.

       

      Getting closer: (After learning more about the relationship graph)

      Create a global field--we'll call it "gID"--in the parent table.  Add a new table occurrence (TO) of the child table.  Relate it to the parent TO by  parent primary key = child foreign key,  AND  gID = child PRIMARY key.  By this relationship, only ONE child record will be associated with its parent record at a time, determined by the value given to gID.  Now specify the child fields on the parent layout as from this new TO.  As in the first attempt, whenever MediaNumber is modified, call a script which navigates to the related child records and goes to the record number defined by MediaNumber.  Instead of copying all the data, simply set the parent gID field equal to the child record's primary ID.  Return to the parent record.  The fields from the new child TO will display the data from the selected child record because of the new relationship.

      --With this method performance seemed a little better, but it still required navigating away from the parent layout to find the associated child record's ID.

       

      Most recent iteration:

      To avoid the need to navigate away from the parent layout, add a new unstored calculation, "ListChildID", to the parent table.  Define it using the List function and the child primary ID, as in List( OriginalChildTO::PrimaryID ).  Make sure the TO used has the standard primary = foreign key relationship to the parent table.  Now, when the MediaNumber is modified, only one script step is required to change which child record should be seen on the parent record.  Simply set the gID field using the GetValue function, passing in the new ListChildID field as the set of carriage return delimited values -- something like GetValue( ListChildID ; MediaNumber ).  This calculation will return the primary ID of the child record desired, just the same as navigating to the related child records, only much faster. 

      --Using the complex relationship, a couple extra fields, and the List function, I can finally display and navigate through data from child records one at a time within the context of a parent layout.

       

      As an added bonus, I placed a portal of the child records outside the boundary of the layout, which I use to delete the current, related child record when a "Delete" button is pushed.  In all, I count, "find" (in a sense), display, add new, and delete related child records within the parent layout without a portal on the user's screen.

       

      Again, I don't expect to have taught anyone something new or to get much interest from this post.  But if you have any questions, I (or somebody smarter!) will be sure to answer.  Better yet, if you are willing to share smarter ways of accomplishing similar tasks, please do!

       

      Thanks for reading,

      Dan

        • 1. Re: Display single child record data on parent record form.
          erolst

          Dan –

           

          it's nice to see that someone is inventive and curious, so even if its contents is not news to me, I still found your post interesting.

           

          You asked for a better implementation; here is one that has less overhead – it requires only one additional field – and performs its work chiefly in a script. No need to create aditional fields to hold data that you can read in just-in-time, e.g. the List() of primary childIDs.

           

          • Create your field gSelectedChildID

           

          • Create the additional relationship with that field as match field against children::primary (depending on implementation, you may or may not add want to add parent::primary = children::foreign)

           

          • Place the desired child fields via the new relationship on your layout.

           

          • Add two buttons “previous” / “next”, give them script parameters of -1 and 1, respectively, and tie them to a script along these lines:

           

          Set Variable [ $IDlist; Value:List ( Children::primaryID ) ]

          If [ IsEmpty ( $IDlist ) ]

            Exit Script [ ]

          End If

          Set Variable [ $sp; Value:Get ( ScriptParameter ) ]

          Set Field [ Parents::gSelectedChildID ; calculation see below … ]


          Let (

            curID = Parent::gSelectedChildID ;

            Case (

              IsEmpty ( curID ) ;

              GetValue ( $IDlist ; 1 ) ;

              Let ( [

                curPos = ValueCount ( Left ( $IDlist ; Position ( $IDlist ; curID ; 1 ; 1 ) ) ) ;

                isFirst = curPos = 1 ;

                isLast = curPos = ValueCount ( $IDlist ) ;

                move = not ( $sp = -1 and isFirst or $sp = 1 and isLast ) ; // if you want to stay on first when prev, or on last when next …

                newID = GetValue ( $IDlist ; curPos + $sp )

                ];

               Case ( move ; newID ; curID )

               // … or as merry-go-round by combining move and newID into newID = Case ( isLast and $sp = 1 ; 1, isFirst and $sp = -1 ; ValeCount ( $IDlist ) ; GetValue ( $IDlist ; curPos + $sp ) )

             )

          )

          )

          • 2. Re: Display single child record data on parent record form.
            wheelodj

            Thanks for the reply.  Lots for me to chew on!  First, I see I need to get more comfortable with boolean results; if I had come up with similar logic in your Let() expression, I probably would have just tried to accomplish it with more If statements... It's awesome that so much is done without additional fields.  I had been holding on to the stored MediaNumber field in case it was desirable to keep your place even if changing parent records.  But using ValueCount to find the current position seems brilliant; if it's global, you could still display, for example, "4 of 12" on the layout if you wanted.  Took me a minute to understand what was going on within the Left( Position() ) functions -- very cool (to me).

             

            One question: is there actually more overhead in using unstored calculations versus instantiating variables within a script? My understanding was that the unstored calculation is performed when called on, so no data is actually being "held".

             

            Thanks again!

            • 3. Re: Display single child record data on parent record form.
              erolst

              wheelodj wrote:

              Took me a minute to understand what was going on within the Left( Position() ) functions -- very cool (to me).

              Something like IndexPositionInList ( element ; list ) is such a common requirement that you should pack the logic into a Custom Function (until FMI deigns to provide that natively) – and get Advanced if you don't have it.

               

              If you want to keep your selection while switching parent records, make the selection field a non-global field (but be aware of the problem that brings in a multi-user environment).

              wheelodj wrote:

              One question: is there actually more overhead in using unstored calculations versus instantiating variables within a script? My understanding was that the unstored calculation is performed when called on, so no data is actually being "held".

               

              It seems to be the same overhead in technical terms (which you could check by amending your List( children::ID) calculation field with a $$counter incrementer, then referencing the field as a Let() variable and watching the variable's count increment with each script call), but it's one entry more in a potentially long list of fields in your table, and it has no further purpose.

               

              I try to cut down on the number of utility fields fields wherever I can, and Let(), $vars and $$vars make that relatively easy. (And they help you writing clearer code – nothing to be sneezed at!)

               

              btw, if you don't play merry-go-round and want to signal that the displayed record is one of the 'edges', you can use a calculation like the above to Conditionally Format your buttons … and a $$ to display # / ## … etc. etc.

               

              Happy coding!

              • 4. Re: Display single child record data on parent record form.
                wheelodj

                Excellent tips, and more to digest!  I'm glad I posted.  Thanks so much for your time.