2 Replies Latest reply on Mar 25, 2010 12:30 PM by e0d

    ODBC lookups with criteria?



      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.

        • 1. Re: ODBC lookups with criteria?

          1.  Create an unstored calc in the Student Table.  Formula:  "Current"

          2.  Make another Table Occurrence (TO) of Academic_Year.  (ie - "Academic_Year_Current")

          3.  Make a relationship between the field created in step 1 and the status field in Academic_Year_Current.

          4.  Use an auto-enter field that sets the value to:  Academic_Year_Current::Year


          That should create a relationship that related the year that is marked as current...and then new records will auto-enter the year from that specific TO.

          • 2. Re: ODBC lookups with criteria?

            Thanks that did the trick.