11 Replies Latest reply on Mar 12, 2014 8:17 AM by philmodjunk

    Calculation with empty Fields Issue.

    gullfounder

      Title

      Calculation with empty Fields Issue.

      Post

           Hi.

           My issue is a calculation field is not being calculated. I know because they are empty.  I can find a way around that issue. I tried some methods i saw in FileMaker Help. Like using of  if AND case statement. But it give out Error the an Operator is missing.

      Table

           There are 2 tables involved in it. First Employees which stores Employees Records along with there Original Salary. Another Table is Adjustments. Which stores the ups and Downs of salary.  Adjustments table is related to Employees Tables via relationship where Employees id field is used as FK in Adjustments.

            

      Fields

           In Employees table there is field for salary. which stores the original salary. its number field. There are 2 fields in Adjustments tables. Allowance and Deductions which stores ups and downs respectively and there sum fields as s_Allowance and s_Deductions. They are summary fields. There is a calculation field net_salary. where net salary is calculated. Formal was

           "Employees::Salary + s_Allowance - s_deductions"

           but it dint work until i had some records in Adjustments fields for that Employees. The issue was the calculation field dint calculated till i had some values in s_Allowance and s_Deductions.

           I tried this calculation too.

      If ( IsEmpty ( s_Allowance ) ; s_Allowance = 0 ;"")
           
           If ( IsEmpty ( s_Deduction ) ; s_Deductions = 0 ;"")
           
           Employees::Salary + s_Allowance - s_Deductions

           It gave me Error of operator is missing. Tried another calculations.


      Case ( IsEmpty ( s_Allowance) ; Employees::Salary + 0 - s_Deductions ; IsEmpty( s_Deductions ) ;  Employees::Salary + s_Allowance - 0 ; Employees::Salary + s_Allowance - s_Deductions  )

            

           It dint work. I need help on solving that issue.

        • 1. Re: Calculation with empty Fields Issue.
          philmodjunk

          Employees::Salary + s_Allowance - s_Deductions

               should be all that you need to use in a calculation field. The syntax you've posted is correct if the calculation field is being defined in the same table as s_Allowance and s_Deductions.

               The value returned should not be blank unless the Employees::Salary, s_Allowance, and  s_Deductions are all empty. Since Employees::Salary refers to a field in another table, make sure that the relationship that links to it and the "context" you have selected for your calculation field (drop down at top of specify calculation dialog), is correct or it could evaluate as empty.

               When you got this error, was this the definition for a calculation field or a script step? Was it for an auto-entered calculation or a calculation field?

          • 2. Re: Calculation with empty Fields Issue.
            gullfounder

            Employees::Salary + s_Allowance - s_Deductions

            This calculation results are Good as long as There is values in s_Allowance and s_Deduction. But there are scenarios where i don't have to enter vales in  both fields. Like for example when i have New employee. In that case there will be no records for that Employee in s_allowance and s_Deductions. In that case The result becomes Blank!!! I want it show only Employee salary.

                 Its a Calculation field not a Auto Enter Calculation. I tried to change to it too. But same issue.

                 The error i get is in Definition Calculation.

                 Attaching a File

            https://www.dropbox.com/s/c2x7m83dqtt7yvp/08-Mar-2014.fmp12

            • 3. Re: Calculation with empty Fields Issue.
              philmodjunk

                   That should not be the case unless Employees::Salary is also blank. But there is a check box: "do not evaluate if all referenced fields are empty" that can be cleared.

                   I will repeat my suggestion that you may have an issue with either your relationship to the Employees table or the "context" selected for the calculation as you should get a blank result only if all three fields are empty, not if just the first two fields.

              • 4. Re: Calculation with empty Fields Issue.
                gullfounder

                     If i change the Layout based on Adjustment Table. The problem gets solved. But i cant sort the list like i want to.

                     Here is what i want to sort to. Employee status and then Categories which comes form Employee table then in Adjustment table to current month only.

                • 5. Re: Calculation with empty Fields Issue.
                  philmodjunk

                       I opened your file and took a look. There appear to be a number of problems. is "c_net_Salarary" the field that doesn't work?

                       This field should be defined in Employees, not adjustments. The summary fields referenced in this calculation will not return totals for just a single employee's data when the field evaluates from the context of adjustments.

                       Try this calculation field define in Employees:

                       Salary + adjustments::s_Allowance - adjustments::s_deductions

                  • 6. Re: Calculation with empty Fields Issue.
                    gullfounder

                         Yeah that was the field. I have updated the File. Now i have created the Table based on Adjustments. s_Allowance and s_Deductions changed there setting to Running total and Restart Summary for each Month field. c_net_salary is still in Adjustment Table.

                         The Result is fine. I can now sort Employees based on there Categories and Adjustment are sort per month. hence the salary for each employee is different on each month deepening on s_Allowances and s_Deductions. But now the issue is. If there is no record of employee in Adjustments then it dosen't calculate its net salary(Salary +- of its Allowances).

                         Uploading the updated File.

                         https://www.dropbox.com/s/c2x7m83dqtt7yvp/08-Mar-2014.fmp12

                    • 7. Re: Calculation with empty Fields Issue.
                      gullfounder

                           If i add the field net_salary in Employees table. Then i cant sort the record based on Month! rest works fine.

                      • 8. Re: Calculation with empty Fields Issue.
                        philmodjunk
                             

                                  Then i cant sort the record based on Month!

                             What records in what table on what layout?

                        • 9. Re: Calculation with empty Fields Issue.
                          gullfounder

                               Record of Employees Based on Employee table. Record sorted on Status of Employee then its category. Then from Adjustments Table based on months.

                               Name                             Father Name                 CNIC                 Salary                    s_Allowance                                        s_Deductions          

                               Employee name            Father name              CNIC no.             Original Salary        it total allowance of the month    it total allowance of the month

                                

                               net_Salary = Salary + s_Allowance - s_deductions

                                

                               This is what i am trying to achieve.

                          • 10. Re: Calculation with empty Fields Issue.
                            gullfounder

                                 If i simply sort the records first based on Employee status then Category. Then From adjustment Tables Months. It omits those Employees records which don't have any Adjustment records. I want to include those records too.

                            • 11. Re: Calculation with empty Fields Issue.
                              philmodjunk

                                   I don't follow your description. What "Category"? What "Status"? (I don't have your file downloaded to the machine that I am using at this moment.)

                                   You should be able to include this calculation in a record sort without any trouble, if you are sorting records on a layout based on Employee. A sorted portal to Employee should also be able to include this calculation field.

                                   This field must calculate from the context of Employee or allowances and deductions for other employees are included in the calculation. By defining it in Employees, you get just the allowances and deductions for the one Employee.