getNthRecord with related tables

[Nomad Suit]

I noticed that GetNthRecord, when referencing a related table, "returns the Nth record of the related set (relative to the current record), regardless of how the related table (or portal) is sorted" - as defined in the Filemaker Help.

Is there any way to make a related table function get the nth record based on how it's sorted? (i.e., as if it were referring to the current table?).

Thanks

[comment]

I'm afraid the help is (once again!) very poorly worded. In fact, GetNthRecord, when referencing a related field, returns the value from the Nth record of the related set - based on the sort order of the relationship.

[cfoster]

I'm using FileMaker 8.5 and find that GetNthRecord does NOT return the value from the Nth record of the related set based on the sort order of the relationship. But I really need it to! Is this something that has changed in FileMaker 9, or is there a workaround I can use to return the values in the sort order I need? Thanks!

[Fenton]

It sure seems to for me. FileMaker 8.5, Mac (but that shouldn't matter).

[comment]

There's not much that can be replied to that, other than "yes it does". How about some facts, like a file, or a description of how you arrived at your conclusion?

[cfoster]

Actually I came up with a work-around to do what I needed to do, but I do appreciate the feedback!

FYI, here's what I was trying to do ...

I have three tables - Departments, Staff, Staff2Dept. Staff2Dept links the other two with DeptID and StaffID, plus a Sort Order field that I used to specify sort order in the Departments/Staff2Dept relationship and the Staff2Dept/Staff relationship. Then I was populating a repeating field in Departments with the following code:

Let (

[

f = Extend ( Staff::Staff Name Entry);

g = GetNthRecord ( f ; Get (CalculationRepetitionNumber ))

];

If (g<>"?" ; g; "")

)

The sort order was not recognized, it grabbed records in creation order instead.

Now I'm running a script that copies staff data to a new table before populating the repeating field, so that the original sort order becomes the new table's creation order.

[mr_vodka]

:idunno:

[comment]

You didn't say there were TWO relationships involved here. This can be indeed somewhat problematic.

But first thing: GetNthRecord() DOES follow the same order as the relationship. You can see this quite easily by putting a portal to Staff onto a layout of Departments. Do NOT specify a sort order for the portal, and you will see that the order of records in the portal is exactly the same as the one you would get using GetNthRecord() or List().

So the real question is: what is the "sort order of the relationship" when there are multiple relationships between the two TO's involved? This has been discussed here:

http://www.fmforums.com/forum/showtopic.php?tid/150327/

If I had to summarize the various findings into a rule, I would say that:

The sort order of the relationship is ONLY the sort order specified for the target TO. Any sort orders specified for the intervening or subsequent TO's are ignored and replaced by creation order.

This is just my attempt at formulating a rule - it needs to be tested extensively in a number of different scenarios.

This behavior is rather different than what one would instinctively expect. Perhaps it could even be called a bug - though it does make sense in certain situations.

If you're looking for a workaround, I would suggest a calculation field in the join table =

List ( Staff::Staff Name Entry )

Then in the Departments table you can use List ( Extend ( Jointable::cList ) ) as the source for your repeating field.

[cfoster]

Your workaround makes so much more sense than mine, thanks!

[Agnès]

Hello,

Perhaps an other thing, may be this example can help (or not), I am using objects with the portal to keep sorting but it requires the portal on the layout (portal hidden or not, I do not really know what is done after to your list)

GetLayoutObjectAttribute ( "Object" ; "Content" ; 1 ; n ) Keep the sort what you see on the layout.

I am not sure the repeating field is good, may be a Custom Function recursive (or not ) is better or List (), of course, as stated Michael, but I wanted to propose objects also

Agnès

TestSort.fp7.zip

[comment]

Your workaround makes so much more sense

But now I am puzzled: when I made that suggestion, I was still thinking of the situation discussed in the thread I linked to i.e. Parent -< Child -< Grandchild. But you've described a completely different structure: two parent tables linked through a join table. My suggestion makes very little sense in such setup.

If you want to sort the 'other parent' records by some attribute of the join table, that's an entirely different matter. As there can be several joining records between any two parent records, this cannot be solved by tunneling data from the join table to the 'other parent' table. But de-duping the join table using the "Ugo method" might work.

[cfoster]

Actually it seems to be working. The data I want my Departments table to access is stored in Staff, while the sort order is determined by a value in Staff2Dept. By adding a calculation field to Staff2Dept that essentially mirrors the data I want in Staff, I'm able to basically ignore the Staff table and extend the field from Staff2Dept, and as a result there's only one relationship involved when I use GetNthRecord.

If you see a flaw with this please let me know. Even though I tested it out a bit, I'd hate for the client to discover an area where the data isn't sorted properly. Thanks!

[comment]

It might work in your case, because it's (probably) a special case. I don't think you have the same staff assigned to same department twice, do you? But it wouldn't work in a typical many-to-many, such as Invoices -< LineItems >- Products. At least not without some additional measures to eliminate duplicate product values in the produced list. OTOH, if you have someone who was promoted within their department (i.e. a new join record with the same parents), you WILL see the same problem.

In any case, there's no need to use List ( Staff::Staff Name Entry ) in the join table, since each join record has only one staff - so just plain Staff::Staff Name Entry should suffice.