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
-
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.
Content aside
- Status Answered
- 1 yr agoLast active
- 2Replies
- 96Views
-
2
Following