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

    calculate last date

    thumper

      Title

      calculate last date

      Post

      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
          davidanders
            

          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
            RickWhitelaw
               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
              EmiliaLoss
                 'MAX' function is suitable for your need
              • 4. Re: calculate last date
                thumper
                  

                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
                  RickWhitelaw
                     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
                    thumper
                       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
                      thumper
                        

                      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
                        thumper
                          

                        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
                          comment_1
                            

                          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
                            thumper
                               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!