I suspect this is not an unusual situation, but I can't find a good solution. I have Parent.db with 1:M relationship to Child.db. Child.db includes a 1:1 link to ItemList.db. ItemList records include ItemKey (PK) and ItemName (string). On a form, I want to show a Parent record with a TableFrame for Child records sorted by ItemName. I can think of a couple of clunky ways to do this (flat tables, creating a temporary table every time I move to a new Parent record) but is there a good, simple way to accomplish this?
1) you could link the child records/ frame as a query instead of a table. If the table is not huge this is probably the fastest way.
2) You could create add a calculated field to your form view which contains the item name and sort on this field ( or add a secondary index).
3) you could go into your data model and create one specifically for this view and then make a form from that data model. (Longterm best way.)
I've spent much of the day thinking about this. I agree that table size could impact solution 1, but it has some appeal. Could you provide a bit more information about solution 2? It was my understanding that TableFrames could not index on calculated fields and I'm not sure how this would work with an external field. With solution 3, it seems one is still stuck with setting a range on the Child table index and then sorting on ItemName from another table. One could add ItemName to the Child table and index it, but this seems kind of unelegant to me.