How to setup a self-join relationship to show users modified records for the past 30 days?

I am trying to setup a portal in the footer of a list view layout. In the portal it would show records that the user had modified in the past 30 days. I am surprised I have not ran into this issue before but thinking back this is the first time I have tried to do this.

At first I figured this would be really easy to do. I made 2 new global fields in the Main table. One for current user and one for a date 30 days ago. I than made another Main table occurrence tying my modified by and modification date fields two the newly created global fields.

But instead of only showing records created by the user and withing the last 30 days it is showing all the records from the Main table. What is the best way to solve this issue?

Here's in short how I would set this up:

Main table (TO_Main) has 2 global fields:

  • g_UserKey
  • g_DateMin

Related table (TO_Last30Days) has 2 fields:

  • UserKey
  • LastModificationDate

TOs for the layout context:

A script that runs each time the layout is open/switched sets

  • g_UserKey to the key that ties records in TO_Last30Days to the current user
  • g_DateMin to Get (Current Date) - 30

TO_Main and TO_Last30Days are related by 2 predicates:

  • g_UserKey = UserKey
  • g_DateMin <= LastModificationDate

A portal based on TO_Last30Days will show all records with a modification date of 30 days or less.


By 2 predicates :slight_smile:

1 Like

Oops, thanks @bdbd!

Thanks for the detailed example. It looks like I was not crazy than. The way I was trying to make it just will not work?

Haven't seen your file but it looks like the date predicate needed to be adjusted.

I am not sure what you are saying here.

After playing around with your suggestion I instead made a script that would populate a related table. It is simple to setup and has less overhead on the solution.

The only down side is if another user makes a change to the record it will drop form the users list of modified records. In this solution that is not very likely so it still works.