Last Record

[eric333]

Hi Everyone,

I am working with an access control system. The system logs each time a staff member uses a card to access a door. The cards are stored in a Card table and the transactions are stored in the Transaction table.

The Card has an internal ID for the table and an external ID for the number that actually appears on the card. So, for the card that is printed with number 007250, the internal ID is 100. That intenral ID periodically changes, but is never duplicated. So, the internal ID for card 007250 could be 100, 101, 102, etc.

The Transaction table stores the data based on the internal card ID number.

I am attempting to identify the last time a card was used (to see if we have inactive cards). If there were not various internal IDs for each card, I could just use something along the lines of Maximum. But, that's not working for me.

Any ideas?

 

[lbass]

Is there a date field in the transaction table? How can YOU tell the last time a card was used? If you don't have a date field, does it have to do with how high the internal number is? Are the internal numbers unique, i.e., NOT used for other external IDs? I think you should show us some sample data that references both internal and external IDs for a couple of people.

-LB

 

[eric333]

I apologize for the delay, and for not being more clear in my original post.

Yes, there is a date field in the transaction table. The last time a card was used would be the highest/latest date of activity in the Transactions table for that specific card.

There are numbers associated with each transaction, but they are not unique, they reset approximately every week.

Here are some samples of the data:

CardID Card# Name
1143 7250 Smith John
608 7250 Smith John

Trans# Date/Time CardID
35809 4/28/2010 14:54 1143
36080 4/28/2010 15:19 1143
36167 4/28/2010 15:26 1143
36391 4/28/2010 15:54 1143
37278 4/28/2010 17:04 1143
37283 4/28/2010 17:05 1143
37843 4/28/2010 18:01 1143
37867 4/28/2010 18:02 1143

So, I need to identify the highest number CardID for each employee and the last transaction for that CardID.

 

[lbass]

Insert a group on card# (if that is static per employee) and a second group on {table.cardID}, and then go to report->selection formula->GROUP and enter:

