12 Replies Latest reply on Feb 6, 2013 2:38 PM by philmodjunk

    Creating totals from multiple portals - report setup

    DougBlemker

      Title

      Creating totals from multiple portals - report setup

      Post

           So, I'm not sure where to start. I know just enough to cause myself problems. I took one of the startup solutions and combined project management and time tracking. I have figured out how to add up my "worked time" from a portal within one of my main layouts, but I need to take all the different projects related to one client and have the system add that up. The attached image shows my tables, the relationships and a sample of the output of a report where I need to add up the totals and it is only adding up the first section (.74) instead off all three.

           Once I get the basic reporting, I need to be able to put that in an invoice where I pull all the projects from a certain client ID for a time period (one month). I don't even know where to start with that.


           Thank you so much for any assistance you can provide. I'm happy to provide anything additional if you need it.

           Doug

      FMSamples.jpg

        • 1. Re: Creating totals from multiple portals - report setup
          philmodjunk

               It's not clear to me why you have the table TimeBilling with its own primary key. What is the purpose of this table?

               It would seem a link from Projects, directly to TimeBillingData would make more sense.

               And you do not need that table to get totals or subtotals from the TimeBillingData table in most cases.

               Is your portal a portal to TimeBillingData?

               And one which table is the layout that contains this portal based? Clients? Projects? or ???

               In many cases, if the relationships support doing so, the best approach is to use a portal for data entry but not for printing out invoices or other reports. Take a look at how the invoices starter solution prints an invoice using a layout that does not contain a portal. Instead, it pulls up the lineitems (Called InvoiceData in FileMaker 12 starter solution) records on a list view layout for a given invoice and prints from that layout.

          • 2. Re: Creating totals from multiple portals - report setup
            DougBlemker

                 Hi, thank you for your quick response. The TimeBIlling table and timeBillingID table were pulled directly from the FMPro starter solution. I simply copied that. I wondered the same thing upon initial setup but that is how the time tracking is set up in the sample piece, so that is what I did. The portal is a portal to TimeBillingData, yes.

                 The portal is included in a table that is a combination of PROJECT and my external MySQL database that clients and coworkers submit their jobs from. 

                 Thank you for your thoughts. I figured the portal is best for the data entry, I've avoided using it in anything else but not sure the best way to pull all the pieces together and to get the math to add up.

                 Doug

            • 3. Re: Creating totals from multiple portals - report setup
              philmodjunk

                   Where do "multiple portals" come in? So far, I only see one portal.

                   If you want to see a report like this:

                   Client abc
                      project 1 total time, cost, etc
                      project 2 total time, cost, etc

                   Total for Client abc

                   Client xyz
                      Project 3 total time, cost, etc
                      project 4 total time, cost, etc

                   Total for Client xyz

                   (Data for more than one client is an option, it works the same for just one client.)

                   Then you can set up a Creating Filemaker Pro summary reports--Tutorial layout based on the TimeBillingData table and include fields from TimeBilling, Projects and Clients as needed to get this report. Both Go TO Related Records or a scripted find can be used to pull up the desired records in TimeBillingData to produce the needed found set. Each row of data shown in this example would be from one of three sub summary layout parts (with no body layout part) with the records sorted first by Client and then by project.

              • 4. Re: Creating totals from multiple portals - report setup
                DougBlemker

                     I'm very sorry. I just tried this and it doesn't make sense because nothing adds everything up? I just end up with a blank report. Please help me understand, I really apologize but this seems to take me one step back instead of one step forward.

                • 5. Re: Creating totals from multiple portals - report setup
                  philmodjunk

                       The report will be blank unless your sort your records. If you don't intend to list the individual records in your report, you need to delete the body layout part from this layout.

                       Did you click the link on Creating Filemaker Pro summary reports--Tutorial in my last post?

                  • 6. Re: Creating totals from multiple portals - report setup
                    DougBlemker

                         Ok, so I went back and re-read everything and although not perfect, I realized I was reposting my calculated fields in the summary sections, not the actual fields. I corrected that and step one seems to be working better and I have a better understanding of how those summary sections work compared to summary fields or calculated fields on portal-loaded layouts.

                          

                         Thanks!

                    • 7. Re: Creating totals from multiple portals - report setup
                      DougBlemker

                           Ok, back again. Everything was working, I moved the fields around a bit to make it look better and now it doesn't work again. I only get the total of the last section. Here is a screenshot of the report setup.

                      • 8. Re: Creating totals from multiple portals - report setup
                        philmodjunk

                             Are you referring to Total in the bottom sub summary part?

                             If so, it is referring to amount instead of sub total.

                             Or are your referring to Project Time and Subtotal in the next sub summary above it?

                             Those fields, judging by the :: that precede their names have been selected from a different table occurrence than TimeBillingData and thus are unlikely to be computing the correct subtotals. (Subtotals from related tables summarize the set of related records instead of a group of records in the current found set.)

                        • 9. Re: Creating totals from multiple portals - report setup
                          DougBlemker

                               I am referring to project time and subtotal in the first lower subsection (I have tried both the calculated fields - from my original picture) and standard fields of hours and amount and it doesn't change the outcome. I also can't get it to change in the final subsection either. Everything worked fine yesterday and I don't know what would have changed it. I removed fields from the body and re-arranged things. Here is a screen shot with current "sort" to show you. It shows the sorted project category, then the time listing within that project. It doesn't add up the time in the first subsection, nor does it total it from all the projects above it. I'm totally lost.

                                

                          • 10. Re: Creating totals from multiple portals - report setup
                            philmodjunk

                                 These fields should be summary fields defined in TimeBillingData, not from a related table.

                                 And then this same field would also be put in the final subsection.

                            • 11. Re: Creating totals from multiple portals - report setup
                              DougBlemker

                                   Ok, so I need to create new fields within TimeBillingData as summary fields. See current field listing attached.