6 Replies Latest reply on Sep 6, 2016 6:12 AM by flamecoder

    Script to create summary of time

    flamecoder

      I want to create an emailed report of time spent on a project during the last week. I have a scaled down drawing to show the relations.

      Project Summary.png

      From the Project table my script finds the records in the Time table between a range in Time Date field.

      Records are sorted by __kf_Designer ID.

      I have a calculation field on the Project table that takes the Time Length seconds value from the Time table and puts it into hh:mm:ss.

      I also have a summary field on the Project table that totals the hh:mm:ss from the calculation above.

      I'm trying to use the GetSummary(Project:Time Log Summary;Project:_kf_DesignerID) while looping thru the groups, when a change in _kf_DesignerID is detected, to get only the records that are within the range but it doesn't seem to work correctly.

       

      Can anyone tell me how to go about doing this?

        • 1. Re: Script to create summary of time
          philmodjunk

          Sounds like the summary field should be defined in Time, not Projects,

          • 2. Re: Script to create summary of time
            flamecoder

            I've tried a variation of that... From what I understand to use GetSummary() in a script I have to have the summary and the group by in the same table. I want to group by __kf_Designer ID which isn't in the Time table.

            • 3. Re: Script to create summary of time
              philmodjunk

              Yet the summary itself won't compute the correct totals or subtotals if not defined in the correct table for the totals desired. I Probably don't have all the details here, but if you are performing a find on the time table, you should be basing your layout and any needed calculations on the time table, not project.

               

              I don't see yet why you would need the getsummary function for this particular task.

               

              And summary fields are not your only options for computing aggregate values there are also aggregate functions that can sometimes be used.

              • 4. Re: Script to create summary of time
                flamecoder

                Thank you for pointing me in the right direction. I'm almost there...

                I built a layout based on the Time table. I find the records with a range of dates and I have a leading sub summary grouping them by Designer ID. I also have a trailing sub summary to total the individual groups with a summary field in the Time table.

                 

                Now I need to take each individual group and compose an email to send to each designer to show invested project time. This is where I was going to use the GetSummary to get the sum for each group by looping thru each record and detecting when ID changed.

                 

                Loop

                Set Variable [$DesignerID; Value:__kf_Designer ID]

                if [$DesignerID ≠ $DesignerIDLast

                Send Mail [...GetSummary(Time::Time Summary;Project::_kf_Designer ID)]

                End If

                Set Variable [$DesignerIDLast; Value:$DesignerID]

                Go to Record/Request/Page [Next; Exit after last]

                End Loop

                 

                I would like to email each line of time and the sub summary to compute the total to each person like the layout shows but not whole report to a group. Is there a way to go about this?

                Screen Shot 2016-09-01 at 2.41.20 PM.png

                • 5. Re: Script to create summary of time
                  philmodjunk

                  There are multiple options. You can build a simple list of information in the body of the email or you can use this same layout in a separate window (to keep this report and found set unmodified) to produce a PDF of just one such group at a time for attachment to the email.

                   

                  You'll need to decide which method you want to use here.

                  • 6. Re: Script to create summary of time
                    flamecoder

                    I'd like to try the pdf attachment of a single group first. I have the designer's email address in the Designer table. How do I iterate thru the groups to create this pdf. Sometimes not all available designers will have logged time, only the ones that show up on the original report need sent the information. Where do I start in taking this report and composing one group at a time into a pdf for sending email?