Help

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

Topic Labels: Base design
Solved
Jump to Solution
1449 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Ahmed_Elagami
7 - App Architect
7 - App Architect

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:
Screen Shot 2022-07-01 at 2.45.45 PM

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 :
Screen Shot 2022-07-01 at 2.49.19 PM

Gantt Chart view
Screen Shot 2022-07-01 at 2.49.55 PM

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

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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.

Ahmed_Elagami
7 - App Architect
7 - App Architect

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