6 Replies Latest reply on Sep 24, 2013 2:05 PM by philmodjunk

    Allow Users to enter data used in if then function

    schmity

      Title

      Allow Users to enter data used in if then function & related tables

      Post

           I have several fields in an employee database that use if then statements to populate a field. 

           For example, for health insurance, if they have option 1,it is this amount, option 2 is this amount...

           if(MedType = "EMP"; 33.45,

           if(medType = "Fam"; 139.57

           ))

           It works, but will require me to manually change the calculation every year when the rates change.

           I would like to make it easier for the HR department to change these values.  I would like to have basically 3 extra records (on a separate table) (Medical, Dental, Vision), and each of these 3 records would contain fields with the different options for each type of insurance.

           so basically

           if(medtype = "EMP"; get(tableX(benefits)::recordX(medical)::fieldX(EMP),

           if(medtype = "FAM"; get(tableX(benefits)::recordX(medical)::fieldY(FAM)))

           The problem Im running into is I get an error when specifying the calculation- This field comes from an unrelated table.  Only global fields can be referenced in an unrelated table.

           Im not sure how to relate these tables, since all the tables in table 1 (where the calculation is taking place) are individual employees) and table 2 (benefits) has 3 record of the different types of insurances.

           1. what is the best way to relate these tables

           2.  Am I even on the right track for setting this up?  This is my first stab at making a DB with more than 1 table.

            

            

        • 1. Re: Allow Users to enter data used in if then function & related tables
          philmodjunk

               You are on the right track. Creating a table of these values so that your HR department can simply edit the data instead of having to redefine the calculation is exactly correct. But what you do next is key.

               Define a relationship that links this new table, (let's call it "Rates".) to link it to your original table by the Med Type field.

               OriginalTable>------Rates

               OriginalTable::Medtype = Rates::MedType

               Then you can replace:

               if(MedType = "EMP"; 33.45,

               if(medType = "Fam"; 139.57

               ))

               with just Rates::RateValueField

               The value selected in OriginalTable::MedType will determine which record supplies the value in RateValueField to your scripts, lookups and calculations.

               PS. this type of value is often looked up (Copied) into a field in your original table so that future rate changes do not affect the values used in existing records.

          • 2. Re: Allow Users to enter data used in if then function & related tables
            schmity

                 In case it makes a difference, future rate changes WOULD affect values in the current records.

                 I think I understand this, but am not totally sure how to set it up.

                 I created a new table called rates, and in the rates table created a medtype field and created a relationship between the two tables on that field

                 In my original table, the medtype field is a drop down of 8 options (value list), there is also a DentType (Dental) with 4 options and a VisType (Vision) with 4 options. 

                 How would I set up everything in the rates table to correlate with this?  Do I need a separate record for each rate (16 total) or could I do it off of 1 record with all 16 rates listed.

                 Ive been tinkering with it for a few hours and cant find anything that works.

                  

            • 3. Re: Allow Users to enter data used in if then function & related tables
              philmodjunk
                   

                        In case it makes a difference, future rate changes WOULD affect values in the current records.

                   But what about PAST records--say records created last month, quarter or year? should they also update if the rate changes?

                   Yes you should use a separate record for each rate. For a "live" or "dynamic" link to the data that always updates when changes to the related data is made, simply place the field from the new related table on your original layout. When you select a med type, you'll see the correct rate value appear in this field.

              • 4. Re: Allow Users to enter data used in if then function & related tables
                schmity

                     This is what I am currently trying to figure out...

                      

                     "The value selected in OriginalTable::MedType will determine which record supplies the value in RateValueField to your scripts, lookups and calculations

                      

                     I'm not sure how to write that out to reference a table, field and record.  the only way I can think of do this is:

                     if(medtype = "HSA Emp"; get(rates::hsaEmp:rate)- -> hsaemp being the record, rate being the field on that record

                     if(medtype = "HSA Fam"; get(rates::hsaFam:rate)))-->hsafam being the record, rate being the field on that record.

                     Is this what you meant or am I going down the wrong path? 

                      

                     thanks for all your help.

                • 5. Re: Allow Users to enter data used in if then function & related tables
                  schmity

                       Also, the original table is individual employees, so whenever the rates are changed on the rate table, it would need to update all the records in the original table, since their rates would also be increased.  Rates are changed once a year.

                  • 6. Re: Allow Users to enter data used in if then function & related tables
                    philmodjunk

                         You do not need any calculation at all in your employees table. Unless you need to capture the current value via a look up, you don't even need a field for it in the employees table. Just go to your employees layout, enter layout mode and use the field tool to add the field from the new related table that holds this rate value directly to your layout.

                         And it will update automatically for all records in employees if you edit the matching record's value in the new table.