1 2 Previous Next 18 Replies Latest reply on Oct 13, 2009 1:17 PM by philmodjunk

    Script Trigger or Calculated Result ?  Help !

    AAgraphics

      Title

      Script Trigger or Calculated Result ?  Help !

      Post

      Using FileMaker 10 Advanced

       

      Okay, I have multiple fields for Nutrition Facts and one field for Ingredients.

       

      I have another field titled "Ingredients and Nutrition Facts Received" and this field should always be a date.

       

      Once data is entered into the Nutrition Facts and Ingredient fields, I would like the "Ingredients and Nutrition Facts Received" field to be updated with the date that the relevant fields were essentially modified.  Also, the date should only be entered when all of the relevant fields have data entered into them.

       

      Do I use script triggers, and if so what script do I use?

      --(I tried doing this, but the script I used would take me to the "Ingredients and Nutrition Facts" field when enter the data in the other fields.  The solution should not do this.) 

       

      Or do I use a calculated result for the "Ingredients and Nutrition Facts Received" field?

      --I think this would be the best solution, but I have no idea how to write the calculation for this.

      ----I do have a calculation that I found and it is:

      Let (

      trigger = FIELD NAME ALPHA and FIELD NAME BETA and FIELD NAME DELTA

      ;

      Get ( CurrentTimeStamp )

      )

       

      However, there is an issue when I use this script.  If there are default values that are in any one of the fields (FIELD NAME ALPHA, FIELD NAME BETA and FIELD NAME DELTA) whenever a new record is created, it calculates the date the record was created because there are default values in one of the fields.

        • 1. Re: Script Trigger or Calculated Result ?  Help !
          philmodjunk
            

          Design Comment: The way you structured your fields, "I have multiple fields for Nutrition Facts and one field for Ingredients" , seems a bit odd. There is likely a better way to set this up with at least two, maybe more tables.

           

          Sticking to your specific question, the last statement you make is the key issue:

          "However, there is an issue when I use this script.  If there are default values that are in any one of the fields (FIELD NAME ALPHA, FIELD NAME BETA and FIELD NAME DELTA) whenever a new record is created, it calculates the date the record was created because there are default values in one of the fields."

           

          You need to be able to set up a method that clearly identifies whether the data has been completely entered or not. Until you do so, this is an impossible task. Once you have this part figured out, you can easy set up an auto-enter calculation to capture the current date.

           

          One "if all else fails" possibility: Add a button to your layout, title it "Submit" or some such. Set it up to enter the Current Date in to your field when it is clicked. Then the user rather than the database determines when a particular record is "complete".

          • 2. Re: Script Trigger or Calculated Result ?  Help !
            casey777
              

            AAgraphics, I would first use a related table for Nutrition Facts and one for Ingredients, not fields. The Date Received fields in those tables then would get their dates whenever selected fields in each related table contain information and are triggered by an onObjectExit trigger script which would check chosen fields to see which are not empty. When all fields are not empty, a Get(CurrentDate) would be set for the date field. Using a related table for Ingredients would also allow you to have more than one set of ingredients. You should then show them, one or more, on your main layout using a portal showing the date received and ingredients, etc.

             

             

            • 3. Re: Script Trigger or Calculated Result ?  Help !
              AAgraphics
                

              I received this formula: 

               

              Let ( trigger = List ( Vendor 1 email ; Quote Body ; Quote From email ) ; Case ( ValueCount ( trigger ) = 3;Get ( CurrentTimeStamp );Self))

               

              It seems to work.  What do you think?

              • 4. Re: Script Trigger or Calculated Result ?  Help !
                philmodjunk
                   Given the lack of detail in your original post, I am unable to share any opinion as the connections between your original description and this example aren't clear.
                • 5. Re: Script Trigger or Calculated Result ?  Help !
                  AAgraphics
                    

                  Okay, here is a better description of what I am trying to do.  Below is an image of the ingredients and nutrition facts layout:

                   

                  Ingredients and Nutrition Facts 

                   

                  The part of the image that has clouds is not relevant.  Please note, when any new record is entered, the ingredients field has the line of text you see automatically entered into it.  So, when these fields are filled we want the date to be automatically entered here (see blue oval):

                   

                  Critical Dates 

                   

                  On a related note, see the date above "Files Sent to Vendor"?  Well, how can we get that date to be automatically entered when a certain value is selected from a value list?  See "Stage" field below.  When "Production Files Sent to Vendor" is selected from the value list, we want the date "Files Sent to Vendor" to be automatically entered.  As the stage field changes, we do not want the "Files Sent to Vendor" date to continuously change.  Does this make sense?  Do the images help?  I hope this does a better job of explaining what we are trying to do.  Sorry if I frustrated anyone.  Thanks!

                   

                  Stage 

                  • 6. Re: Script Trigger or Calculated Result ?  Help !
                    philmodjunk
                      

                    First the basic expression as an auto-entered calculation for when all required could be adapted to auto-enter a date for ingredient and nutrition facts received. It might look like this:

                     

                    Let ( trigger = List ( Nutrition Field 1; Nutrition Field 2; Nutrition Field 3 //list each nutrition field// ; If (IngredientsField = "Please enter ingredients in all caps"; "" ; IngredientsField) ) ; Case ( ValueCount ( trigger ) = //Put total number of fields here// ;Get ( CurrentTimeStamp );Self))

                     

                    For the second date field, Files Sent to Vendor, I'd use a script trigger set on the On Object Exit event for your combo box field.

                     

                    If [YourTable::ComboBoxField = "Production Files Sent to Vendor" AND IsEmpty(yourtable::FilesSentToVendorField)]

                       Set Field [YourTable::FilesSentToVendorField; Get (CurrentDate) ]

                    End IF

                     

                    Will record the current date the first time the text is selected from the combo box.

                     

                    You can also use the auto-enter calculation: Upper(self) (Be sure to clear the do not replace existing value option.) to force typed in ingredients to be upper case even if the user enters lower case text.

                     

                    On a design note. I'd create a related ingredients table and replace the multi-line text field with a portal displaying the ingredients (one per row) in this portal. That would give you better reporting options and you might even be able to compute the nutritional data if you wanted to.

                     

                    Edit Note: added semi colon after Trigger = List

                    • 7. Re: Script Trigger or Calculated Result ?  Help !
                      AAgraphics
                        

                      Thank you, thank you! 

                       

                      When I enter the first formula I receive an error that says "The Specified Field Cannot be Found" and then it highlights the last appearance of the word "trigger" that appears in the part:

                       

                      ... ValueCount ( trigger ) = ...

                       

                      Any idea why that is happening? 

                      • 8. Re: Script Trigger or Calculated Result ?  Help !
                        philmodjunk
                           There was a missing semi-colon in the expression. I've updated the post to fix it. It should read: Let (trigger = list;
                        • 9. Re: Script Trigger or Calculated Result ?  Help !
                          AAgraphics
                            

                          I added the semicolon exactly where you indicated, but I am getting the same error and it is highlighting the word "list" that is right before the semicolon.  Any ideas?

                           

                          Also, I entered the second script and the script trigger and that did not work.  The date is never updated.  Any thoughts on that one? 

                          • 10. Re: Script Trigger or Calculated Result ?  Help !
                            philmodjunk
                              

                            What version of filemaker are you using?

                             

                            If not FMP 9 or 10, enter FMP's help and look up the list function to see if it exists in your version of fmp. If it doesn't, you'll need to use a different approach.

                            • 11. Re: Script Trigger or Calculated Result ?  Help !
                              philmodjunk
                                

                              AAgraphics wrote:

                              I added the semicolon exactly where you indicated, but I am getting the same error and it is highlighting the word "list" that is right before the semicolon.  Any ideas?

                               

                              Also, I entered the second script and the script trigger and that did not work.  The date is never updated.  Any thoughts on that one? 


                               

                              I had an error there.

                               

                              use: If [YourTable::ComboBoxField = "Production Files Sent to Vendor" AND IsEmpty(yourtable::FilesSentToVendorField)]


                              • 12. Re: Script Trigger or Calculated Result ?  Help !
                                AAgraphics
                                  

                                I am using FMP 10.

                                 

                                Any thoughts about the nutrition facts table date and the other script trigger?

                                • 13. Re: Script Trigger or Calculated Result ?  Help !
                                  philmodjunk
                                    

                                  It's not a script trigger, it's an auto-entered calculation.

                                   

                                  Let's check the exact syntax of your expression. Paste it into your next post.

                                  • 14. Re: Script Trigger or Calculated Result ?  Help !
                                    AAgraphics
                                      

                                    SCRIPT TRIGGER NOW WORKS!!!

                                     

                                    Thank you!

                                     

                                    Now, I just have the issue with the ingredients and nutrition facts updating the date. 

                                    1 2 Previous Next