Discussions

Expand all | Collapse all

Automation to add multiple records to a child table.

  • 1.  Automation to add multiple records to a child table.

    Posted 05-21-2020 10:00
    I have 2 tables that are related. Projects -> Milestones. Each project has scheduled start and completion dates. Depending on the duration of the project (Completion-Start) I have x number of weekly milestones , i.e. ToWeeks(Duration). How do I create an automation that adds a record in the project child table that represents each of the X number of milestones including the start date for each of the milestones. I would like to do this addition of project milestones on a schedule for all projects and by a trigger in a Project.

    child table result after the automation runs should look like this:

    Project name    Milestone # Start Date
    ABC                    ​1                  Project Start Date
    ABC                    2                  Project Start Date +1 week
    ABC                    3                  Project Start Date +2 weeks
    ABC                    4                  Project Start Date + 3 weeks
    .
    .
    .
    ABC                   X                   Project Start Date + X weeks

    The duration of weeks changes so I am trying to get a history of information in the child table.

    ------------------------------
    George Bramhall
    ------------------------------


  • 2.  RE: Automation to add multiple records to a child table.

    Posted 05-21-2020 11:12
    There are a few solutions, but how long (how many weeks) is your longest possible project?

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



  • 3.  RE: Automation to add multiple records to a child table.

    Posted 05-21-2020 11:24
    in looking at our nearly 100 Active projects, the longest duration is 73 weeks and our shortest is  week.

    ------------------------------
    George Bramhall
    ------------------------------



  • 4.  RE: Automation to add multiple records to a child table.

    Posted 05-21-2020 12:00
    I can generally describe the approach I would use in this situation but it might need an hour of one on one consulting to get this working.
    I would set up a helper table with a single record in it called automation focus. What a project is created or when the user pushes a button this record would be updated with the record ID of the parent project record and the number of weeks.  It will also need to be supplied with the start date of the project.

    That would also be a table with say 200 records in it with record ID is numbered from 1 to 200. This table would receive the information from the automation focus record by one of the relationship and that's with no the record ID of the parent record and the number of weeks record.  This 200 record table would then have the calculations for the mile stone dates and would have a checkbox feel to say whether or not the particular date was needed. For example if the project was only five weeks then only the first five records would be  checkboxed.

    Then the process would run a saved table to table copy and copy the check box records or the day before five or 73 of them across to be children of the focus project record. 





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



  • 5.  RE: Automation to add multiple records to a child table.

    Posted 05-21-2020 12:30
    I was thinking I was going to need a 3rd table for this to work. I follow your plan for the most part. Let me take a stab at it and then will let you know if I need some additional consulting time.

    ------------------------------
    George Bramhall
    ------------------------------