Skip to main content

I have a form that i am sending out to my team members to understand how much time they anticipate they will spend on a project. 

I'd like to create a dashboard that shows in a bar chart how much time each member of the team anticipates to spend this week by project. So for example a bar should look like [----Blue----][--Green--][-Red-] with blue representing one project, green representing another, and red another. giving us the idea that the team member's time is 80% spoken for this week, with 30% going to blue project, 20% going to green, etc. 

The issue that I am running into is that the way that we have structured the form, the output table looks wide. so it looks like :

Survey EntryName (link to project record)Survey DateProjects (link to another record)Blue ProjectGreen Project Red Project
1Sarah Johnson11/20Blue Project, Green Project40%60% 
2Javier Smith11/20Green Project, Red Project 30%50%
3Charles Estevez11/20Blue Project, Red Project20% 70%

In order for me to create the bar graph that i would like, Airtable will only recieve the information if it looks like this:

NameProject% of time
Sarah JohnsonBlue Project40%
Sarah JohnsonGreen Project60%
Javier SmithGreen Project30%
Javier SmithRed Project50%
Charles EstevezBlue Project20%
Charles EstevezRed Project70%

I know how I might do this in R Studio, but I do not know how I would do this in Airtable. 

How do I convert the top table - a wide one - into the bottom table - a long one - for the sake of creating a bar chart in an interface?

Some further specifics:

- Our team's projects are changing all the time, so i don't want to hardcode something if I don't have to.

- I am open to changing the format of the form, or learning how to have the bar chart use the existing format of responses.

- Maybe this could be done with an automation? I am not sure. 

Hey @juliabontempo, 

I would recommend moving from table view to list view. 

Here is the structure you need to have in tables:
- [people]
- [projects] linked to time per project
- [time per project] link to person

The percentage can be calculated from the time in the project record and the given value in time per project record, if you need support on this let me know.

Create a list view on interfaces with data sources the time per project with the second level of the project. You can display the calculations with lookups. 

Feel free to post me here for any questions. Very happy to help you complete this task 🙂 

Thanks 
@Dimitris_Goudis 

Hm yeah I'd recommend a new form to get the format you need for bar charts, and so that'd be a table where each record represents a single person linked to a single project.  Unfortunately this means either multiple form submissions on the same day or letting users create multiple records in a single form submission, which Airtable doesn't allow via Forms right now

If your users are all paid, you could create an Interface to help with this, and if not I'd recommend using Fillout (https://www.fillout.com/) or some other form software that lets you create multiple linked records per submission

---
You could potentially convert the wide table into a long one via automations, but you'd probably need a script to help with that.  Further, your wide table would always have hardcoded fields for each project, and you're trying to avoid those, right?

If you're really confident that the people keying in data will do it right, you could potentially allow them to key stuff into a Long Text field and then parse that with JavaScript with .split().  This wouild be like a 30 second thing to setup and simple enough to do, but also relies on the users never having typos in the project names etc.  Let me know if you go down this road and I'll send you the script for it

@juliabontempo 

You could stick with Airtable's native forms, and just use automations to convert your data to long instead of wide in a new table.

However, your best option for accomplishing this is probably to use Fillout’s advanced forms for Airtable.

Fillout is 100% free and it offers hundreds of features that Airtable’s native forms don’t offer.

You can use it in one of two ways for your needs:

1. Use its ability to add unlimited linked records to a form. Using linked records for what you're looking to do would be ideal.

2. Leave your form structured as-is, but after a form is submitted from Fillout, use Make's integrations for Fillout & Airtable to make the data long instead of wide. If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread.

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

Reply


Terms & Conditions