4 Replies Latest reply on Dec 29, 2013 9:20 AM by john.s

    Creating subfolders and reporting multiple jobs



      Creating subfolders and reporting multiple jobs



      I Need a way to track sports teams coverage for multiple schools.  The challenge...at least for me...is to be able to track games under multiple schools.  Here is an example.

      We have contracts with school A B C and D.  Those schools play various other schools and also each other.  When a photographer covers a game they will enter data into FM database, a job name is generated, and a folder is created on the desktop with that job name.  The format of the job name for action sports is yyyymmdd_sport_hometeam_vs_opponent  when the sport is something like cross country or swimming there could be several of our teams participating in which case I need to create a separate folder for each team within a master folder for the event.  For example the master folder would be yymmmmdd_swim_meet_at_location.  The location may not be one of our schools. I need to be able to report for each school but also need to know that it was a single event covered by a single photographer.


      I have a table of schools we contract with and we need to report all the activity we covered for each school and also need to be sure we have covered all their sports, proms, and other special events.  I have the senior photos and special events working but have hit a snag with the sports.  The layout for creating an action sports job currently has a drop down list of schools we contract with and I use that to capture the first team.  I put in an opponent text field but that doesn’t help when the opponent is one of our schools.  I also don’t know how to handle the cross country or swimming type of sports where several of our teams participate. I’m thinking checkbox but the list of schools could get very large and don’t know how I would report out when some are not our schools.  We currently cover eleven schools...each with about 30 sports teams when you count boys and girls and spring and winter sports...and they each play a lot of other schools that we do not contract with.


      To complicate things even more I need to put subfolders inside the job folder for each team so the photographer can sort the photos and send them to the appropriate school.


      I received some very valuable help from this forum in creating the job names and  folders and was able to get that working...then I hit this wall.


      I’m still very new at this...as you probably can tell...and can use all the help I can get.





      Here are screen shots of the relationship table, input screen and fields for the jobs table.




        • 1. Re: Creating subfolders and reporting multiple jobs

               "creating a folder" is not a built in capability for FileMaker. Either a system script or a plug in must be used to create the folder. What method are you using?


          I have a table of schools we contract with

               In your tables what is the difference between a customer and a school?

               Say you send a photographer to a Football game where you have contracts with both schools. Do you send one photographer or two? Do you log that as one job or two?

          • 2. Re: Creating subfolders and reporting multiple jobs

                 Hey Phil,

                 I thought I posted a reply but I don't see it so I'll try again.

                 The Customer table has a field for CustomerType.  A consumer is either a School, Corporation, Youth League, or Person (which includes families, brides, etc).  So a School is just another type of customer.  I then have a Schools table that contains information unique to the school including contract obligations, and percentages of revenue that go back to that school, etc.

                 Only one photographer goes to a game and it is considered one job however I need to report it out to both schools at the end of the season.  That photographer might have shot several jobs that day and when they return to the studio they create a job  for each assignment.  They might have covered a pep rally for school A, then shot an on location family portrait, and finally went to a football game between school C & D.  They offload all images from the day into a folder called "Dumps" which is on the desktop.  Hopefully this system will create folders for each job and within the sports jobs a folder for each team.  The photographer than will drag the images into the appropriated folder…in the case of the sports thy might duplicate some of the images and add it to both teams folder.  Later an editor will pull the folders out of the Dumps folder, edit the images, and go into FM and mark the job as edited.

                 Here is the script I'm currently using to create the folders.  I couldn't figure out how to get them into the "Dumps" folder so they just go to the desk top…something I hope to fix.


            • 3. Re: Creating subfolders and reporting multiple jobs

                   I haven't owned a Mac in years so I cannot help you with an AppleScript.

                   Only one photographer goes to a game and it is considered one job

                   But you might want to have the system automatically log it as two jobs. I'd think that you'd have different subsets of the total pictures taken for each participating customer. I wouldn't think that school A would be happy to get several pictures of School B's cheerleading squad, just to give one example, but that's more a "business issue" than a database issue. It might however become one when it comes to setting up the links to different or the same sets of photos.

                   As it is, with one job that may be linked two more than one customer, you have a many to many relationship. One job can be performed for two or more customers and you can perform many different jobs for the same customer.

                   Start with these relationships:


                   Jobs::__pkJobID = Customer_Job::_fkJobID
                   Customers::__pkCustomerID = Customer_Job::_fkCustomerID

                   You can place a portal to Customer_Job on the Jobs layout to list and select  Customers records for each given Jobs record. Fields from Customers can be included in the Portal to show additional info about each selected Customers record and the _fkCustomerID field can be set up with a value list for selecting Customers records by their ID field.

              • 4. Re: Creating subfolders and reporting multiple jobs

                     Thanks Phil,

                     I need to take another look at this whole Portion of the database and rethink the creation of Jobs. The more I look at it I think I'm confusing a Job File/Folder name and Job for a client and they really are not one in the same.  When we cover a game for more than one school we really are doing more than one job even though we need to file it as one game.  We will also have more than one photographer at an event when we shoot youth sports leagues and for some weddings.  So the whole structure needs to be redone.  This is my first shot at creating a database and while frustrating I guess I shouldn't be discouraged…I did get the Senior Portrait section working