14 Replies Latest reply on Dec 10, 2015 7:32 AM by dtcgnet

    How to write a script trigger to change the value of another field

    g4guitar

      I have 2 fields. 1. Name 2. Available.

      Both are on a list layout. When the Name field is empty I want the Available field to say 'Yes'. When the Name field is populated I want the Available field to say 'No'.

      What is the best way to achieve this?

       

      Thank you.

        • 1. Re: How to write a script trigger to change the value of another field
          serval

          I assume here that the “Available” field is only to reflect the status of the “Name” field; that is, you are not going to enter data into the “Available” field.

           

          In File > Manage > Database

          select the field “Available” and click on “Options”.

          Then click “Calculated value” and Specify a formula of

          If ( IsEmpty ( Name ) ; "Yes" ; "No" )

           

          OK to get back to Options

          Click the, “Prohibit modification of value during data entry” checkbox.

          Click off the, "Do not replace existing value of field (if any)"

           

          If you try to change data in the “Available” field, FM will not allow it and will say,

          “This action cannot be performed because the field is not modifiable.”

           

          To prevent that, go to Layout mode, bring up the Inspector, select the Data tab, and in the Behavior section, click Off the Field Entry Browse checkbox.

          Now the Available field be a display-only field; you will not be able to enter it.

           

          No Script Trigger needed.

          • 2. Re: How to write a script trigger to change the value of another field
            erolst

            If you just need an indicator, use two stacked layout text objects and hide them according to the status of the Name field.

            • 3. Re: How to write a script trigger to change the value of another field
              g4guitar

              Thank you serval. I have been away so sorry for the later reply. It looks good but I am using a portal so I guess this is a bit trickier as its not automatically changing. Is there a step I could be missing due to the use of a portal field on the layout keeping in mind that the 'Available' field is not part of the portal?

              • 4. Re: How to write a script trigger to change the value of another field
                serval

                Sorry, but I am a relative beginner myself, and I do not yet know anything about portals, so I can't help you with that.

                • 5. Re: How to write a script trigger to change the value of another field
                  erolst

                  I think you need to explain that a bit more. In your original post you stated that

                  g4guitar wrote:

                  I have 2 fields. 1. Name 2. Available.

                  Both are on a list layout

                  so where and how does that portal come into play?

                   

                  And if you have FM13 or 14, you don't really need a field just to indicate a calculated status.

                  • 6. Re: How to write a script trigger to change the value of another field
                    g4guitar

                    Sorry erolst please forget the portal as I am not using it now. I tried using serval's instructions but nothing happened. I may be missing something

                     

                    Erolst the problem is I currently have 3 choices. 1. Yes 2. No 3. Hide. In other words I want the option to be able to hide the record as well. So I want to be able to select from the Yes/No/Hide list as well as have it automatically say 'No' when their is a name in the Name field or Yes when there is no name. Perhaps separating out the hide would be better. Do you have a recommended solution to this?

                    • 7. Re: How to write a script trigger to change the value of another field
                      dtcgnet

                      The field called Available will be of use to you here and there (to find all Yes, or all No, or all Hide, for instance).

                       

                      If Available and Name are in the same table (and they should be), you want Available to be an "auto-entered calc". Go into Manage Database, and double click on Available. You'll see a window like the below window. This is what serval was referring to. You click on "Calculated value", and you enter a formula that will automatically adjust "Available" based on values in "Name" OR "Available".

                       

                      Case (

                      Available = "Hide" ; Available ;

                      IsEmpty ( Name ) ; "Yes" ;

                      "No"

                      )

                       

                      Uncheck that box that says "Do not replace existing value of field (if any).

                       

                      If Available contains Hide, it will not change when anything happens in the Name field.

                      If Available contains anything else, it will automatically enter "Yes" any time you clear out Name (and data is committed), or it will enter "No" (because if Name isn't empty, then it has data).

                       

                      No need for a script or anything else.

                       

                      Autoenter.tiff

                      • 9. Re: How to write a script trigger to change the value of another field
                        g4guitar

                        Thank you again for this solution but I have another question. I have now set up using portals on my layout. My question is is to do with the Validation below. In my previous setup your solution worked perfectly. When I added a student to an empty class (Student1 field) the Available field would automatically change from 'Yes' to 'No' via the Calculated value script. In the new set up replacing the ( Student1 ) field with the portal field doesn't work. How would I get this to work for a portal? Thank you.

                         

                        Screen Shot 2015-12-10 at 1.23.58 PM.png

                        • 10. Re: How to write a script trigger to change the value of another field
                          dtcgnet

                          First suggestion...uncheck the "Data" option. You are auto-entering "Yes" with that, but since Student1 will default to empty, this will auto-evaluate to "Yes" anyway.

                           

                          But...as to a portal field, that's a different animal. When a field has an auto-enter which references a field in the same table, then changing the referenced field will automatically change the auto-entered value. BUT...that isn't true when the referenced field comes from a related table. And...portals involve related tables. When you change a value in table A, auto-enter values in table B won't automatically change. That's the way it is with auto-enters.

                           

                          So...what are you changing? Are you changing something in the table occurrence the layout is based on, and you want that change to carry through to something in a portal?

                          • 11. Re: How to write a script trigger to change the value of another field
                            g4guitar

                            Okay that makes sense.

                             

                            So my table is called 'Class' and this is where the 'Available' field resides. The portal is based on the 'Contacts' table. The portal is set up to add students to a class. Each student on a new row of the portal. When the class (portal) is empty I would like the 'Available' field to be 'Yes' and when a student is added the Available field to automatically changed to 'No' just like it did in my previous setup.

                             

                            Below is how my timetable looks. Its a list view layout with portals for each student in the class. Each line represents a different class at a different time. The orange 'No' and green 'Yes' is what I want to automatic change when I add a student to the first portal row 'Student 1'. Just to clarify Student 2 is part of the same portal except its displaying row 2.

                             

                            The idea is to automate the Availability setting ('Yes' or 'No') of each class time based on whether there is a student booked in or not. I should add that there is a 3rd option in Available called 'Hide'. This is for hiding the class (record) from the timetable layout.

                             

                            Hope that makes sense and thank you.

                             

                            Screen Shot 2015-12-10 at 2.18.34 PM.png

                            • 12. Re: How to write a script trigger to change the value of another field
                              dtcgnet

                              How do you get to this layout? Via script? You enter this layout and display which records? How many records are there in the Class table?

                               

                              When you enter the layout, somehow I'm guessing you're running a script. Add to that script a "Replace Field Contents" script step. You'll use the "Calculated" option, and the formula would be something like:

                               

                              Case (

                                   Count ( Contacts::IDKeyFromContacts ) > 0 ; "No" ;

                                   "Yes"

                              )

                               

                              Or something like that.

                               

                              As for the Hide option...are you eliminating "Hide" records when you land on the layout?

                              • 13. Re: How to write a script trigger to change the value of another field
                                g4guitar

                                I enter via a script 'Go to layout' and a sort and find. I added the script but nothing happened. It looks like its missing information because I can't see how it would know to change the 'Available' field because there is no mention of it in the script. Sorry still trying to get my head around this one. Thanks again.

                                • 14. Re: How to write a script trigger to change the value of another field
                                  dtcgnet

                                  When your solution runs the script, let it do whatever it's doing: Going to the layout, finding, sorting.

                                   

                                  Then add a statement to your script. Modify things so that the field whose contents will be replaced is "Available", and modify the calculation so that you use the ID field from your Contacts table. Your script will examine every found record, and the result will be an auto-calculation of what you want.

                                   

                                  Replace Content script.tiff