Hi!

I have a database for work that tracks hours worked for my employees. If an employee is absent, I have a field to track that data.

What's the easiest way to find all employees for a given time period (such as a month) who have perfect attendance? These employees would not have any entries in the "absence" field. If someone was late or absent one one more times during the time period, I do not want their name in the final report, but I don't know how to generate this report. Should this be done with ScriptMaker? If so, I'll post there!

Any help is most appreciated!

Russ Conte

FM 5.5 on Mac OS X.1.3

The answer really depends upon the structure of your files. You indicated that you have an absence field, but what is the structure of the file the field is in? What is a record (what does each record represent) in your "absence" file? Also, what is in final "final report"? Is it a list of employees?

The hard part of this type of solution is generating the absence field, which you already seem to have.

-bd

Each record tracks the following information for one day for each employee: The unique employee ID # (so it's relational), and auto enters the following: employee's first name, last name, social security #, department worked, start time, end time, and lunch (Y/N). In addition, it calculates hours worked for the day and there are two pop-up fields which I will enter manually, one for absence code and one for "closed" (such as terminated, quit, moved out of area, etc.)

So to make is simple for now, if I have 5 employees (I will be working with hundreds, but that's not the issue right now), I will have five records per day to track the Hours Worked for each employee, 25/week, and so forth in this database.

Given this setup (which I can modify, it's my database!) how can I find employees who have no absences in a certain time period? Perfect attendance is one of many good indicators for raises and promotions, so this is not just for a certificate and a photo

Thanks!

Russ Conte

First, by "auto enter" I hope you mean to display the appropriate related fields from the Employee file, not lookup this information in every time record (this would just eat up space to no advantage). If I understand correctly, you will make time enteries in this file for absent employees on the days they are absent? If so, here is one way. Create afields in the time file defined as:

Key (calculation, text, indexed, ASCII) = EmployeeID & "-" & Month(EntryDate)& "-" & Year(EntryDate)

AbsentFlag (calculation, number, indexed) = If (whatever indicates an absence, 1,0)

This ASCII index setting is important. Otherwise FM ignores the "-" and Employee 1 for November looks just like Employee 11 for January (111 instead of 1-11 and 11-1).

Create fields in the Employee file:

gMonth (global, number)

gYear (global, number)

Key (calculation, text, indexed, ASCII) = EmployeeID & "-" & gMonth & "-" & gYear

Create a relationship from Employee to Time using these keys.

then

Absences (calculation, number) = Sum(relationship::AbsenceFlag)

Now you can find in the employee file using:

Enter Find Mode()

SetField(Absences, ">0"

Omit

Perform Find()

There might be a more clever way using just relationships, but it doesn't occur to me. You could also do a month find absences in the time file, copy all on EmployeeID's in the time file to a global field, and go to related record in the Employee file (relationship global text field to EmployeeID), and then show omitted in the Employee file.

Hope this is somehow understandable.

-bd