Convert multiple Dates from 1 record into a multiple unique records with dates

[Ahmed_Elagami]

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

[Kamille_Parks · Accepted solution]

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

Need help with creating a record of dates from one table to another Dates and timezones

Hi guys! I have a table called “Production Stage” (reference below), with lots of dates which can be quite tricky to look at. So I created another table “Project Breakdown” with a simpler view and only have Start & End date. I need to fill up the dates in Project Breakdown by linking it from the Production Stage, but by linking it, it creates a record for the entire line (vertically) instead of just in its specific category. For example, a date was created for “Drug Bust Pre-Production End” i…

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.

[Ahmed_Elagami]

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