1 2 Previous Next 15 Replies Latest reply on Feb 28, 2014 5:13 AM by john.s

    Update value in field in one table based on Summary total from different table

    john.s

      Title

      Update value in field in one table based on Summary total from different table

      Post

           Hi Folks,

            Once again I think I am in over my head and could use some help.  I have several schools for which I photograph their Sporting events, Pep Rallies, dances etc.  I need to get a handle on which sports have been covered for each school.  Each school has different sports that they participate in so keeping track is a nightmare.  Compounded by the fact that I need to provide a certain number of images for their yearbook for each sport and sometimes it takes going to more than one game to get that many images.  I need to be able to sum the number of images for each sport by school and if the required number of images is reached I want to set a status field as complete.  This doesn't sound too difficult but with the table relationships I have I can't figure it out.  Any help would be greatly appreciated.  Here is what I have for relationships and data.

                               ---< JOIN_SCHOOLS_JOBS >--- JOBS >---Sports
           SCHOOLS 
                               ---< JOIN_SCHOOLS_SPORTS >--- SPORTS
            
           Top relationship is:
           SCHOOLS::_pk_SchoolCode to JOIN_SCHOOLS_JOBS::_fk_SchoolCode
              JOBS::_pk-JobID to JOIN_SCHOOLS_JOBS::_fk_JobID
               SPORTS::_pk_SportID to JOBS::_fk_SportID
            
           Bottom relationship is:
           SCHOOLS::_pk_SchoolCode to JOIN_SCHOOLS_SPORTS::_fk_SchoolCode
           SPORTS::_pk_SportID to JOIN_SCHOOLS_SPORTS::_fk_JobID
            
           SCHOOLS table has School Name, address, etc
            
           JOBS table is created via a layout and has a portal to JOIN_SCHOOLS_JOBS  one record is created in the jobs table and one record for each school photographed for that job is created in the JOIN table  so Job1, Job2 and Job3 would be records in the Job table and the JOIN table might have 5 records for those three jobs
               job1   school1   Sport1
               job2   school2   Sport2
               job3   school1   Sport1
               
           JOIN_SCHOOLS_JOBS  Table has the following fields: (ImageCount is blank when created)   _fk_JobID   _fk_SchoolCode     ImageCount
             Data might look like this
               job1   school1
               job1   school2
               job2   school2
               job3   school1
               job3   school3
            
           SPORTS Table has a list of Sports and the season in which they are played
              _pk_SportID
              SportName
              Season  (1=winter, 2=fall, 3=spring)
            
           JOIN_SCHOOLS_SPORTS table has records for each school and sport that needs to be photographer  it will be set up at the beginning of each season. It has three fields, _fk_SchoolCode,  _fk_SportID, and Status
             School1   Sport1
             School1   Sport2
             School1   Sport5
             School1   Sport10
             School2   Sport1
             School2   Sport2
             School2   Sport3
             School2   Sport14
             School2   Sport15
           Not all schools participate in all sports
            
           After photographing a game the photographer edits the images and puts them in folders by school,  the images are provided to the school and the number of images is entered into the ImageCount field on the JOIN_SCHOOLS_JOBS table.
            
           What I need to do is be able to sum the number of images by school and sport (more than one job might have been necessary to get enough images for that sport) and now the real tricky part...if the image count is equal to or greater than lets say 150 I need to set the status in the JOIN_SCHOOS_SPORTS table to complete for that particular school and sport.
           I am way over my head on this one.  I have created a layout to monitor the status of work to be done. It has a self join portal for schools with a go to related record and two other portals on the layout.  One is a list of jobs for that school and I was able to pull the images for that school by job.  I can’t sort by Sport because that field in not in the table which the portal is based on it is in a related table.  The third section is a portal to the Sports that need to be covered for each school with a checkbox to show if it is complete or not.
            

        • 1. Re: Update value in field in one table based on Summary total from different table
          philmodjunk

               There are many different options for producing such summary data. The simplest to set up might be a summary report based on your Join table. You can perform a find--say for all records for a given school over a given time period, then sort your records by sport. A sub Summary layout part can bet set up "when sorted by sport" with a summary field to compute sub totals for that sport. The individual job records could be listed under the sub summary part or completely hidden from view.

               Here's a tutorial on summary reports that you might find useful: Creating Filemaker Pro summary reports--Tutorial

               There are also other methods such as setting up a summary recap using ExecuteSQL: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: Update value in field in one table based on Summary total from different table
            john.s

                 Thanks Phil...I'll read the tutorial and see if I can get this working.  Will it be possible to set the status in the other Join table based on the summary amount?

            • 3. Re: Update value in field in one table based on Summary total from different table
              philmodjunk

                   You may not need to. A relationship might be possible that eliminates the need and that would have the advantage of updating automatically without relying on a script to update the total.

              • 4. Re: Update value in field in one table based on Summary total from different table
                john.s

                     That sounds good Phil...I'm still working on getting the summary amounts on a layout, so the relationship thing will be the next step.  thanks again for your guidance I could never have gotten this far without the folks (mostly you) on this forum...I really really appreciate all the help.

                • 5. Re: Update value in field in one table based on Summary total from different table
                  john.s

                       Ok…I've now learned how to create summary reports and have one that shows me total images given to each school for each sport that we photographed.  I can't figure out how to either incorporate that report into an existing layout or use it to update a field indicating we have enough images for a given sport.  I'm including a screen shot with some test data of where I was prior to creating the summary report and it might help explain what I need to do.  The left column of the report is a portal to schools  the center is a portal to Jobs showing all jobs for the selected school and the right column is a checkbox of all the sports that we have contracted to cover for that school  I need to "check off" the sport once I have 150 images of that sport for that school.  I also need to check off the Team Photo once the job is created.  I can't sort the center column by sport because the sport field is non the the table that the portal is built on…it's in a related table.  If I could replace the center column with the summary report I created and check off the right column based on the summitry total, life would be very good.

                       Phil, I'm hoping you have some more magic and can help me do this update based on a relationship?

                        

                  • 6. Re: Update value in field in one table based on Summary total from different table
                    philmodjunk

                         If you recall, I mentioned using a relationship so that the same sub totals appear in your other table. I can guess, but can easily guess wrong here. In what other table/layout do you want to see these totals?

                    • 7. Re: Update value in field in one table based on Summary total from different table
                      john.s

                           Right now I have two join tables 

                                         ---< JOIN_SCHOOLS_JOBS >--- JOBS >---Sports

                           SCHOOLS 
                                               ---< JOIN_SCHOOLS_SPORTS >--- SPORTS
                            
                           Join_Schools_jobs is where we input the image totals, the table has a record for each sport and school shot.
                           Join_Schools_Sports has a record for each sport we cover for each school.  I need to be able to update a status field in this join table based on the summary totals in the other join table.
                      • 8. Re: Update value in field in one table based on Summary total from different table
                        philmodjunk

                             Yes, but you don't actually need to use a script to update such a field.

                             SCHOOLS::_pk_SchoolCode to JOIN_SCHOOLS_JOBS::_fk_SchoolCode
                                JOBS::_pk-JobID to JOIN_SCHOOLS_JOBS::_fk_JobID
                                 SPORTS::_pk_SportID to JOBS::_fk_SportID

                             SCHOOLS::_pk_SchoolCode to JOIN_SCHOOLS_SPORTS::_fk_SchoolCode
                             SPORTS::_pk_SportID to JOIN_SCHOOLS_SPORTS::_fk_JobID
                              
                             An unstored calculation field in Join_Schools_Jobs, cSportID can be defined as: Sports::_pkSportID
                             Then this relationship:
                              
                             Join_Schools_Sports::_fk_Schoolcode = Join_Schools_Jobs 2::_fk_SchoolCode AND
                             Join_Schools_Sports::_cSportID = Join_Schools_Jobs 2::_fk_SportID
                              
                             then a calculation field can be defined in Join_Schools_Sports that uses the Sum funciton to sum the field in Join_Schools_Jobs that records the number of pictures taken. Or it can refer to the summary field you've already defined in Join_Schools_Jobs 2.
                        • 9. Re: Update value in field in one table based on Summary total from different table
                          john.s

                               Thanks Phil,

                               The problem I'm having is that Join_Schools_Jobs does not have _fk_SportID as a field.  That table is created because a Job can have multiple Schools but only one sport.  The schools are created using a portal...to the Join Table from a layout based on Jobs Table.  I tried to add the sport to the Join table but if I change the sport after picking a school the data gets screwed up.  Right now _fk_SportID only exists in the Jobs table.  Is there a way to connect to it from the Join_Schools_Job table?  I spent two days working on this and can't figure it out.

                               I really appreciate your help with this.  You have probably forgotten more about this stuff than I'll ever know :-)

                          • 10. Re: Update value in field in one table based on Summary total from different table
                            john.s
                                 OK I woke up in the middle of the night and realized what I did wrong.  Lesson for this week...don’t work on detail items when very tired.  I now have the cSportID field on the correct table…so Join_Schools_Sports now has the _fk_SportID.  I have image totals showing up in that table but they are not correct.
                                 I’ve placed a calculation field in Join_Schools_Sports using the Sum function to sum the ImageCount field in Join_Schools_Jobs. What I get is one amount for each sport for a school.  For example School1 has 89 in each record...89 is the number of images in the first record for that school in Join_School_Jobs.  School2 has 28 in each record...that is the number of images in the first record for that school.
                                 I then tried to refer to the summary field defined in Join_Schools_Jobs2 and I get the total number of images for each school in each record.  This one I can understand but when I place the field in the portal row every record has the same amount (total images for that school).  At this point I think I’m close but I’m still missing something.
                                  
                            • 11. Re: Update value in field in one table based on Summary total from different table
                              philmodjunk
                                   

                                        What I get is one amount for each sport for a school.

                                   I must have missed something as that is what I thought you wanted for data in this table--especially given the fact that you have one record for each school for each sport in that table to begin with.

                              • 12. Re: Update value in field in one table based on Summary total from different table
                                john.s

                                     I do want one amount for each sport I'm getting the same amount for each sport.  i.e. every sport for school1 shows 89 images

                                • 13. Re: Update value in field in one table based on Summary total from different table
                                  john.s

                                       Maybe a clearer example is school1 has a 5 jobs and the total number of images for Boys Basketball is 152 and the total for fencing is 127.   What I'm seeing is 152 showing up under each sport.

                                  • 14. Re: Update value in field in one table based on Summary total from different table
                                    philmodjunk

                                         Then we aren't getting the correct sportID value to use in the match field or the field you are using is not from the correct table occurrence.

                                    1 2 Previous Next