1 Reply Latest reply on Jun 29, 2015 9:32 AM by philmodjunk

    Min Date in related table::field



      Min Date in related table::field



      I have two related tables:

      ServiceActivity::WaitUn5 = Un5LWait::Link

      Un5LWait is another table occurance of ServiceActivity.  WaitUn5 is a calculation field that returns 1 if criteria is met, 0 if not.  Link is a field that auto enters 1 in every record. There are three other fields/links like this all with different criteria.

      My logic behind the link/relationship is I'm trying to find the minimum date in AccDate field where these fields equal each other.

      So I created a calculation field called U5LongWait, evaluated from context of ServiceActivity, using field from Un5LWait::AccDate.  When I'm looking at the ServiceActivity Table, it gives me results for the correct records, and they all have the same date.....good.  But, it's not a date found in any of the AccDate fields in any record.  Appears to be a random date....telling me 10/06/0015.  I've tested on only five records so it is easy to see all the data I'm dealing with.  All have proper dates in the AccDate field none mataching the result I get in U5LongWait.  Any ideas?

      Thank you in advance.

        • 1. Re: Min Date in related table::field

          It would appear that this is something to evaluate from the context of Un5lWait, not ServiceActivity. A given record in ServiceActiity will either match to all records in Un5LWait or no records in Un5LWait and that does not appear to produce the results that you want here.

          Min ( ServiceActivity::accDate ), set to evaluate from Un5Lwait would return the minimum date value from those records in ServiceActivity where WaitUn5 is 1--assuming that this calculation is not an unstored calculation.

          You could also sort ServiceActivity by AccDate in ascending order in this relationship (if needed) and then a reference to the first related record in ServiceActivity from the context of Un5LWait would refer to the min AccDate value of all related records.