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.
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?
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.
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.
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?