3 Replies Latest reply on Nov 1, 2013 2:30 PM by philmodjunk

    Updating information based on the most recent evaluation

    CaitlinMcKelvie

      Title

      Updating information based on the most recent evaluation

      Post

           I am creating a database where we have animals that get evaluated every two years. At these evaluations, they receive restrictions that they must follow. I have the database set up so that each evaluation is in its own table with the animal, the evaluation, and restrictions. Each animal, on its layout, then has its own information that includes restrictions. Right now, the animal restrictions and the restrictions that it receives that the evaluation are separate. I am wondering if there is a way to make it so that the animal's restrictions show the most recent restrictions from the evaluation. Thanks!

        • 1. Re: Updating information based on the most recent evaluation
          philmodjunk
               

                    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----<Evaluations-----<Restrictions

               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.

          • 2. Re: Updating information based on the most recent evaluation
            CaitlinMcKelvie

                 I apologize. I should have been more specific in my first post. This is what my database looks like.

            Tables

                   
            •           Members           
                               
              •                     Includes: Unique ID number
              •                
              •                     Has a portal of all of their current animals, including their current restrictions
              •           
                   
            •      
            •           Animals           
                               
              •                     Includes: Unique ID number, restrictions(A) , Member ID
              •                
              •                     Links to members via the member ID (one member to many animals)
              •           
                   
            •      
            •           AnimalEvaluations           
                               
              •                     Includes: Unique ID, restrictions (B), Animal ID, evaluation ID
              •                
              •                     Links to the Animal via Animal ID (one animal to many evaluations)
              •           
                   
            •      
            •           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.

            • 3. Re: Updating information based on the most recent evaluation
              philmodjunk

                   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.