Auto-Enter Based on Date Comparison

[Jud420]

Hey everybody, first post here. I'm brand new to scripting in Filemaker, as I've been mostly using its xml web publishing feature for Flash backend communications.

What Im trying to figure is how to make a script that does the following:

There is a table called 'Sessions' that contains the records to be used. There is a field called 'sesID' that contains a unique identifier for each record. There is also a field called 'criID', which is a 8 digit number assigned to each of my clients. When each record is created, there is a timestamp field that auto-enters on creation.

This database is designed to track when users log into our system (a piece of software automatically creates a new record in this database each time a user logs in).

I would like to make a new field, called sessionNumber, that tracks how many times the user has logged in. However, I need to make a script that runs through all of the previous related records (related to the criID), and compare the time stamps. If the times between two logins are less than 6 hours apart, the session count shouldn't increase.

SO, I think I need a variable to hold the session numbers to count up, a loop function that cycles through all records related to that criID and compares the time stamp in that record with the one before, and if the time difference is over 6 hours, to add 1 to the session number variable, then finally auto-enter that amount into the sessionNumber field when a record is created.

Can anybody help to point me in the right direction?

Thanks!

Edited October 29, 2008 by Guest

[Fitch]

Subtract one timestamp from another to get the time difference in seconds. One hour = 3600 sec, 6 hrs = 21,600 sec.

If you store the last login time and session number in a client table, you can compare the time to the current session upon login, and if the time difference is greater than 21600, update the client timestamp and session number.

Edited October 29, 2008 by Guest
Welcome to the forums!

[Jud420]

That will work to implement from now on, but i have 3000+ records already in the database that I need to parse through, and figure out the current session #. Also, I am trying to have that number calculated each time a new session is started, because I need to retroactively go back sometimes and remove sessions, which should decrease their current session number when they log in.

So, i need a way of making it a calculation each time a record is created.

[Raybaudi]

I think that a calculation along this may work:

Case(

Max ( related::timestamp ) + 21600 ≤ Get ( CurrentTimeStamp ) ; SessionNumber + 1 ;

SessionNumber

)

that is the same as:

SessionNumber + ( Max ( related::timestamp ) + 21600 ≤ Get ( CurrentTimeStamp ) )

[comment]

These two requirements are self-conflicting:

I need to retroactively go back sometimes and remove sessions, which should decrease their current session number when they log in.

...

i need a way of making it a calculation each time a record is created.

If the session number is calculated and stored at record creation, it will NOT automatically decrease when previous sessions are deleted.

You have to make a decision between an unstored calculation, calculating the session number dynamically as needed (and getting progressively slower as the number of records increases), or a stored field that needs to be updated by a script every time a record is deleted.