Lookup Field to Gantt with Multiple Start & End Dates

[Staffing]

Hi,

I am new to the Airtable forum but been using Airtable for over a year now. I am trying to create a Gantt view with multiple start and end dates for an employee (the employee has multiple contracts and I want to make sure the contracts don't overlap (too much!) with a visual display. Currently I was able to create a Gantt as seen here:

The different contracts are highlighted with boxes, are sourced from a Lookup Field (from a separate, linked table) and separated by commas. How can I make the Gantt recognize the start & end dates associated with each contract? Right now it just shows the employee working (this is hidden off to the left side of the screenshot) from the start date of their first contract through the end date of their last contract, even if there are dates in which they are not working (on the top example 7/7–7/11).

The Grid View the Gantt is based from is this: 

Person#1 is the upper Gantt chart & Person#2 is the lower one. Currently the Gantt is sourced from the columns "In the Plan—SD" & "In the Plan—ED" and the Gantt just recognizes the earliest SD(start date) and latest ED (end date). Ideally I (or the Gantt) could parse out the commas separating the contract dates in the "TEST—In the Plan Season Primary" column and use the start and end dates associated there. 

Thanks for any thoughts or advice!

jared

[TheTimeSavingCo · Accepted solution]

I don't think that's possible at this time I'm afraid.  To achieve what you're looking for I think you're going to have to create a Gantt view in the table that contains all the contracts and group by the person

[ScottWorld]

@Staffing 

You won't be able to do that with lookup fields.

Even though Airtable automatically helped you out by extracting the earliest date & the latest date from your lookup fields, that's as far as it will go.

To get what you want, you will need to restructure your database as a many-to-many relationship.

Looking at your screenshots, I can see that your database is structured improperly. You shouldn't actually have one tab for each contract. You should have all of your contracts in one tab.

Then, you will need another tab with all of your staff.

Then, you will need a "junction table" that serves as the intersection of staff & contract.

I discuss many-to-many relationships in more depth in this Airtable podcast episode and this Airtable podcast episode, and Airtable has a support article on many-to-many relationships here.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Staffing]

Thanks for the prompt reply. Not quite what I intended, but your solution of creating the Gantt in the other table actually ended up producing a workable solution. I was able to create the Gantt view of all the contracts, then filter by person giving me a usable outcome! Was looking for a more challenging solution. 🙂

[Staffing]

Thanks a bunch for the prompt reply. I think I figured out a solution using a previous reply.