3 Replies Latest reply on Mar 17, 2015 7:32 PM by philmodjunk

    Compare and Analyse Values Day by Day



      Compare and Analyse Values Day by Day


      I need to be able to compare the Gain or Loss in weight (kgs) in a discreet population, each day, over a period that will always be at least 8 weeks but could be up to 26 weeks (occasionally longer).
      I have a table of Individuals and a related table of Weights and use a List Layout to collect the Weights each day.
      So far so good but….
      I need calculated fields in each day’s record for each Individual that shows the;
      Actual Gain or Loss since the last weighing
      Percentage Gained  or Lost since the last weighing
      Percentage Gained or Lost since  birth
      Date of Birth is the same value for all individuals in the population and is held as a record on the Population table
      I am collecting the data in Numbers at the moment which is very easy but messy and of limited use in analysing the development of the population and comparing it to other similar populations.
      The population consists of 9 individuals at current time and that cannot increase (although it could potentially decrease)
      Populations could consist of up to 14 individuals or as few as 1 although both these extremes are rare.
      Populations will always be discreet.
      I can’t work out a means to get FMP to find the most recent previous record so that I can compare it with the current day’s record.
      Finally I want to use conditional formatting to indicate potential problems - but I think I can sort that out if I can crack the issue of getting a reference to the latest previous record.
      I should add that the previous record will almost always be from the day before, it would be ideal but not essential to refer to that latest previous record but I would be happy if it was just the day before’s record.
      I am using FMP Pro 13 Advanced although I am certainly not an advanced user!


        • 1. Re: Compare and Analyse Values Day by Day

          Does the UIN pup column uniquely identify each individual?

          • 2. Re: Compare and Analyse Values Day by Day


            Each pup has a unique identity number that stay stays with it throughout its life (links to a Dnnn number when homed), the colours refer to nail varnish that is used to identify each puppy visually for weighing, health etc. otherwise it could be very difficult to tell the pups apart.

            The UIN Pup column is set up as a drop down list that shows the Id Mark colour but returns the UIN.  The Id Mark column just displays the nail varnish colour so that it is easy to see who has and who has not been weighed.



            • 3. Re: Compare and Analyse Values Day by Day

              Assumptions on which I am basing this answer:

              a) this data is collected at most once a day for a given pup

              b) Weighed Date is a field of type date.

              Ideally, I would set up this relationship:


              Fields such as ToB, UIN Pup, Sex and ID Mark would be defined in Pups. The other fields in your screen shot would be defined in WeightStatistics. I'd use an internally generated ID field defined in Pups to link a record in pups to all the weight records for that pup in WeightStatistics.

              But whether you do or or have done that or not, the following setup can allow you to access a pup's previous measurement data:


              WeightStatistics::UIN PUP = WeightStatistics|SamePup::UIN Pup AND
              WeightStatistics::Weighed Date > WeightStatistics|SamePup::Weighed Date

              Weightstatistics is the name I am giving a second occurrence of your table where you record these weight measurements. Double click the relationship line between these two occurrences and specify a sort order for WeightStatistics|SamePup::Weighed Date that sorts on this field in descending order. This makes the record for the same pup with the latest weighed date that is less than the current record in WeightStatistics the "first" related record and this will be the immediately previous record for this pup.

              Now you can simply refer to fields of WeightStatistics|SamePup to access data from that previous record.