4 Replies Latest reply on Jan 21, 2013 1:03 PM by philmodjunk

    Field value greater than previous record

    JohhnyHilly

      Title

      Field value greater than previous record

      Post

           I have a layout with a portal in. In this portal I have a drop down list to select a certain piece of equipment. I then enter a field value for this equipment, total running hours. Every week I'll be creating a new record and entering the new total running hours for this equipment, so how do I ensure that the most recent value entered will be greater than from the previous record. The records are linked by a vessel name and each record has an ID number associated with it.

           Also I need to take into account the fact the first entry/record will not have a previous record to compare to.

           I'm not sure how to go about this. Can anyone help me with this? Thanks.

        • 1. Re: Field value greater than previous record
          Sorbsbuster

               You can create a self-relationship for that table, where:

               Vessel = Vessel
               and
               ID does not equal ID

               You can sort that relationship in descending ID order, or use the Max () function to find the previous highest value, and check against that.

          • 2. Re: Field value greater than previous record
            philmodjunk
                 

                      The records are linked by a vessel name

                 This is not a good idea. What will you do if you get two vessels with the same name? What will you do if someone misspells the vessel's name and you con't catch the error until after you have created related records in the portal?

                 The Vessels table needs a unqiue, auto-entered ID, such as  fileMaker generated serial number as that way you avoid these issues.

            • 3. Re: Field value greater than previous record
              JohhnyHilly

                   Yeh sorry Phil, I was suppose to write "linked by vessel ID" which is what I have. What I forgot to add in the original post is that it is not just the previous record of the database...it is the previous record from a specific vessel. So how would I be get the field value "total running hours" from the previous record from that vessel? Do I have to find the vessel ID? If so, how do I do a generic find when I am seaching for the one vessel?

              • 4. Re: Field value greater than previous record
                philmodjunk

                     My advice would match Sorbsbuster here, but use EquipmentID instead of VesselID. Where Equipment ID uniquely identifies one piece of equipment installed on one specific vessel. Referring by memory to other posts, that ID field would be the primary key field used in your EquipmentList table.

                     ServiceLog::_fkEquipementListID = ServiceLog 2::_EquipmentListID AND
                     ServiceLog::__pkServiceLogID ≠ SerivceLog 2::__pkServiceLogID