Multi-item selection lookup formula? or: Formula that can handle multiple data points in one cell

I haven’t answered you earlier because I wasn’t sure I understood what you were asking — and I’m still not sure. Can you give me an example of what a [StudentProgram] might be?

My quick guess is that you’ll probably have to link [Student]s to [StudentProgram]s, even though you’ve already made the other links, because the relationships that can be assumed descend from the [Student] :left_right_arrow: [StudentProgram] link. (That is, once you know a student’s program, you can derive the program group and type.) It’s not so much a failure in Airtable as it is simply the need to tweak your data model slightly, as is common during initial development.

Of course, I could be completely wrong; I’m just making assumptions based on syntax.

Even if the model has to be changed, depending on your answer to my first question there’s a chance you might be able to generate the modified tables without having to re-enter the data. (That’s assuming you have a substantial base already populated.) Such activity wouldn’t be something you’d want to do regularly, but for a one-off restructuring it could be worth the effort.) Again, though, I’m not certain enough of what you want this new table to contain to speak with any confidence.

Thank you for following up though!

Hopefully this helps clarify:

Right now there are three tables:

• Student (or applicant)

• Program Type (lots of related data; length, offset from the group start, cost, etc)

• Program Group - these are basically clusters of programs that overlap, usually by start date, but more than one group may share a start date

right now each have a many:many relationship with the other two.

Technically though, each student’s balance, and their funding is by Diploma Program they are registered in; not for all programs. Each different program also has its own start date, which of course affects the timing for their tuition due-dates.

a Student-Program table would basically be a many:1:many table, but contain the financial info for that student, for that particular program (funding can’t be transferred between programs, controls due dates, etc.)

EG:

Student-Program | Student Table Link | Program Group Link | Program Type Link | $ records

Mary - program 1-April | Mary | April | 1

Mary - program 2-April | Mary | April | 2

John - Program 5- April | John | April | 5

Bob - Program 3-April | Bob | April | 3

Jill - Program 2-July | Jill | July | 2

Jack - Program1-April | Jack | April | 1

Jack - Program 2-May | Jack | May | 2

Jack - Program 4-July | Jack | July | 4

Right now the student table entry for the above would look like:

Name | Program Group | Program Type | Tuition1 | Tuition2 | Tuition3 | kit1…

Mary | April | 1,2 | $0.00 | $0.00 | error | $0.00 | $0.00 | error…

John | April | 5 | $0.00 | error | error | $0.00 | error | error…

Bob | April | 3 | $0.00 | error | error | $0.00 | error | error…

Jill | July | 2 | $0.00 | error | error | $0.00 | error | error…

Jack | April, May, July | 1,2,4 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00…

I do also want to keep extra data entry and jumping between tables as minimal as possible - too complex and it is going to confuse the other users, or worse increase workload. Is there any way to set the associated program group/type link at the same time as entering the main bulk of the student data?

I’m still having trouble deconstructing the underlying data relationships from your sample base. I’ve tried building some additional tables and links, but I keep getting lost as to what should go where.

I’m sure there are reasons the following structure won’t work for you, but if you can tell me why, perhaps I can fine something that would.

You say

This is where I would think a [StudentProgram] table would come into play. (Conceivably this could be done without relying upon an intermediate table, but using one makes certain types of correlation and reporting possible or simpler.)

Tuition costs, schedules, and the like would be stored in the [ProgramType] table. Each [Program] would have a single [ProgramType]. A [StudentProgram] would consist of one or, in the case of double diplomas, two [Program]s. Tuition and schedules would bubble up from [ProgramType] to [Program] and would be either carried through or summed up in [StudentProgram].

I’m not certain of the reasons behind [ProgramGroup], but it could either be used to provide data surfaced in [ProgramType] or, possibly, reside at a layer of the diagram shown here between [Program] and [ProgramType]. (For instance, if [ProgramGroup] is used to identify which semester or academic year a certain [ProgramType] is offered, with schedules and tuition adjusted accordingly, then [ProgramGroup] would fall in this intervening layer. If it instead identified, say, an associate degree from a baccalaureate, and designated total credit hours and major hours required, it would fall ‘below’ [ProgramType] in this diagram, with a one-to-many link from [ProgramType] to [ProgramGroup].)

Again, this is merely my current best understanding of the relationships you wish to model. IO maybe (most likely am) quite wrong. Once we arrive at your ultimate structure, we can figure out how to get there from where you currently are…

thank you so much for your help.

If each [Program] would have a single [ProgramType]& all the info would be in program type, and bubble up twice, what is the purpose of the Program 1&2 in the Diagram?

We don’t actually use a semester basis for our programs. Instead we use rolling start dates, and each program has a specific class sequence they have to follow. Which I suppose can be treated similarly, but there are probably going to be quirks related to that.

