1 2 Previous Next 16 Replies Latest reply on Jan 15, 2015 4:27 PM by disabled_rosalie

    Summary Report Part II

      Title

      Summary Report Part II

      Post

       Hi Everyone,

      Here I am again, need some guidance,  before that I want to give some info regarding my database that Im working on. Im am trying to make all my excel report to a Filemaker.

      Below are the info of my Two tables 

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      Time Table
      Field NameTypeOptions/Comments
      idTextIndexed, Auto enter Calculation (Get (UUID)
      CreationAccountTextIndexed, Creation Account Name, Can't Modify Auto
      AccountNameCalculationUnstored, = Get (AccountName)
      ID_timeTextIndexed, Auto-enter Serial
      Date_createdDateCreation Date, Can't Modify Auto
      Date_modifiedDateModification Date, Can't Modify Auto

       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      ProjectsTable
      Field NameTypeOptions/Comments
      IdTextIndexed, Auto enter Calculation (Get (UUID)
      CreationAccountTextIndexed, Creation Account Name, Can't Modify Auto
      AccountNameTextUnstored, = Get (AccountName)
      Id_projectTextIndexed
      DateDateDate
      ActivitiesTextIndexed
      Activities_timeNumber 
      Activities_summarySummary=Total of Activities_time
      Lot_No.TextIndexed
      Project_CodeTextIndexed
      EquipmentTextIndexed
      Equipment_timeNumber 
      Equipment_summarySummary=Total of Equipment_time

      My two tables are connected with a relationship ( Time::ID_Time = Project::Id_project)

      I've been trying to make a summary like the attached image but I didn't get any luck. 

      Please push and direct me to achieve the report that I want.

      Thank you so much in advance.

       

        • 1. Re: Summary Report Part II
          SteveMartino

          Based on your screenshot, I would look into/google a cross tab report

          • 2. Re: Summary Report Part II

            How can I get the summary of each project code by month?

            Thank you.

            • 3. Re: Summary Report Part II
              philmodjunk

              You can't angle your dates as shown, they'll either be horizontal or rotated 90 degrees.

              But you can define a global date field, gFirstColDate,  for the first column and then define a series of calculation fields for the remaining columns.

              2nd column field:

              Date ( Month ( gFirstColDate ) + 1 ; 1 ; Year ( gFirstColDate ) )

              3rd column field:

              Date ( Month ( gFirstColDate ) + 2 ; 1 ; Year ( gFirstColDate ) )

              And so forth. The date function will automatically return a year for the following year when the value for the month exceeds 12.

              You can format these date fields to only show the month name and two digit year using the data formatting options on the Inspector's data tab.

              And you can then create the columns of summarized data just like the last cross tab that I helped you with, but now your portal filter expressions can be something like this:

              Month ( LayoutTable::gFirstColDate ) = Month ( PortalTable::Date ) AND
              Year ( LayoutTable::gFirstColDate ) = Year ( PortalTable::Date )

              Just subsitute a different field for gFirstColDate in each portal's expression and use your table occurrence names in place of LayoutTable and PortalTable.

              • 4. Re: Summary Report Part II

                Hi Phil,

                Thank you so much.

                Please correct me if I'm wrong. Am I gonna put all those global date fields to my table Projects?

                Thank you.

                Rose

                • 5. Re: Summary Report Part II
                  philmodjunk

                  I thought your projects table was what you were using to record the time worked on each project?

                  This global field can be defined in any table in your file and it is still accessible from any layout. The only time that it really matters in which table the field is defined is when that global field is used as a match field in a relationship.

                  • 6. Re: Summary Report Part II

                    Thanks Phil,

                    Please find the image of the fields that I've created.

                    Thanks.

                    • 7. Re: Summary Report Part II
                      philmodjunk

                      That is not what I recommended.

                      ONE field would be global.

                      The others would NOT be global but would calculated their values from the single global field. This will make them unstored calculations, not global fields.

                      And the calculations you show are not the ones I recommended. The calculation fields use the Date function only.

                      The portal filter expressions are the ones that refer specifically to month and year values.

                      But I repeat a question yet unanswered: From your other thread, isn't Projects the table where you record time for each project, for each account? If so, how do you plan on using that table to get one row for every project?

                      That can be done here if this is how the data is structured in this table, but you might consider creating a table with one record for every project as this would likely have uses other than just this report.

                      • 8. Re: Summary Report Part II

                        Hi Phil,

                        Sorry if I didnt follow your instruction, I have changed now the gFirstColDate into global and the other calculation showing now unstored.

                        But I repeat a question yet unanswered: From your other thread, isn't Projects the table where you record time for each project, for each account?

                        Yes, Im storing all the records on my Project Table.

                        Thank you. 

                        • 9. Re: Summary Report Part II

                          Hi Phil,

                          Month ( LayoutTable::gFirstColDate ) = Month ( PortalTable::Date ) AND
                          Year ( LayoutTable::gFirstColDate ) = Year ( PortalTable::Date )

                          Just subsitute a different field for gFirstColDate in each portal's expression and use your table occurrence names in place of LayoutTable and PortalTable.

                          Im having a confusion on how to do it on portal filtering.

                           

                          Thank you. 

                          • 10. Re: Summary Report Part II
                            philmodjunk

                            What is "Date_Crosstab Column". What is it's data source table and how is it related to projects?

                            This touches on the reason that I asked about the projects table as it appears that the records you need to sum in these columns is already data in your projects table. That's only part of why I have recommended that you set up a table with one record (and a project ID) for each project. You can use such a table as the basis for this report layout as well as the source of values for a value list listing project names and ID's (which might come in handy in other parts of your database.)

                            • 11. Re: Summary Report Part II

                              What is "Date_Crosstab Column". What is it's data source table and how is it related to projects?

                              It is a table of occurrence of Project table and I used as my portal filtering. I also made a relationship with my Table Time (Time::ID_Time x Project::id_project).

                              That's only part of why I have recommended that you set up a table with one record (and a project ID) for each project. You can use such a table as the basis for this report layout as well as the source of values for a value list listing project names and ID's (which might come in handy in other parts of your database.)

                              I made another table (Value List) with  fields (id, text, Auto-enter calculation(Get(UUID)) and project code. Can I store all my project code in this table?

                              Thanks. 

                               

                               

                               

                              • 12. Re: Summary Report Part II

                                Which table I'm gonna connect the relationship of my new table (Value List)?

                                Thanks

                                Rose

                                • 13. Re: Summary Report Part II

                                  Hi Phil,

                                  I tried playing with this calculation on my portal filtering but Im not lucky to get the summary each month

                                  Month ( LayoutTable::gFirstColDate ) = Month ( PortalTable::Date ) AND
                                  Year ( LayoutTable::gFirstColDate ) = Year ( PortalTable::Date )

                                   

                                   

                                  • 14. Re: Summary Report Part II

                                    Hi Phil,

                                    Sorry I forgot to mention that this part of my summary report is I want to pull out all the project and the summary time of all the activities that is done by each user (Account Name) for certain month.

                                    Thanks.

                                    Rose

                                    1 2 Previous Next