1 2 Previous Next 16 Replies Latest reply on Apr 11, 2014 12:47 PM by BruceRobertson

    Update field once a year


      Hey guys,


      it seems like this is a very easy thing to do, however I feel like I am trying to kill an ant with a missile


      I am just simply want to Upgrade the School year of a person once a year (in the summer)!


      I thought of multiple approaches, like e.g. create a few new fields with year and a check if it already has been updated with a script...


      But all my approaches feel rather complicated! And I start thinking there must be a super simple way to do this!
      Is there?

        • 1. Re: Update field once a year

          What does that exactly mean? In solutions you might have years, within years you might have semesters and those have a start/end dates. 


          And then you create join records for students enrolled in courses that are attached to semesters. 



          Best regards,


          agnes b. riley

          filemaker and web development

          zeroblue (http://www.zerobluetech.com/)

          download vCard (http://www.zerobluetech.com/docs/Agnes%20B.%20Riley.vcf.zip)



          FileMaker Certified in 10 and 11 • Member, FileMaker Business Alliance

          T: 877 917-9079 . C: 917-660-7221

          Store (http://store.zerobluetech.com/) | Blog (http://blog.zerobluetech.com/) | Facebook (http://www.facebook.com/zerobluetech) | Twitter (http://www.twitter.com/zerobluetech) | LinkedIn (http://www.linkedin.com/company/zeroblue-technology-solutions)

          • 2. Re: Update field once a year

            Agnes brings up good points. We need more information. I am confused as to which kind of year you are referencing. A literal year (2014) or the student's grade level (Freshman)



            • 3. Re: Update field once a year

              are you referring to the "grade level" such as: she's in 4th now and over the summer that gets changed to "5th"?


              That sounds fraught with possible errors!


              I'm with the others. MORE INFO and perhaps this is a data normalization issue that needs to be addressed.



              • 4. Re: Update field once a year

                Sorry guys,


                yeah I wasn't really clear on that.

                This is not a solution for a school or so, just for a programm where 4th to 12th graders attend.

                So its more simple then doing something detailed.

                It is just one field that where I can put the grade in. But I figured it would be good if I put the grade in once and every summer it does go to the next grade! And once it hits 13 it would just say out of school, or something like it.


                So basically what Beverly thought I could mean:

                are you referring to the "grade level" such as: she's in 4th now and over the summer gets changed to "5th"?

                • 5. Re: Update field once a year

                  If your grade is a numeric value, you could use a replace with the following calculation grade_field + 1.  This will increment each grade by 1.  This is also with the asumption that all students are being promoted.

                  1 of 1 people found this helpful
                  • 6. Re: Update field once a year

                    Assuming that all students are promoted is fine (it can still be manually be updated).


                    But with just replacing it, where would you type in the grade at the first time?

                    Also how could I trigger the field to recalculate in summer?

                    • 7. Re: Update field once a year

                      Use a calculated Replace. the calculation gets the grade from the grade field. I would put the script in the script menu and then somebody can run the script in the summer.

                      • 8. Re: Update field once a year

                        By putting it on your calendar.


                        You mention several cases where manual operations will be required.

                        Automating it seems doubtful.


                        Also, since you are choosing not to do this in a normalized way (set of related records) you run the danger of doing it twice and completely losing track of actual student history and progress.

                        • 9. Re: Update field once a year

                          There defintitely could be a potential problem with doing this twice.  I did something similar in the past.  I stored in a field when the update occurred. This field was used to prevent a second update.  I would also store a duplicate of the original grade in another field.  If a problem occurred, the grade could be reverted back to the original.

                          • 10. Re: Update field once a year

                            Thanks guys for all the suggestions!


                            I was looking at them and got kind of a mix


                            This is what I did, in case someone ever is looking to do something like this!


                            I created a second field dev_grade

                            Then I created a setup button with a script that just shows a custom dialog with an input field that parses dev_grade

                            and finally I made, like suggested, the grade field a calculation field, almost treating it like a birthday, just the other way round. With a Date in Summer. So when the date is passed it adds on a year to grade, otherwise it adds nothing!

                            Also I included that if the Grade is < 12 it just shows "out of school".


                            Thanks again for all your ideas and tips!

                            • 11. Re: Update field once a year

                              A question for you to consider is what value you get from having this data. If the only thing you want is to know what a student's current grade is, then I guess your OK with what you are doing. But your current approach breaks a database rule about not deleting data. Consider:


                              •     a student is shown as currently in grade 4, but you cannot tell from this whether (a) last year they were in grade 3; (b) they are new to the school this year; (c) they are repeating grade 4; (d) they did grade 2 two years ago, left, and are now back again

                              •     if you instead kept this information in a separate enrollments table you would build a history over time, at the same time delivering the data you currently require in a more robust way, capable of being updated year by year without throwing away the history.

                              1 of 1 people found this helpful
                              • 12. Re: Update field once a year

                                Thanks alot!


                                That is really good insight. And I am definately learning something good here.

                                In this case it is really only a side comment what grade the students are in. But I think I might still update it following the suggestion. It doesnt hurt to get have more detailed Data.


                                Would this be a correct approach:

                                I could have a "Grade of first enrollement in programm" field. Also saving current year.

                                Then a "repeated grades" field.


                                Based on those fields, I can just do a value count on the grades field and calculate the grade by doing:

                                (Current Year-Year of enrolement) +Grade of enrolement - Valuecount repeated grades


                                That gives me the current grade and gives me more detail and not breaking the rule about deleting data.


                                Does that sound right?

                                • 13. Re: Update field once a year


                                  Read Keyword's suggestion again.

                                  • 14. Re: Update field once a year

                                    Here is a completely different solutions you can use if you don't want to deal with this every year.  Create a new field called graduation_year for the year the student should graduate and then create a grade calculation field that determine the grade based on the current date and the graduation_year.  If a student is retained all you would need to do is update the graduation_year for the student.

                                    1 2 Previous Next