Using a ~DateTime field in a Transaction list - to work out an elapsed even

[Former Member]

Hi all. I do hope you Gurus can help me.

the situation is that we have an event table, with event codes...and event times.

say code 100, occurs for a record on Tuesday at 14:00:00...then the next event we are looking to measure the time between event 100...lets call this code 200...occurs on Tuesday at 16:00:00....i need to extract the elapsed time between the time of event 100 and event 200.

In other words the elapsed time of 2:00:00 hours.........

I have created a time event based on code 100 happening...then a time event based on 200 happening...But they cannot subtract 200 from 100 time, to give me the 2:00:00 elapsed hours........

Can any one give me, guide me on the route to solve this one.

[Former Member · Accepted solution]

Hi, These are getting very helpful, but I am missing a bit of knowledge, and I am not a Basic syntax user....

So this, from my thoughts/testing might help you to help me!!

Formulae for {@R_BothTimes}

whilereadingrecords;

if {ticket_events.te_event_code} = 100 then {ticket_events.te_date}

else

if{ticket_events.te_event_code} = 147 then {ticket_events.te_date}

This looks at the two event codes, 100 and 147...then reads in the singel te_event_code time...and places this onto the transaction line...

I then am looking at.....this sort of code...

if previous ({ticket_events.te_event_code}) = 100 then {@R_BothTimes}

//this coudl look back and get the time 100 started, so i can then look at the eleapsed time between 100 and 147.....

but it is not working!!!!! Your help is trully apprecaiated.

[Former Member · Accepted solution]

{@R_BothTimes is not looking at the prior record. It's returning the date from the current record. The formula should be

if previous ({ticket_events.te_event_code}) = 100 then Previous({ticket_events.te_date})

in order to get the date of the 100 record. This would then be DateDiff()ed with the current record's date to get the time between records. Note also that this would only be good for the record after the code 100 record. If you had three records for the event, say 100, 147, and 900, this formula will not give the date of the 100 record for the 900 record. For that you need to use global variables, as my original post shows.

HTH,

Carl