5 Replies Latest reply on Apr 7, 2015 7:22 AM by philmodjunk

    Finding records based off of equal values

    johnharrison

      Title

      Finding records based off of equal values

      Post

      Hello,

      Is there a way to find values that are equal by finding 1 based on a calculation field that is Timestamp = A summary field that is the timestamp of the last instance of that record?

       

      i.e.  I have a summary that calculates the max of all the timestamps from a certain group of data that all has a similar characteristic (ie product number).  Then I say time1 = summary filed, but for every instance it returns 0 even when both values are the same.  I assume this is because there may be extra "somethings" in the summary calculation which is why the equivalence test is failing, but I am not sure.

       

      UPDATE: GetSummary ( Date Summary; Trailer #) = Date Completed

      This returns the correct is equal value so then I can find all the 1's.  However this fails, because the find only works when the data is sorted.  Is there a way to know the summary field without having the data sorted so I can return the results that I want ?

        • 1. Re: Finding records based off of equal values
          philmodjunk

          I suggest not using a find to find this record.

          You can sort your records so that this record is the first or last record and then use go to record to go to that record.

          or

          A relationship that matches to all records in the table but is sorted in descending order on this field will make that record the first related record.

          • 2. Re: Finding records based off of equal values
            johnharrison

            So I can sort based on the equivalence value, but for some reason when i do this it takes O(n) time, which in my case n = 20000.  I am not sure why this sort takes exponentially longer than the others, but it does.  Are there any ways to get around this ? I have tried exporting the records found in the continuous sub summary view to excel, but this exports nothing.  Would there be a way to export these sub summary records to another FileMaker Pro table using a script ?

            • 3. Re: Finding records based off of equal values
              philmodjunk

              Sorting on an unstored calculation field will take longer than sorting or searching on a stored/indexed field. The absence of a ready made index on a field that is not indexed makes a significant difference in how fast you can search or sort on that field.

              But you do not need to use a summary field to reference the last instance of a record or to access a related record with the greatest timestamp value...

              • 4. Re: Finding records based off of equal values
                johnharrison

                Then how would I be able to return the last instance based on most recent date out of all the records with a similar ID ? I am not sure what I am trying to do is clear.

                Ex.

                Product #      Date     Info

                      1            4/2      Test

                      2            4/5       Case

                      1            4/3       One

                      2            4/4       Two

                My desired return using a find search would be

                Product #      Date     Info

                      2            4/5       Case

                      1            4/3       One

                • 5. Re: Finding records based off of equal values
                  philmodjunk

                  Set up two tables: Products and Tests

                  Products::ProductNumb = Tests::ProductNumb

                  Double click the relationship line and specify that Tests be sorted by Date in descending order.

                  Now set up a list view layout in products, but add the date and info fields from Tests to this layout and they will show the data from the related test record with the most recent date.