Forum Discussion

DavidBerndt1's avatar
DavidBerndt1
Qrew Member
5 years ago

Create new child records based on a date range

We need an ability to create multiple children records at one time.  The use case is to create a forecasting table for a project based on the start and end dates on the project parent table.  As the forecasts are monthly, we would want to create a child for each month (first of is fine) starting for the month of the start through the end.  Some projects are multi year so we don't want to click for each one and enter the date.

Open to automation or other options if it would work via a trigger field they enter (i.e. "Create forecast...").

------------------------------
David Berndt
------------------------------
Pipelines
Quickbase Automations
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    There are a variety of ways to do this.
    One method would be to have a table of say 48 records, numbered from 1 to 48. Those would represent the length of say a four year project. 

    You can have an automation trigger to copy those 48 records in as a trial table to your project.  

    Look up the start and end dates from the project down to the months table and create a formula that will calculate to the beginning of each month for each of the 48 months.  

    So the first action of the automation could be to copy in those 48 records. 

    The second action of the automation could be to delete any of those 48 records which are beyond the end date of the project. So for example if a project is three years you would copy in 48 records and then give it a haircut and delete off the last 12 records.  




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • QuickBaseJunkie's avatar
    QuickBaseJunkie
    Qrew Commander
    5 years ago
    So long as you're working with fewer than 100 iterations/months this should work. It creates a looping scenario where the prior run triggers another until the specified number of records are added. (After 100 iterations the automation automatically shuts down thinking it's in an infinite loop)

    Fields

    • Value (Formula - Numeric) – This is where you have the value for how many times the automation will run
      • Formula: (([Project End Date] - [Project Start Date]) / Days(30)) – This is a simple formula, if you need partial months, you may need something more complex
    • Counter (Numeric)
    • Counter +1 (Formula - Numeric)
      • Formula: [Counter]+1
    • Month (Formula - Text)
      • Formula: NameOfMonth(AdjustMonth([Project Start Date],[Counter]-1)) – Adjust accordingly if you want a date or other format

    Automation (or Pipeline)

    Trigger

    • Add or Modifies a record
      • When Value is greater or equal to 1
      • When Counter is less than the value in the field Value

    Actions

    • Add a Record (this is the action you want taken multiple times)
    • Update a Record
      • Record ID# is equal the trigger Record ID#
      • Counter copy value from another record trigger Counter + 1
      • Forcast Month copy value from another record trigger Month

    Note: I adapted this from this post from @Mark Shnier (YQC)​

    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------