12 Replies Latest reply on Oct 7, 2011 9:19 AM by LambdaEnt

    One weekly summary record, for multiple records

    LambdaEnt

      Title

      One weekly summary record, for multiple records

      Post

      Multiple entries are made for payments to independent contractors each weekending date. Those payments are submitted weekly for reiumbursement, but as a single "lump sum" for the week, not by individual timesheet. I need to record status of reiumbursement for those payments, but for each week, not for each timesheet. Having trouble figuring it all out.

      Tried several things and I'm sure, once again, I'm missing something painfully obvious. Whenever I struggle with the logic and I'm about to go nuts, I know one of you will enlighten me!

      Example:

      I ALREADY HAVE THIS:

      Timesheets
      Name          Weekend   Amount
      Employee 1  9/25/2011   $525
      Employee 2  9/25/2011   $610
      Employee 1  10/2/2011   $450
      Employee 2  10/2/2011   $725


      I ALSO NEED THIS:

      Payment Status
      Weekend     Amount     Sent         Paid
      9/25/2011    $1135    9/26/2011  9/29/2011
      10/2/2011    $1170    10/4/2011  10/6/2011

        • 1. Re: One weekly summary record, for multiple records
          Sorbsbuster

          Presumably there was something common linking your first list of employees - they all worked for Contractor A?  That suggests to me you want to link these Detail (child) records up to a Header (parent) record, where each record is one Contractor, one Week.  Then record the paid Status on that record.  By relationship all the child records pertaining to that invoice will be classed with the same status.

          • 2. Re: One weekly summary record, for multiple records
            LambdaEnt

            Ok, but could said record be created automatically when new timesheets are created?

            • 3. Re: One weekly summary record, for multiple records
              philmodjunk

              could said record be created automatically when new timesheets are created?

              That can be scripted pretty easily or you can use a portal from the header record to create and update the timesheet records. Kinda like an invoice where the time entries are your line items.

              • 4. Re: One weekly summary record, for multiple records
                LambdaEnt

                The timesheet records are what the end user will be creating, so need it to go the other direction.

                • 5. Re: One weekly summary record, for multiple records
                  Sorbsbuster

                  Most Parent-child records start with the parent (eg: the Purchase Order header) then once that is created the child records (Purchase Order lines) are created from the header layout.  It sounds like yours are naturally crated in reverse.  If the common thing is, say, ContractorID and the WeekNumber, then why not enter each time sheet as it arises and make sure the ContractorID and WeekNumber are completed.  Then when aan invoice arives create that record once, to marke the receipt of the invoice.  As soon as it is created with the ContractorID and WeekNumber  it will immediately 'own' all of the child records.

                  I wouldn't think it is worth the effort, even, to write a script to check if a parent record exists as each child record is created, and if no parent exists, create one.  But nothing to stop it being done.  If you know what weeks contrcators will be submitting invoices you can even prepare them as a set in advance of the timesheets.

                  • 6. Re: One weekly summary record, for multiple records
                    LambdaEnt

                    Thank you, but that's not what I'm needing, and won't solve the problem. Perhaps someon else can chime in.

                    • 7. Re: One weekly summary record, for multiple records
                      philmodjunk

                      "it won't solve the problem" is the kind of statement that makes it hard for us to suggest alternatives as we can't see why it won't work for you. Can you elaborate on that a bit?

                      Another option is to set up a summary report with sub summary parts to pull your multiple records into a single row in the sub summary part. If you delete the body from such a layout, you get just the "condensed/subtotaled" view of the data instead of the individual records.

                      • 8. Re: One weekly summary record, for multiple records
                        LambdaEnt

                        Sorry, I was trying to indicate that I was hoping for something a bit more fluid, where the weekly summary record would be created when a timesheets are generated. I'm concerned a script would generate a new weekly record with each timesheet, making for multiple entries, when only one is wanted. That would take some finesse.

                        What you just suggested is kind of what I had already created, but then had issues of what table the summary field would go and what the relation needs to be. Tried a few things and that's where i got stuck and wrote this!

                        The entire purpose of this system was to essentially automate what had been an extensively complex weekly task. I've got it ALMOST down.

                        So to recap, the end-user (data entry) is going to create timesheets. I need a summary record created automatically for each week. 

                        • 9. Re: One weekly summary record, for multiple records
                          Sorbsbuster

                          Why not create a summary record every time a time sheet for that contractor for that week is created for the first time?  There are many ways to automate that, but one would be a script trigger on the second field (the week number, say).  Have a relationship to the Summary file, and if the count of matching records is zero (ie: no header exists), then create one.

                          • 10. Re: One weekly summary record, for multiple records
                            Sorbsbuster

                            "but then had issues of what table the summary field would go and what the relation needs to be."

                            The Summary table sounds like a table of its own.  And its relationships (for this) are simply to the Time Sheets, linked by whatever the defining criteria are.  In my example, it is ContractorID and WeekNumber (or WeekEndingDate).

                            • 11. Re: One weekly summary record, for multiple records
                              philmodjunk

                              The script involved shouldn't need to be terribly complex and checking for a pre-existing record would be pretty simple to do. Creating the record at a later point when you actually need it for your report as suggested by Sorbsbuster is also an option worth looking at here.

                              What you just suggested is kind of what I had already created, but then had issues of what table the summary field would go and what the relation needs to be. Tried a few things and that's where i got stuck and wrote this!

                              What I am describing does not require a separate table. Everything, including the summary field, is set up in your TimeSheets table or a layout that refers to it.

                              I don't really see why working from a portal on a layout based on the parent record to create time sheet records common to it would be a problem, but then I also have only a very limited view of your design, so I freely admit that you may have very good reasons for rejecting this option...

                              • 12. Re: One weekly summary record, for multiple records
                                LambdaEnt

                                Thanks both of you. Have friends in from SF and we are headed to Magic Mountain. I'll check this stuff out later tonight, and et you know the progress. Thanks for you patient assistance!