
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 01, 2022 03:53 AM
I have a client that I am working with: has a content production company and they have their production timeline split in phases like Shooting , Pre production , post production and Delivery with start and End date like this:
What I want to do is to break it through into separate unique records so I can group them together in a Gantt chart and able to see each phase inside the same project like this
gird view :
Gantt Chart view
Is that something I can automate if I have all dates starts and ends in one record , that would be great to automate , because they still want to use the same structure and not to break it down to this as insertions .Thank you guys for your assistance .
My Best
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 01, 2022 09:12 AM
You could write a script that does this, or set up a structure where Table 1 has a formula field that outputs the names of all the Date records you need to create:
CONCATENATE(
{Project Name}, " - Development",
{Project Name}, " - Pre-Production",
{Project Name}, " - Shooting",
{Project Name}, " - Post-Production",
{Project Name}, " - Delivery"
)
Use an Automation that copies that^ Formula field into a Link to Record field pointing at your Dates table when Project records are created or when {Project Name} has been filled in. Manually copy and paste for your existing records.
See this thread for a similar use case/implementation
In your Dates table, insert Lookup fields for each of your dates.
Then convert your Start and End Date fields to formulas that do something like:
SWITCH(
REGEX_REPLACE(Name, "(.* - )(.*)", "$2"),
"Development", {Development Start Date Lookup},
"Pre-Production", {Pre-Production Start Date Lookup},
"Shooting", {Shooting Start Date Lookup},
"Post-Production", {Post-Production Start Date Lookup},
"Delivery", {Delivery Start Date Lookup}
)
^ The regex gets the “type” of date by selecting everything that is after the " - ".
Then hide all your lookup fields from view to just focus on the now-calculated Start and End dates.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 01, 2022 09:12 AM
You could write a script that does this, or set up a structure where Table 1 has a formula field that outputs the names of all the Date records you need to create:
CONCATENATE(
{Project Name}, " - Development",
{Project Name}, " - Pre-Production",
{Project Name}, " - Shooting",
{Project Name}, " - Post-Production",
{Project Name}, " - Delivery"
)
Use an Automation that copies that^ Formula field into a Link to Record field pointing at your Dates table when Project records are created or when {Project Name} has been filled in. Manually copy and paste for your existing records.
See this thread for a similar use case/implementation
In your Dates table, insert Lookup fields for each of your dates.
Then convert your Start and End Date fields to formulas that do something like:
SWITCH(
REGEX_REPLACE(Name, "(.* - )(.*)", "$2"),
"Development", {Development Start Date Lookup},
"Pre-Production", {Pre-Production Start Date Lookup},
"Shooting", {Shooting Start Date Lookup},
"Post-Production", {Post-Production Start Date Lookup},
"Delivery", {Delivery Start Date Lookup}
)
^ The regex gets the “type” of date by selecting everything that is after the " - ".
Then hide all your lookup fields from view to just focus on the now-calculated Start and End dates.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 01, 2022 11:56 PM
Thanks @Kamille_Parks , That worked quite well.
Just a small correction to convert into multiple records in the formula: is a small Comma ’ after each key word.
CONCATENATE(
{Project Name}, " - Development, ",
{Project Name}, " - Pre-Production, ",
{Project Name}, " - Shooting, ",
{Project Name}, " - Post-Production, ",
{Project Name}, " - Delivery"
)
Thanks alot for you help
