3 Replies Latest reply on Jun 26, 2009 2:19 PM by jerrolyn.brees

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

    jerrolyn.brees

      Title

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

      Post

      Background: 

      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").

       

       

      Problem:

      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

        • 1. Re: 0 values entered in FMP 10 do not transfer to the SQL table (field remains empty)
          jerrolyn.brees
            

          After several hours of tinkering, I was able to come up with a generic script to fix this problem.

           

           

          • 2. Re: 0 values entered in FMP 10 do not transfer to the SQL table (field remains empty)
            mrvodka
              

            You can set a field to null by using a step Set Field [ Field; "" ]

             

             

            Just as a clarification for those others that may not be following your post...

             

            When using ESS, changing a value in a numeric field in the external source to either zero of null WILL properly change when there is another numeric value in the field when committed.

             

            However, if that field has a value already of zero or null then it seems as the ESS engine does not see that as a change when committing, and thus it remains what the orig value was.

             

            For example, lets say Amount field in the ESS table has an orig amount of 45:

             

            1. Change it from 45 to 0, it will updated to zero

            2. Change it from 45 to null, it will update to null

            3. Change it from 0 to null, it will remain zero

            4. Change it from null to zero, it will remain null

             

             

             

            You could use a script trigger ( OnObjectExit ) that performs an Execute SQL 

            Something like:

             

            If [ IsEmpty ( ESSTable::money ) ]

               Commit Record

               Execute SQL [ ] // UPDATE ESSTable SET money = NULL WHERE recordID = recordID 

            Else If [ ESSTable::money = 0]

               Commit Record 

               Execute SQL [ ] /// UPDATE ESSTable SET money = 0 WHERE recordID = recordID 

            End If

             

             

            I believe that an Execute SQL is a much better option that temporarily setting the field and then to something else.

            • 3. Re: 0 values entered in FMP 10 do not transfer to the SQL table (field remains empty)
              jerrolyn.brees
                

              Thank you for the reply.  I came up with the following script, attached it to the necessary objects via "On Object Modified", and given each object a name. 

               

              It seems to work and is generic so that it can be quickly added to the many fields that I was having this issue with.

               

              ----------------------------------

              Set Variable[$ObjName; Value:Get( ActiveLayoutObjectName )]

              If [GetAsText(Get(ActiveFieldContents)) = "0"]

               Freeze Window

               Set Field[1]

               Commit Records/Requests [No dialog]

               Go to Object [Object Name: $ObjName]

               SetField[0]

               Commit Records/Requests [No dialog]

              Else If [GetAsText(Get(ActiveFieldContents)) = ""]

               Freeze Window

               Set Field[1]

               Commit Records/Requests [No dialog]

               Go to Object [Object Name: $ObjName]

               SetField[GetAsNumber( "" )]

               Commit Records/Requests [No dialog]

              End If