4 Replies Latest reply on Aug 28, 2014 4:27 AM by RelativeNewbie

    auto enter info from Field A on Layout A dependent on selections in Fields B

    RelativeNewbie

      Title

      auto enter info from Field A on Layout A dependent on selections in Fields B&C on Layout B

      Post

           I don't know if the title is clear enough, but this if what I'm aiming at...

           I want the charge rate on my Employee's forms to auto populate base on which department they are in and what their skill level is.  The departments are being pulled from a value list that points to a  table called DEPARTMENTS, in which there are currently 8 entries.  The skill levels are being pulled from a value list in which I've manually typed the following :

           Trainee
           Semi Skilled
           Skilled

           The DEPARTMENTS table has fields named Trainee Charge Rate, Semi Skilled Charge Rate, and Skilled Charge Rate.

           So basically if Employee A is a Trainee working in the Joinery department, his charge rate should look to the Joinery entry in the DEPARTMENT table and see what the Trainee Charge Rate is.

           I've tried various calculations and can't seem to get this right but them I've only just downloaded a trial version to see if it will work for my new company and have absolutely not experience in writing databases but am a very quick study.  Any assistance would be very much appreciated.

           Thanks

           Karen

        • 1. Re: auto enter info from Field A on Layout A dependent on selections in Fields B&C on Layout B
          philmodjunk

               Without making major changes to your data model, you need a relationship between your Table (not layout) A and Table B that links the records by department.

               I'll call Table A "WorkRecords" and Table B "Departments". If they are linked like this:

               Departments::__pkDepartmentID = WorkRecords::_fkDepartmentID

               Then in any record in WorkRecords on your WorkRecords layout, you can select a department in the _fkDepartmentID field followed by selecting a Skill level in a SkillLevel field using your value list.

               You can then set up a Number field, ChargeRate, in WorkRecords that auto-enter's the needed value using a Case function:

               Case ( SkillLevel = "Trainee" ; Departments::Trainee Charge Rate;
                          SkillLevel = "Semi Skilled" ; Departments::Semi Skilled Charge Rate;
                          SkillLevel = "Skilled" ; Departments::Skilled Charge Rate ;
                        )

               A more drastic approach would be to split up your 8 Department Entries into 24 records, one for each unique combination of Department and Skill Level. There is no immediate need to do this, but it might make some aspects of managing this (such as making it easier to add new skill levels and rates) data easier. With the data structured in that format, you'd use both the Department ID and the skill level to match to the record with the correct date and you would no longer need the case function shown above as the relationship would then only match to the single value needed.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: auto enter info from Field A on Layout A dependent on selections in Fields B&C on Layout B
            RelativeNewbie

                 Hi PhilModJunk,

                 Thank you for your answer but it doesn't seem to be working 100%.  Maybe I'm doing something wrong but it's changing the rate on the Employee form as I change the skill level but it isn't changing the rate based on the department they are in.

                 My Table 'A' is called Employees and Table 'B' is Departments.  

                 Departments                Employees

                 __DepartmentIDpk     __EmployeeIDpk.
                                                         _DepartmentIDfk

                 I have this set as one [Departments: :__DepartmentIDpk] to many [Employees: :_DepartmentIDfk] as one department can have many employees.

                 Even with this set as such and the case calculation entered into the [Employees: :Charge Rate 1] field, it isn't working.  

                 Please let me know if you have any other suggestions.

                 Thanks
                 Karen

            • 3. Re: auto enter info from Field A on Layout A dependent on selections in Fields B&C on Layout B
              philmodjunk

                   My suggestion should work for what you describe. Please describe the details of what you did in your attempt to implement this in more detail.

              • 4. Re: auto enter info from Field A on Layout A dependent on selections in Fields B&C on Layout B
                RelativeNewbie

                     Hi Phil,


                     My bad, I just checked the table layout and noticed the foreign key field hadn't been populated because my department dropdown was related to a field called department as opposed to the _DepartmentID_fk.  

                     Thanks so much, it works fantastically. :)

                     Karen