Display value from second portal row

[oa2022]

I’m trying to create a field calculation to display the date found in the second row of a portal. This will be used in reports and other layouts to show the precious date something was done. I already can display the current date (first row). The portal is a running history and has many entries for each item, but want to just show the previous date in the calculation field. 
 

I’d like to do this at a calculation, but lost on how to do this. Can’t find any answers online and the few ways I’ve tried don’t work. 
 

is this possible?

Edited May 30, 2023 by oa2022

[comment]

You can use:

GetNthRecord ( RelatedTable::Datefield ; 2 )

to return the date from the 2nd record in the related table, in the sort order of the relationship. If your portal isn't filtered or sorted on its own, this will also be "the date found in the second row of a portal".

As a rule, calculations work at the data level and don't go through layout objects such as portals. If you really need to get the data from the portal row, then you will need to give the field an object name first. Then you will be able to get the value using:

GetLayoutObjectAttribute ( "YourFieldObjectName" ; "content" ; 1 ; 2 )

But this is not only more complicated, it will also work only on the layout which has the portal with the named field object in it.

 

[oa2022]

Thank you! That worked when displaying on a separate table/layout. Is there a way to also show this on the current table in a layout?  It just shows one date for all items if I do it that way. 

Edited May 30, 2023 by oa2022

[comment]

I am afraid I got lost in your explanation and I am not able to understand which table is which.

Does the attached demo work as you expect? 

2ndDate.fmp12

Do note that the calculation field is unstored.

[oa2022]

Yes, thank you!  Works great in parent table/layout!

Follow up question. If in a layout with the child, can I also have a calculation like this for each parent ID?

so if I have a report or layout that is in the child layout, it would be nice to have a calculation there also to show that “previous” date.   

[comment]

9 minutes ago, oa2022 said:

if I have a report or layout that is in the child layout, it would be nice to have a calculation there also to show that “previous” date.   

But what exactly is that "previous” date? If it's the same "date found in the second row of a portal" (on the layout of the parent table), you can simply place the calculation field (defined in the parent table) on the child layout. If it's something else, then you need to explain in more detail.

 

[oa2022]

Good point.  It is perhaps nothing needed in the child. I was mostly thinking if we also needed to generate a report straight from the child table…but that shouldn’t be an issue since we have this field on parent table.  Thank you again!

[comment]

If you need this only in a report produced from the child table, then you could define the calculation field in the child table as:

GetNthRecord ( Date ; Get ( RecordNumber ) + 1 )

and place it in leading sub-summary by parent part. This would work the same way, provided that all child records of the parent are in the found set and sorted in chronological order.

But if you want to use it in both layouts, then the original suggestion is a better one.

 

 

[oa2022]

Have a quick follow up question. 

If I want to also do an If statement to show some text if there is no second portal row/additional values, can that be done somehow using what you provided before?

GetNthRecord ( RelatedTable::Datefield ; 2 )

1) what I would like to build is a calculation field that will show the days between the most current date and the previous (most recent) date.  Your explanation helped with that!

2) If there is only one date, I’d like to have some text display such as “new” or better yet showing the days elapsed between that date and the current system date   

3) And if there is no dates at all, something saying “no activity” or something like that. 
 

I just discovered I’d need 2 & 3 when I was testing this out on some records and it just displayed a bunch of numbers because it didn’t have any other date to compare to. 
 

Thank you very much!

Edited May 30, 2023 by oa2022

[comment]

You can use the Case() function to branch between the possible scenarios. Or perhaps even the Choose() function - say something like:

Choose ( 
Min ( 2 ; Count ( ChildTable::ParentID ) ) ;
// RESULT WHEN NO CHILD RECORDS EXIST:
"No activity" ;
// RESULT WHEN ONLY ONE CHILD RECORD EXISTS:
Get ( CurrentDate ) - ChildTable::Datefield ;
// RESULT WHEN TWO OR MORE CHILD RECORDS EXIST:
ChildTable::Datefield - GetNthRecord ( ChildTable::Datefield ; 2 )
)

(untested).

But this approach has a flaw: the result type must be text, even though the result is sometimes (perhaps most of the time) a number. This is acceptable if the field is used for display only, but can be problematic when used for further calculations. It might be better to return the numeric results only and use either conditionally hidden text or placeholder text for the message.

 

Edited May 31, 2023 by comment