Program Group would be a cluster of Programs that share the same classes, so having that group allows for scheduling instructors, classrooms, and tracking total # of students in the group. (some rooms have different capacities than others). They’re cohorts of students. The occasional student ends up shifting cohorts, but those are an exception not the norm.

I see “student program” Looking more like your Program 1 & 2 in the diagram (this may be a make it simpler to enter step though?)- tracking costs on a per-program basis (overall balance is pretty irrelevant when the second program’s due dates are nowhere near), Study permits for international students, etc.

Maybe this diagram will help?

Down the line we may also want to add classes so instructors can track attendance here. But figuring that out is going to be a not-now problem I think.

And an aside: Do you know of a way to use the Kaban view (or later, the matrix view) with formulas or links instead of drop downs?

I can see this kind of structure possibly working-is there a way to not have to create the student-program and program instance records each time and have them generated off of the others?

My breaking out of [Program] as a separate table was based on [what could easily be a misunderstanding of] your comment

In my model, assuming a double diploma might consist of, say, a program (diploma? degree?) in digital media and another in journalism, each component program would be represented by its own [Program] record. In comparison, a student in purely a journalism program would only have a Program 1.

I should mention here that ‘Program 1’ and ‘Program 2’ would actually be implemented as individual instances of the linked-record field {Link to Program} from the [StudentProgram] record. Conceivably, one could link directly from [Student] to [Program], eliminating [StudentProgram], but my inclination would be to keep the intermediate [StudentProgram] table on the assumption doing so might simplify the performance of some desirable queries. (“How many students are in a digital journalism double diploma program?” for instance.)

At the [StudentProgram] level, I envisioned schedule tracking as nothing more complex than simply indicating the student’s schedule should begin on the earlier of his or her programs’ start dates and run through the later of their end dates. I would see room and instructor scheduling as operating essentially in parallel with student scheduling, with the only point of contact being, perhaps, the number of students enrolled in a given [Program] — or, perhaps more correctly, [ProgramGroup] — to ensure appropriate instructor:student ratios were met and appropriately sized classrooms reserved.

One place where the law of diminishing returns likely kicks in would be cross-program scheduling. While conceivably routines could be written to watch for conflicts between courses mandated by the component programs of a given double diploma, it’s probably easier to make that the responsibility of whatever human is tasked with arranging course schedules — especially since, if your college is anything like the one I attended, an acceptable schedule must also take into account such things as distance from instructor’s home to school, instructor’s attitude towards working on Fridays or Mondays, and an inverse relationship between likelihood of hangover and earliest class scheduled.

At some point, I recall tossing together a quick demo base involving proper sequencing of courses within a program. When I get a chance I’ll poke around my workspace and see if I can’t find it. (In the 15 months since I learned what an Airtable is, I’ve created or duplicated from a shared base roughly 160 bases, so it sometimes takes a while to figure out which one I’m vaguely remembering…)

If [ProgramGroup] refers to a specific instance of a [Program] — for instance, ‘Digital Journalism, 2018 - 2021’ — then a more accurate hierarchy might be

[Student]->[StudentProgram]->[ProgramGroup]->[Program]->[ProgramType]

or, for a student pursing a double diploma

[Student]->[StudentProgram]->[ProgramGroup]->[Program]->[ProgramType]
                           ∟>[ProgramGroup]->[Program]->[ProgramType]

Again, it’s debatable whether [Program] is necessary — but my first inclination would be to retain it. In this model, tuition and schedules would probably reside at the [ProgramGroup] level (although internal costs, as opposed to price, might best be calculated by [Program]). In this scenario, [ProgramType] might disappear, or it might be used to house data common to a collection of [Programs] — characteristics specific to all programs representing associate’s degrees versus those representing bachelor’s degrees, for instance.

A-ha! You edited that! :winking_face:

Yes, your bottom-most diagram is close to what I was trying for with the hierarchies I limned a paragraph or so earlier.

The way I envision your work-flow is something like the following:

1. [Rarely; Possibly Optional] Create [ProgramType]s. These would be, in the broadest strokes, what types of programs your college offers: Associate’s, Bachelor’s, Master’s, professional certificates, continuing ed — whatever. I would assume these would rarely change and probably reflect, as much as anything, the school’s charter or accreditation.
2. [Infrequently] Create [Program]s. These reflect the degree (certificate, etc.) programs the school offers. I would assume these would be updated, at most, annually. Prescribed courses and sequences of study might be defined here; alternatively, they might be defined at the [ProgramGroup] level. (Edit: Actually, I would probably define them here as a linked record to [RequiredCourses], with some sort of versioning mechanism similar to that used in my pricing schedules demo. That way, one wouldn’t be forced to enter all that information each time a new [ProgramGroup] was created, but changes could be made to the list of requirements for programs starting next academic year without impacting students currently pursuing a program.)
   Each [Program] would have a one-to-many link to [ProgramType].
