8 Replies Latest reply on Sep 23, 2009 12:34 PM by BMarot

    GetField Question



      GetField Question


      I am trying to create a script that will check if a field contains a certain string of text (in this case I'm specifically looking for a name) and if it does contain that specific string then the script will replace that text with something else.


      As an example.  Let's say there is a field called ExperimentLeader.  I know that there are going to be 4 possible entries into this field (Mr. Smith, Mr. Jones, Mrs. Johnson and Ms. Wood) and there will be multiple records for each of these contacts.  However, there will be mutiple different people entering the data and they may enter things like John Smith, K. Jones, Wood, etc.  I want the script to be able to read what has been entered into the field and notice the last names.  If the script sees "Wood" then it will automatically output "Ms. Joanna Wood".


      I have tried to create this script and this is what I've come up with:


      If [GetField ("ExperimentLeader") = "Wood"]    Set Field [table::ExperimentLeader; "Ms. Joanna Wood"] End If

      This works if the text entered into the field is "Wood", but does not return the desired result if there is anything else in the field.  Is there any way for me to modify this so that as long as the field *contains* "Wood" then the result will be "Ms. Joanna Wood"?


      I am using FM Pro 10 on Mac OS Leopard and I have just started using FM.


      I know it's possible to create an unmodifyable value list and use that for validation.  However, if I import anything from a tab delimited text file (which I plan on doing quite often) it seems the imported records are not required to match the value list.  I thought a script running on an OnObjectModify script trigger would be able to do the trick.



        • 1. Re: GetField Question

          If [RightWords ( table::ExperimentLeader ; 1 ) = "Wood"]
             Set Field [table::ExperimentLeader; "Ms. Joanna Wood"]
          End If


          May do the job.


          A script trigger won't "trigger" during an import. It only kicks in when a user interacts with the triggered layout object.


          I'd set up a script either as part of the import or to run immediately after that looped through each newly imported record and updated each field where this is an issue.



          • 2. Re: GetField Question

            That is working better than the script that I made.  However, if someone were to enter "JWood" the script doesn't catch that.


            Edit:  There must be a way to generally search the contents of the field.  Is there any way to use the funtionality of this symbol: "*"?  Similar to the way I could use it in searches.


            How do you set up a script as part of an import?

            • 3. Re: GetField Question

              It'll be difficult to catch every permutation.  Right ( table::ExperimentLeader, 4 ) = "wood"  will catch the JWood example you give.


              Instead of making a script as part of your import, make the import part of your script:


              Import Records // specify the options you need for your import here

              Go To Record [first]


                # add code Clean up your fields here

                Go to Record [ next, exit after last ]

              End Loop


              One quick way to check for similar entries that may have slipped through:


              Define a value list that specifies table::ExperimentLeader as it's source of values.

              Use field setup... to format table::ExperimentLeader with a drop down menu that uses this value list.

              Enter Browse mode and drop down the menu. You'll see one copy of every value that is stored in that field.

              If you see something that has leaked past your script (such as "JWood"), enter find mode and select that value. Now use Replace Field Contents or Find/Replace to correct them.

              • 4. Re: GetField Question

                Thanks for your help.  I know for a fact that many of the imports will result in a value in the "ExperimentLeader" field that looks like "345098JWood_RS409".  Which is why I was hoping that there was some way to just generically identify a specific string of text.  Since it looks like that isn't something that can be done in FileMaker, it will be impossible for me to catch almost all of the possible permutations.


                I just wish it was possible to use * in a script in the same way you would for searches.

                • 5. Re: GetField Question

                  You can also use PatternCount. PatternCount ( table::experimentLeader ; "wood" ) > 0 will identify any record where the given text contains "wood" anywhere within the field.


                  You can use * in a scripted find.


                  Enter Find Mode []

                  Set field [ table::ExperimentLeader ; "*Wood*" ]

                  Perform find []


                  will find all records with Wood, somewhere in the ExperimentLeader field.

                  • 6. Re: GetField Question



                    If [PatternCount ( table::experimentLeader ; "wood" ) > 0]

                       SetField ( table::experimentLeader ; "Ms. Joanna Wood" )

                    End If


                    Works perfectly.


                    Thanks for taking the time to help me out!

                    • 7. Re: GetField Question

                      Works perfectly as long as you don't get entries such as


                      Mr. Woodpecker


                      Joanna wod


                      It's pretty much impossible to trap for all possible data entry errors. If you can control the user's input options with a drop down menu or some such feature in whatever application is generating the file you are importing, life will be much simpler.

                      • 8. Re: GetField Question

                        True, I can see how that would cause some problems.  However, there is only a list of 10 or so names that will ever be entered into this field.  None of these names are similar and it is highly unlikely that new names will be necessary in the future.  I think, for my application, this works perfectly.


                        With respect to data entry errors, I will use a drop down list for human input.  The imports are computer generated files that automatically insert characters on either side of the name (for some unknown reason).  I needed to figure this out so I don't have to manually go through and fix hundreds of records just so they are readable.