I have the database set up so that each evaluation is in its own table with the animal, the evaluation, and restrictions.
That sounds like a very inefficient design. Does this mean that if you add an animal you have to add a new table?
Tables and relationships should look something like this:
Animals::__pkAnimalID = Evaluations::_fkAnimalID
Evaluations::__pkEvaluationID = Restrictions::_fkEvaluationID
A portal to Evaluations on the Animals layout will list all evaluations. A portal to Restrictions placed on an Evaluations layout will list all restrictions linked to that evaluation. And you can set up a sort order on a portal OR on it's underlying relationship that will sort the most recently dated records to be first or those with the largest auto-entered serial number to be first.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Let's see if that makes sense first and then we can discuss a layout design that lets you view the restrictions for the most recent evaluation on your Animals layout.
I apologize. I should have been more specific in my first post. This is what my database looks like.
- Includes: Unique ID number
- Has a portal of all of their current animals, including their current restrictions
- Includes: Unique ID number, restrictions(A) , Member ID
- Links to members via the member ID (one member to many animals)
- Includes: Unique ID, restrictions (B), Animal ID, evaluation ID
- Links to the Animal via Animal ID (one animal to many evaluations)
- Includes: Unique ID, AnimalEvaluationsID, evaluator ID
- Links to AnimalEvaluations based on evaluation ID (one evaluation to many AnimalEvaluations)
- This shows a portal to show the animals that were done at the evaluation and another to show the evaluators there
- This then links over to evaluators, but it is not important
My question is if there is anyway to have restrictions (A) show the most recent restrictions (B) while the animal is in the portal on the members page. There purposefully isn't a way to see the animals not on the member layout.
A one Row portal to AnimalEvaluations can be set up with a specified sort order to show only the most recent record for that animal.
If you set up a sort order at the relationship level, you can add fields from AnimalEvaluations to your Animals layout without using the portal and they will show data from the most recent record in animalEvaluations.