3. [Annually, perhaps more frequently] Create [ProgramGroup]s. These represent instances of a specific [Program]. The bulk of data would be retrieved from [Program] and as such not need to be re-entered; the main variables at the [ProgramGroup] level would be those specific to a given group: schedule, tuition, and the like. (Obviously, tuition and fees could easily change from [semester-equivalent] to [semester-equivalent], and schedules will likely be set on a yearly or similar academic cycle basis. However, if [ProgramGroup] is best thought of as a cohort of students progressing together through a degree program, I’d design [ProgramGroup] to have a life expectancy equal to the amount of time one is expected to take to fulfill the degree requirements. Such things as start-stop dates and tuition could then be provided as linked records, updated annually. Similarly, depending on how monolithically students move through the program, individual course schedules and classroom assignments could be linked at whatever interval makes sense.) The goal would be to update annually/bi-annually only those fields that need modification, inheriting as much as possible from [Program].
   Each [ProgramGroup] would have a one-to-many link to [Program].

Those three tiers represent your infrastructure, as such, incorporating data from general to specific as you move from item 1 to item 3. Links from tier to tier would essentially be ‘set-and-forget’: Once the link was created, it would rarely, if ever, be modified.

Against that background would be your more dynamically upodated tables: Namely, [Student] and [StudentProgram]. [Student] would be updated, unsurprisingly, for each enrolled student (or, possible, each applicant). The [Student] record would contain data particular to that student: Address, academic background, test scores, emergency contacts, payments made, and the like. As, presumably, not all students would have a declared major, some way is needed to track undeclared students, either through an ‘undeclared’ [ProgramGroup] or through appropriate data fields. (Personally, I lean toward an ‘undeclared’ group record; this would simplify the correct allocation of tuition, etc., for any student without a major.)

Students with a declared program of study would be linked to a [StudentProgram]. This would be accomplished as a drill-through from the [Student] table. I expect the [StudentProgram] record to be a relatively ‘thin’ record, primarily containing links to one or more [ProgramGroups] and a few consolidating rollup fields designed to consolidate tuition charges for each component [ProgramGroup], and the like.

During data entry, I would expect the work flow to go something like this:

1. Create a [Student] record. Enter student-specific data.
2. As appropriate, create a linked [StudentProgram] record. This would be performed by selecting the plus sign in the {StudentProgram} linked-record field and selecting ‘+ Add a new record.’ This creates a new [StudentProgram] record and drills through to the expanded record.
3. As appropriate, link to one or two [ProgramGroups]. This would be performed by selecting the plus sign in the {ProgramGroup} field in the expanded [StudentProgram] record and choosing the appropriate [ProgramGroup] record. Once a [ProgramGroup] is selected, the user is returned to the expanded [StudentProgram] record.
4. After all necessary links have been created, the user selects the X mark in the upper right-hand corner of the expanded [StudentProgram] record and is returned to the [Student] record.

At this point, necessary data from the applicable [ProgramType], [Program], and [ProgramGroup] records can propagate upward to the [Student] record, thanks to lookup and rollup fields defined along the way. This should allow the user to create and populate each student’s record with an absolute minimum (none, actually) of redundant data entry.

It would be to your benefit to search Airtable Community for references to ‘aggregation formula.’ This is an un[der]documented feature of rollup fields: Rather than simply the double handful or so of aggregation functions defined in the documentation, it is possible to define complete formulas within the configuration of a rollup field. The syntax is slightly different — namely, once you define which field you wish to roll up, you refer to that field within the formula with the keyword values. The main limitation is that one can access only a single value by reference — that is, if you are rolling up {Field A} from a linked table, there is no way also to access {Field B} from the same linked table; all other variables must refer to fields in the table that contains the rollup field. However, I expect using aggregation formulas may significantly streamline your code, especially in the [StudentProgram] table. For instance, assuming, in the case of a double diploma, tuition is calculated as 100% of the tuition cost of the higher-priced program plus a lower percentage of the other program, you could rollup {StudentProgram::Tuition} as a concatenated string containing both tuition components, identify the larger of the two, and calculate the combined tuition — all within a single rollup field; otherwise, you’d need at least two rollup fields (using aggregation functions) plus a formula field to perform the same calculation.

[Student]->[StudentProgram]->[ProgramGroup]->[Program]->[ProgramType]

[Student]->[StudentProgram]->[ProgramGroup]->[Program]->[ProgramType]
                           ∟>[ProgramGroup]->[Program]->[ProgramType]

Thank you!

I’m going to see what I can do with this, because just reading it through my brain melted a little - it should be easier to figure out by doing. Sadly that may take a little while.

I would love to see what you have for the program one.

@W_Vann_Hall @Mariah_Malczewska You can make the life simpler.

Create a merging field and hide it. Then, lookup that filed only.

I hope that helps.

Thanks