Skip to main content
B
blackburncollege
Participant
March 23, 2018
Question

Process Rows if condition is met

  • March 23, 2018
  • 1 reply
  • 896 views

Hi all,

I have been asked to develop something in CF. I am new to the world of CF development and trying to learn as best I can.

I am using CF8 as that is all that is available in my work environment.

I have posted my requested here in the hope someone can assist and give some pointers.

We have a system which records login and logouts for computers on our campus. I need to work out how long each computer has been used for and we can't change what the program logs as it is a third party tool used for managing the network.

I have got to a point where I can get the time calculations if a user logs in and logs out once a day or multiple times on different computers. I do this by creating two queries one with retrieves the logins and one which retrieves the logouts. I then do an if statement which matches the MacStudent and if the ID for the logout is greater than the ID of the login which works although it may be overkill and probably not best practice!

However my main issue is when a user logs in and out of the SAME computer multiple times in a a single day. I cant figure out a way of identifying which login relates to which logout programatically.

So for testing purposes say I have the following records retrieved:

I need to be able to loop through all the records and then calculate time between a login event and only the NEXT logout event.

So for the example recordset above - I would expect to calculate 25 mins use recorded. but at the moment i cant figure out how to ignore the rest of the logouts as when i do my loop it takes the first login and calculates the time against ALL the logouts, then moves to the second login event and calculates the time against ALL the logouts below that and it carries on until the loop finishes.

I hope that makes sense to someone and any help appreciated!

Thanks in advance!

This topic has been closed for replies.

1 reply

WolfShade
WolfShade
Legend
March 23, 2018

Hello, blackburncollege​, and welcome to the world of coding CF.  I hope you enjoy it as much as I and others here do.

I would like to mention that you don't need to do two separate queries and compare.  You can do this with one query.  As far as which record is login and which is logout, if your database is as per the image provided, then you're already registering the login / logout, you just need to sort by user then timestamp (I hope you didn't really name the column 'timestamp') as shown in the image.  This helps to assure that all odd-numbered rows will be login and all the even numbered rows will be logoff.

I recently learned a neat trick from BKBK using an array to calculate differences that might help.

V/r,

^ _ ^

B
blackburncollegeAuthor
Participant
March 23, 2018

Hi,

Thanks for your response, I am enjoying my time CF so far!

I did try the one query and sorting by User/Timestamp but it doesn't necessarily mean that odd numbers are logins and even numbers are logouts as a single user can login to multiple computers, so by doing  user/timestamp I can have several rows together showing as login.

I originally did it as two queries as i nested the loops like:

set totaltime used variable

loop login

     loop logout

          if login.macstudent eq logout macstudent and login.id LT logout.id

               do calculations between login.timestamp and logout.timestamp and append to totaltime used variable

          close if

     close logout loop

close login loop

like i mentioned, this approach works fine if you assume that a user logs into a computer once a day or logins to multiple computers in the same day as the macstudent variable is a combination of the computerid-loginid-dd-mm-yyyy.

My only issue is if the user logs in and out of the same computer many times in a day as the loop above will always tally up the time difference between all logout events taken place after the initial login. i need some way to identify the next logout event only.

hope that makes sense!

thanks again!

WolfShade
WolfShade
Legend
March 23, 2018

It does.  I'll have to give that some thought.

V/r,

^ _ ^

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices