Find the value of the last record of one talbe from another table.

[Tpaairman]

My file has a couple tables that have almost nothing to do with each other, but I have a relation between the two because of what I'm trying to accomplish.  It's just a key field on both sides.  Table A is a list, Table B is more of just a viewer, with portals to a few tables, and does some calculations, but only one record.

I have one field in Table B, where I need the contents to be the value of a given field on the last record of Table A.  The field in Table B can be a calculation, but how do I tell it the calculation equals the contents of Field X on Table A's last record?

[comment]

Here are a few options:

1. Sort the relationship in reverse order; then place the field from the related table onto the layout.

2. Place the field from the related table inside a one-row portal and sort the portal.

These solutions are suitable if you only need to display the value. If you need to actually get it, you could:

3. Define a calculation using the Last() function.

Note that this returns the last non-blank value in the related set. If the given field is empty in the last record, the function will go back to earlier records. If that's a problem, you can:

4. Define a calculation field as = GetNthRecord ( RelatedTable::YourField ; Count ( RelatedTable::Matchfield ) )

[Tpaairman]

I've tried a calculation using the Last function, and it's not returning anything.

What I have in there is Last (Table A :: FieldX)

If it makes a difference, the field I'm trying to using in Table A is a running total summary.

[comment]

19 minutes ago, Tpaairman said:

I've tried a calculation using the Last function, and it's not returning anything.

It works for me - see the attached demo.

 

19 minutes ago, Tpaairman said:

If it makes a difference, the field I'm trying to using in Table A is a running total summary.

It makes a difference in the sense that you could simply sum the related values in the parent table (also shown in the attached file). And also that the suggestions based on reverse sorting will not get the expected results.

 

 

LastVal.fmp12

Edited September 6, 2023 by comment

[Tpaairman]

I found the problem.  I know FMP has a key field set up when you create a new table, but I still always just do my own.  Old habit.  Anyway, the key field on Table A was set to index.  I set it to be a global value, and suddenly it works.  Not really sure why that would be an issue since it's a calculation that always is the same anyway, but it's working now.