I have an audit log table which records various user actions. Records are added against various categories.
This is an example table.
RecordID Log CustomerID PrimaryKey Category Date Author isLast
1 note 1 1 21 Catch up 24/12/18 ..... 1
2 note 2 1 21 Catch up 23/12/18 .....
3 note 3 1 22 Create 21/12/18 ......
4 note 4 2 23 Create 21/12/18 ........
5 note 5 2 23 Delete 20/12/18 ......
What I want to do is show a portal. The portal will be filtered by log category and it will only show the "Catch up" notes for all customers.
I have a field called "isLast". I want to achieve is to have 1 or 0 in this field. And i want to achieve this by calculation.
The portal will ALSO be filtered by ONLY showing the last "Catch up" record for a customer. So in log tables, I might have number of records that are for 1 customer and have same Primary key and they might be different notes. So on the portal I only want to show the last catch up record.
So if we look at above example table; record 1 and 2 are 2 different notes for same customer and same primary key and same category "Catch up"; how ever I only want portal to show me the record 1 because it is the LAST record for that category, customer and primary key.
I think if I can have calculation in "isLast" field to work out if this is last "catch record" auto populate 1. Than we filter portal by "Catch up " category and "isLast" equal 1.
My question is how I can achieve the calculation to put 1 if the records is last in table for the category.
and is their any better way to achieve this.