1 Reply Latest reply on Dec 27, 2016 9:38 AM by philmodjunk

    from performed tasks to invoice - summarized by task


      I'm working on a projectmanagementsystem for my own company.
      Basically there's a database with projects, and there's a database with all tasks performed.

      Then there's a database with invoices, and there's a database for the invoicedetails.

      When creating the invoice, I'd like to have all tasks for a given project summarized into the projectdetails. For example: there are 5 records "editing" and 3 records "finishing" for a project. In my Invoicedetails-database I'd like to create 1 record with the summarized amount of editing and record with the summarized content of finishing. Off course the terms editing and finishing are variable, it could be named anything...

      Any clue what the best scripting-approach would be?

        • 1. Re: from performed tasks to invoice - summarized by task

          Basically there's a database table with projects, and there's a database table with all tasks performed.

          Then there's a database table with invoices, and there's a database table for the invoicedetails.

          "database" is a fuzzy term that can refer to a single table, one file with many tables or even many files with many tables in each file. So I am showing you how I understand your description. I will further assume that you have these relationships:


          Projects::__pkProjectID = Tasks::_fkProjectID

          Invoices::__pkInvoiceID = InvoiceDetails::_fkInvoiceID

          Projects::__pkProjectID = Invoices::_fkProjectID


          There are a number of possible ways to do what you want. The devil is in the details and which combination of details works best for you will depend on how your business operates.


          Are Tasks the only source of data for what you bill by listing item in the InvoiceDetails table or are there other goods and services that need to be included in that invoice that aren't tasks?


          Am I correct that you do not have a standardized list of tasks performed and that these can be completely unique to each invoice?


          Do you create one invoice for each project or can there be multiple invoices for a given project?


          Most possible solutions that you might set up would be variations of this method:


          Find all uninvoiced tasks for a given project. Sort them by Task ID or Task Name to group them by task. Loop through these records creating one record in InvoiceDetails for each group of "same task" task records--entering a subtotal based on that group into the new invoice details records. But there are then a number of different ways that you can do the above. A script can perform a find to find records. Go To Related Records can pull up a set of related records. ExecuteSQL can access the data. And your subtotals can be computed from the Sum function in one context, a "total of" summary field in another and ExecuteSQL also can compute a sum for a given group of records accessed via its query.


          If you have or create a table of "task types", there's another approach that can be used from the context of that table and some relationships, but if I am correct that your "tasks" vary widely from project to project, this isn't likely to be a useful approach.