12 Replies Latest reply on Apr 19, 2017 7:11 AM by NickLawrence

    Creating a report that includes multiple rows from a portal.

    NickLawrence

      OS:     macOS Sierra (10.12.4)

      FMP:  15 Advanced (15.0.3.305)

       

      Hi all,

       

      I have created a report based on the fields in a layout, however the layout includes a portal which so far I have only been able to show the first line in the report. How can I get all the lines from the portal to be included in the report. I only need 1 field from the portal, but I need all the lines that include this field.

       

      Many thanks in advance

       

      N.

        • 1. Re: Creating a report that includes multiple rows from a portal.
          carlsson

          There are two ways to solve this:
          1. Include the portal on the layout. Make it more rows than you think and use the Sliding Up functions to not show the empty rows.

          2. Create the report from the "lines" table, and use Sub-summarey layout parts to show the data correctly.

           

          Option 1 is quicker to create, but many things can happen to destroy such a report (eg printouts over several pages).

          Option 2 is the more "correct" one, from a database point of view (or, at least, in some cases )...

           

          HTH

          • 2. Re: Creating a report that includes multiple rows from a portal.
            beverly

            if there are multiple portals on a layout (different relationships), then some fancy "temp table" reporting may need to be done. Not at all easy.

             

            beverly

            • 3. Re: Creating a report that includes multiple rows from a portal.
              NickLawrence

              Thanks for the suggestions,

               

              There is only one portal I need information from and it does not have many rows, normally 1 or 2 but could have 4/5 max. Unfortunately the report contains a lot of other fields and is printed in A4 format, so I'm not sure if I can use the 'Lines' Table for this.

               

              I am new to FMP, so please bare with me if I as stupid questions

              • 4. Re: Creating a report that includes multiple rows from a portal.
                user29424

                You can include the relevant contents of the portal as pure text to merge in your plain report text.

                 

                To aggregate the relevant contents you can use either:

                 

                List

                 

                or

                 

                ExecuteSQL

                 

                You can either store the results in a field you then use to merge in the report text or in a global variable that you can merge in the report text.

                 

                Merging the results into the main text will allow the text to flow dynamically.

                1 of 1 people found this helpful
                • 5. Re: Creating a report that includes multiple rows from a portal.
                  philmodjunk

                  If needed, you can also research or test a few other options

                   

                  The List and ExecuteSQL functions can be used to pull data from your portal's related records into a single field with tabs, returns and paragraph formatting used to get the data to look right. This, like a portal, can be set to shrink but not grow. But unlike a portal, you can get data from a single record to wrap into a flexible number of rows.

                   

                  There is also a technique called "Virtual List" that you can research as an option for temporarily pulling data from different tables for your report.

                  • 6. Re: Creating a report that includes multiple rows from a portal.
                    NickLawrence

                    I have looked at these and they are a little bit beyond my current knowledge if FMP so will investigate further. I do however have another question regarding reporting.

                     

                    We do have a company who is currently doing the development work on our database, however they seem to have a set price for anything we wish them to do, even when I know it only takes a couple of minute we get charged 2hrs work. So, I am trying to do some of the simpler task myself, such as creating reports, however some of the ways they have set fields up that I need to use, does not work in the report. The current one is I need to include a field which has been set to have a global result, however when I am running the report on multiple records the global attribute is raising it to show the same data for every record. How can I get around this in the new report?

                     

                    Many thanks

                     

                     

                    N

                    • 7. Re: Creating a report that includes multiple rows from a portal.
                      philmodjunk

                      One way to lose a consultant is to do your own design work unless you do so with their knowledge and cooperation. So do your own design work at risk of losing them as your consultants.

                       

                      You can't get around this as a global field, by design, has the same value for every record. That's what it's designed to have. It sounds like this is not the right field to use in your report the way that you want to use it.

                      • 8. Re: Creating a report that includes multiple rows from a portal.
                        NickLawrence

                        I'm not doing anything without their knowledge, it was always the arrangement from day one that any grunt work we would do unless we specifically asked them to do it, there is a lot of work for them to do as it is. any work I do is using what is there, the existing fields, tables and relationships, I am not adding anything new except report layouts based on existing tables, and this is probably part of the challenge. Because I am trying to use existing relationships and fields, one field (the one mentioned above) only exists in the table I used as the starting point. This is the only field I cannot get to display anything other than the same data for all records. So I will need to look for other means to get the data (if there is any) from these fields to the report I am making.

                         

                        Many thanks

                        • 9. Re: Creating a report that includes multiple rows from a portal.
                          user29424

                          You should be able to get this listing using List or ExecuteSQL easily.

                           

                          What is the name of the field you want to list, is it a number or text field?

                          What is the criteria for the relationship. As in what field should match what field or value?

                          Also, what is the name of these fields respective table.

                          • 10. Re: Creating a report that includes multiple rows from a portal.
                            NickLawrence

                            Hi user29434 I have answered your questions below and thank for your replies

                             

                            What is the name of the field you want to list, is it a number or text field? The field name is Analyst 02, it is a Text Field and it has the global setting checked. It also has the following on it: Object Script Triggers 1: 1) OnObjectModifiy : Set initials from Contract and Hide Objects When: Contracts::Shown03ProjectDayID= (can’t see the rest of it)

                             

                            What is the criteria for the relationship. As in what field should match what field or value?

                            Contracts                                             Project Days for Contracts SHOWN

                            __ID                                    =                Contract ID

                            And

                            Shown 03 Project Day        =               __ID

                             

                            what is the name of these fields respective table.

                            Table 1 = Contracts

                            Table 2 = Project Days for Contracts SHOWN

                             

                            Hope this helps you help me

                             

                            Regards

                             

                            N

                            • 11. Re: Creating a report that includes multiple rows from a portal.
                              user29424

                              The field you're trying to aggregate a list from in the portal table is a global field?

                              • 12. Re: Creating a report that includes multiple rows from a portal.
                                NickLawrence

                                My apologies, I think I have confused the issue, the above details are regarding the question I asked at #8 above regarding including a global field in a report.

                                 

                                With regards to including the portal fields in the report, I ran the report with just the first line, there are only 43 records, less than 20% have more than one line so thought this wasn't too bad and for what the report was used for acceptable. Apologies for not updating the thread with this and thank you for your advice on the matter.

                                 

                                N.