Form responses come in wide format, want to convert to long.

[juliabontempo]

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 Entry	Name (link to project record)	Survey Date	Projects (link to another record)	Blue Project	Green Project	Red Project
1	Sarah Johnson	11/20	Blue Project, Green Project	40%	60%
2	Javier Smith	11/20	Green Project, Red Project		30%	50%
3	Charles Estevez	11/20	Blue Project, Red Project	20%		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:

Name	Project	% of time
Sarah Johnson	Blue Project	40%
Sarah Johnson	Green Project	60%
Javier Smith	Green Project	30%
Javier Smith	Red Project	50%
Charles Estevez	Blue Project	20%
Charles Estevez	Red Project	70%

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. 

[Dimitris_Goudis]

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 

[TheTimeSavingCo]

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

[ScottWorld]

@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