1 2 Previous Next 19 Replies Latest reply on Dec 7, 2011 11:07 AM by philmodjunk

    Subsummary labels inside a portal?


      Subsummary labels inside a portal?


      I was wondering about the best (or maybe just easiest) way to create labels/dividers in a portal.  I was thinking that a subsummary would be perfect, but can't seem to see how to utilize it.  That isn't the only possible method, though:  I was originally thinking about creating a false parent record, and defining a "DISPLAY" field that would calculate some different text based on various things.


      So a bit more explanation:  Using FMP 11.  I am trying to implement a new UI for Filemaker search functionality.  (I have posted about this before with various other issues.)  We are trying to figure out how exactly to implement some OR vs. AND functionality for criteria.  We are going with a 'grouping' method, where stuff that is 'grouped' together will get AND'ed, everything else is OR'ed.  But how to best show that?  My co-worker came up with some nice functionality for creating the groups and showing them, but now we want to make things a bit more obvious to the end user by creating some more generic language. 


      This portal is in a layout based on a Users table; there is a Searches table and a Criteria Lines table.  There are portals to both Searches and Criteria on the layout.  (Trying to keep things unified.)

      Here are a few screen shots.  This is the portal view that I would like to create labels in.  The first one shows two selected rows which will then be grouped together via a button (not shown).

      [Images aren't showing up when I view the thread, but show up when I am editing the post.  I have attached the images to the message, instead.  Aw crum; only one image can be attached?  Made a combined version.]



      This shows the grouped items, but with the partially implemented solution of using a parent record that uses calculated display text.  You may notice a missing criteria line; that's because currently an active line is defined as the parent (hence 'partially implemented'  :)).  I was just testing the DISPLAY text feasability (seems to work well).  (We already use the display text to create the indentation and "+" sign visual.)


      BUT, it seems that what would work even better is a sub-summary report.  I could create a field for some descriptive text value, then summarize on the value.  This would then create the sub-summary line with that description in it and I wouldn't have to create false parent records.


      Any suggestions?





        • 1. Re: Subsummary labels inside a portal?

          Sub summaries are layout parts you can include in a list view type of report. They cannot be used in portal.

          You may be able to use color to group your portal records or you can limit the records shown in the portal to just one group at a time.

          One group is all the criteria used to perform a single find? If so, I'm not sure there's a reason to list more than one group of criteria in the portal at a time.

          • 2. Re: Subsummary labels inside a portal?

            Well, it is kind of what I figured, but still a bummer.  Maybe in the next release...


            No, all criteria are used for the search.  The primary concern is to try and communicate, clearly, how the search is going to be built using those criteria.  For most users the idea of AND and OR just doesn't make sense, so we wanted to show groups and then provide the language to support what was happening. 




            • 3. Re: Subsummary labels inside a portal?

              You could set up conditional formats where each group of portal records has a common value in a field that in turn determines the fill color of that field to visually group your portal records.

              It could be as simple as a text field, groupColor, that stores the values "Red", "Green", "Blue", or "Yellow".

              You could then define a series of conditional format expressions stacked like this:

              Portaltable::groupColor = "Red"
              PortalTable::groupcolor = "Yellow"
              PortalTable::groupColor = "Blue"
              PortalTable::groupColor = "Green"

              with the appropriate fill color assigned for each expression.

              Then it's a matter of assigning a common color to all portal records that are part of the same group.

              You might also be able to set up an indented format where all the records except the first record of a group. (Perhaps that's what you meant by a "parent" record?) have several spaces in their text field to indent them slightly to the right.

              • 4. Re: Subsummary labels inside a portal?

                We are using color for row highlighting of selected rows.  (The blue color in the first image.)  I don't know if we want to throw in more colors; I think that might make the highlighted color unclear.


                Yeah, we are using an indentation based off a parent record, currently.  I think I will just have to go with the false-parent record method.  The sub-summary thing just seemed nice and a bit more elegant.


                --  Justin

                • 5. Re: Subsummary labels inside a portal?

                  OK, a bit of further work has been done.  I went with placeholder records to act as the parent of the group.  I am now up to three types and would like to have certain placeholders NOT shown in the portal if they are empty.


                  So I have one group that is the catch-all (I am calling it Root Group) for anything that is 'un-grouped', i.e. it is NOT part of an AND related find criteria set.  (To refresh, when a person adds criteria to the list, they can then group criteria into a group, creating a "find ALL of these criteria" group, or an AND group as I am calling it here.)  So this Root Group MAY, conceivably, have nothing in it.  But the parent record gets created at creation time and is always present.  So I want a way to filter it out of the portal display if it has nothing in it.


                  My present plan of attack:  include a counter field for that record and then filter my portal based on those counters (i.e. visible when:  counter <> 0).  My question is, how best to populate that counter field?  I could put inc/dec-rements in my scripts and just do thorough testing to make sure I catch all possible transitions.  I was thinking, or hoping, that there might be some way to do a calculation field instead.  Perhaps using an aggregate function of some sort?


                  --  Justin

                  • 6. Re: Subsummary labels inside a portal?

                    I think you'll need a self join relationship so that your placeholder record links to the related records by a value they all have in common. Then a summary field or a count function can be used to count the related records from the self join--producing the value you can filter on in the portal.

                    • 7. Re: Subsummary labels inside a portal?

                      OK, some additional questions about this issue.  Well, a related issue, literally.  I think I am having problems with my relationships, or at least fully understanding them.  I have most of the functionality I want in place and things seem to be working OK.  I am having a problem with the context of the portals, though, and thus with things updating correctly.  I am guessing it is because I am not fully grasping the nature of the relationship.


                      I have included another screen capture.  The layout is based on Users; the left portal is on Searches; the top right is on Searches; the lower right is on Criteria.  (Again, it is a system of creating searches with the various criteria line items for each search in a separate table.)  I have highlighted a few numbers in the image and listed the fields that they are showing.  I put them in to try and figure out the context of each point of reference.  I can see that there's a problem, but not how to fix it.  :)


                      On the left, the layout based in Searches, is SearchID; this is the primary key for that table (well, in my mind - I understand the FM uses its own behind the scenes PK).  I have repeated that field in the lower right hand portal.  The blue highlighting on the left is a selection indicator; thus, in this one, "Blank 2" or #79 is 'selected', and passed around the layout using a global "Searches::gSlct_Search" field.  The portal in the upper right is just a data display of the currently selected search.  


                      The problem comes in on my use of the OMIT and ROOT counters.  Each search has two default groups that are created when the search itself is created:  a ROOT group and an OMIT group.  These are kind of catchall groups; every search criteria defaults into membership into the ROOT group and the counter is incremented.  If a criteria line is marked as "Omit" then it is added to the OMIT group, and again the counter is incremented.  There is logic in the scripts to remove an item from the other group if it is already a member there, and again, the counter should update.  Lastly, if a criteria line is deleted, update the counters.  I am using the counters to hide the labels in the right hand portal when there are zero criteria lines in that group.  In the screen capture the two label lines shown are NOT supposed to be visible.  If you look the counters for both Root and Omit are zero, so these lines should be hidden.  They are showing up, however, because the Searches::SearchID field referenced is pointing to a different Search (#77) from the context of that portal.


                      Here are the relations and filters:

                      Searches -> Criteria:  "=" on SearchID and SeachID_FK respectively.

                      Users -> Searches:  "X" on UserID and UserID_FK respectively.

                      Left portal filter (Searches based):  Users::__UserID = CM_Report_Bldr_Searches::Creator_User_ID

                      Lower-right portal filter (Criteria based; this one is a bit complicated because of trying to hide the label lines when the counters are zero):

                      CM_Report_Bldr_Criteria_Lines::_SearchID_FK = CM_Report_Bldr_Searches::gSlctr_Search
                          Case  (
                                CM_Report_Bldr_Criteria_Lines::__Line_ID = CM_Report_Bldr_Searches::gOmit_Parent_ID  and  CM_Report_Bldr_Searches::Omit_Group_Count < 1 ; 0 ;
                                CM_Report_Bldr_Criteria_Lines::__Line_ID = CM_Report_Bldr_Searches::gRoot_Parent_ID  and  CM_Report_Bldr_Searches::Root_Group_Count < 1; 0 ;


                      Another issue arises from the deletion of a criteria line from the portal row.  The script is using the context of the portal (I think) and as you can see in the numbers, it is refering to a different Search, so it is updating the wrong counters (#77 instead of #79).  That and the fact that the portal is looking at the counters from the wrong Search are my two issues, but probably the same cause.


                      Screen shot with what it should look like follows in the next post.




                      • 8. Re: Subsummary labels inside a portal?

                        What it should look like.

                        • 9. Re: Subsummary labels inside a portal?

                          Oh, I know I can fix one of the directions by doing a search for the right SearchID record and then updating the fields.  But that seems a bit in-elegant.  :)


                          And I don't think that I can use that method to solve the portal display issue, where it is using a calculation to figure out whether or not to show the line.


                          --  Justin

                          • 10. Re: Subsummary labels inside a portal?

                            From your initial description, you have these relationships: Users---<Searches----<Criteria

                            Users::anyfield X Searches::anyfield (does not matter what fields you specify when X is used as the operator)
                            Searches::SearchID = Criteria::SearchID_FK (Hopefully, SearchID is defined as an auto-entered serial number)

                            and your layout is based on Users so that make Users our "starting from" or "context" table occurrence.

                            But from your layout you appear to have this relatioinship for the lower right portal:

                            Users::gSelectedSearchID = SelectedSearchCriteria::SearchID_FK

                            Where SelectedSerchCriteria and Criteria are two different occurrences of the same table.

                            Is that correct?

                            What I am suggesting is that you add an additional occurrence of criteria so that you have this relationship:

                            Criteria::SearchID_FK = SameGroupCriteria::SearchID_FK AND
                            Criteria::GroupCategory = SameGroupCriteria::GroupCategory

                            With this relationship, a calculation field defined as either:

                            Count ( SameGroupCriteria::SearchID_FK )


                            SameGroupCriteria::sCount  //sCount would be a summary field that "counts" the SearchID_FK field

                            Would count all records for the same searchID and same group (such as the "omit" group). You might want to subtract 1 so as not to count the "label" record in this count.

                            • 11. Re: Subsummary labels inside a portal?


                                  I was reading some about self-joins today and thought that they might be able to apply here, but hadn't quite figured it out yet.  That is what it appears you are recommending for a new TO.  Thanks for the pointers around that.  Wouldn't it make sense to have the count in the Searches table, though?  The self join would require that I put the calc in the Criteria table so that it would utilize that new relationship, right?  And I am currently using a 'groupParentID' field to tie the groups together.  I guess that could operate as the 'GroupCategory' field you mention in your advice.


                              No, there is no 2nd occurrence of Criteria.  There is only the one, with the relation:  

                                    Searches:SearchID_PK   =   Criteria::SearchID_FK

                              Yes, SearchID is auto-enter serial.  And the relationship in the bottom right portal is, well, I guess it has two steps, really, if you have to trace from Users to Criteria (they are not directly related):

                                    Users::UserID = Searches::CreatorID     -->      Searches::SearchID = Criteria::SearchID_FK

                              Also, the multi-layered filter (the one with the case statement shown in my posting on Nov 26 about this particular aspect) is applied to the portal.  In my head I guess I was thinking that the portal context would just be the reverse of the 2nd step from above, Criteria::SearchID = Criteria::SearchID_FK, and thus would be directly tied through the SearchID to the Searches table (like a related record) and not go through the Users relation.  Thus, I thought I could just directly make references to the Searches table.  But that obviously isn't the case.  :)

                              Not that it matters, but the global is in the Searches table, not the Users table (i.e. Searches::gSlctd_Search).


                              Small side question:  what are the performance differences between a calculation field and just using scripts to update a count in a field?  The calc field is a bit more thorough and effective, perhaps, but seems like it could be a drag if it has to recalculate things with every step.  Especially being in the criteria table, wouldn't every record in the table need to check and update this calc field when any new record is added?  This could conceivably be a lot of records here.  There will probably only be a few (<10) for each SearchID, but there could be many searches overall, each with their criteria of course.


                              I will do some playing around with this and see what I come up with.  Thanks again.


                              --  Justin


                              PS.  Just thought about this one:  what if I had a multi-relationship from Users to a new TO of Searches:

                                         Users::UserID = Searches2::CreatorID


                                         Searches::SearchID = Searches2::gSlctd_Search

                              ?  Then my script would use the Searches2 TO update the Searches field.  My script step for updating the Count field is (and the script fires from the portal in the lower right, from the context of Criteria...again, I am thinking that the context of the script is based on the portal that it is launched from):

                                   Set Field [Searches::Root_Count; Searches::Root_Count - 1]


                              PPS.  Yeah, I have already subtracted one to account for the label line, so that it won't show.  I even started that field at -1 so that if someone were looking at the data table directly it would show 0 as the Count of the group because of the label line being added in.

                              • 12. Re: Subsummary labels inside a portal?

                                One nice thing with FileMaker is that it's pretty easy to set up different scenarios and test them.

                                I think a scripted update of the count may be a very good option, as long as you can make all user actions that might modify the count "air tight". both the creation and deletion of portal records will change the totals shown so you have to make sure that both can only be done with a script--where you include steps to update the count at the same time.

                                • 13. Re: Subsummary labels inside a portal?

                                  I have figured out part of my issue at least:  when I am adding new criteria lines, I am first going to a layout based in the Criteria table.  So, from there, the SearchID referenced back to Searches correctly picks the currently selected search.  (In the screen shot above, this would mean that Search 79 is selected (the blue highlighted one), and the Search::SearchID evaluates to 79.  This is the behavior I want.)  However, when looking at the screen capture it is obvious that from the portal's point of view the Search::SearchID is evaluating to 77.


                                  This problem is two fold:  when removing an item from the Omit group I am currently NOT switching to a Criteria layout, thus it updates the wrong SearchID.  The second issue is that the portal filters based on that SearchID, so can show the wrong lines if it isn't evaluating to the correct SearchID. 


                                  Doesn't the portal evaluate based on the table it is based upon?  Another portal on that same layout as the malfunctioning portal is based in the Searches table.  This portal correctly evaluates to 79 while the other portal evaluates to 77; it seems that if the point of view is is the same (from Users -> Searches) that it would evaluate the same.  Yeah, slightly different paths...or are they?  They are both going from Users to Seaches.


                                  -- J

                                  • 14. Re: Subsummary labels inside a portal?

                                    Tables, table occurrences, and layouts are three separate things.

                                    A calculation field will evaluate, not from the context of the current layout but from the context of the table occurrence specified for it in the context drop down at the top of the Specify caluclation dialog used to define it.

                                    A portal filter expression, however will evaluate from the context of the current record of the table occurrence specified by the Layout--not the portal's "show related records from" portal. Thus it is very important to figure out the "context" by identifying the relevant table occurrence--a box in your relationship graph for any expression you are analyzing.

                                    1 2 Previous Next