Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Base design
Solved
Jump to Solution
2384 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