3 Replies Latest reply on Mar 31, 2011 12:04 PM by philmodjunk

    CALCULATION help with the LAST Function

    TracyBogans

      Title

      CALCULATION help with the LAST Function

      Post

      I am using the LAST function, LAST(DUE_DATE),  to get the date of last due date. But when the field is empty it pulls the date from the next record that has a date in the field.

       I want to have it show the information from the last record entered even if the date filed is empty.

      IE.

                    F1         F2

      Record 1   test   

      Record 2   test   

      Record 3   test   4/1/11

       I want it to show the date that is in record 1 even if there is no date entered in the field. Instead of pulling the date the was entered in record 3.  Record 1 was the last information entered into the database.

        • 1. Re: CALCULATION help with the LAST Function
          raybaudi

          If the relationship is sorted descending by ID, all you need is a calculation field with calc:

          Related::Due_Date

           

          • 2. Re: CALCULATION help with the LAST Function
            LaRetta_1

            "I want it to show the date that is in record 1 even if there is no date entered in the field. Instead of pulling the date the was entered in record 3.  Record 1 was the last information entered into the database."

            I believe you may be confused about LAST vs. OLDEST.  Record 1 in your table would be the oldest and it would be the first.  Records in a table, by their very nature, are entered in your table in their unsorted state of creation order, with Record 1 being the first and so on.

            The third record would be the LAST but it would be the newest.

            So if you want the FIRST record in your table, no need to sort the relationship at all ... simply place the related field directly on your layout (or use it however you wish).  If you want the LAST record (it would be record 3) then you would STILL not sort your relationship but use the Last() function to get that information.

            If you are suggesting that you are working with a sorted set then the relationship doesn't adjust to fit that criteria.  So maybe it will help to get clarification.  Also, you don't indicate that this is a related (or self-join) table you are looking to.  Any time you wish to look across a group of records, you need to create a relationship and look through the relationship to compare to associated records.

            • 3. Re: CALCULATION help with the LAST Function
              philmodjunk

              And a sort order specified in the relationship will change which related record is "first" and which is "last".

              I remember another poster, Fitch?, indicating that last returns the last non-blank value. If this is the source of the trouble here, you might try this trick:

              Define a field, cBlankDateFlag, as a calculation field: IsEmpty ( DateField ).

              In the parent record, define a calculation that checks this field for the last related record.

              IF ( Not Last ( RelatedTable::cBlankDateFlag ) ; Last ( RelatedTable::DateField ) )