Summary Fields in Calculation fields

[gullfounder]

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  )

 

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

 

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

 

[Todd Geist]

Hello,

 

If I understand you correctly Summary fields are probably not the way to go here.

 

You might want to check out the Aggregate Calc functions, specifically the Sum() function

 

Try creating a field in your Employees table that looks like this

Orignal_Salary
+
Sum(Adjustments::Amount_Allowance)
-
Sum(Adjustments::Amount_Deduction)

That translates as

 

Original Salary plus ( the Sum of the related Amount_Allowances ) minus  ( the Sum of the related Amount_Deductions )

 

Does that help?

 

 

Todd

 

 

[gullfounder]

 

Hello,

 

If I understand you correctly Summary fields are probably not the way to go here.

 

You might want to check out the Aggregate Calc functions, specifically the Sum() function

 

Try creating a field in your Employees table that looks like this

Orignal_Salary
+
Sum(Adjustments::Amount_Allowance)
-
Sum(Adjustments::Amount_Deduction)

That translates as

 

Original Salary plus ( the Sum of the related Amount_Allowances ) minus  ( the Sum of the related Amount_Deductions )

 

Does that help?

 

 

Todd

 

 

Nope, Because s_Allowance and s_Deduction are different for each employees. This formula worked for me.

 

Employees::Salary + s_Allowance - s_Deductions

 

The only issue i get with this is. If i have new employee which has no records for s_Allowance and s_Deductions. Then the result comes out blank.

[comment]

There is a calculation field net_salary. where net salary is calculated. Formal was

"Employees::Salary + s_Allowance - s_deductions"

 

In which table is this calculation field? It looks like it is in the Adjustments table. Are you trying to produce a report from the Adjustments table, summarized by Employee? Or calculate the current salary of an employee - in which case the calculation needs to be in the Employees table and look like this:

Salary + Adjustments::s_Allowance - Adjustments::s_Deductions

[gullfounder]

 

In which table is this calculation field? It looks like it is in the Adjustments table. Are you trying to produce a report from the Adjustments table, summarized by Employee? Or calculate the current salary of an employee - in which case the calculation needs to be in the Employees table and look like this:

Salary + Adjustments::s_Allowance - Adjustments::s_Deductions

Yes. its based on Adjustment table. Yes, the report is summarized by many factor. First is Employee status then Adjustment is sorted to current month so it only the current months record is calculated in to the salary.

 

I have uploaded the file in dropbox. So, you look at it.

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

[Lee Smith]

Post your file here. Attachments posted elsewhere often disappear and make the thread hard to follow for future readers..

 

If you need help in how-to attach a file, here is a link to the steps. Attachments.

[comment]

Yes. its based on Adjustment table.

 

If your report is produced from the Adjustments table, then any employee that does not have a related record in the current found set of Adjustments is not going to be included in the report.

 

I am not sure what business needs this report serves, so it's difficult to suggest alternatives.

[gullfounder]

Post your file here. Attachments posted elsewhere often disappear and make the thread hard to follow for future readers..

 

If you need help in how-to attach a file, here is a link to the steps. Attachments.

Sorry, I will post it know.

 

If your report is produced from the Adjustments table, then any employee that does not have a related record in the current found set of Adjustments is not going to be included in the report.

 

I am not sure what business needs this report serves, so it's difficult to suggest alternatives.

What if i try to make a report based on Employees Table?

08-Mar-2014.zip

[Todd Geist]

Nope, Because s_Allowance and s_Deduction are different for each employees. This formula worked for me.

 

Employees::Salary + s_Allowance - s_Deductions

 

The only issue i get with this is. If i have new employee which has no records for s_Allowance and s_Deductions. Then the result comes out blank.

 

 

Is this what you are trying to do?  (see image ) Get the new Adjusted Salary for an employee?  If you are then check out the attached file.

 

 

08-Mar-2014_a.fmp12.zip

