8 Replies Latest reply on Feb 3, 2016 1:55 PM by Mike_Mitchell

    Displaying (and hiding) related records


      I am designing a database for keeping track of swimming pool participants (swimmers, courses, etc).


      I have a table for Household and a table for Persons.


      I would like to display a list of Households, with the capability of clicking a display arrow which would then display all the members (Persons) of the household.


      I am doing this (sort of) right now by putting a portal in the row with the Household, but I then need to determine the size of the portal. So if I think there will be at most 3 members of the household, and the household has 4 members, then a scrollbar displays on the portal and a member is 'hidden'. Not desirable. If I set the portal to 7 rows, and a majority of households only have 4 members, then the screen has a lot of wasted space. Plus I do not have a way of 'collapsing' Households so I do not see any members unless I ask (click) on the Household to display members.


      Is there a way to do what I am asking?

        • 1. Re: Displaying (and hiding) related records

          The number of rows in a portal is fixed, so you can't dynamically allocate it based on the number of related records. (Feature request?)


          What you can do is use a List view and a Virtual List technique to expand / collapse as you see fit. In this way, the number of rows will be based on the population in the global variable you use. (You can script finding / omitting records as needed.)


          To your second request: You can conditionally expose any object on a layout using the Hide Object When feature in the Inspector.





          • 2. Re: Displaying (and hiding) related records

            If your objective is to Preview or Print, you can use the Sliding & Visibility options in the Inspector to do what you asked.


            Or: what about using a popover?


            Or: just display the list from the person table, add a sub-summary by household part and sort by household.

            • 3. Re: Displaying (and hiding) related records

              Oh, good one, Tom. I forgot about the subsummary option.

              • 4. Re: Displaying (and hiding) related records

                Subsummary will display all members of all households or will display no households and only members depending upon Sort Order.


                What if I only want to display all Households, and only members of 'selected' Households (toggle display of Members for individual Households)?


                Households are most important to display (which is why I was starting with that Table). I like using the Persons Table, and using SubSummary, but then I need to make sure that the user is not allowed to select Find, as that would then 'hide' Households. So I need to script everything (which is ok).


                Is there a way of hiding members (Persons) from Households, yet display ALL Households? If a Find is done to only find specific Persons, then the Households are hidden as well.

                • 5. Re: Displaying (and hiding) related records

                  Given those requirements, you'll likely need to use a Virtual List technique. You can use this custom function from Bruce Robertson to manipulate the list.



                  cfAccordion ( numberedList; selectedGroup; showNumbers )

                  Bruce Robertson - Concise Design



                  Given a numbered list n.0, n.1, etc and a selected group,

                  collapse all items in unselected group.

                  You may pass group number or value of item 0 of selected group



                  1.0 Vegetables

                  1.1 Peas

                  1.2 Brocolli

                  1.3 Brussel Sprouts

                  2.0 Fruits

                  2.1 Apple

                  2.1 Pear

                  2.2 Peach




                  Let ( thisList = "1.0 Vegetables¶1.1 Peas¶1.2 Brocolli¶1.3 Brussel Sprouts¶2.0 Fruits¶2.1 Apple¶2.1 Pear¶2.2 Peach" ;

                  cfAccordion ( thisList ; 2 ; "" ; "" )











                  Example file:




                  Modified February 2014 to use tail recursion (added "result" parameter)

                  Mike Mitchell, Net Caster Solutions (www.netcastersolutions.com)

                  "result" parameter is used for recursion and should be empty when function is called






                  Let ([



                  thisLine = GetValue ( numberedList; 1 ) ;

                  display = Trim ( MiddleWords ( thisLine ; 2 ; 25 )) ;

                  LineNum = LeftWords ( thisLine ; 1 ) ;



                  selectedGroup = Case (( selectedGroup ) = display  ; Int ( lineNum ) ; selectedGroup ) ;

                  display = Case ( showNumbers ; thisLine ; display ) ;

                  isHeader = Right ( LineNum ; 1 ) = "0" ;

                  listCount = ValueCount ( numberedList ) ;

                  remainder = RightValues ( numberedList ; ListCount - 1 ) ;

                  groupMember = ( Int ( LineNum ) = selectedGroup ) ;

                  nextResult = List ( result ; Case ( isHeader ; display ; groupMember ; "  " & display ))

                  ] ;



                  Case ( listCount ≤ 0 ; result ;

                  cfAccordion ( remainder ; selectedGroup ; showNumbers ; nextResult )



                  • 6. Re: Displaying (and hiding) related records

                    Another option might be to use slide controls? Seedcode example on youtube.



                    • 7. Re: Displaying (and hiding) related records

                      I got to thinking about this last night and I believe this is totally achievable in list view. The problem is that the solution goes against relational database design principals. There would be some reporting limitations and it might be better to have a script construct and delete the table records for that view when navigating to/from it.


                      Anyway, here's how I think it can be achieved: Have all the records in the same table. You would have empty fields in the "parent" records that are only relevant to the "child" records, and visa versa. You would have the parent ID on all records which you currently have anyway and this would be very important as sort order of your records could through the whole thing out.


                      Include a find in your navigation script which excludes all child records and then have your arrow button expand the found set to include the child records for that particular parent.


                      If you don't want to have the child fields at the end of the parent fields in the row you could stack them on top of each other and use conditional formatting to determine which ones to show.


                      I don't have time to work up an example but I'm pretty sure this would all work.

                      • 8. Re: Displaying (and hiding) related records

                        See if the attached file is of any use.