AnsweredAssumed Answered

How to identify the last record in table using calculation

Question asked by muhammad.ikram@contedia.com 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 

Outcomes