Allow Users to enter data used in if then function & related tables
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.
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.