12 Replies Latest reply on May 8, 2015 6:43 AM by planteg

    Having a 'summary' field displayed inside a portal

    planteg

      Hello to everyone,

       

      I searched for in the forum but couldn't find an answer. What I am trying to achieve is summary field inside a portal instead inside a layout. I can get the record set I need for the portal, but can't total by a field.

       

      I have two tables - there are more fields that are not relevant for the question:

       

      TimeEntry

       

      This is a table where employees enter the time they work for a specific project. The fields of interest are:

       

      EntryDate: date for this entry (date)

      EntryProject: the name of the project (text)

      EntryTotal: nb of hours for that entry (calculation to a number)

       

      Enterprise

       

      In that table I have two fields that are used to set the date range for the data I need to display in the portal.

       

      DateFrom: beginning of range (Date)

      DateTo: end of range (Date)

       

      I created a relationship between these two tables this way:

       

      Enterprise                    TimeEntry

       

      DateFrom     <=          EntryDate     AND

      DateTo         <=          EntryDate

       

      On top of that I sort the portal by EntryProject. This way I get in the portal all of the individual records for the date range.

       

      But what I would like to display in the portal is the total for every single project in the range. How can I achieve that in a portal, if at all possible ?

       

      Thanks in advance

       

      Gilles Plante

        • 1. Re: Having a 'summary' field displayed inside a portal
          erolst

          planteg wrote:

          But what I would like to display in the portal is the total for every single project in the range. How can I achieve that in a portal, if at all possible ?

           

          First, create a real summary field in TimeEntry, say, sTotalOfEntryTotal – where entryTotal would more aptly be named duration and be of type time, since (I assume) you're subtracting a start time(stamp) from an end time(stamp).

           

          Try adding the TOs in bold, using the obvious match fields projectID_PK and …_FK:

           

          Enterprise --< TimeEntry >-- Project_forTimeEntry --< TimeEntry__forProject_forTimeEntry

           

          Then display a portal into Project_forTimeEntry, with Project_forTimeEntry::name and TimeEntry__forProject_forTimeEntry::sTotalOfEntryTotal

          • 2. Re: Having a 'summary' field displayed inside a portal
            planteg

            erolst,

             

            I am not sure my understanding is good . Project_forTimeEntry would be on Project table, and TimeEntry__forProject_forTimeEntry on TimeEntry ?

             

            I don't see in Project_forTimeEntry where I would get a SUM of EntryTotal per project.

             

            Thanks

             

            Gilles Plante

            • 3. Re: Having a 'summary' field displayed inside a portal
              erolst

              Please read the description on where to create the summary field, and what fields exactly to place into the portal (as per their fully qualified names).

               

              Note that a field in a portal doesn't necessarily have to belong to that very same TO:

               

              Enterprise -> TimeEntry = all time entries in date range

              TimeEntry -> Project (A)= all projects in date range

              Project (A) --> TimeEntry (B) = all time entries of all projects in date range


              A portal into Project(A) will list all projects in date range; add fields for

              Project (A)::name, and

              TimeEntry (B)::sTotalOfEntryTotal (from “behind” Project(A))


              This works by “condensing” all the related TimeEntry (B) records and their values into one summary field for of each project record (similar to the way a sub-summary part works). Display the field in the portal, et voilà – there's your project name / time entry summary combination.

              • 4. Re: Having a 'summary' field displayed inside a portal
                planteg

                erolst,

                 

                it almost works. I get only one project listed in the portal, but the totals are wrong but for a project. My guess is there is something wrong with the sub-summary field. But I almost there .

                 

                When someone is used to SQL with it's relationships and JOIN clauses, it hard to make its way into TOCs. I understand that relationships are kind of JOIN + WHERE in FileMaker - if I am not wrong - but it's only the tip of the iceberg, In your example, I would never have figured out that Project (A) would only contain a list of Projects (kind of SELECT DISTINCT).

                 

                Is there a document that fully explains what are TOCs and what they can do ? If there is a single very important notion in FileMaker, I think it's TOCs.

                 

                Many thanks erolst.

                 

                Gilles Plante

                • 5. Re: Having a 'summary' field displayed inside a portal
                  erolst

                  planteg wrote:

                  If there is a single very important notion in FileMaker, I think it's TOCs.

                  Not sure what TOC would stand for, other than Table of Contents (but not in FM, which has a concept of TOGs (groups)); it's just TO.

                   

                  Regarded as the single most important concept in FM usually is context – though, since context depends entirely on the current layout, which in turn is based on a TO, this distinction is immaterial (but context sounds less technical/forbidding than TO).

                  planteg wrote:

                  In your example, I would never have figured out that Project (A) would only contain a list of Projects (kind of SELECT DISTINCT).

                  Probably an issue when you come from / have a mindset of SQL, which is context-free (and that, ironically enough, is what FM developers like about it).

                   

                  To solve your problem, one needs to know/remember that

                   

                  • a relationship is a filter, due to its predicates

                  • in a relationship chain, these filters work cumulatively (as long as no NULL set breaks the chain)

                  • a portal always shows records from a single related table (though, as demonstrated, not necessarily fields from only that table)

                  summary fields work with the related, as well as the found set

                  • a relationship chain works properly only forwards (relative to your starting TO), not backwards – that's why you need another TimeEntry TO “at the back”

                  • 6. Re: Having a 'summary' field displayed inside a portal
                    planteg

                    Thanks for the added information erolst. I wrote TOCs, but meant TOs .

                     

                    Gilles Plante

                    • 7. Re: Having a 'summary' field displayed inside a portal
                      planteg

                      Hi again erolst,

                       

                      I still have an issue, can't fix it. In my portal, I get the projects for the range of dates as expected, but the totals per project are for all of the records in the table, not for the range of dates. Can't figure out what's wrong.

                       

                      I checked all along the relationship chain, all is fine but for TimeEntry (B). I run FMPA 13.0v5. I will try this for a different set of data.

                       

                      Thanks

                       

                      Gilles Plante

                      • 8. Re: Having a 'summary' field displayed inside a portal
                        siplus

                        I still have an issue, can't fix it. In my portal, I get the projects for the range of dates as expected, but the totals per project are for all of the records in the table, not for the range of dates. Can't figure out what's wrong.

                         

                         

                        This is very basic in interface but does exactly what you asked for. IMHO.

                         

                        On changing the dates on your Enterprise layout you will see the portal update, both including/excluding projects altogether and updating the totals to reflect only work done in the selected period.

                         

                        That said, I still think that reports belong to list layouts based upon tables containing the data to be summarised, mostly for speed and interface reasons.

                        But if you really need it that way, it can be done.

                        • 9. Re: Having a 'summary' field displayed inside a portal
                          erolst

                          Hi Gilles,

                           

                          sorry, got carried away and totally forgot about that little problem – when you look from  projects into TimeEntry, it's again only by project, i.e. you lose the date constraint.

                           

                          Do this: Add two calc fields, type date, to the Projects table – cDateBegin ( = global date begin from Dashboard/Enterprise table), and cDateEnd (analogous).

                           

                          Now define the final relationship as matching on project_id and constraining by dates

                           

                          Project (A)::id = TimeEntry (B)::id_project

                          Project (A)::cDateBegin ≤ TimeEntry (B)::date

                          Project (A)::cDateEnd ≥ TimeEntry (B)::date

                           

                          Maybe the same thing siplus did in his file.

                           

                          You could also (as you will know) do this by SQL, along the lines of

                           

                          ExecuteSQL ( "

                            SELECT P.name, SUM ( cDuration )

                            FROM TimeEntry T

                           

                            JOIN Projects P On T.id_project = P.id

                           

                            WHERE cDate >= ? AND cDate <= ?

                            GROUP BY P.name

                            " ; Char(9) ; "" ; Dashboard::gDateBegin ; Dashboard::gDateEnd

                          )

                           

                          I second siplus in that using a layout report is more straightforward; but i can understand the need to instantly see a result …

                          • 10. Re: Having a 'summary' field displayed inside a portal
                            planteg

                            Hi siplus and erolst,

                             

                            if finally works with siplus method in the attached database, corresponding to latest erolst's message. I must say I learned a lot with that portal !

                             

                            The reason I am doing it this way is that the data is attached to a dashboard layout set on table Enterprise. Now that it works, I found out that the portal is slow to update when I change the date range. Just below the portal, I display the grand total from a field in the Project table that is a summary field, which even takes longer to refresh. At this time, I set a Pause for 1 sec. and force refresh the field.So the method does not seem optimal in terms of speed.

                             

                            I could also create a virtual list with an ExecuteSQL() calculation whose result would be parse into lines of a table, but again would need to display it through a portal. Would that be faster, I don't know yet. I will try and compare on a second portal on the same dashboard.

                             

                            ADDED: the database is hosted on FMS.

                             

                            Gilles Plante

                            • 11. Re: Having a 'summary' field displayed inside a portal
                              siplus

                              Hi Gilles,

                               

                              what I mentioned (speed and interface reasons) has a big weight.

                               

                              Unfortunately, many incoming questions do not land on the forum together with the correct context:

                               

                              - how many records are we talking about in 4 years from now / are involved as of today;

                              - Why do you want it that way / what do you plan to use it for.

                               

                              The solutions given are sometimes the answer to the challenge posed by the problem but not to the problem itself, and this is bad. A lot of people here are happy to make apparently impossible things happen or play Holmes, I'm implicitly adding myself to that figure. But after unknotting it, a bitter taste remains: a poster asked for a prime number never mentioned before, we found one, but were not offered the opportunity to express the algorithm behind our thinking. That specific algorithm does have parameters but unfortunately was called without them, so the answer does not represent the whole thinking, it's only a photo of it instead of being a movie.

                               

                              Hijacking your thread in order to express a bigger concern ? Maybe, but I'm sure you won't mind

                              • 12. Re: Having a 'summary' field displayed inside a portal
                                planteg

                                Hi siplus,

                                 

                                I agree that most questions do not include all the information needed to provide an accurate answer.

                                 

                                In my case, I know quite well SQL, having worked with SQL Server and Access. I know basically that FileMaker works without needing to code SQL queries. So I kind or refrain sometimes using ExecuteSQL() in order to learn the FileMaker way. This is why I wanted to know how to implement the dashboard using portals - in this instance only to display information. Then I realized by myself that it's quite slow. So I replaced the portal by a text field on the table on which the layout is based, a simple Edit Box field, that I feed from ExecuteSQL(), one result per line. It's way much faster . All I need to do is call a script when the dates range is changed, and in a fraction of a second the field is updated.

                                 

                                Gilles Plante