Sorting by a Related Field

[Glenn Trewitt]

I have a secondary table that associates, among other things, a name with an ID, and a main table.

Users:

• ID
  
• Name
  

MainTable:

• UserID
  

And the obvious relationship: MainTable::UserID = Users::ID. It's a many-to-one mapping, with many MainTable records matching one User record.

I'd like to make a nice layout with subsummaries, but I ran across a problem that just seems too basic to be anything other than a mistake on my part.

If I'm in a layout that's viewing MainTable, I can sort by MainTable::UserID. Subsummary layouts work fine.

If I try to sort by Users::Name, I get nothing. Well, actually, MainTable gets "unsorted" - exactly as if I pressed the button.

Here are some things that I've tried:

• Sort by Users::Name, then MainTable::UserID. This sorts exactly as if I'm sorting by MainTable::UserID.
  
• Create a calculation field "UserName" in MainTable that's
  "a" & Users::Name
  (The concatenation was just in case FileMaker might take a shortcut if I just mirrored Users::Name.) The field displays properly, but sorting by it produces exactly the same result as sorting by Users::Name.
  

Any help is appreciated.

[comment]

Can you post a small sample showing this? There shouldn't be a problem getting the child table to sort by a related field from parent. Of course, sub-summary parts must be defined to break by the same field, too - but that doesn't affect sorting as such.

[Glenn Trewitt]

I'm trying to sort MainTable by a related field from related table - Users. Not sure precisely what you meant by "parent" and "child".

When you say "post an example", do you mean an example DB with this problem?

[comment]

In a one-to-many relationship, one is the parent and many the child. A bit easier to visualize than Users and Main (one User has many "Mains"??).

do you mean an example DB with this problem?

Yes.

[Glenn Trewitt]

Here's the sample - Sort Problem.

I pared this down from the actual problem DB.

I created another DB from scratch, and it does not have this problem - Sort Not-A-Problem.

I can't really tell that there's any difference between the two - other than this problem.

Thanks for the help!

Sprt_Problem.zip

[comment]

Change the Name field to type Text.

[Glenn Trewitt]

I am totally in awe - and feeling really silly.

Thanks very much!

[comment]

LOL, we've all been there.