AnsweredAssumed Answered

How to identify the last record in table using calculation

Question asked by on Dec 24, 2018
Latest reply on Dec 26, 2018 by philmodjunk

Hello Community,


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.


Thank you