Skip to main content

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…

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:

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:


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

Reply


Terms & Conditions