[gullfounder]

Is this what you are trying to do?  (see image ) Get the new Adjusted Salary for an employee?  If you are then check out the attached file.

 

 

 yes exactly but there are few adjustments i cannot reach. Like, if i have not Adjustments of specific employee then its original salary should be calculated.

In a table form where i can sort the employees based on there status and Category. And Current months Salary. When i do that. It doesn't include those records where employees don't have any adjustments.

[gullfounder]

there is issue with it i think. There employee Ahmed Mumtaz has adjustments record of deduction for February. Hence it should not be calculated in March's Salary. It should on me shown for March.

[comment]

Would something like this work for you?

 

 

AdjSalary.fp7.zip

[gullfounder]

Would something like this work for you?

That is Exactly what i have been trying to archive. Can you explaine what i was doing wrong in my file? and how to correct it.

[gullfounder]

Would something like this work for you?

 

I have to manually add Dates into Global Fields. Is there way i can just Insert month.

[comment]

That is Exactly what i have been trying to archive.

 

That's good, but note that there are flaws in this solution. There is no history of an employee's salary changes. 

 

Perhaps you should consider having all salary-related data - initial salary, permanent salary changes and one-time additions/subtractions - in the same table. Then you could produce the report from this table (since all employees would have at least an initial salary record in this table), after finding both the permanent records and the ad-hoc records for the given period.

 

 

That is Exactly what i have been trying to archive. Can you explaine what i was doing wrong in my file? and how to correct it.

 

Sorry, I cannot open .fmp12 files right now.

 

 

I have to manually add Dates into Global Fields. Is there way i can just Insert month.

 

You can have the report script calculate the month's start and end dates. But how will it know which year you mean?

[gullfounder]

That's good, but note that there are flaws in this solution. There is no history of an employee's salary changes. 

 

Perhaps you should consider having all salary-related data - initial salary, permanent salary changes and one-time additions/subtractions - in the same table. Then you could produce the report from this table (since all employees would have at least an initial salary record in this table), after finding both the permanent records and the ad-hoc records for the given period.

 

 

 

Sorry, I cannot open .fmp12 files right now.

 

 

 

You can have the report script calculate the month's start and end dates. But how will it know which year you mean?

In that case i guess i have to insert year and Month then. But i want it to be very simple. So user doesn't have to enter date ranges for each new month. Maybe there should a drop list with Current years months list only and user can select from them.

[comment]

I can help you with the "how", not so much with the "what" - not being familiar with the workflow. A drop-down list is a bit problematic, because you would want this to self-update as time goes by - and a value list needs to be based on an indexable (i.e. stored) field. But a calendar drop-down might work: they could just select any date in the requested month.

 

I get the impression that this report is meant to run routinely each month with regard to the previous month? Perhaps you should make that the default choice and provide them with a device to select an alternative only when required..

[gullfounder]

I can help you with the "how", not so much with the "what" - not being familiar with the workflow. A drop-down list is a bit problematic, because you would want this to self-update as time goes by - and a value list needs to be based on an indexable (i.e. stored) field. But a calendar drop-down might work: they could just select any date in the requested month.

 

I get the impression that this report is meant to run routinely each month with regard to the previous month? Perhaps you should make that the default choice and provide them with a device to select an alternative only when required..

Yes you are right it is meant to run monthly. With each months Adjustments for each employees salary depending on his performance. Your idea on using Drop down Calender will do the trick. Can you tell me how can i implement this?

[comment]

1. Add a global gDate field to the Employees table. This will be the field with the drop-down calendar.

 

2. Change the gStartDate field to cStartDate (Calculation, Unstored, result is Date) =

gDate - Day ( gDate ) + 1

3. Change the gEndDate field to cEndDate (Calculation, Unstored, result is Date) =

Date ( Month ( gDate ) + 1 ; 0 ; Year ( gDate ) )

Hint: format the gDate field to show only month  and year.