0

Calculating Date Difference Between Current and Previous Record

I'm trying to create some code to work for a table's On Update expression that looks up the most recent record (in terms of record ID), that was created before the current record, and calculated the number of days between dates in the two records. The maxId field doesn't seem to get populated. Anyone have ideas as to where I went wrong?

let i := this.Id;
let dt := this.'Mow Date';
let maxId := max((select 'Lawn Mowing Log' where Id < i).Id);
let r := record('Lawn Mowing Log',maxId);
'Days Old' := dt - r.'Mow Date';

3 replies

null
    • Mconneen
    • 4 yrs ago
    • Reported - view

    @ChuckHoffman

    I do not fully understand the use case.  I am not sure why you would use the Table's On Update... In that case.. the trigger fires for each field that you update on the row for that table.   That said.. Here is some code that I think does the logic you are looking for..  I simply put it in a formula field on the row.. but you can also put it in the Trigger After Update on the FIELD 'Mow Date' .. and have it populate another field. 

    I created a simple table.. I put a view on the row just so you can see all the rows.. 

    MowLog

    The code.. 

    TheCode

    Hope this gets you pointed in the proper direction. 

    Happy Ninox-ing. 

    • chuckhoffman
    • 4 yrs ago
    • Reported - view

    I don't want to use Id - 1, because the code will fail, if that record has been deleted. That's why I was trying the max function, to find the previous record, even if not all ID's exist. Can you help with that?

    • Mconneen
    • 4 yrs ago
    • Reported - view

    Ahh .. Good point.. Try last. 

    TheCode

Login to reply

Content aside

  • 4 yrs agoLast active
  • 3Replies
  • 1059Views