4 Replies Latest reply on Nov 26, 2014 9:51 AM by CharlotteLasek

    How do I choose the nearest date from multiple date fields

    timbottrill

      Title

      How do I choose the nearest date from multiple date fields

      Post

           I am looking to create a field that chooses the nearest date from 3 other fields.

           The 3 fields all have dates in and I want to create a field that looks at each of the 3 fields and states which one is the soonest and when that is.

           Any ideas??

        • 1. Re: How do I choose the nearest date from multiple date fields
          philmodjunk

               Sounds like dates that should be stored in related records rather than fields.

               But do you want the name of the field or the date stored in it?

               And is it possible that one or more of the dates in these fields might be a date in the past?

          • 2. Re: How do I choose the nearest date from multiple date fields
            timbottrill

                 Thanks for the reply.

                 I perhaps should explain it in a little more detail.

                 I am building a property database that includes, amongst other things, 3 fields:

                 Lease expiry

                 Next Rent Review

                 Tenant break

                 I then need a field to show me when the next event is and another field showing how far away that event is.

            • 3. Re: How do I choose the nearest date from multiple date fields
              philmodjunk

                   You could use a table of "events" where a text field identifies the type of event and a date field records its date. A sorted relationship could then be set up so that the nearest pending date is the first related record. The Subtracting that date from Get ( CurrentDate ) then computes "how far away it is" by computing the number of days between the two dates.

                   MainTable::cToday > Events::EventDate

                   cToday would be an unstored calculation field: Get ( CurrentDate ) with Date specified as the result type.

                   This relationship can be sorted in ascending order by EventDate and then a reference to  fields from Events when placed on the MainTable layout will show data, such as the event name and date for the next upcoming event.

              • 4. Re: How do I choose the nearest date from multiple date fields
                CharlotteLasek

                Hi there, 

                I'm trying to do this, too. I have two tables: Assets, Events.

                       
                • Each event has a Start Date. 
                •      
                • Each Asset can be included "on tour" via a checkbox and associated with an event in a separate field. 

                I'd like the nearest event to automatically be associated with an asset when the "Include on tour" option is selected.

                I tried following your directions above, but I feel like I'm missing something. I created Assets::cToday (calculation: Get (CurrentDate); result: Date) and created the sorted relationship as described. I'm not sure what to do with this: "Then Subtracting that date from Get ( CurrentDate ) then computes "how far away it is" by computing the number of days between the two dates." Is a calculation in another field? How does this connect a specific Event to the chosen Asset?

                Thanks!