10 Replies Latest reply on Jan 11, 2010 7:01 AM by thumper

    calculate last date



      calculate last date


      I have a field in 1 table called "Last feed date" and in another table that records records of feedings there is a field called "Feed Date", these tables are related and work great togther, but im having trouble creating a calculation for the "last feed date" to pull the date from the last record created in the feedings table. im not 100% sure if a calculation is required or if its a script i should be after, could someone point me in the right dirrection.

        • 1. Re: calculate last date

          So when a Feedings is created, the field LastFeedDate is reset to the creation date of the newest Feeding entry?

          The Set Field command would not suffice. because when Modify would have to be set? 

          • 2. Re: calculate last date
               I believe you could use the "Max" function. Look it up in help. You could apply it to your feeding dates and it should return the latest.
            • 3. Re: calculate last date
                 'MAX' function is suitable for your need
              • 4. Re: calculate last date

                Thanks for the replies,


                I tried using the max function, what i did was kept my "last feed date" field as a calculation and just changed the calculation value to 


                Max ( Last Feed Date; Feedings::Date Of Feeding )



                , is this correct? its showing up blank for most records and for some its showing a date that isnt even  used in the feeding table for that animal, 

                • 5. Re: calculate last date
                     I believe the calculation would be   Max (  Feedings:: Date Of Feeding ) and date of feeding should be stored as date.
                  • 6. Re: calculate last date
                       Works great! I changed the calculation text and then changed my "date of feeding" field to a date value (which i must have forgotten to do upon creation of field) and now its works like a charm! Thanks :D
                    • 7. Re: calculate last date

                      so if i were to calculate the "next feed date" field, i would just use a calculation like ...



                      ( Last Feed Date + (Feedings::Interval) )



                      "feeding interval beeing a number field

                      • 8. Re: calculate last date

                        I got the calculation to work for the "next feed date" field, im using the following calculation:



                        GetAsDate  ( LeftWords ( Last Feed Date  ; 1 ) ) + 7



                        the number "7" represents the feeding interval field from the feedings table, but when i try the code :



                        GetAsDate  ( LeftWords ( Last Feed Date  ; 1 ) ) + Feedings::interval


                        the result displays the same date as the "last feed date" field



                        but i have no clue as to why since the interval field is formatedas number and the inserted text is a number






                        • 9. Re: calculate last date

                          The context is not quite clear here. It seems there should be a table of Animals and a table of Feedings, related by AnimalID (one animal has many feedings). The feeding interval is an attribute of the animal, not of any specific feeding, and should be stored in the Animals table. Then the next scheduled feeding is =


                          Last ( Feedings:: Date ) + Interval


                          and this calculation needs to be done in the Animals table, too.

                          (Note that this is assuming records in Feedings are created in chronological order or that the relationship is so sorted.)



                          The expression  LeftWords ( Last Feed Date  ; 1 ) makes no sense unless Last Feed Date is NOT a Date field and contains some other data in addition to the date - not a good idea.

                          • 10. Re: calculate last date
                               ok I understand, the interval needs to be apart of the animal table, works great now, the "Last" function seems much more simple too! Thanks!