6 Replies Latest reply on Sep 25, 2012 1:23 AM by NaturSalus

    Record Grouping

    NaturSalus

      Title

      Record Grouping & Sorting in a Portal

      Post

           Hello,

           Based on the relationships shown in the attached picture, I have a the "Appointment" layout based on the Appointment TO with several tabs.

           The relevant tabs to my question are: the "Rubrics" & the "Bach Flowers" tabs.

            

           The purpose of the "Rubrics" tab is to show the "Rubric" records related to the current "Appointment" record. In this tab the users creates relationships between the current "appointment" record and the "Rubric" records.

            

           On the "Rubrics" tab there is a portal to the AppointmentRubric TO with the following fields:

            Rubric::Rubric

       Rubric::sCategory

       Rubric::sSubCategory

            

           The users creates relationships between the current "appointment" record and the "Rubric" records with the "Add Rubric" button that triggers a script that established relationships with the "Rubric" TO.

            

           The purpose to the "Bach Flower" tab is to show the "Bach Flower" records related to the "Rubric" records selected by the user and related to the current "Appointment" record.

            

           On the "Bach Flowers" tab there is a portal to the bFlowerRubric TO with the following fields:

       BachFlower::bFlowerCommonName

       bFlowerRubric::_kf_Rubric

       BachFlower::Found. Calculation = Get (Found Count). Unstored. calculatio result is Number.

            

            

           My problem is that I don't know how to control the way data is shown on the portal  to the bFlowerRubric TO.

           I would like the "Bach Flkower" records to be shown on the portal in alphabetical order by their CommonName, so that repeated records are shown just once but if there is repetition it is accounted for in the BachFlower::Found field.

            

            

           Let us image that some "Rubric" and "Bach Flower" records have already been entered in the db and that the following relationships have been established between them:

            

           For the "Rubric" record with Rubric = Enthusiastic there are 3 related "Bach Flower" records: Elm, Vine & Vervain.

           For the "Rubric" record with Rubric = Diligent there are 3 related "Bach Flower" records: Elm, Oak & Vervain.

           For the "Rubric" record with Rubric = Perfectionist there are 3 related "Bach Flower" records: Beech, Oak & Vervain.

            

           Now let us imagine that the user has created "Appointment" record 1 and in the "Rubrics" tab adds to the portal the following the rubrics: Enthusiastic, Diligent and Perfectionist:

            

            

           Under the "Bach Flower" tab the portal to the bFlowerRubric TO shows the following:

            

            

           BachFlowerName--------------Rubric-------------Rubric Count

           Beech------------------------Perfectionist---------1

           Elm--------------------------Enthusiastic----------1

           Elm--------------------------Diligent---------------1

           Oak-------------------------Diligent---------------1

           Oak-------------------------Perfectionist----------1

           Vine------------------------Enthusiastic-----------1

           Vervain---------------------Enthusiastic-----------1

           Vervain--------------------Diligent----------------1

           Vervain--------------------Perfectionist-----------1

            

           Since the portal is based on the bFlowerRubric TO I cannot sort out the values.

           Since repeated "Bach Flower" records are not added up (as desired)  and instead are shown as individual records, the Rubric Count of all "Bach Flower" records is 1.

            

           Under the "Bach Flower" tab the portal to the bFlowerRubric TO, I would like the portal values:

            

           either

           "Bach Flower" records ordered alphabetically, repeating values shown as one but accounted for in the "Rubric Count" field

            

           BachFlowerName--------Rubric Count

           Beech--------------------1

           Elm----------------------2

           Oak----------------------2

           Vervain-------------------3

           Vine----------------------1

            

           or

           "Bach Flower" records ordered from higher to lower according to their "Rubric Count", like this:

            

           Rubric Count------------BachFlowerName

           3------------------------Vervain

           2-------------------------Elm

           2------------------------Oak

           1------------------------Vine

            

            In case this cannot be achieved from a portal in Browse mode,  I have also tried to get what I want from a Report layout on List view based on the following alternatives: Bach Flower TO, or Rubric TO, or Appointment TO, or AppointmentRubric TO or bFlowerRubric TO. But I didn't manage to get it working.

            

           What is the best way to get what I need to achieve?

            

           Thanks,

            

           natursalus

        • 1. Re: Record Grouping & Sorting in a Portal
          philmodjunk

          In a portal, each row can represent one and only one record--not a group of records with a common value (common flower name in this case).

          Get ( foundcount ) will return the count of records in your layout's foundset--not the records in a related table so that won't give you the count you want either.

          To list each flower once, you'll need to use a portal based on BachFlower instead of bFlowerRubric.

          But still doesn't give you the count of Rubric records that link to a given Bachflower record.

          I'm not sure which count you want here:

          The count of all Rubric records that link to that each BachFlower record

          Or

          The count of all Rubric records linked to the current appointment record that link to each BachFlower record.

          • 2. Re: Record Grouping & Sorting in a Portal
            NaturSalus

            Hello Phil,

            Thanks for looking into my question.

            In a portal, each row can represent one and only one record--not a group of records with a common value (common flower name in this case).

            That's correct, I am afraid that I misled you, because BachFlower::bFlowerCommonName refers to the vulgar name of each "Bach Flower" record.

            In the project each "Bach Flower" record has two names:

            BachFlower::bFlowerCommonName

            BachFlower::bFlowerScientificName 

             

            I'm not sure which count you want here:

            The count of all Rubric records that link to that each BachFlower record

            Or

            The count of all Rubric records linked to the current appointment record that link to each BachFlower record.

             

            Since in my case the starting point is an "Appointment" record, the "Bach Flower" records refer only to those "Rubric" records linked to the current "Appointment" record, and since a "Bach Flower" record can be related to more than one "Rubric" record the way to record to how many it is linked is by using the Rubric Count field.

            If this makes no sense, the example provided might shed some light

             

            BachFlowerName--------Rubric Count

            Beech--------------------1

            Elm----------------------2

            Oak----------------------2

            Vervain-------------------3

            Vine----------------------1

             

            Since the "Bach Flower" record with common name Beech is related to just 1 "Rubric" record related to the current "Appointment" record its Rubric Count = 1

            Since the "Bach Flower" record with common name Vervain is related to 3 "Rubric" records related to the current "Appointment" record its Rubric Count = 3

             

            ...

             

            Thanks,

             

            natursalus

            • 3. Re: Record Grouping & Sorting in a Portal
              philmodjunk

              I wasn't mislead. I was just trying to explain why you couldn't use a portal to bFlowerRubric to get a list where you have only one entry for each different BachFlower.

              Let's see if we can use a new occurrence of bFlowerRubric plus some creative calculations to get this to work. If you are using FileMaker 12, let me know as we might also be able to come up with something that uses the new ExecuteSQL function to do this.

              Define a global field, gRubricIDList in the BachFlower table.

              Make new occurrences of bFlowerRubric and BachFlower and link them like this:

              bFlowerRubric>----BachFlower----<FilteredbFlowerRubric

              BachFlower::__kp_BachFLower = FilteredbFlowerRubric::_kf_BachFlower AND
              BachFlower::gRubricIDList = FilteredbFlowerRubric::_kf_Rubric

              define a calculation field in BachFlower as Count ( FilteredbFlowerRubric::_kf_bachFlower ) or define a summary field in bFlowerRubric as the count of _kf_BachFlower.

              Now put a portal to BachFlower on your appointments layout and put either the field with the count function or the summary field from FilteredbFlowerRubric in the portal row to display the count.

              One final detail: You'll need to update gRubricIDList with the correct list of _fk_Rubric values before you get correct counts in your portal rows:

              Set Field [BachFlower::gRubricIDList ; List ( Rubric::__kp_Rubric ) ]

              You'd use OnRecordLoad as a trigger to perform this script step and you'd also need to perform the same step each time you use the portal to Rubric to add/remove/change the Rubric records linked to the current appointment record.

              • 4. Re: Record Grouping & Sorting in a Portal
                NaturSalus

                Hello Phil,

                 If you are using FileMaker 12, let me know as we might also be able to come up with something that uses the new ExecuteSQL function to do this.

                As a matter of fact I do have FileMaker 12, but in its current state to me it is as reliable as a bag full of "poisonous snakes". Still waiting for FMI coming up with an update that fixes at least the most relevant issues of FM12. It is a pitty that such a long wait period (25 months) for FM12 release has produced a buggy Beta version that has downgraded so many good things of FM11 and has offered in return no clear, relevant new improvements...

                Anyhow, neither the right place nor time to talk about FM12 shortcomings.

                 

                 

                The first time that I tried  your directions it didn't work as expected, but after writing a detail and long post, it started to work and has since then, so oh well!

                 

                A final request:

                 

                I understand that the Rubric::__kp_Rubric can show just the value related to the last "Bach Flower" record which is related to. However, in an ideal world I would like to have a field that shows all the "Rubric" records related to each "Bach Flower" record shown on each row line. Maybe this could be done with the List function mentioned by you before. 

                Naively I thought, well let's create a gRubricNameList global field in the BachFlower table.

                And to the script step suggested by you I added:

                Set Field [BachFlower::gRubricNameList ; List ( Rubric::Rubric ) ]

                 

                In the hope that if I put the BachFlower::gRubricNameList  field in the portal to the BachFlower TO I would get listed all the related "Rubric" records 

                BachFlower::bFlowerCommonName--------BachFlower::gRubricNameList---------------FilteredbFlowerRubric::Count

                Elm----------------------------------------------Enthusiastic Diligent-------------------------------------2

                 

                but it doesn't seem to work that way.

                 

                Is it possible to have listed the names of all the "Rubric" records related to each "Bach Flower" record?

                 

                Many thanks for your valuable help.

                 

                natursalus

                • 5. Re: Record Grouping & Sorting in a Portal
                  philmodjunk

                  Put the list funciton in a calculation field rather than using a script to load a global field.

                  The resulting list is separated by returns so you will only see the first value in the list unless you either click into the field or resize your field to be several rows of text tall.

                  But you can enclose the list funciton in a substitute funciton and replace the return characters with ", " to get a single row list if that's what you want here.

                  • 6. Re: Record Grouping & Sorting in a Portal
                    NaturSalus

                         Phil,

                          

                         Got it!

                          

                         Thanks,

                          

                         natursalus