8 Replies Latest reply on Sep 29, 2010 8:23 PM by jared67

    totaling time from another table

    jared67

      Title

      totaling time from another table

      Post

      I'm trying to build a dirt-simple scheduler.

      I have a tasks_table and a daytasks_table. Daytasks is built to refer back to tasks, it represents a record with a date and an ID field back to tasks_table. I can sucessfully pull descriptive tasks_table data in.

      However, I'm also pulling in the time per a task (inside daytasks). I'm trying to see a grand total field TaskTimeTotal, which lives in tasks_table. Currently, my total is only showing the total of the one record I've selected, when inside the daytask view layout. I've tried placing this field in the footer and inside of a part, neither of which works. I'm also reaching a dead-end building a summary field inside daytasks, as the remote fields are not available to be summarized.

        • 1. Re: totaling time from another table
          philmodjunk

          Do you use a portal to display the DayTasks records? Do you want to see the total of all values in the portal?

          A calculation field defined in Tasks using the Sum function should compute that total: Sum ( Daytaskstable::Time )

          A summary field defined in Daytasks_table should also work. Don't quite follow what "remote" fields would exist that would need to be part of this summary field's definition.

          • 2. Re: totaling time from another table
            jared67

            Sorry to make my post confusing, it's just I am confused.

            In another view/database I have a portal that successfully totals the portal entries. This is currently not what I am trying to do. Right now I am trying to be very plain vanilla with my ambition. Recapping: I have "loose" tasks in a task table. I only (think I?) want a DayTask table as a 1/2 join table. Each record in DayTasks points up at it's original parent. The data flows downward into the DayTask record. Each task record's time correctly appears in the DayTask view. I'm stuck trying to total DayTask times, even though

            My calculated field in DayTasks currently is:

            >Field Name: CALCTotalTime -> Unstored, from DayTasks, = Sum ( Task 3::TimeTrackEst_HoursReq)

            ... which was placed in the body, part, a summary, and the footer, and it doesn't work.

            I also have another calcuted field in the Tasks table:

            >Field Name: TimeTrackTaskTotalCALCsum -> from Tasks, = Sum (TimeTrackEst_HoursReq)

            ...this doesn't work either in body, summary, or footer part.

            However, when I make a summary field in the Tasks table, I can see a proper total of time. This field (to my knowledge) can't be used in the DayTasks table/view.

            I can't build a summary field in DayTasks, as the tasks are in the (remote) Tasks table.

            At this time, I've simply build a [show related records] buttons. Going back to the task view seems like the only option at this point.

            Many thanks for your advice.

            • 3. Re: totaling time from another table
              philmodjunk

              Now I'm confused!

              You started with two tables: tasks_table and Daytasks_table, but in your last post you refer to a table occurrence called "task 3".

              Where'd that table occurrence come from? You need to document all the table occurrences and their relationships before anyone can take a real stab at helping you.

              Sum (TimeTrackEst_HoursReq) will give you the same result as just referring to TimeTrackEst_HoursReq. The typical uses of Sum are Sum ( field1 ; field2 ; field 3 ) or Sum ( relatedTableOccurence::relatedfield ). The second form is typical for totaling a value from all the records in the related table that are related to the current record of the table where you defined this calculation.

              My original post was built on the following assumptions

              1. One record per task in Tasks_Table.
              2. Many records per task in DayTasks_Table, each documenting time spent working on a given task. Add up all the DayTask_Table records for a given task and you get the total time spent on that task.
              3. That the database design was based on this relationship: Tasks_table::TaskID = DayTasks_Table::TaskID

               

              Now it looks to me like those assumptions were quite incorrect... :-(

              • 4. Re: totaling time from another table
                jared67

                Tasks 3 is just an alias of the Task table. Tasks 2 is referenced to other tables wholey unrelated. My apologies for firstly referencing tasks_table, and secondly not noting the alias bit. I forgot the KISS principle.

                Your assumptions are almost correct. DayTasks is really just a placeholder to figure out if x tasks will go over a x hours in a day and/or if sleeping is an option. That's a warm and fuzzy hope of mine. Tasks are the "absolute" records of a task and their detail.

                In DayTasks I have a calculated field in the footer:

                Field Name: CALCTotalTime :  Sum ( Tasks 3::TimeTrackEst_HoursReq )

                which shows not the total of 2+ records, but just a copy of one selected field's time.

                • 5. Re: totaling time from another table
                  philmodjunk

                  As previously requested, please document for me exactly how your tables are related. To me your Sum calculation appears backwards. A sum calculation defined in tasks should be able to return the total from a specified field in DayTasks if there are many daytask records related to a given task record--but that could just be due to my lack of understanding about how you've set this up.

                  • 6. Re: totaling time from another table
                    jared67

                    I tried putting together a graphic composite, but the size nazis at photobucket truncated everything to 1022x569. I wasn't swift enough to save the original with layers. Sorry that I have to explain.

                    Top left: Tasks table in list mode. Two records correctly can be summarized totaled at the bottom, even though one record is selected.

                    Top right: DayTasks table in list mode, showing only two records. Two green arrows point to a total that can't be summarized/calculated in red, and instead show one time from the second record pointed at in an oversized blue arrow. Sharp green arrow points to a [show related records] button.

                    Bottom left: field list of DayTasks. First 5 fields are for creation/mod time/date +ID. 2 user fields (unused). TaskID (parent task that data is being imported from). And a calculation field that was previously mentioned as broken.

                    Bottom center: my table graph. It's only two tables. One is named Tasks 3, one is named DayTasks.

                    • 7. Re: totaling time from another table
                      philmodjunk

                      Well, I zoomed in to 200%, but all the text/numbers are too blurry to read. :( Perhaps you can make several images, upload them to a share site and post the links here. Or perhaps you can just post the text infor up after you read my following comments:

                      Top left: Tasks table in list mode. Two records correctly can be summarized totaled at the bottom, even though one record is selected.
                      I see not two but three records here. A straight summary field in this type of layout will total up all the records in the current found set and this has nothing to do with which record is currently selected. Set up this way, you can control the totals shown in the footer by controlling what records are present in the current found set.

                      Bottom left: field list of DayTasks. First 5 fields are for creation/mod time/date +ID.
                      Why do you have 5 fields for this instead of just one? Did you mean you have fields for creation date, creation time, modification date, modification time and a fifth for ID?

                      What fields and in which table actually hold the data you need to summarize?

                      Top right: DayTasks table in list mode, showing only two records. Two green arrows point to a total that can't be summarized/calculated in red, and instead show one time from the second record pointed at in an oversized blue arrow. Sharp green arrow points to a [show related records] button.
                      Can't do much with this as I can't read the text and numbers. If the data being summarized is all in DayTasks, it most definitely can be summarized with a summary field. If the data comes from the related tasks table, it can be summed in a calculation field that uses the Sum function.

                      It's only two tables. One is named Tasks 3, one is named DayTasks.
                      But what fields in each table match and what options, if any have you selected for this relationship. One way to document this is to type in text in this format:

                      Tasks3::FieldName = DayTask::FieldName (if a different operator than = is used in this relationship, use the correct operator.)

                      Another option would be to upload an empty copy of your file to a share site so that it can be downloaded and inspected.

                      • 8. Re: totaling time from another table
                        jared67
                        I'm sorry I can't explain my problem simply, with detail, or attempting to show an overview with with a picture. Out of frustration, I talked to a "FM expert" on the phone, recommended to me by a friend. After a short introduction, and a brief overview of the problem, he immediately suggested I make a calculation field inside the Daytasks table (to convert Task time data to a Daytask field that would be recognized by a Daytask:summary field). That's the solution and the system now works. And just to explain this one more time, the Tasks table is the meat of the database. The Daytasks table is just a container that held references and would function as a calculator only.