Newbie Scripting Question
I'm a medical resident about to start my chief year and one of my projects is to improve our current scheduling system which had previously been done with Google Docs and Excel Spreadsheets. I thought that FileMaker Pro (and probably FMS) would be a much better solution and I've been able to figure out how to do most of the things I want... but I do seem to be stuck with 1 critical problem.
The way this database needs to work is we need to have a table with all the residents (for now we're just going to start with the interns) and this table can have other useful information abou the residents (contact info, career track, etc.), another table that lists all of the different rotations the residents go through (general med, ICU, ED, etc.) and some information about those rotations (vacation allowed?, minimum number of interns needed on that rotation each block), a table that lists the blocks (every 4 weeks, roughly corresponds to months but not exactly and we do 13 'blocks' in a year) and a master schedule (lists interns and what they're doing each block). Now I want to create another table called "Intern Issues" so that I can track the vacation requests we get from interns and see if they're allowed to take vacation during the block they're requesting (and if they request before we've finished making the schedule it will let me know what block # I need to put them on suitable rotations).
Sorry for the verbosity but the problem comes in that in this last table I want to be able to Type in a date and have FMP #1 look up what block that corresponds to, #2 what the intern's assigment is that month. I'm not sure how to do this.
I was hoping I could write a script where I would say: "If Intern Issues::Block # = 1 then InternSchedules::Block 1" and have it return the text in that block (and then join it with else if statements for blocks 1-13). But that doesn't seem to work - I'm thinking it might be my syntax? I haven't programmed since college which was >10yrs now so it's starting to get foggy.
Thanks in advance!