Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Summary Report: calculating average pay over several records


craman
 Share

Recommended Posts

Every two weeks student workers submit online directly into Filemaker the hours they work each day- one record with their name, 14 Date fields and 14 hour fields (plus a calculation to sum the hours).

 

We need to calculate holiday pay by averaging the daily hours worked (counting only days they worked) in the 30 day period before the holiday. The two week reporting periods have nothing to do with the 30 day period (it generally spans 3 pay periods). Someone that works 15 12 hour days gets twice the holiday pay of someone working 30 6 hours days. There is no consistency to the number of hours worked per day- they work a lot one pay period and just a few hours another pay period, so I can't average the average of Time Periods.

 

My problem: How do I calculate average hours worked between different fields and two or three records in a summary report?

 

I can create a report that summarizes the number of days & hours worked between periods- but I don't know how to remove those that worked less than 15 days from the report or how to do the average hours worked.

# Days worked-

If ( (workstudy = "") ; (
(If ( ((Date_01  ≥ statutory holiday period start) and  (Date_01 ≤  statutory holiday period end)  ) ; " 1"; "")) + 
(If ( ((Date_02  ≥ statutory holiday period start) and  (Date_02 ≤  statutory holiday period end)  ) ; " 1"; "")) + 
...etc to
(If ( ((Date_14  ≥ statutory holiday period start) and  (Date_14 ≤  statutory holiday period end)  ) ; " 1"; "")) 
) ; "")

 

# hours worked-

If ( (workstudy = "") ; (
(If ( ((Date_01  ≥ statutory holiday period start) and  (Date_01 ≤  statutory holiday period end)  ) ; Hours_01; "")) + 
(If ( ((Date_02  ≥ statutory holiday period start) and  (Date_02 ≤  statutory holiday period end)  ) ; Hours_02; "")) + 
...etc to
(If ( ((Date_14  ≥ statutory holiday period start) and  (Date_14 ≤  statutory holiday period end)  ) ; Hours_14; "")) 
) ; "")

 

Additional summary fields total the hours and days worked. (Also, Work-study people do not get holiday pay, nor do people that work less than 15 days in the 30 day pre holiday period).

 

Should I switch to keeping the days/hours worked in a related table (which would make the online code a pain). Should I export information to another Table? Or should I stick with this. Any dialog and suggestions are welcome.

Link to comment
Share on other sites

  • 2 weeks later...

I went for several looping scripts- probably not the best way. Worker entries in the main table (Timesheets) are unchanged- most processing in a temporary table (Statute Holiday Table). It is very complex (about 18 scripts), so I am not very happy with it, but it does the job. Any suggestions for simplifying the process are welcome!

 

Detailed overview of Scripts-

a. Check that Statute Holiday Table is empty, if not, offers to delete records or halt script

 

b. searches for all Timesheet records (in Timesheets table) with dates including the date span [using calculated Fields Max (Date_01; Date_02; Date_03; Date_04; Date_05; Date_06; Date_07; Date_08; Date_09; Date_10; Date_11; Date_12; Date_13; Date_14) and Min equivalent.

 

c. exports one record for each of the 14 date fields (14 scripts exports Date_01, Date_02, etc to a single date field and hoursworked01, hoursworked02, hoursworked03 etc to a single hoursworked field) Statute Holiday Table. Each new record has '0' autoentered for #days worked.

 

d. Deletes all records with 0 hoursworked.

 

e. Searches for duplicate dates entered by same user email/supervisor email- if found, Loops- Goes to first record, copies user email/supervisor email and then searches only for those records, sums hours worked, replaces hours worked in first record and deletes extra records.

 

f. Searches for #days worked =0. Sorts by calculated field user email/supervisor email. Loops- Goes to first record, copies user email/supervisor email and then searches only for those records. If records found is equal or more than minimum # of days it copies the # records found and entered in field #days worked, ave hours (summary field) copied to field here and in Timesheets table. If not enough records it deletes those records. Continues loop until no records with 0 days worked found.

 

g. Copies the Holiday Name/ Year to remaining records and related records in Timesheets table.

 

I discovered that if people did not enter their names the same on different timesheets that calculations still worked (they are based their email, which is unique) but the display listing people sorted by name broke them into two- I added a step to Replace Field contents for NameFirst and NameLast.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use