Diplay Field From Sorted Portal

[SedonaMTB]

This has got to be easy.

I want to display the content of a field from the first SORTED portal row in the parent file. I have created a calculation field (in parent file) to display the field but the calculation ignores the sort and displays the first portal row entered.

How can I get the calculation to look at the first portal row according to the sort?

Thanks!

[Ender]

Calculations don't know anything about how portals are sorted (how could they?). A script could, since a script can interact with the layout.

But a better method is to sort the relationship instead, then use relationship::field to get the first related field value, or last(relationship::field) to get the last related field value.

[comment]

If it's only for display, you can put the field in another portal of one row only, sorted in the same order.

[SedonaMTB]

Right sorry I wasn't more clear. The relationship and the portal are sorted and yes I did come up with a script that would update it just fine but I want to do it through a calculation. Actually the field is not displayed but another calculation field looks at it to determine if a membership is current or not.

Sounds like a script might be the only way to do it.

[comment]

a script might be the only way to do it

I don't think so. It would be much easier if you were more forthcoming with details, though. For example, you could define another relationship to the same table, with a different sort order. Or, if the field used for sorting is the same field that supplies the value, you could refer to it simply as Min ( Related::Field ).

[SedonaMTB]

Well, the essential issue is that FM will always look at the first related recorded entered in the portal. Therefore sorting has no effect whatsoever whether it's in the portal used by the user or another portal sorted by record number, serial number, etc.

Just for details sake this is a membership db:

Parent file is Members and the child file is membership history. The field in the portal that I am trying to capture is latest year of membership which will always be the last entry in the portal. But FM can only look at the first record entered in the portal row. It also turns out that sorting the relationship in define relationships does not sort the related records in a fundamental way except for display in the layout.

So rather than belabor the issue and suck up the customers time. I'm just going to add an "Update Membership" button that the user has to click to look-up the value. That works fine.

I'd still like to see if someone could comeup with a way to calculate it though!

[comment]

I am afraid you are mistaken in your basic assumption:

FM will always look at the first related recorded entered in the portal.

No, it will look at the first related record by the sort order of the relationship. A portal can have a different sort order, overriding the relationship's sort order. You can sort the relationship one way (for calculations) and a portal in another (for display).

In your case, you could sort the relationship by Date, descending. Then History::Date would refer to the latest date in the History table. Or you could get the latest date directly by Max ( History::Date ) - whatever the relationship's sort order might be.

[SedonaMTB]

This is not true in the tests that I have done. I cannot prove your hypothesis.

Is there anyone, Johnmark O., that can answer to this?

[comment]

What can I say, except devise a better test. You don't seem to attach much importance to what I say anyway, so I think I'm done here.

[David Jondreau]

This is not true in the tests that I have done. I cannot prove your hypothesis.

It's not a hypothesis, it's expected behavior.

[Stuart Taylor]

You would be well advised listen to Comment you are not understanding what he is saying.

Portals can be sorted in 2 ways...

1. When the portal is double clicked you can set a sort order, this will not effect your calculation result.

2. When you create the relationship between the 2 tables there is a box in between the line linking the table occurances. Double click it and you will get a window with greater control. One option is sort, If you sort here then the sort order will be reflected in the calculation and you will also not have to specify the sort everytime you use the table in a portal.

[SedonaMTB]

If you read my response above I have sorted the relationship in the ER diagram using the little box along the line. It does not work. FM still displays the first record entered into the protal. Apparently FM is looking at the record ID# of the rows in the portal.

Yes, I agree that I would have expected this to work but it doesn't.

[bcooney]

This is too weird, sorting the relationship is definitely the way to control which record the parent TO sees in the child TO. And there should be no need for a calculation field to display the last or first related record since you'd just put the related record's fields on the parent layout (not in a portal). On your ER you have a TO for Parent (Members) and a TO for child (Membership). Since you are sure that the last record created in Membership will give you their latest membership, you should sort the relationship by __kP_MembershipID descending. This will let you put the fields from Membership on the layout based on Members TO.

I wouldn't rely on the last record entered is the last membership assumption, however. Why not have a membership_yr field (number) and sort the relationship desc on that field. I would also suggest creating a new relationship from Members to Membership that is exclusively used for this sort.

[LaRetta]

The answer is simple ... there is something small which is not being considered. What you have been told is consistent, standard, FileMaker behavior. It is not a bug; it is simply something overlooked. If you are sorting on Membership date, is it true date field? Or do you have that puppy as a text field? If text, it won't sort properly.

We'll be happy to help you pinpoint it. But saying "... I have sorted the relationship in the ER diagram using the little box along the line. It does not work" doesn't tell us a thing! What field name and data type are you sorting on? If it is a calculation in your child table, what is the calculation? As Comment indicated, be more forthcoming with information. Or simply post your file. Otherwise, we can guess all week. :wink2:

[Oldfogey]

Back to the main question, why worry about a portal row? I have precisely the same requirement in a DB I'm working on. I use Max(History::Date) to get the last payment. Portals are primarily visual aids and, IMHO, are rarely the best bet for calculations.