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.
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
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.
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