I’m no expert but your base structure makes sense to me. But here are some ideas you may find useful:

• I would recommend adding a Lookup field to your rAppointment Book] table that points to the {Priority} field in the eAppt Type] table. This way in your rAppointment Book] you’ll be able to filter and sort appointments by their priority


• I’d rephrase your {Priority} options to have numbers preceding the text (i.e. “1-Super Dooper Urgent”,“2-Middle of the Road”, etc.) so that you can sort records by priority and have them appear with “1” priorities at the top.


• I’d also add a formula field to your rAppointment Book] table with a formula like IF(WEEK({Appointment Time})=WEEK(TODAY()),"This Week", IF(IS_BEFORE({Appointment Time},TODAY()),"Lapsed Appointments","Upcoming Appointments")). This should give you a good field to group records or filter records by if you want to get completed/historical appointment records out of the way

thansk @Kamille_Parks, i have implemented that idea. although the function WEEK wouldnt go in , so changed to DAY and it was fine

Thanks & Merry Christmas

Hi @Duncan_Fischer. Hope you had a great holiday! (I ended up being busier than anticipated). Glad @Kamille_Parks could help out. Also your base looks great. the linking makes sense and now you can just build smart fields to help with workflows. I think I can help you out here:

What you seem to be looking for is the ability to use a ‘single select’ based on the ‘lookup’ from the rAppt type] table. Sadly this isn’t possible. That said you can still create some data validation in the event someone does choose someone who isn’t qualified. This is what I came up with:

Which then has a filtered for iInvalid Technician]:

If you have pro you could also do a conditional color. Or you could group by lTech Qualified] which will pull them out separately.

Hope this helps!