Help splitting multiple select field into individual records

[tdstarkey]

Hi! 

I am working on maintaining student learning records. I have two multiple select fields: one with "Student" names and one with demonstrated "Competency". I would love to create new rows for each combination of Student and Competency, and copy over the same Date, Experience Description, and Location. For example, record number 2 on the screenshots,, I would like one record with the same date/description/location for Chloe/STEM-Scientific Application, one record with the same date/description/location for Chloe/Design-Outcome, one record with the same date/description/location for Emily/STEM-Scientific Application, Emily/Design-Outcome, one record with the same date/description/location for Kendall/STEM-Scientific Application, and one record with the same date/description/location for Kendall/Design-Outcome. 

[TheTimeSavingCo]

Hmm, there are various ways we can accomplish this and which way we settle on depends on your workflow and how open you are to changes to your base structure

For example, if you're creating each record via a form submission, a possible route would be:
1. Creating a new table called "Competencies per student" or something
2. Creating an automation that will trigger when the form is submitted
  - Add a repeating group that takes the "Students" field as a list
  - Add a "Create Record" action within that repeating group that will create a new record in the "Competencies per student" table, where each record will be created with a student's name, and the value from the "Competencies" field
3. Create another table called "Student <> Competency" or something
4. Create another automation that triggers when a new record gets created within "Competencies per student"
  - Add a repeating group that takes the "Comptencies" field as a list
  - Add a "Create Record" action within that repeating group that will create a new record in the "Student <> Competency" table, where each record will be created with a student's name, and a single value from the list

This should get you a table where each record represents a single student and a single competency

I'm curious what your end goal with this is though?  Perhaps we could find another way to accomplish it that doesn't involve the need to create the above

[tdstarkey]

I will absolutely try this! 

I'm using Airtable as a way to keep track of when students demonstrate a competency (when they show "algebraic processing" when they're cooking and using fractions or "problem solving" when they test and iterate to find the best way to 3D print a machine part). Most times, students are working in groups and demonstrating multiple competencies.  For each of these instances, I record the date, the experience description (what is happening), the students involved, the competencies demonstrated, and the location, as well as picture evidence if I remember to take them. I am working now to send data to Tableau through Google Sheets (because it doesn't read the csv files as cleanly as I'd like), so I can package it nicely for their parents to see their learning. There isn't a clean way to split the data in Tableau either. So for each new student and competency record, I need to copy over all the other information in the original record. 

[TheTimeSavingCo]

Ahh hmm, and so you're formatting everything to send over into Tableau?  If so yeap this sould be alright

I put something together here on the off chance it proved useful as well, and it uses linked fields so that we can use lookups to get all of the competencies for each student easily

[SharonC]

Hi Adam

How exactly did you create the linked tables? Do you mind explaining it step by step?

I am trying to create something similar but I am not getting there yet...

[Rich_Easyapps]

Hey @tdstarkey  , if the purpose of creating this new table is to ingest data into Tableau, an alternative is to use our Airtable Tableau Connector.

Our Connector supports the full range of many-to-many relationship types, including Multiple Linked Record fields and Multiple Users fields. That means you can build Tableau reports off of your Airtable data even when your Base contains these many-to-many relationships. The Connector creates a dedicated table in Tableau for every Multiple Linked Record field that exists in the Airtable data you're exporting. Then it's simply a matter of recreating those relationships in Tableau - a 30 second job.

(Also due to the nature of Tableau's Web Data Connector architecture, the data flows directly from Airtable to Tableau without ever touching our servers so there's no data security issues).

There's a demo on our landing page if you're interested in seeing how it works. There’s also a 30 day free trial if you want to try it out for yourself.