4 Replies Latest reply on May 29, 2012 9:29 AM by MehrdadBastan

    Script Help



      Script Help


      Hi Everyone

      Please help me figure out what I need to do. I am a self thought FM user and love learning more. Last year I developed a Schedule solution for my work which worked pretty good however I've been adding more and more functionality to it.

      Now I feel I am stuck so I could use your help.

      I have about 20 photographers which go to schools for photo shoot & they need to know where they go each and everyday. In the example below I have 2 school one needs photographer for only one day and the other need photographer for 2 days.

      The fields are

      School name

      Shoot date Jan1/2000

      Photographer1 Day 1    (John)   Photographer2 Day 1   ( Steve)

      Photographer1 Day 2    (Steve)  Photographer2 Day 2


      I have a script that will find the schools in a date range provided.

      Enter Find mode[Restore]

      Set Field (School_Info::Start Date & "..." & School_Info::End Date)

      Perform Find[]

      What I need if a script that will add the the same school to the next day if it is a 2 day job


      School 1 is scheduled for Jan/1/2000 and Jan/2/2000 but only one photographer need to go back and finish up.

      School 2 is scheduled for Jan/1/2000 only


      the script should bring back something like this in the sub-summery report.

      Jan 1/2000

      school 1 -- Photographers John, Steve

      School 2 -- Photographers Jane, Ben

      Jan 2/2000

      School 1-- Photographer Steve

      School 3-- Photographer John, Michelle

      and so on and so forth

      I'll be more than happy to send anyone the clone version of the file if it helps understanding my dilemma.



        • 1. Re: Script Help

          Photographer  (there are many)

          Location of shoot  (there are many)

          Schedual Appointments  (there are many - each has one or more Photgraphers, but only one location)

          Your focus is Schedual, which would be a Join Table to link Photographers to Location within Schedual.

          Without knowing your table setup...


          A student can take many classes and a class can have many students.  Related these two tables together would be difficult without the use of a join table.  The join table will contain the primary keys from the student table and the class table.  The join table can be called anything you want.  For this example we will call the join table “signups.”

          1. Create a new table in FileMaker Pro and call it "Signups"
          2. In the “SignUps” table, create a primary key field called “SignupsID.”  This step is not necessary for the join table to work properly but it is good relational database practice for every table to have a primary key.
          3. In the "SignUps” table, create, at least, a “StudentID” field and a “ClassID” field.  Since these fields will be populated when records in either the “Students” or “Classes” tables are created, no auto-enter field options are needed.
          4. Additional fields can be added to the “Signups” table as necessary.  Some examples of other fields that might be appropriate for the “Signups” table would be a “date” field to keep track of when someone started a class or a “cost” field to keep track of how much someone paid to take a class.  Join tables typically hold fields that might not make sense to have in any other table.
          5. Relate the tables together based on the primary and foreign keys


          • 2. Re: Script Help

            Thanks for your response. I feel you have cut and pasted the answer form an earlier post because it does not apply to my question. My schedule although it is school related it does not deal with individual students and attending courses. I am scheduling photographers to do the photo shoot. I know you will answer me that the name does not matter it is the principal (relationship) that matters.

            Would you please read my question carefully and help me with the answer? I'll be more than happy to send you the clone file if you'd rather see what I am asking for.


            • 3. Re: Script Help

              @Mehrdad Bastan,

              It would help to more fully describe the tables that you have. What DavidAnders is suggesting is that you have a many to many relationship.


              And you would build your shooting schedule in the join table (ShootingSchedule) By linking a photographer record to a school record and then entering date, time and other data about that day's photo shoot in fields in the ShootingSchedule record.

              Does the design of your database resemble this structure or do you have a different design?

              • 4. Re: Script Help

                Thank you for cleafying. I knew David had a great point however I missed the point he was tring to make. I am not good with relationships :)

                enclosed is the simple relationship I have in my file. I appreaciate the help. Can I send you the file to see the entir opration?