Discussions

Expand all | Collapse all

Calculating and charting monthly data from a total duration

  • 1.  Calculating and charting monthly data from a total duration

    Posted 11 days ago
    I have a set of tasks where I have a start date, end date, calculated duration in months, and total labor cost for each task. A task can be years long. Labor costs are linearly prorated across the task duration. My goal is to be able to chart total labor costs for each month. I could create a dedicated field for each month, but that scales up quickly to a lot of fields if tasks run across a long time duration like 15 years.

    I'm thinking instead that each month's labor cost has to be written to a record in a table. I know how to do that. My challenge is how to iterate through a task's duration to calculate a given month's labor costs, since in theory the application would start in the month and year of the task start, calculate the labor cost, write a record to the table, then go to the next month, repeat the cycle, and so on until the task end month is reached. In standard code a loop like this would be easy, but I'm not clear how to do this in the Quick Base environment. I've seen that a pipeline can iterate, but I think only as part of a search (query) of a set of records. I thought about having twelve tables, one for each month, and simply having fields for the labor cost and year, and then having twelve automations, one for each month, that would write the records to the appropriate table. Each of these month tables would be a child table to a parent tasks table, which would enable the rollup.

    Am I on the right track or is there a better strategy?

    Thank you,
    Paul

    ------------------------------
    Paul Streit
    ------------------------------


  • 2.  RE: Calculating and charting monthly data from a total duration

    Posted 11 days ago
    Edited by Mark Shnier (YQC) 11 days ago
    I do know how to do this but it might take some one on one working together to get this set up.  

    Create a child table of projects called project months. The truck now is how do we automatically get the correct number of projects months and have them labelled with the first day of the month for every month that the project will be active whether it be one month for 15 years.  

    The concept that I use is to have a table of months for as long as the longest possible project could take.  There's no cost to the records of those tables so make it 1000 records.  Perhaps call this standard project month template. 
    You would then fire an automation which would initiate when a project is created or modified and any relevant dates change.

    Automation would update a single field and a single record on a single table with the record ID of the focus project.   The record idea of that single record in this automation control table will be 1 obviously as you will enter one and only one record.   

    Then do a reverse relationship to the table so that one project has many admin focus records.  Even though there is only one admin focus record.  Look up key information from the focus project into that single record.  Then look up information from that single record down to the 1000 month field. table.  There will be a formula on those thousand records to calculate the first day of the month starting at the project start date and ending at the project and date. Any of the 1000 month records which are not needed will calculate to know dates.

    Then the automation will Delete any project month records that already exist and then it will run to save table to table copy to re-create child project month records for the focus project..

    So one project has many project month records and they will be maintained by the automation so that each project has the correct number of project month records as children they will have the correct dates on them and they can then self calculate their prorated share of the project cost divided by the number of project month record.  


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Calculating and charting monthly data from a total duration

    Posted 11 days ago
    Mark,

    Thanks for the quick and detailed reply. I get the idea behind the project months table, but I'm not clear on why the focus project table is needed. Couldn't the same lookups be passed down to the project months table directly from the projects table? I'm also not clear what the delete automation is doing - are you saying it's deleting unused template records in the project months table? And then calling a saved table to table import to copy records from the project months table to ... where? The focus project table?

    Thank you,
    Paul

    ------------------------------
    Paul Streit
    ------------------------------



  • 4.  RE: Calculating and charting monthly data from a total duration

    Posted 11 days ago
    Do you have any budget to book time on one time with me to get this working?  That would be faster.

    Thanks for the quick and detailed reply.   np

    I get the idea behind the project months table, but I'm not clear on why the focus project table is needed.

    The focus record is needed to temporarily record the Focus Project for a split second.

    Couldn't the same lookups be passed down to the project months table directly from the projects table?
    You still need a way to create the Project months children records and they need to each know what month they represent.  


    I'm also not clear what the delete automation is doing - are you saying it's deleting unused template records in the project months table?

    Suppose a project has 5 children because it's a 5 month project.  Then the user adjusts the dates and makes it a 10 month project starting a year later.  Th easiest way to deal with edits is just to start with a clean slate, delete all the children for that project focus project and import in a new set for the 10 months.

    And then calling a saved table to table import to copy records from the project months table to ... where? The focus project table?  The records are being copied from the 1,000 record table into the Project Months child table, but just as many of hem as we need of the "1,000".


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------