- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Mar 25, 2024 04:34 AM - edited ‎Mar 25, 2024 04:35 AM
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:
- Student enters in their student ID.
- Student chooses a research topic from the list.
- 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).
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Mar 25, 2024 05:11 AM - edited ‎Mar 26, 2024 09:55 PM
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! 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Mar 25, 2024 05:11 AM - edited ‎Mar 26, 2024 09:55 PM
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! 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Mar 26, 2024 05:47 PM - edited ‎Mar 26, 2024 05:51 PM
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 🙂
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Mar 26, 2024 09:56 PM
Ah, so sorry! I've updated the link so that it works properly now, and here it is for your convenience
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Mar 26, 2024 11:08 PM - edited ‎Mar 27, 2024 12:11 AM
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!
