Anyone? : )
Please document the relationships between your three tables.
My three tables as listed above are "Projects", "Assets", and the join table is "ProjectAssets". The relationship between them is a primary key in "Projects"and "Assets" and corresponding foreign keys in the "ProjectAsssts" table. The question is where should the sort relationship be, using the 4 sort fields I created - that's the problem I'm having. The solution I am replicating requires the sort relationship to be between the "Assets" table and the "ProjectAssets" table. This DOES work if I am sorting the fields that reside in the "ProjectAssets" table but does not work sorting fields from the "Projects" table which is what I'm needing.
In a nutshell I have a layout (based on the "Assets" table) with a portal showing related records from "ProjectAssets" table. I have fields from the "Projects" table located on the portal. These "Project" fields are the fields I want to sort. Maybe there is a better solution?
I hope this makes since.
These would then be your relationships:
Projects::__pkProjectID = ProjectAssets::_fkProjectID
Assets::__pkAssetID = ProjectAssets::_fkAssetID
A portal can't sort on fields that aren't in the portal's table occurrence. To sort on fields from Projects in your ProjectAssets table, you'll need to add calculation fields in ProjectAssets that copy the data from Projects.
Bingo! Thanks Phil!