I'm not sure about a couple of things. You say "I want it to show the dates specified". Does that mean "every date" (or absence?) or just the start and end dates (your global field's values)? If you want to see the dates/absences, then the report has to be in the Absences table/layout.
The other question is: What is an Absence table record? Is it only 1 record for an "absence spell" (with Date Start and Date End fields), or is it 1 record per date of absence?
If there is a record for each date of absence, then you'd need a way to only count the 1st date out for your "number of spells". Which I imagine you could flag using a Loop, or using a GetNthRecord (-1); in both cases records sorted by PersonID.
They talk about (on Wikepedia, where I learned what a Bradford Factor is) a rolling 52 week period. In any case, I think you could just Find for your date range (for all people) in the Absence table; then Sort by Staff ID. The totals could be produced via Summary fields. But the "a record per absence date" would make it a little more difficult.
Maybe someone else has more experience with this, and could answer better. But I need a few more concise details.
Hi Thanks for the reply.. I'll try and explain a little more..
An absence record - is the absence spell... However, if all absence were counted towards Bradford Points, then it would be fairly straight forward. However, they are not, we try to still have some "human" factor in there.. example..
A person is off for a day for a funeral - they have an absence record for it... but doesn't count towards Bradford points...
A person is off ill for a day - it counts towards Bradford points.
So, in my absence database .. I can have multiple absences for people, but some count towards Bradford Points and some do not.
So over in the People database, I have a portal for all absences based on a single relationship from the absence database,
and then i have another layout with a portal, based on a multi criteria relationship from the absences database, (using a TO of Absence) for just pulling in the bradford points... the start dates and ends dates are important, because I need it to calculate a figure for each term, and then for the last 3 terms... BUT only across the records that have the flag they are used for the bradford points...
incidentally, my calculation field is stored in people database using the to of absence with the multi criteria relationship
Sorry if this is a "little long winded" and not the correct wording..
when i go to the absence table - it has all records - some of which are not counted for bradford points
sorry.. pressed the post button by accident
..some of which are not counted for bradford points.... and the calculation doesn't work, or the start dates and end dates either, and i think it is because TO can't be seen in the absence database.. is that right?
So, what can i do, to make this work? - I just need the report to run once a term (every 14 weeks) roughly
Since you only have a single Absence record per "absence spell," I think you could do this from People, in a calculation field, using a filtered relationship to Absences.
So, you need a way to only hit the "Bradford points" counted records, not the non-counted. Just add a criteria to the relationship to only hit those. I can't say exactly what the criteria are, because I don't know exactly what you marked them with. If a "1", ie. a Boolean, then create a constant calculation, =1 in People. If a word, do similar with the word, ie. a calculation field in People, just for this relationship (can be [x] Do not store, to save disk space).
In each Absence record you (hopefully) have a Start Date and End Date for each "absence spell record". If so, create a calculation field, to subtract to get the "days absent" for that record.
Assuming the relationship from People to Absences is:
constant1 =::Bradford mark
gStartDate ≥ Start Date
gEndDate ≤ End Date
The Bradford Absences "count" would be just a Count (relationship::PeopleID)
The # of days of term would be a Sum (relationship::days absent calculation field)
So the Bradford calculation would be the above: count squared * days of term
At least I think so, without trying it.