1 Reply Latest reply on Jul 27, 2011 11:01 AM by philmodjunk

    Creating a Calculation or Script



      Creating a Calculation or Script


      I worked with Filepro 15 years ago in a past position. Now back working and have come across it again so I am a bit rusty.

      I would like to create a calculation in a "salary" field that would come from contents of three fields. The first field contains a "department" code, the second a "catagory" code and a third a "guide" code.

      Depending on the contents of these three fields is what the employees "salary" would be.

      I do have an Excel spreadsheet that contains this info but I need to create it in Filepro and am unsure how. I also need to make the calculation or script work but just need a little guidance on how to start this and would it be considered a “calculation” or “script”.

      If you can direct me in the right location I may be able to pick up where I left off 15 years ago.


        • 1. Re: Creating a Calculation or Script

          In those 15 years, FileMaker has changed a lot.

          Seems like you need a third detail here: How the values in those three fields are used to determine the Salary. I would guess that you have a table of at least 4 fields: Department Code, Category Code, Guide Code and Salary. If so, this becomes either a "looked up value" or a dynamic reference to the related value. Either approach requires a relationship between two tables: The table where you need to use this value and the table I've just described where you store each combination of Department, Category and Guide codes.

          Open Manage | database and use the tables tab to create the two tables and the fields tab to define the needed fields. You'll need to define Department, Category and Guide code fields in both tables as you will need all three in your relationship. You may or may not need a salary field in both tables--it depends on how you choose to set this up.

          Now click on the Relationships tab and drag from field to field to link Department to Department, category to category and Guide to Guide. This will produce the following relationship: (And a relationship set up like this wasn't possible 15 years ago...)

          MainTable::Department = SalaryTable::Department AND
          Maintable::Category = SalaryTable::Category AND
          MainTable::Guidecode = SalaryTable::Guidecode

          Now you'll need to decide whether to copy the salary amount into the Main table or just refer to the Salary field in the related Salary table. Here's the key difference: If you copy the value, subsequent changes to the Salary field in the Salary table will not update existing records in Maintable unless you specifically re-copy the value into the field. If you refer directly to SalaryTable::Salary, changes to the amounts in the salary table will automatically update. Both approaches can be a valid option for you depending on what you need to accomplish. Think about what you want to see happen (or keep from happening) to this value in existing MainTable records should a pay raise be implemented by updating the values in the salary table.

          If you want to copy the data, define a number field in MainTable and use the looked up values field option to copy the salary amount from the realted salary table record.

          If you want to directly refer to the value in the salary table, you can add the SalaryTable::Salary field to your MainTable layout or you can refer to it in a calculation by using: SalaryTable::Salary as a term in your calculation.