6 Replies Latest reply on Jan 13, 2017 12:38 PM by waynemartin

    Automaticly update a field

    waynemartin

      Hi all,

       

      I'm relatively new to Filemaker so bear with me,

       

      I have a table where I keep track of my equipment/vehicles, I also have another table where I keep track of repair orders and daily equipment logs on related equipment.

       

      On my equipment table I have a hours field to display the hours of the machine. On my equipment log or repair logs i have a hours field as well. I would like to have the machine hours update every time a new daily log was created that would reflect the current hrs. of the machine.

       

      Any thoughts on this?

       

      Thanks

        • 1. Re: Automaticly update a field
          philmodjunk

          THere are several ways to do this. One way is to use a calculation field in the equipment table that uses the Last function. Make the calculation field unstored. Another method is to define a relationship between your two tables where you specify a sorted relationship that sorts your repair records in descending order by a date field. Using that relationship, you can just place the "hours" field from the Repair table on your Equipment layout.

          • 2. Re: Automaticly update a field
            SteveMartino

            philmodjunk  Phil, being that engine hours is always an escalating number, could the OP also use the Max function?  Just wondering

            • 3. Re: Automaticly update a field
              philmodjunk

              Yes, but why tax your system with an unnecessary calculation?

               

              For small numbers of records--such as you might have while testing things in development, Max will work fine, but it will take more and more time to update as the number of related records increases. How many records you'd need before you see a noticeable delay will depend on a lot of factors, but we can, I think, avoid the whole issue by just accessing the value of the most recently added repair record for a given equipment record.

              • 4. Re: Automaticly update a field
                SteveMartino

                Not trying to hijack or disagree...Does Last depend on the related records being sorted correctly?  Or does it go by creation order?  I tried reading the knowledge base, but it is a little ambiguous to me.

                • 5. Re: Automaticly update a field
                  philmodjunk

                  Like any aggregate function, the relationship's sort order determines what records is First or Last so the relationship, not a portal's, sort order will affect what result is last. In an unsorted relationship, Last will return the most recently created related record where the field specified in the Last function is not empty.

                   

                  That's why I made two recommendations:

                  1. Sort the relationship and just refer to the field from the related record by putting that field directly on the parent record's layout. Doing this will reference the first related record.
                  2. Don't sort the relationship and use Last(  )

                   

                  Please also note that I said: "I think". This is not something that I've tested or researched, but I'm assuming that these two methods are more efficient that using Max. Clearly, options 1 and 2 will also, to some degree, have performance affected by the number of related records so others more knowledgeable than I might chime in at any time and tell me that I'm wrong and in such case, I'll freely welcome the correction and learn something new.

                  • 6. Re: Automaticly update a field
                    waynemartin

                    This worked! i had trouble with it at first but found out it was the way my relationship was set up.

                     

                    Thanks so much!