ODBC lookups with criteria?
I'm a FM noob. I'm using FM10 for now, but will upgrade to FM11 soon.
I'm sure I'm missing something here, but I cannot quite figure out how to do something fundamental.
I have a database with a simple relationship.
Student -> Academic_Year.
In the Academic_Year table I want to have a column "current" that specifies whether that one year is the current one. Only one record would be flagged current.
This field cannot be part of the relation because the current year will change over time. I want to be able to change the year flagged current and have it used as the default when new records are created without hard coding it or storing the value in multiple places.
I'm looking for something simpler and more elegant than having a script that runs SQL to set a value in a local table.
Any suggestions will be appreciated.