Time calculations from imported event log

[JariV]

Hi,

I'm trying to calculate various elapsed times from data, which was imported as an event log from a vehicle data recording system.

Every event log row (= each record) consist of 5 fields: EventDate, EventTime, EventCode, EventDatabytes, EventData.

The 2 first fields are a timestamp for the event, EventCode tells the type of the event: 2(Trip Start), 3(Trip End), 4(Idle Over 5 Min), 2048(CardInserted), 2049(Card Removed)&some other (irrelevant here).

The event log is downloaded from a flash memory card, typically a single log consists of 300-500 rows (records).

I'm trying to figure out how to calculate the following from the event data:

- total working hrs (consecutive CardOut-CardIn (event codes 2048-2049, several pairs))

- total driving hrs (consecutive TripEnd-TripStart (event codes 3-2, several pairs))

- total Idle time (Idle Over 5 Min (event code 4, EventData in that record shows the time in mins)

I'm not very experienced with using scripting and functions, and I would really appreciate I someone could point me to the right direction as how to approach this.

Ideally the solution would process the data once when it is downloaded from the flash card, and store the results in different database for further use.

Also, ideas whether Filemaker is the right tool for this sort of data crunching are welcome - if necessary, it might also be possible to further refine the data in the event log at the other end.

Thanks in advance,

Jari

FileMaker Version: 6

Platform: Windows XP

EventLog.zip