4 Replies Latest reply on Jul 24, 2013 5:08 PM by danlee

    Script to find data from date fields.

    danlee

      Title

      Script to find data from date fields.

      Post

           I have table MeasurementData with a date field BodyAgeDate.  How can I set this up finding data from this field of first date and most recent date.  Example:  In MeasurementData table I have a field named BodyAgeDate, with another field in it titled Weight.  I want to set up a calculation field Beginning Weight and Current Weight where it would take the data from field Weight and populate the Beginning Weight and Current Weight.

           Example:

           BodyAgeDate 7/12/2013; Weight 233 --> This would be Beginning Weight

           BodyAgeDate 8/12/2013; Weight 200 ---> If for some reason 7/12/2013 record was deleted or changed to later date, 8/19/2013, then this would be Beginning Weight

           BodyAgeDate 8/22/2013; Weight 220

           BodyAgeDate 9/2/2013; Weight 190 ---> This is the most current/recent weight

           So for Beginning Weight field it would calculate to be 233

           Current Weight field it would calculate to be 190

      MeasurementData table

            

        • 1. Re: Script to find data from date fields.
          philmodjunk

               Note: By using the Upload an Image controls located below Post a Answer, you can upload a Gif JPG or PNG file directly to the forum so that it appears in your post.

               You would seem to have this relationship as the one in question here:

               Clients::_pkClientID = MeasurementsData::_fkClientID

               In this relationship--I am assuming that no sort order has been specified for the relationship, the earliest record in MeasurementsData linked to a given client will be the record with the beginning weight. The most recently created record would be the last related record.

               Thus, if you put  MeasurementsData::Weight on your Clients layout, it will show the beginning weight measurement for that client. And Last ( MeasurementsData::Weight) will return the most recent weight measurement for that client.

               Note: To make sure that this set up always works correctly--even if you have to import this data into a new copy of your database, specify a sort order on your relationship that sorts the related records in ascending order by the date the measurement was taken.

          • 2. Re: Script to find data from date fields.
            danlee

                 Thanks for the suggestion.  The beginning weight works.  I created a calculatioin field, c_RecentWeight and used, Last ( Weight ), but when I put that in the Clients Layout it still shows the beginning weight.

            • 3. Re: Script to find data from date fields.
              philmodjunk

                   Is that a field of type calcualtion or a number field with an auto-entered calculation? The auto-enter option won't work for this.

                   and the syntax should not be Last ( Weight ) , it should be Last ( MeasurementsData::Weight ). The calculation field should be defined in Clients, not Measurements data.

              • 4. Re: Script to find data from date fields.
                danlee

                     The calculation field was the issue.  I did not put it in Clients table.  Thank you!!!