Extract data from rows to create tables/relationships

[Tanya_McIlravy]

I am looking to manage a wellness challenge, team format, using Airtable. The team registration survey returns one row per team, with data about that team in separate columns. There are extra columns in it (like survey record #) but these are the ones I am interested in:

Team Leader Leader email Member 2 Member 2 email (etc up to 10 members) Organization

Basically each member email is a unique identifier/lowest level, and each email has a member name, a team, an organization, a team leader/email, and eventually 8 weeks of data that should be received. Teams are at least three (leader plus member 2 and member 3) up to 10. So some of the fields will be blank after member 3.

I want to extract the members and their corresponding data from the survey results. Is that possible in Airtable ?

[Ken_Moholt-Sieb]

Sounds like you need a table, MEMBERS, that lists each member and status (leader vs. team-member), and team.
Then you need a table, TEAMS, that lists each team. For each team there will be multiple team members (linked to MEMBERS, multiple members in the field).
Then you need a table, ACHIEVEMENTS, where each row is a level of achievement.
Then you need a table, ACTIVITY, where each row has a date and a team and an achievement reached (linked to the ACHIEVEMENTS table)

Once you have these set up (or similarly named) you should be able to add a column to the MEMBERS table that looks up the achievements of that member/team.

Finally, you can create the form based upon the ACTIVITY table for the members or team leaders to enter their achievements and populate your database.

[Tanya_McIlravy]

Yes, that is my goal, but I need a way to create the members table from the registration survey data. Thanks.

[Ken_Moholt-Sieb]

Why can’t you create a form from the MEMBERS table? Each member would be a record row, and the columns would include in addition to what I suggested above, any additional info (contact info, for example) that you wanted to keep for each member. You might not allow the form to collect the team, since you might assign that. Then, once you have created the form, distribute it to all your potenail members.

[Tanya_McIlravy]

The team name and member/leader info is collected via an internal survey/product. We had 400+ teams and 2500 participants the first year. So if the team leader registers their team name and members once, that is 400 people affected and one survey (or form in the case of AirTable). The members have to know their team in order to register using a members table in your method. But the team they input would not be consisitent, so would have to come from a drop down. That means all 2500 participants would have to complete a survey to register, and hope they typed in the team name correctly, or the leader would have to create the team, then load each member on a form. We get a lot of push back about how much ‘work’ it is to register, report, etc already so that is not an option for us.

Our workload aside, the same problem would exist for anyone using your method…how do you make sure the team names are consistent if you don’t create that table first, and how do you load members without filling out multiple forms.

Last year we used VBA code in Excel to convert the registration to the team and member lists. This year we have someone using Access to do it. The hope with AirTable was that our group, who doesn’t have the VBA or access skills, could do it on our own/make changes without having to recruit technical help. Thanks.