AnsweredAssumed Answered

Filter portal for most recently created record by group

Question asked by brad.cannell on Sep 17, 2017
Latest reply on Sep 24, 2017 by brad.cannell

I have a portal on my "Clients" table. The related table contains the results of surveys that are updated over time. For each combination of client and category (a field in the related table), I only want the portal to display the most recently collected row.

 

I attached a trivial example to illustrate the issue I'm trying to address. I have two tables in this example (Related on ClientID):

  • Clients
  • Table 1 Get Summary Method

 

The Table 1 Get Summary Method table looks like this:

Screen Shot 2017-09-17 at 3.40.53 PM.png

Where:

  • MaxDate is a summary field = Maximum of Date
  • MaxDateGroup is a calculated field = GetSummary ( MaxDate ; ClientIDCategory )
  • ShowInPortal = If ( Date = MaxDateGroup ; 1 ; 0 )
  • The table is sorted on ClientIDCategory

 

Issue 1 that I'm stumped on:

ShowInPortal should equal 1 in row 3 (PKTable01 = 5), row 4 (PKTable01 = 6), and row 6 (PKTable01 = 4) in the table above. I'm not sure why FM is interpreting 1Red and 1Blue as the same category, or perhaps I'm just misunderstanding what the GetSummary function does.

 

The Clients table looks like this:

 

Screen Shot 2017-09-17 at 3.28.49 PM.png

Where:

  • The portal records are sorted on ClientIDCategory

 

Issue 2 that I'm stumped on:

I only want rows with a ShowInPortal value equal to 1 should appear in the portal. I tried creating a portal filter with the following formula: Table 1 Get Summary Method::ShowInPortal = 1. However, using that filter removes all row from the portal.

 

Any help is greatly appreciated.

Outcomes