Base Design Scheduling Requests + Integration suggestions

[Carolyn_Snider]

Before I make too many trial and error mistakes, can you help me with a base design for the following:

Over the coming year we will have over 400 residents do rotations in our emergency department. There are 13 different rotation blocks (each 4 weeks). There are multiple levels of learners (Medical Students, Juniors, Senior, Fellows) and four different schedules (Medical students, Juniors, Seniors) (Some Fellows get scheduled as juniors, others as seniors). Some learners are CORE and others are here as electives. Some will work only one block - others will work multiple blocks - not always consecutively.

We will fill in the following information on the initial Table: Name, email, level, schedule, and Blocks that they are assigned to.

Block Design Question:
Would it make sense to have one learner request table where they choose the block and enter their requests? Or have 13 additional tables for each block?

Can we set up an individualized URL that they can get emailed on submission in case they need to change their requests on the same record?

Additional Integration Suggestions

1. We want to ask them a few more standard demographic questions that we don’t receive automatically. We want to send them a summary email on form submission that includes a URL so that they can then go back and change if the details change in the future (e.g. cell phone, prefered email etc.). Is this doable from within AirTable and will it allow us to change the original record rather than to add a new record for the same person? [ If this doesn’t work, we would do this with JotForm and then feed it back as an integration - but ideal to stay using airtable as I believe that just makes a new record after each submission. We would prefer to update the same record rather than add a new record]

2. We would like to set up a process that 6 weeks prior to their assigned rotation block, they get an automatic email - either to fill in our update their additional demographics (i.e from #1). Once they submit that, they would be redirected to a second form to input schedule requests - then to be redirected to an external hospital email for the required hospital process? After submitting their schedule requests they would get an automatic email summarizing them and an URL that they can go back to update (up until a certain date).

I am interested in people’s favoured integrations for sending the emails (and/or text message).

Thanks for your suggestions.

[Vivid-Squid]

Hi @Carolyn_Snider
These are some lofty goals, and that is awesome! I will put some thought into it and I am sure some other experts here have good ideas too.

One quick observation, you want people (over 400) to update records in a Base. Each one of them will consume a paid seat to the workspace. Since you are still in the planning phase does your subscription account for that?

Either way, a great integration tool that will help you immensely is make.com you can use it to set up much of the workflow routing that you posted.

[TheTimeSavingCo]

Hi Carolyn, hmm, as I don’t fully understand how the data might be used, my suggestions might not be that useful. Let me know what does / doesn’t work and we can refine it together?

It seems like you have 5 sets of data (Please correct me if I’m wrong!):

1. The residents and their details (Residents table)

• Including their learner level and whether they’re CORE or here as electives

2. The 4 schedules (Schedules table)

• Unclear what data this would include

3. The 13 blocks (Blocks table)

• Unclear what data this would include

4. Form submissions for demographic data (Demographic Submissions table)
5. Form submissions for schedule requests (Schedule Requests table)

And as such I would have them in 5 separate tables, with link records set up accordingly between 1, 2, and 3

It seems like the workflow involves asking residents to:

1. Submit personal details
2. Submit schedule requests

I’m assuming that each resident will have their own unique identifying number within the department.

If so, I would set up all the Airtable forms that get sent out to students to have a field for said unique ID. We can generate form URLs with these ID’s pre-filled if you like, but it seems easier to just ask the respondents to key it in along with their name.

With reference to the demographic questions form, as you stated, each submission would create a new record. Personally, I think this is good for record keeping purposes and would keep it, and would use an Airtable automation to do one of the following, depending on your own preferences w.r.t. keeping records:

1. Link the form submission directly to the resident in the Residents table via said unique identifying number and replace the data in the Residents table with the latest details
2. Link the form submission directly to the resident in the Residents table via said unique identifying number and use lookups / rollups to display the latest submission’s data

I would also use an Airtable automation to send the email 6 weeks prior to their assigned rotation block with a link to the second form to input schedule requests, again requesting that they key in their unique identifying number within the department.

In both forms, I would ask for the respondent’s email so that I could set up an Airtable automation to send them a summary of their responses, as well as BCC it to one of the department’s email accounts for record keeping purposes.

Having no integrations is preferable for me as well (at some point it gets too crazy to maintain, as I have found to my own detriment), and the above does not require any third party integrations to work

Let me know what you think!