Form for students to select topic: do not display duplicates when topic has multiple availabilit

[jes-m]

I have been using this booking form template (here is the community post where I found it) to try and create a student registration form, but I'm running into some snags. I'm hoping someone may be able to help me. 

The basic requirement is below (once I have that established, I'll play around and try and make it a bit better). What I need is a form where:

1. Student enters in their student ID.
2. Student chooses a research topic from the list.
3. When they submit, this reserves that topic for that student, assigning their student ID against the topic. 

I have five topics (for simplicity sake: blue, green, red, pink, brown), and each topic has 25 spaces available. My setup so far is:

Table 1: Available Topics:

The fields are:

• Primary: Topic (this is not a unique field, it will be one of the 5 colours)
• An autogenerated number
• Unique ID using formula based on auto ID and region name
• Student ID (linked to the other table)
• A status field (formula tests if student ID is blank, if yes "available" if no "reserved).

I have different views based on the status, and the form from table 2 connects to the "available" view. 

Table 2: Student Reservations

So far, this table has a Student ID primary) linking to table 1, and the 'Research Topic field" linking to the name field in Table 1. The form is based on this. 

My issue

I want the form to just list the topics once, and then if all slots of a topic are taken, for it to no longer show. I thought that a view may be the way to go with this, but I can't figure out how to get a view to only show the first record that meets set conditions. 

Any ideas on how I can achieve this (with or without the view). 

[TheTimeSavingCo · Accepted solution]

I've put something together here that should do what you're looking for

I created a "Topics" where I linked the records from the "Available Topics" field appropriately:



I also created an "Autonumber" field in "Available Topics", and then created a rollup field in "Topics" with the formula "MIN(values)", as well as a conditional to only include the records where the linked field to "Student Reservations" was unlinked:


In "Available Topics", I then created a lookup field to display the value from the previously mentioned rollup field, and then created a formula field to check the lookup field value against the autonumber field value.  In the Interface form, I gave it a condition to only display records where the formula field's output is "1"

Sounds super complicated but it's fairly easy to set up and should work like you want! 

[jes-m]

Wow! 

Thank you so much for taking the time to do this, how incredibly generous of you. I appreciate this so much. I've just requested access to the base so I can have a look through the set up. Having a working template will really help me to learn more and understand the system. 

I wish the college I worked for was better suited for my needs (so I didn't have to rely on external resources). Alas, us lowly grad students don't have much scope to get new software! Airtable has served as a useful workaround, though.

Thanks again 🙂 

[TheTimeSavingCo]

Ah, so sorry!  I've updated the link so that it works properly now, and here it is for your convenience

[jes-m]

Thank you for this. It took me a minute to wrap my head around, but I'm getting there. Am I correct that new records are added to the Available topics table by entering in a link in the Topic field (as the name is a formula)? If so, do you know if there is a fast way to add multiple records? It will not allow me to enter these in with Excel. 

Thanks 🙂 

Edit: I created a helper column, imported to that and copy pasted. So far, so good!