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

Questions
1

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 within the last 30 days it is showing all the records from the Main table. What is the best way to solve this issue?

2

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:
TO_Main--------TO_Last30Days

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.

2 Likes
3

By 2 predicates :slight_smile:

1 Like
4

Oops, thanks @bdbd!

5

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?

6

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

7

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.

8

What Torsten said.... with a couple minor suggested deviations .......

Set the globals for the user account and currentDateMinus30 in the login startup script.

Using this path, you reduce the error potential of forgetting to set this in some script or new layout entry as the fields are ALWAYS set for the current day and user. The only place this would be "brittle" is someone logged in over Midnight, in which case, Torsten's approach would be better.

9

Not sure if anyone is still watching this thread.
But, my question is why use globals in this case at all?
They could be (explicitly) unstored calc fields in Main:
zc_UserKey, defined as: Get ( AccountName )
and
zc_DateMin, defined as: Get ( CurrentDate ) - 30
respectively. Relationships work with unstored calls on the LOCAL side of the relationship, which is what you are doing. Then, no need to make sure they get set. They are always live-evaluated when used. But, note that when you include a Get function in a field definition and you want the calc to always display the current result, you need to explicitly set the calculation to "Do not store" in the Storage Options. Otherwise, it will just keep whatever the value was when you created/saved the calc field definition.

1 Like
10

I'd say for performance reasons. While very convenient, unstored calc fields come with a high computing cost. Not relevant for short lists, but longer lists will be impacted by longer load times and sluggish scrolling. Global fields do not have that inconvenience.