9 Replies Latest reply on Jul 12, 2012 9:50 AM by singerdf

    Changing Field Value based on auto enter calculation

    singerdf

      I am befuddled on this one. Here is my scenario: I have three fields: On consignment, Sold, Return Date. All start with On Consignment checked. The item either sells and is marked as sold or is returned and the date put in. When either of this things happens, I would like the On Consignment check box to automatically be unchecked. In essence changing its status from Yes to nothing.

       

      I tried the auto enter with a calculation using if statements and lots of iterations in terms of when and where the data can be changed. At one point it would not let me check the Yes box for On Consignment.

       

      I read in a technical note that this may not work or be possible. Just want to make sure that is the case. I have a script I can trigger with a button that does the job, but it is not as elegant.

       

      Thanks for any insights you might provide,

       

      Debbie

        • 1. Re: Changing Field Value based on auto enter calculation
          digital-carpentry

          Would it be easier to just use one field and set it up as a radio button?  then authenticate your "Return Date" field to not allow the field to be empty if this field is set to "Returned"?

          • 2. Re: Changing Field Value based on auto enter calculation
            BruceHerbach

            Debbie,

             

            Are all of the fields in the same table?  If so a straight forward/simpel autoi-enter calculation should work.  If there are in a different table then you need something in the local table to trigger the calculation.  You can add a field and put it in a Let statement.  Then have a script change the value of the new field when ever one of the key tems has changed.  This could be done with an script trigger.

             

            HTH
            Bruce

            • 3. Re: Changing Field Value based on auto enter calculation
              singerdf

              Good Morning, Bruce.

               

              All three fields are in a related table and sit in a portal in the layout in question.  When a new record is created, the item is always marked as on consignment.  Currently I have On consignment set to auto enter a calculated value which says that If Sold = Yes then On consignment  does not equal yes otherwise Yes.  I do not have checked 'Do not replace existing value and I have checked to do not evaluate if all fields are empty. BUT now it will not let me check On consignment to get started.  If I tell it DO NOT replace existing value, I can check On Consignment but when I click sold it does not change the On consignment value.

               

              Sold and On consignment are check boxes with one value:  YES.  If it is not checked then it implies NO.  Return Date is a Date field.

               

              I am not familiar with LET statements and will look that up.

               

              It seems to me this has something to do with the way FM evaluates the calculation in the auto enter.

               

              I have a script that will fire with a button, but the employees want the least amount of room for error(forgetting) to change the status of an item.

               

              Thanks for commenting and I look forward to learning more

               

              Debbie

              • 4. Re: Changing Field Value based on auto enter calculation
                brucewilson

                Hi Debbie,

                 

                If Sold is not Yes then it is empty, so On Consignment does not get evaluated. Uncheck "do not evaluate..." and you should be fine.

                 

                Bruce

                • 5. Re: Changing Field Value based on auto enter calculation
                  singerdf

                  STill does not let me check On consignment!!!

                   

                  This is my sticking point since last night.

                   

                  I have put together some script triggers and that is almost as good.  Struggling with the Date field. 

                   

                  Thanks again,

                   

                  Can't wait to see what I am still doing wrong.

                   

                  Debbie

                  • 6. Re: Changing Field Value based on auto enter calculation
                    thomas_staehli

                    Hi Debbie.

                     

                    How about this:

                     

                    If ( not IsEmpty ( Sold ) or not IsEmpty ( Return_date ) ; "" )

                     

                    Also make sure that the "Prohibit modification of value during data entry" is NOT checked

                    • 7. Re: Changing Field Value based on auto enter calculation
                      singerdf

                      First a Thank You to all who have replied.  The hardest part of this is making sure I provide all of the necessary details.  Seems I was missing a part of my IF stateement.

                       

                      So here is what is working:

                       

                      Calculated Value

                      No check in Prohibit modification

                      No check in Do not evaluate if all referenced fields are empty

                       

                      And the calculation is as follows:

                       

                      If(Sold = "Yes" or Return Date > 0; On consignment =/ "Yes"; "Yes")

                       

                       

                      I had left out the last yes and that seems to have solved my issues.

                       

                      Again,

                       

                      Thank You.  I always learn something when I post.

                       

                      Debbie

                      • 8. Re: Changing Field Value based on auto enter calculation
                        ariley

                        I just read this thread. Glad you got it sorted out. Generally, if you don't have to use calculations, you're better off using scripts and and script triggers to get your desired result.

                         

                        You could send the state of the fields and use an if statement to check for validity. Then set your marker accordingly. All with a script that runs when the field is modified or saved.

                         

                        Hope this helps.

                         

                        Best regards,

                         

                        agnes b. riley . filemaker and web development

                        www.zerobluetech.com

                        T 201-299-6223 (NJ) . 212-842-8830 (NY) . 917-660-7221 (C)

                        FileMaker Certified in 10 and 11

                        • 9. Re: Changing Field Value based on auto enter calculation
                          singerdf

                          Since I am always interested in learning, I have a few questions:

                           

                          1.  What does it mean to set the state of the Field?

                           

                          2.  Why is scripting better than calculations?

                           

                          3.  Can the script trigger be a button?  Do you have some script triggers you like better than others?

                           

                          Thanks for you help.

                           

                          Debbie