3 Replies Latest reply on Aug 20, 2014 11:39 AM by philmodjunk

    Auto Enter value based on two fields' values?

    MarkRiddle

      Title

      Auto Enter value based on two fields' values?

      Post

           Is it possible to auto enter a number value in a field, based on the values in two other fields?  AND have that change based on various combinations of values in each field that are not numeric values?

           Let me explain:  I have a database for tracking employees that take trainings.  I would like to have my database automatically enter the number of hours for a particular training into a field based on: 1) if the training module name equals a certain value (i.e. Basic Training) AND 2) if the attendnace field is "not empty" or checked for the date of that training.  

           For example:
           If my "training module" field is set to "Basic Training" AND the attendance field is check, the auto entered value in the "hours" field should be 3
           BUT, if the "training module" field is set to "Crisis Training" AND the attendance field is checked, the auto entered value in the "hours" field will then be set to 6
           AND if the "attendance" field is empty  or unchecked, the auto entered value is 0, no matter what the "training module" field is set to.

           Is that possible?  I hope that made sense.

           Thanks for your help in advance.

        • 1. Re: Auto Enter value based on two fields' values?
          philmodjunk

               Presumably, you have a table of employees with one record for each employee. You'll also need a table of Training with one record for each training class that might be taken with a field for the hours needed for that class. Then you need a third table where you document the fact that an employee has taken or (at your option) been scheduled/assigned to take that class that links to the other two.

               Start with these relationships:

               Employees-----<Employee_Training>-----Training

               Employees::__pkEmployeeID = Employee_Training::_fkEmployeeID
               Training::__pkTrainingID = Employee_Training::_fkTrainingID

               You can place a portal to Employee_Training on the Employees layout to list and select  Training records for each given Employees record. Fields from Training can be included in the Portal to show additional info about each selected Training record and the _fkTrainingID field can be set up with a value list for selecting Training records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               An hours field in Employee_Training can be defined like this:

               If ( IsEmpty ( Attendance ) ; 0 ; Training::HoursRequired )

               and clear the "do not replace existing value..." check box

               Anytime you modify the attendance field, this calculation will kick in and look up the hours for the specified training class.

          • 2. Re: Auto Enter value based on two fields' values?
            MarkRiddle

                 PhilModJunk,

                 Thanks for the reply.  WOW, i'm not as savy with functions as you are.  Actually, the database is set up to count each training event as a separate file since we also train people from outside the agency.  So I can have a person listed multiple times for taking different trainings as nobody takes the same one twice. 

                 So I have been noodling around with the CASE function and I seem to have a basic version set up that works partially. Here is the actual formula:

                 Case ( Training Topic="Wrap Basic" and Attendance Session 1="X"; 3)

                 That works for that single instance.  but I have other trainings.  I have discovered that all other trainings are 6 hours trainings.  So it would seem all I need is to add something to that function to that if the "Training Topic" field does NOT equal "Wrap Basic", then it should set the hours field to "6".  That would pretty much do it for all the functionality I would need for that.  Is that possible?

            • 3. Re: Auto Enter value based on two fields' values?
              philmodjunk

                   What I have outlined should do exactly what you have requested. The system allows you to record training info on any number of employees and for any given employee, you can document as many training sessions/classes/whattever as needed for that one employee. There is no upper limit on the number of classes that you can link to any one employee.