0

Retrieve a Previous Record Date

Okay, this one is turning out to be a little tricky.  For my contact tables, I also have a sub table labeled Chronos.  This is the table for all the notes kept on each individual contact.  However, I want to improve workflow and accountability, thus I want to calculate days between the date of the current Chrono record for said client and the previous Chrono record for same client.  Obviously, you can use first and last statements for record dates, however those won't return what I need.

Date Time of Previous (the one immediately preceding the current record.  If I use last(), it will just give me whatever chrono record is the latest.  Look below.  So my ultimate goal would be to calculate the days between each record.

2 replies

null
    • Fred
    • 8 mths ago
    • Reported - view

    Try something like, (make sure to correct any table/field names):

    let t := this;
let xBase := (Contact.Chronos order by 'Date + Time');
let xCurIndex := index(xBase, t);
if xCurIndex = 0 then
    0
else
    let xPrev := item(xBase, xCurIndex - 1);
    days(xPrev.date('Date + Time'), t.date('Date + Time'))
end

    Line 1: gathers the data of the current record

    Line 2: gets all related records in the Chronos table through the relationship in Contact table and then orders the array by the field 'Date + Time'.

    Line 3: using the index() command we can find what order in the array from Line 2 the current record exists.

    Lines 4 - 10: checks to see if the current record is the first record in the array so we don't need to do anything. If it is not the first record then we can find the previous record (Line 7) using the item() command and data from Line 3. Line 8, we use the days() command to find the number of days between two dates. Since you have a date/time field you need to use the date() command to strip out the time data so the days() command will work.

      • Dave_Irving
      • 8 mths ago
      • Reported - view

       That was perfect after replacing my fields.  I then also added a days since last activity.  For my last activity date I use this formula 

      let x := sort(Chronos.'Date + Time');
last(x)

      and then for my Actual Day amount I use this:

      days('Last Activity Date', today())
Login to reply

Content aside

  • Status Answered
  • 8 mths agoLast active
  • 2Replies
  • 59Views
  • 2 Following