Skip to main content
Question

Create new child records based on a date range

  • February 23, 2021
  • 0 replies
  • 41 views
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • February 24, 2021
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
------------------------------
Mark Shnier. Your Quickbase Coach
QuickBaseJunkie
Forum|alt.badge.img+15
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
------------------------------
Terms & ConditionsAccessibility statement