calculations

[kanaps]

Hi ALL!!

I have a situation here. Please HELP!

I have mane field called STUDENTS which linked to ATTENDANCE by ID

In ATTENDANCE I have records like: id=1,Monday=1, tuesday=0,wends=1,thus=1,fri=0

Now I am trying to calculate attendance! I need to find all ID matching current. ADD them and divide buy amount of founded records. Please let me know how I can I calculate it or where can I find more information on calculations us manuals which File Maker send me are not good for this.

Thnk you in Advanced. Kanaps

[LiveOak]

I'm not sure what you mean by: "In ATTENDANCE I have records like: id=1,Monday=1, tuesday=0,wends=1,thus=1,fri=0". Are these fields within a single record? Does each record represent a week?

One thing I wouldn't do is use one attendance record for multiple days. The secret to attendance or timecard databases is to make each record represent the smallest increment of time or attendance possible. If your smallest entry is one person for one day, I'd make each record in attendance one person/day. Your problems with calculations are a symptom of storing data for multiple days in a single record. With separate records your calculation would be:

AttendPercent (calculation, number) sum(rel::present)/Count(rel::id)

where present is a field with a "1" if the student was there for the day and blank or zero if they were absent. The Count() function will work with any non-empty field.

-bd

[kanaps]

Thank you for your help, One more question please. Where can I get information about: "AttendPercent (calculation, number) sum(rel::present)/Count(rel::id)"?

How can I find out the way of building formulas like this one?

Thank you. Kanaps

[hayesk]

AttendPercent is the name of your field. You can name it whatever you want. Make it a calculation field. THe calculation is

sum(relation::present)/Count(relation::id)

It's a simple calc field - all of the functions are described in FileMaker's help system. Look at the examples too to help you learn.

[kanaps]

Thank you.

It is very helpfull. Please let me know where did you learn information like:

sum(relation::present)/Count(relation::id)

or

AttendPercent (calculation, number) sum(rel::present)/Count(rel::id)

from? What is this system called?

Thanks

Kanaps

[-Queue-]

A/B is how one calculates percentage. 5/20 = 0.25 = 25%. This is simply math. The calculation uses the Sum and Count functions of FileMaker to give the result. Information about them can be found in the online help. Sum(relationship::field) adds all related values of 'field' and Count(relationship::field) counts how many non-empty values there are in 'field' for all related records.

[correct76]

hi Queue,

what if i add another condition for attendance calculation? say for example, in Attendance Table, i have another field called "Lesson" which could be maths lesson, literature lesson or music lesson. now i want to calculate the attendance percentage of a particular student for his music lessons. how can i do it?

sum(relation::present)/Count(relation::id) will only return percentage of all the lessons.

thanks in advance!

[-Queue-]

You need a different relationship, based on StudentID and ClassID, so that the only related records on a particular record are for the same class. If you want to display more than one class percentage on a record, use multiple relationships based on globals or calculations containing the id for each class for the left-hand side.