4 Replies Latest reply on Jul 14, 2013 5:24 PM by RobertRichmond

    Working out a school term



      Working out a school term



           I have a fikemaker database, where I need to work out which term it is in one of my Tables using a calculation. One table - called "Term Dates"  has the fields: Record ID (Unique field), Year, Term, Start Date and Finish Date.  These details are entered into this table at the begginning of each school year. Each year has only four terms. Each term dates do not overlap.

           In another table (not related) I have the Unique ID, Student ID, Year, Term and Date Recorded. I am easly able to calculate the Year by using the "Year (Date Recorded)" calculation. What I am not sure how to do is to calculate what the term is?

           I thought using the "Case" calculation might work, but just can't see how to get it formualted. I essentially want to take the "Date Recorded" field, and see which date range it falls between inside the "Term Dates" table. That is which row the "Date Recorded" field falls between the "Start Date" and "Finish Date" fields. Once it finds this, I want it to provide the Term value (which is in the same row) back as the calculation result. 

           If the date does not fall between any of the dates, I would like it to return the value 0 (which just tells me it didn't find anything) The  Term values it returns are 1, 2, 3 or 4 as we have four terms a year.

           Any help would be appreciated.

        • 1. Re: Working out a school term

               Use a relationship with inequalities or use ExecuteSQL() if you have FIleMaker 12.

               OtherTable::Date Recorded > Term Dates|ByDate::Start Date AND
               OtherTable::Date Redorded < Term Dates|ByDate::Finish Date

               I've set this up with a new occurrence of Term Dates as this relationship should be used only to look up the record ID (assuming that this field auto-enters a serial number or Get ( UUID )) and then all other parts of your system should then use a relationship to Term Dates that link by the ID numbers as relationship based on inequalities can be slow.

          • 2. Re: Working out a school term

                 Ok.  So if I understand correctly, I have changed the value

                 "Homework Support with Work Variations::Term" to
                 "Homework Support with Work Variations::Record ID from Term Dates Table"  and have adjusted it so it is now a calculation instead of text.

                 The calculation is  (Date Recorded > Term Dates::Start Date and Date Recorded < Term Dates::Finish Date) .

                 I am hoping that this returns the auto entered serial number field "Term Dates::Record ID"  for the record that conforms tho this requirement -  (? Can't see how it does this though ?) I also wanted to return a 0 if it cannot find the date range. Not sure how to get it to do this?

            • 3. Re: Working out a school term

                   It's not a calculation. It's a relationship that uses the inequality operator instead of the default = operator. You set this up in Manage | Database | Relationships.

              • 4. Re: Working out a school term

                     Thanks Phil.

                     I am glad you are patient with those of us who are learning! ;-> .  

                     So each record in "Homework Support with Work Vatiations::Record ID from Term Dates Table" should (assuming the date range has been entered correctly with no overlaps - up to the user) only see one record in the "Term Dates" table this way.  

                     Very smart! 

                     I suppose I could always adjust the rules which allow dates to be entered into the the "Term Dates" table so no overlapping ranges could be entered?