7 Replies Latest reply on Feb 8, 2013 11:38 AM by mif

    Auto enter calculation in SQL field not triggering.

    mif

      I have two tables,

       

      One ESS SQL table and one Filemaker table related by "shop_product_item" ID. SQL Table A is set to "allow creation of records...." in FM Table B

       

      Table A. name = "shop_product_item" (SQL Ess connection)

      Field A1 - "is_published"

      (Auto enter calc - "Case (shop_product_item::in_stock = 0 and shop_product_item15_ShopProductItem_ID::z_unpublishAtZero = 1;0)"

       

      Field A2 - "in_stock"

       

      Table B. name = "ShopProductItem" (Filemaker table)

      Field B1 - "z_unpublishAtZero

       

      Problem - when SQL Table A > Field 2 "in_stock", changes to zero, Field

      A1 -"is_published", doesn't change to "0"

       

      If I change "A2" (in_stock) to "0" manually, "A1" changes to "0"

       

       

      How do I trigger the calculation in "A1" when SQL changes "A2" to zero?

        • 1. Re: Auto enter calculation in SQL field not triggering.
          BruceHerbach

          Put a let statement into the formula of A1 that connects the two fields.  It would look something like this.

           

          Let(

           

          A2value = TableA::A1;

           

          (Auto enter calc - "Case (shop_product_item::in_stock = 0 and shop_product_item15_ShopProductItem_ID::z_unpublishAtZero = 1;0)" )

           

          This willtell FileMaker that field A1 is dependant on the value of field A2.  So anytime A2 is changed A1 will recalculate.  As an auto enter calc, this required that both fields are in the same table.  If there were in different tables it would have to be an Unstored Calc.

           

          Bruce

          • 2. Re: Auto enter calculation in SQL field not triggering.
            Oliver_Reid

            Filemaker auto calcs only trigger when a change is made IN filemaker. You can setup a Trigger in SQL to do the same thing so if a SQL user makes the change the fields is updated, (Best way)

             

            Or run a FM scheduled script to run through table every 30 min (say) and reset the values.

             

            Possibly,for each layout that refrences the tbale you could add an "On record load" triggered script that checks and corrects the value.

             

            OR

             

            If you only need to view Field A2 in FM, you could simply make it an unstored calc. Then it will also chnage also if B1 changes, which presumably is needed.

            • 3. Re: Auto enter calculation in SQL field not triggering.
              mif

              Bruce,  thank you very much.  That part worked great.  This has lead me to one more issue.  The SQL schema requires that an "is_published" field in a sister table (Table C) based on a one to one relationship also be tagged as unpublished.  Using your advice of requiring an unstored calc, 'if it were in different tables", I was able to create an is_publishedTemp field whose value also changes along with the "is_published" in the original Table A.

               

              Here is what I have left to do.

               

              Table C = shop_product

               

              Field 1 = "is_published" (SQL native number field set to auto enter calc as follows:

              Let (

              [is_published = is_publishedTemp];

              Case

              (

              is_published = 0; 0; is_published = 1; 1;

              )

              )

               

              Field 2 = "is_publishedTemp" (Filemaker calc field)

              Let (

              is_published = shop_product1_SHOP_PRODUCT_ITEM_shop_productID::in_stock;

              Case (shop_product1_SHOP_PRODUCT_ITEM_shop_productID::in_stock = 0 and shop_product1c_shop_product_item_ShopProductItem::z_unpublishAtZero = 1;0;1)

              )

               

               

              Field 2 works great and changes along with "is_published" field in Table A

               

              THE QUESTION IS - How do I get Field 2 to trigger Field 1 to change to the same value?  The above calc  for Field 1, is one of many I have tried, with no luck.  What am I missing?

               

              Thanks again,

               

              Dave

              • 4. Re: Auto enter calculation in SQL field not triggering.
                BruceHerbach

                Dave,

                 

                Are field 1 and field 2 in the same table?  If so add field 2 to the let statement in field 1.  That creates a dependancy.  So field one could look like this:

                 

                Field 1 = "is_published" (SQL native number field set to auto enter calc as follows:

                Let ([

                f2 = Field 2;  // dependancy added here...

                is_published = is_publishedTemp ];

                Case

                (

                is_published = 0; 0; is_published = 1; 1;

                ))

                 

                If field 2 is in a different table then you need to come up with another method of setting the value.  One method would be to use a script and a script trigger that set the fields.

                 

                Bruce

                • 5. Re: Auto enter calculation in SQL field not triggering.
                  mif

                  HI Bruce,

                   

                  I not 100% clear on that.  Yes Field1 and Field 2 are in table C, so I have added what you suggested, but I must not be interpreting it correctly.  Here is what I now have in Field 1:

                   

                  Let (

                   

                  [f2 = is_publishedTemp; is_published = is_publishedTemp];

                   

                  Case

                  (

                  is_published = 0; 0; is_published = 1; 1;

                  )

                   

                  )

                  _______________________________________________________________

                   

                  Here is another description of everything, without labels.

                   

                  The table I am in is "shop_product"

                  It houses the two fields we are dealing with: "is_publshed" and "is_publishedTemp"

                   

                  "is_publishedTemp" uses a calculation based on what you showed me for the first challenge ( calcuation is described in Field 2, in previous post) This succesfully changes dynamically to the value of the "is_published" field in the other table called "shop_product_item".   I thought I could reference it through an auto enter calcualtion set in the field "is_published" field since it is now in the same table (shop_product)

                  My whole reason for doing this is because I am at the mercy of the SQL database changing the in_stock value internally and I couldn't get a script to trigger it.

                   

                  Thanks again for your help.

                  • 6. Re: Auto enter calculation in SQL field not triggering.
                    BruceHerbach

                    Hi

                     

                    Possibly you should look at the work flow and when/how you are using the information.  Possibly a script triggered by a button or script trigger can update the field when you need it.  The key issue ( I think ) is the two tables.  Only an unstored calculation will automaticly update a field in Table A from a field in Table B.  You can use a stored calc or an auto enter calc and have them update automaticly if both fields are in the same table.

                     

                    I'm not sure what you layouts look like,  but possibly an OnRecordload triggered script can update the field for you. 

                     

                    If you want some additional help with this please feel free to contact me.

                     

                    Bruce

                    • 7. Re: Auto enter calculation in SQL field not triggering.
                      mif

                      Hi Bruce,

                       

                      Yes the is_publishedTemp is an unstoired calc and in the same table and as you say, it does update here, triggered from another table. The is_published is the goal.  It needs to be triggered as soon as a user on the website purchases the last product, so the item doesn't show up anymore. I don't think I can rely on a script becuase it wouldn't be timely.

                       

                      Here are the fields in the "shop_product" table

                       

                       

                        Because they are in the same table, shouldn't some type of Auto-enter Calc work in the  "is_published" field to get it to mirror the is_publishedTemp field value?

                       

                      The other thing worth noting is that this calc is working in the data viewe immediately, and when I force a window refresh (both flushes checked), the "is_publishedTemp" updates, but not the "is_published".

                       

                       

                      Let (

                       

                      [f2 = shop_product::is_publishedTemp;  is_published = shop_product::is_publishedTemp];

                       

                      Case

                      (

                      shop_product::is_published = 0; 0; shop_product::is_published = 1; 1;

                      )

                       

                      )

                       

                       

                      Message was edited by: mif - added image