1 Reply Latest reply on Apr 15, 2009 8:17 AM by TSGal

    Field to display last found date given a criteria



      Field to display last found date given a criteria


      I have a FileMaker layout which includes a date and a table containing columns for model numbers and serial numbers of various instruments.  Each record, which contains several instruments (model and serial number), has a unique record number field.  I then have a second layout which is a list containing columns for the record number and the FIRST serial number for that record number.  There is also a third column for this second layout in which I would like to display the date for the HIGHEST (largest magnitude) record number which contains that serial number.  I only have a little experience with FileMaker but am fairly capable so I just need a little guidance here.  I'm not sure how to go about this.  My company uses FileMaker Pro 5.0, by the way (it's embarassing but it's all I have to work with).


      Thanks in advance for any help on this.

        • 1. Re: Field to display last found date given a criteria



          Thank you for your post.


          It has been awhile since I've used Filemaker Pro 5, but I should be able to point you in the right direction.


          First, FileMaker Pro needs to look up the Serial Number, which I'm sure you are accomplishing.  However, looking up the HiGHEST record number is the problem.


          1. Create a calculation field, SerialRecord with the formula: SerialNumber & RecordNumber.


          If neither the SerialNumber nor the RecordNumber are a fixed length, then you need to make it a fixed length.  For example, if the SerialNumber is a maximum of six characters, and the RecordNumber is a maximum of four characters, the formula would be changed to:


          Right ("00000" & SerialNumber, 6) & Right ("000" & RecordNumber, 4)


          2. Create another calculation field, MaxSerialRecord, with the formula (assuming the information above):  SerialNumber & "9999"

          or, if the SerialNumber is not a fixed length:  Right ("00000" & SerialNumber, 6) & "9999"


          3. Create a lookup into the same file, and match MaxSerialRecord to SerialRecord.  If no exact match, then copy using next lowest value.


          This way, it tries to find the same SerialNumber and match the RecordNumber to "9999".  Since it can't find it, it will find the next lower value below "9999", which would be the maximum.  Does that make sense?


          This should get you pointed in the right direction.



          FileMaker, Inc.