4 Replies Latest reply on Dec 13, 2010 6:48 PM by FentonJones

    Muti criteria relationship works...- but a report??

    NicolaClarke

      Title

      Muti criteria relationship works...- but a report??

      Post

      Hi 

      I will try and explain this as simple as possible.

      I have a people database and an attendance database (seperate files - upgraded from version 7) They have a "normal" 1 criteria relationship, based on staff ID code.

      I have then created a TO (in the people side) with a multicriteria relationship, based on a new ID code (it is unique - and i needed it to match only some of the data in the absence side, because not all is relevant) and 2 global fields - search start date, and search end date. (so, a 3 criteria relationship, it will only show info in the formula, if all 3 match)

      Now, in a portal on the people side - this is working fine.. i can use the global fields to find between the two dates, and I then have a calculation that is based on the number of records in the portal field multiplied by itself, and then that figure multiplied by the total number of days that person is off. (bradford points calculation)

      However, where do I then create a report??? I want it to show me the dates specified, but sorted by people, and then perform the same calculation (bradford Points) as above...  I have tried in the people side, absence side (but I don't see the TO, so have created another and it doesn't work.... 

      I am getting lost.. and filemaker grumpy!!! And  My deadline is very close....

      ANY help would be appreciated.

      Thanks in advanced

        • 1. Re: Muti criteria relationship works...- but a report??
          FentonJones

          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.

          • 2. Re: Muti criteria relationship works...- but a report??
            NicolaClarke

            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

            • 3. Re: Muti criteria relationship works...- but a report??
              NicolaClarke

              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

              Thanks again

              • 4. Re: Muti criteria relationship works...- but a report??
                FentonJones

                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:

                PeopleID =::PeopleID
                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.