1 2 Previous Next 23 Replies Latest reply on Jan 6, 2015 1:47 PM by disabled_rosalie





      Hi Everyone,

      I have two tables Time & Job

      Time Table
      Field NameTypeOptions
      idTextAuto-enter Calculation, (Get UUID)
      ID_TImeTextIndexed, Auto-enter Serial


      Project Table
      Field NameTypeOptions
      idTextAuto-enter Calculation, (Get UUID)
      AccountNameCalculationUnstored, Get ( AccountName)

      The two tables are connected by Time:ID = id_project

      The Activities Field in Project Table has a Drop-down value ( Assay, Calibration,Content Uniformity,Dissolution,Heavy Metals,ID,LOD,LOI,Moisture,Potency,Related Compounds,Residual Solvents,ROI,Sampling,TOC,Validation/Verification)

      I am having trouble how to get the Activity_hour of each Activity Field. I want the summary will be like the attached image.

      any help and suggestion to achieve this is highly appreciated.

      Thank you so much

        • 1. Re: Summary

          Does each row in this sample report represent a single records or a group of records?

          I would guess that each row represents a group of project records with the same project ID?

          I'd think that the activity field should be defined in Time and that these hours would be totaled up from multiple records in Time all linked to the same record in Project.

          But that doesn't match your description of how you have designed this.

          • 2. Re: Summary

            Sorry, but I don't have time to download and examine a file for free at this time. (I normally get paid for that kind of task...;-)

            • 3. Re: Summary

              Hi Phil,

              Im using the Time Layout from Time Table, Under my time table I used a portal from project Please find attached below.

              I also have a have a button Add Project with the script

              Allow User Abort [Off]

              Set Error Capture [On}

              Set Variable [$Time_Id; Value:Time::ID_Time]

              Go to Layout ["Project_Line Items"(Project)]

              New Record/Request

              Set Field {Project::id_project; $Time_Id]

              Go to Layout [original layout]

              Refresh Window []

              my Problem is how can I capture the value of each activities that is under my Activities field in my summary report. 

              Thank you so much.


              • 4. Re: Summary

                Let's back up and examine basic concepts first. It may be that you need to change your data model before we do anything else.

                What does one record in Time represent?

                What does one record in Projects represent?

                It appears that one record in Time represents one interval of time (a day's work perhaps?) put in by a single person and that you then use the portal to Projects to record what portion of that time interval was worked on different projects during the day. (I do something similar when working as an independent consultant and also had to do this when working for an aerospace company that built communication satellites...)

                Am I understanding your design correctly?

                If so, at some future point in time, I suggest adding another table that has one record for each project as that is likely to prove a useful table for you to have in order to manage data on each project....

                • 5. Re: Summary

                  Hi Phil,

                  Happy New Year!

                  My Purpose for the Time Table is just to capture the Account name of the user who will login to the database. 

                  For the Project Table is to store all the data or all the project or activities they are working for a day.

                  Sometimes one user is working for a two or more project in one day that why I created a portal to enter as many project they are working for one day.

                  If this is the case, do I need to create another table?

                  Thank you.



                  • 6. Re: Summary

                    My Purpose for the Time Table is just to capture the Account name of the user who will login to the database.

                    Does that mean that you have one and only one record in Time for each user account name? (sorry but your table names keep leading me into incorrect assumptions here. I'd name such a table "users", "accounts" or "employees" to better represent the purpose of that table, and I'd name your "projects" table "WorkLog", "TimeLog" or some such for the same reason, but it's your database you can name these tables however makes best sense to you...

                    If I have that correct, it IS possible to set up the report in the format that you are requesting. BUT it's not the easiest report to set up in FileMaker. As a beginning step towards that format, I suggest setting up a simpler summary report that reports the same totals, but in much less "compact" a format. Once you can get that working for you, we can take a run at the "cross tab" format you show at the beginning of this thread.

                    Make a list view layout based on Projects.

                    Add a Sub Summary layout part "when sorted by Account Name. Put the account name and any other fields from Time that you want into this layout part.

                    Add a Sub Summary layout part "When sorted by Activities". Put a summary field define in Projects that computes the "total of" Activity_Hours into that sub summary layout part. Add the Activities field to this layout part

                    Remove the body layout part.

                    Add any other fields to this layout as you see fit.

                    If you then show all records, then sort them first by AccountName, then by Activities, you'll get a report similar to this:

                    Account name: Account 1
                         Sampling       10
                         Calibration     65
                         Disolution  760.5
                         and so forth...

                    Account Name:: Account 2
                         Sampling        18
                         Calibration  160.5
                         Disolution    508
                         and so forth...

                    And so forth...

                    Keep in mind that this need not be the final version of your report. It's just a simpler format to set up just to get you started.

                    • 7. Re: Summary

                      Hi Phil,

                      Please find attached result of your suggestion.

                      Hope I can go to the cross tab reporting.

                      Thank you so much.

                      • 8. Re: Summary

                        Congratulations! You've gotten to the first step and have also produced a report format that can be used in the short term while you set up the crosstab version which can use the same summary field that you defined for this simpler option.

                        How up to speed are you on setting up filtered portals? The cross tab version I will be posting here starts from a list view layout based on Time with a series of one row portals that display data from Projects in order to provide the columns of summarized data in your cross tab. So if you know how to set up a portal and define a portal filter in it, this will save explanation. 


                        • 9. Re: Summary

                          Hi Phil,

                          I am in trouble in setting up the portals. Please help me.

                          I made a Layout ( Summary_Crosstab show record from Time Table) I set up the first Portal with 1 row (Show related record from Project I put the field CreationAccount) the second portal I Put a heading Assay (Show related record from Project I put the field Activities Time ; Sort order by Activities)


                          Thank you.

                          • 10. Re: Summary

                            You are running ahead of my instructions. wink

                            I simply asked if you had set up filtered portals before so that I could better "guage" the amount of detail needed in my next post.

                            What portal filter did you define for the second  portal?

                            The first column does not need to be a portal at all. Just add the creation account name field from Projects directly to this layout.

                            You will need to define portal filters for each portal such as:

                            Projects::Activities = "Assay"

                            That would be your first column of summarized data. Once you have it correctly showing the total that you want in the format that you want, (each of these will use the exact same summary field), you can return to layout mode and duplicate the entire portal before re-entering portal set up to modify the portal filter to modify the quoted text to name the other activities.

                            • 11. Re: Summary

                              How can I get the total of hour of each activities?

                              • 12. Re: Summary

                                You seem to have missed a detail from my instructions:

                                Once you have it correctly showing the total that you want in the format that you want, (each of these will use the exact same summary field),

                                Remember that summary field I had you define in the earlier, simpler summary report? That's the sole field that you put in each of your one row filtered portals.

                                • 13. Re: Summary

                                  Hi Phil,

                                  Thank you. The summary field is working on a row, but I can't get to work on column.


                                  • 14. Re: Summary

                                    That statement makes no sense. These should be one row portals on a list view layout. If they are working on a row, they are also working on a column. Can you post a screen shot to explain what you mean?

                                    1 2 Previous Next