{table.cardID} = maximum({table.cardID},{table.card#}) and
{table.datetime} = maximum({table.datetime},{table.cardID})

-LB

 

[eric333]

Thank you very much.

For the suggested formula, where CardID is referenced, do I replace table with the Card table or the Transaction table? The column exists in both tables.

 

[eric333]

I believe I have it figured out. The formula I am using is:
{TransactionLog.CardID} = maximum({Card.ID},{Card.CardNumber}) and
{TransactionLog.time} = maximum({TransactionLog.time},{Card.ID})

Now my problem is I only want data for cards that are active. The card table has a field for start date and ending date. I seem to be having difficulty selecting only cards that do not have an ending date. The field is formatted as a date/time field.

 

[eric333]

And, one last twist to this debacle, I need the option of including only records that have valid cards and where the card was used more than x days ago.

 

[lbass]

You should use a RECORD selection formula (report->selection formula->RECORD) like this:

(
isnull({table.enddate}) or
{table.enddate} = date(0,0,0)
)

Then create a number parameter {?DaysSinceLastUse}. Then add the following to your GROUP selection formula:

and
maximum({TransactionLog.time},{Card.ID}) < currentdate-{?DaysSinceLastUse}

-LB

 

[eric333]

AS always, your help is greatly appreciated. Thanks!

The parameter selection worked perfectly!

The record selection for the card end date did not work. The formula I used was:

(isnull({Card.EndingDate}) or{Card.EndingDate} = date(0,0,0))

If I have a report listing cards only (no transactions) with no other tables joined, the selection works perfectly.

 

[lbass]

You need to explain in what way it did not work. What unexpected results did you get? You probably should show the entire record selection formula here also.

-LB

 

[eric333]

The Group selection formula is as follows:

Group
{TransactionLog.CardID} = maximum({Card.ID},{Card.CardNumber}) and
{TransactionLog.time} = maximum({TransactionLog.time},{Card.ID}) and
maximum({TransactionLog.time},{Card.ID}) < currentdate-500

Note: I set results for activity that has not occurred for more than 500 days.

The record selection formulas I tried are as follows:

Note: Due to the large volume of data, I used the last name of Smith to limit the number of results.

With the record selection formula set to:
{Card.LastName} = "Smith"

The results are:
Card# Card End Date Name Last Transaction
00000000000000006628 7/9/2008 Smith, John 7/9/2008 12:40:00 PM
00000000000000007241 9/3/2007 Smith, James 9/1/2007 2:58:00 PM
00000000000000007538 Smith, David 9/10/2007 3:47:00 PM
00000000000000008257 9/8/2008 Smith, Ron 9/8/2008 11:45:00 AM


With the record selection formula set to:
{Card.LastName} = "Smith" and
(isnull({Card.EndingDate}) or{Card.EndingDate} = date(0,0,0))

The results are:
Card# Card End Date Name Last Transaction
00000000000000006628 Smith, John 7/9/2008 10:10:00 AM
00000000000000007241 Smith, James 8/30/2007 1:15:00 AM
00000000000000007538 Smith, David 9/10/2007 3:47:00 PM
00000000000000008257 Smith, Ron 9/8/2008 5:55:00 AM

 

[lbass]

Please comment out the group selection formula and show what the Smith results look like when you only use name = Smith. It looks like you are saying that the end date only appears on the most recent record if it exists.

-LB

 

[eric333]

Sorry for the delayed response. And, your help thus far has been greatly appreciated. I'd like to take a slightly different approach. I want to start with just finding out the active cards in the database. So, I am working with just one table.

To refresh the data structure, here are the fields I am working with:

Employee Name
Card ID
ID Number
Card Ending Date

The application assigns an ID Number to the card. The ID Number changes periodically (the manufacturer cannot clearly explain why this occurs) but the ID number never duplicates and is always incremented to a higher value. So, a Card Number can have multiple ID Numbers. Now, here's where it gets a little complicated - the Card Ending Date.

The Card Ending Date applies to the Card ID, not the ID Number. So, if an employee has one card, with multiple ID Numbers that have been generated over time, and the card has been expired (i.e., the employee no longer works here), then only one of the ID Numbers, the last one, will have an Ending Date. For example:

Card# ID Number Card End Date Name
00000000006628 1315 Smith, John
00000000006628 3315 Smith, John
00000000006628 4123 6/12/2010 Smith, John

In the above example, the employee has only one card, the system has genereated three ID Numbers, and that card has been expired.

Another example:

Card# ID Number Card End Date Name
00000000006628 1315 Smith, John
00000000006628 3315 Smith, John
00000000006628 4123 Smith, John

In the above example, the employee has only one card, the system has genereated three ID Numbers, and that card is still active.

My goal (for now), get a listing of call cards in the system that are active (i.e., do not have an End Date) but based upon the Card#, not the ID Number.

 

[lbass]

It looks like active cards would be identified by using a group selection formula (instead of record selection) like this:

(
isnull(maximum({table.cardendingdate},{table.card#})) or
maximum({table.cardendingdate},{table.card#}) = date(0,0,0)
)

If you want to meet the previous requirements for active employees only, then you might be better off creating a SQL expression {%maxenddt};

(
select max(`endingdate`)
from table A
where A.`card#` = table.`card#`
)

Then use go to report->selecton formula->GROUP and enter:

(
isnull({%maxenddt}) or
{%maxenddt} = date(0,0,0)
)

This would then give you the correct dataset as a basis for your group selection.

-LB

 

[eric333]

Thank you!

The active card formula worked perfetly!

I am not at all familiar with SQL Expressions. Attempting to use the syntax you recommend, my exact SQL Expression is:

(
select max("Card"."EndingDate")
from table A
where A."CardNumber" = "Card"."CardNumber"
)

But, it fails when being checked.

 

[lbass]

(
select max("EndingDate")
from "Card" A
where A."CardNumber" = "Card"."CardNumber"
)

In versions 9- XI, you can't use the table name within the function. I believe this is corrected in CR2008.

-LB

 

[eric333]

Thanks again!

The SQL Expression was verified (valid).

However, the recommendation to place (isnull({%maxenddt}) or{%maxenddt} = date(0,0,0)) in the Group formula is problematic, because I already have formula that finds the active cards in the Group Selection Formula.

 

[lbass]

No, that was a typo on my part--sorry. The point of using the SQL expression is so that you can test the maximum date in the record selection formula. The formula is supposed to go in report->selection formula->RECORD--instead of the group selection clause. So the record selection would read:

(
isnull({%maxenddt}) or
{%maxenddt} = date(0,0,0)
)

Then if you still have the other requirements, you would use the following group selection:

{TransactionLog.CardID} = maximum({Card.ID},{Card.CardNumber}) and
{TransactionLog.time} = maximum({TransactionLog.time},{Card.ID})

-LB

 

[eric333]

Okay. Everything is all set. All of the formulas and expressions are in place, all checkout okay.

So, here's what will likely seem a silly question at this point...

How do I actually determine the last date the card was used? I tried inserting the Date field from the Transaction Log ({TransactionLog.time}) into the Details section and the Group Header section, but that did not work.

This should be the simplest part of this puzzle, the part that I should be able to do without assistance. But...

 

[lbass]

Only the most recent date should be appearing in the detail row.

-LB