Help

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

Topic Labels: Base design Views
Solved
Jump to Solution
227 4
cancel
Showing results for 
Search instead for 
Did you mean: 
jes-m
4 - Data Explorer
4 - Data Explorer

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

 

 

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

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:
Screenshot 2024-03-25 at 8.08.09 PM.png
Screenshot 2024-03-25 at 8.08.12 PM.png

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:
Screenshot 2024-03-25 at 8.08.16 PM.png

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! 

Screenshot 2024-03-25 at 8.11.28 PM.png

See Solution in Thread

4 Replies 4
TheTimeSavingCo
17 - Neptune
17 - Neptune

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:
Screenshot 2024-03-25 at 8.08.09 PM.png
Screenshot 2024-03-25 at 8.08.12 PM.png

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:
Screenshot 2024-03-25 at 8.08.16 PM.png

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! 

Screenshot 2024-03-25 at 8.11.28 PM.png

jes-m
4 - Data Explorer
4 - Data Explorer

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 🙂 

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

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!