AnsweredAssumed Answered

0 values entered in FMP 10 do not transfer to the SQL table (field remains empty)

Question asked by jerrolyn.brees on Jun 26, 2009
Latest reply on Jun 26, 2009 by jerrolyn.brees


0 values entered in FMP 10 do not transfer to the SQL table (field remains empty)



I have a FileMaker application connecting (through ODBC) to a Sql Server database.  The sql database has several fields of type "money".  The FileMaker interface is designed to let our end users place values in the "money" fields (which are seen in FileMaker as type "Number").




When our end users try to put a '0' into one of these empty fields (i.e., when the fields have a value of NULL), the '0' value is NOT written back to the sql database.  For "number" types (possibly only over ODBC connections), FM treats '0' and NULL as the same value.  I have contacted FM support about this issue and was told that: "this is expected behavior and there is no resolution."


In my application, this is a problem... because '0' and NULL are distinct values and are treated differently in calculations.



Potential Resolutions:

I have come up with a few possible work-arounds; but I was wondering if anyone else knew of a more elegant solution.


1.  Create special scripts triggered by the user clicking a button near the problematic fields for taking care of zero values.  (The script would basically commit some random number to the field and then commit the '0' or NULL in the field depending on which is needed.)

2.  Create a special script for each number field to run "on modify".



Additional Questions:

Does anyone know how to set a number field back to NULL in a script?



Thank you for your help!

- Jerrolyn Brees