3 Replies Latest reply on Aug 14, 2015 11:08 AM by philmodjunk

    Job scheduling Solution



      Job scheduling Solution


      Hello everyone

       I use Filemaker 12 Advance version. I need your help in building a solution for my photographers.

      We have over 300 schools that we photograph the students, and in each school we do that twice, once as a main shoot and the second time for Retake. We usually call the school and tentatively book a date and send few photographers to do the job when they confirm the date. What I need to be able to do is to schedule a photographer to do a job and then not have her available in drop down when trying to select her again on the same day in that school or any other schools. since she is already scheduled.

      how do I create these table and relationships.

      I know enough Filemaker to be able to create these tables but I get myself in trouble when I create the second part of the job for retakes.

      Please help



        • 1. Re: Job scheduling Solution

          This sounds like a type of event managing solution where a photo shoot is the event. Both first shoot and retakes can simply be treated as two different events.

          So you have a many to many relationship as the "back bone" of your relationship graph:


          Photographers::__pkPhotogID = Photog_Shoot::_fkPhotogID
          Shoots::__pkShootID = Photog_Shoot::_fkShootID
          Schools::__pkSchoolID = Shoots::_fkSchoolID

          I am assuming that this matches your intended design though my names for tables and fields are almost certainly different from yours. The key part is to get a list of all photographer IDs currently scheduled for a shoot on the same date as a shoot for which you  are scheduling photographers. The simplest method for getting that list would be to define an unstored calculation field in Shoots that uses ExecuteSQL to produce a return separated list of all such IDs from Photog_School records that are linked to a Shoot record with the same date.

          If not using ExecuteSQL, you'd need to add some more table occurrences here:


          Shoots::ShootingDate = Shoots|SameDate::ShootingDate
          Shoots|SameDate::__pkShootID = Photog_Shoot|SameDate::_fkShootID

          Shoots|SameDate is another table occurrence of Shoots and Photog_Shoot|Samedate is another table occurrence of the Photog_Shoot table. If "Table occurrence" is unfamiliar, see: Tutorial: What are Table Occurrences?

          These relationships make it possible to define a calculation field in Shoots that evaluates from the context of Shoots with the list function to get the same list of values: List ( Photog_Shoot|SameDate::_fkPhotogID )

          This list is what I call an "exclusion list". To see how to use such a list to produce a diminishing or dwindling value list that drops out your photographer each time you select one for a shoot on the same date, see:

          "Adventures In FileMaking #1 - conditional value lists" There are 10 different conditional value list examples in this file. One is the dwindling value list example that uses this type of exclusion list.

          • 2. Re: Job scheduling Solution

            Thanks for your response however I found it very confusing but managed to create the database with the names you suggested. What I don't understand is which layout is going to be holding the Portals and what portals goes on the layout.

            Can you please elaborate?

            • 3. Re: Job scheduling Solution

              Presumably you'd start the process of assigning Photographers to a "Shoot" by creating a new record on a layout based on Shoots where you'd select the school (may have to first create a school record if they aren't already in your system) and specify the date of the shoot as well as the "type" (Original shoot or retakes). Then you'd use a portal to Photog_Shoot with the diminishing value list to assign photographers.