Copying a specific record:field to another?

[Mary_Alice_Moor]

I think I already know the answer (tables aren’t the same as spreadsheets so you can’t copy cells, only records), but wanted to post in the off chance someone can suggest a possible work around. Trying to copy a formulated date from one table to another so I can produce a gantt for each record in my base.

Table 1 has records of each project with various fields containing start/stop/checkpoint dates
Table 2 I was using as the base for my Gantt with only two fields (start date, end date)

Currently I have to copy and paste but am hoping maybe there’s a scripting with a map loop solution out there?

Example:
Table 1
Record 1: Project 1 (Name) Field 1 (start project - date) Field 2 (check-in with employee - formula off of start project date) Field 3 (90-day assessment - formula off of start project date) Field 4 (end project - formula off of start project date)

Record 2: Project 2 Name Field 1 (start project - date) Field 2 (check-in with employee - formula off of start project date) Field 3 (90-day assessment - formula off of start project date) Field 4 (end project - formula off of start project date)

Table 2 (using this table to make a Gantt)
Record 1: Project Date Range (Name) Field 1 (want to pull from Table 1, Record 1, Field 1) Field 2 (want to pull from Table 1, Record 1, Field 4)

Record 2: Check-in with Employee (Name) Field 1 (empty) Field 2 (want to pull from Table 1, Record 1, Field 2)

Record 3: 90-Day Eval (Name) Field 1 (empty) Field 2 (want to pull from Table 1, Record 1, Field 3)

I feel like there has to be a solution, but I just can’t put my finger on it…

[Kamille_Parks · Accepted solution]

Keep your Projects table mostly as-is but add a Link to another record field to your Gantt table:

Set up your Gantt table to have:

• A single-select field for the type of event
• One lookup for each date field in the Projects table
• A formula field for {Start Date} that uses the formula:

SWITCH(Type, "Full Range", {Start Project (from Project)})

• A formula filed for {End Date} that uses the formula:

SWITCH(Type, "Full Range", {End Project (from Project)}, "Check-In", {Check-In w Employee (from Project)}, "Assessment", {90 Day Assessment (from Project)})

Then create an Automation to handle the creation of the Gantt table’s records for you. The trigger could be “When a record in Projects is created” and it could either have: a) three “Create a record” action steps, where each step creates a record in Gantt table that is linked to the project but has a different “Type” selected, or b) one “Run a script” action step that creates all three new Gantt records at once.

Result:

[Mary_Alice_Moor]

THIS WORKED LIKE A DREAM!!! Thank you times a million!