2 Replies Latest reply on Apr 16, 2013 1:18 PM by philmodjunk

    Newbie Scripting Question

    JustinSmith

      Title

      Newbie Scripting Question

      Post

           Hey guys,

           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!

           -Justin

      Screen_Shot_2013-04-15_at_11.06.18_PM.png

        • 1. Re: Newbie Scripting Question
          JimMac

               I am guessing on your problem and not getting to deep in your databaseangel

               To have fields available to use in your logic statement your script must be able to "See" the table with the fields needed.  You need to write your script on a Layout using the Table "Intern Issues", since from this table you can "See" using your relationships the fields you want to use.

               Jim...

          • 2. Re: Newbie Scripting Question
            philmodjunk

                 To expand on that, there are two basic ways that a database can "find" information matching a specific set of criteria.

                 1) As Jim indicates in his post, a relationship can match a record or records in one table to a record or records in another table if you specify your relationship to match on fields where you can specify your criteria.

                 2) You can also use such criteria to "Query" the database. In FileMaker, we call this performing a find. You can enter find mode, specify criteria and perform the find to pull up a found set of records matching the criteria you specifiy.

                 and such finds can be scripted: Scripted Find Examples