Last function

[Chrism]

Hi,

I have a current field that is a calculation that displays the last data from a relationship (the relationship is sorted by the date of a particular field)

Is there a simple way to display the 2nd last, and 3rd last in the same way

 

Last ( Certs::result )

I think Max works in the same way for this purpose and displays the latest related record

Max ( Certs::result )

Thanks!

Edited November 2, 2022 by Chrism

[comment]

You can retrieve the next to last value using:

GetNthRecord ( Certs::result ; Count ( Certs::MatchField ) - 1 )

But if you only need to display the last 3 values, you might simply use a 3-row portal sorted by date, descending.

 

 

[Chrism]

Thanks for the reply

Sorry what is Certs::MatchField in reference too?

And I need to do more than just view, so a portal with 3 latest records doesn't quite work for what I need

Edited November 2, 2022 by Chrism

[comment]

7 minutes ago, Chrism said:

Sorry what is Certs::MatchField in reference too?

The field used as the match field in the definition of the relationship between your current table and the Certs table.

 

41 minutes ago, Chrism said:

I think Max works in the same way for this purpose

That depends on what exactly is the nature of the value in the  Certs::result field. If it's something that always increases relative to previous values (e.g. a serial number), then in your given sort order Max() will return the same value as Last(). But that's just a coincidence, they are not the same.

 

[Chrism]

Thanks understood! And its sort of working if I switch the sorting of the relationship to descending and then it works but shows the oldest related record data

But my relationship is sorted by a date field in Ascending order, but I need to show the most recent 3 related records by the latest date

[comment]

6 minutes ago, Chrism said:

its sort of working if I switch the sorting of the relationship to descending

Are you referring to the portal? You can leave the relationship sorted as it is, and sort the portal in descending order.

 

[Chrism]

2 minutes ago, comment said:

Are you referring to the portal? You can leave the relationship sorted as it is, and sort the portal in descending order.

 

Do I don't have a portal for this

I have a 3 fields sat in the contacts table, one with my original Last calc - 'Last ( Certs::result )' that correctly displays the last result from the related records and 2 others  that have the calculations - GetNthRecord ( Certs::result ; Count ( Certs::MatchField ) - 1 ) and GetNthRecord ( Certs::result ; Count ( Certs::MatchField ) - 2 )  

And in this case most of the GetNthRecord ( Certs::result ; Count ( Certs::MatchField ) - 1 ) calculations display a ?, unless I reverse the relationship sort order which then displays correct data but oldest first

[Chrism]

I'm happy to reverse the relationship order, but need an equivalent calc to my Last ( Certs::result ) that will work with it sorted descending

But need to display most recent related record data based on date

[comment]

I am afraid we are not on the same page.

First, here's a demo file showing my original suggestions - both the calculation fields and the portal. 

MostRecent3.fmp12

Do note that the relationship is sorted by Date, ascending, but the portal still shows the 3 most recent values, because it has its own sort order.

Now, if you are able to change the relationship's sort order to descending, then both solutions become simpler - as shown in this other demo:

MostRecent3+.fmp12

Note that the calculation formulas are simpler and the portal does not need its own sort order.

In either case, if you only need this for display, then the portal is a better solution than adding 3 calculation fields.

 

[Chrism]

Effectively it does exactly what I want it to do if I have the relationship sorted by date in Descending order, but its displaying info from the oldest 3 records not the newest.

So in theory having it sorted in Ascending order it should be right, but it is displaying 99% ? for the new calculation fields.

No its not for display only, I need 3 x separate fields

Ok thanks for the demo files

I have put into Descending  order and used the GetNthRecord ( Child::Value ; 2 ) calc and works perfectly and just used the Child::Value fore the latest record!

[comment]

 I am afraid you have lost me at this point. Have you looked at my demo files? The behavior you describe is not what I see. The only time the calculation fields will display an error is if there are less than 3 related records. If necessary, this too could be avoided by adding a condition.

 

 

[Chrism]

Its all working thanks! I looked at the demo files

 

 I have put into Descending  order and used the GetNthRecord ( Child::Value ; 2 ) calc and works perfectly and just used the Child::Value fore the latest record!