11 Replies Latest reply on Mar 10, 2015 8:14 AM by ZacherieWalker

    Exclude text if found in a field



      Exclude text if found in a field


      I'm writing a script to evaluate a group of fields and a problem I've been having is excluding certain "phrases" and the text that follows it.  In this case, I'm trying to look for any "NOTES" that could be found in a field and exclude everything that follows it.

      I'm still a bit new to the text parsing and I haven't been able to quite figure it out.  Is this possible or am I just too hopeful thinking?  

      Also, things to consider: The notes could be found anywhere within the fields the script is evaluating.  It's a typical practice to put notes at the end of the field.  All the text before the note is typically important and will be used 95% of the time, given a couple situations where it wouldn't.  

        • 1. Re: Exclude text if found in a field

          The key phrase that I am using as the basis for my response is "exclude everything after it".

          Position ( YourText ; "Notes" ; 1 ; 1 )

          will return the position of the "N" in Notes.

          Left ( yourText ; Position ( yourtext ; "Notes" ; 1 ; 1 ) - 1 )

          will return all text to the left of "N" in notes. You might want to enclose this expression in a Trim function call to remove any trailing space characters between that "n" and the word that immediately precedes it:

          Trim ( Left ( yourText ; Position ( yourtext ; "Notes" ; 1 ; 1 ) - 1 ) )

          • 2. Re: Exclude text if found in a field

            Thank you for your response, Phil.  Appreciated as always!

            Although this works beautiful to how I originally explained it, I just realized a problem with this.  My script variable is set to look through 12 different fields of this report.  What's happening is when it runs into its first Note, it just excludes everything thereafter.  Probably a fault of my own for not realizing that when I originally asked the question, but that's the issue I'm having right now.   

            The notes would be put at the bottom of a field, but when it moves onto to grab the information in the next field, it just excludes it if there's a note in the previous field.  I'm honestly not sure how I would get it to evaluate each field individually, trim out the Note part, and move on to the next field and grab all of the information in there, etc etc.

            • 3. Re: Exclude text if found in a field

              I don't see why this is a problem. your script should be performing this action on one field at a time so the presence or absence of a note in the previous field should have no effect on what happens with the next field.

              • 4. Re: Exclude text if found in a field

                I've set the fields inside a variable and the script is calling out to that variable.  Is this not the right way to do it?  I've several fields in just one section, and I have 22 sections I have to create this for.  So I've just been setting a variable as all of those fields to grab.

                • 5. Re: Exclude text if found in a field

                  Welcome to the world of dealing with text. Others (who have not had to do so) think of it as "easy" :-|

                  Since you want only the last part of the value of each field, then likely you'll need to get that value once at a time. Because otherwise you'd have to know where each "Notes" starts AND where each "Notes" ends. The later is likely almost impossible, unless each "Notes" had a specified "end" (above any other added text).

                  However, I don't think you have to redo the process which gets the "Notes" very differently. I created this script, in a file of my own (which has multiple values in several fields; it does not have an "Notes" values, nor anything similar; so I meanly said where that would go; using something like Phil's {though I might add a little} ).

                  There is one step, which I'd seldom used, but was somewhere in my (illogical) brain. "Evaluate" can allow you to cause several "changed name" variables to evaluate. I named the variables, "$field1", "$field2" (as you can see below). That will solve your multiple fields; but you still have to use them to get the data from each field. 

                  [ This could also be done in a calculation field. Though the above may be less trouble. A calculation doing each (of the 22, etc.) will take longer to do. It would however be kept to date automatically; whereas the script would need to be run again. It kind of depends on what you want this for (and who/what/when).]

                  P.S. Notice that the (critical) $fields_cnt is hardcoded to: 5   You'd have to manually added your number of fields involved! They may be another better (safer) way.

                  • 6. Re: Exclude text if found in a field

                    When I said "Phil's {though I might add a little}". I was messing with your "Notes," and ran into a couple small "text values" which could cause problems. If there is text using the word "notes" in the text, for something other than the "Notes", then it still seems them as that (by mistake). I also thought it should only match "Notes ", i.e., starts the sentence, and was only this [ though it seems there are no words that start "notes" then continues on. So that could ignored. But it seems using "notes" in text for other reasons seems possible].

                    Basically, the Function: Position, is not case-sensitive (whereas Substitute is).

                    [ P.S. Added a Case ( test; otherwise it wouldn't return the value if there are was NO "Notes " (so much for testing :-\ ]

                    Let ( [
                    txt = Substitute ( Text ; ["notes "; "xxxxx "]; ["Notes:"; "Notes "]; ["Notes, "; "Notes  "]);
                    pos_notes = Position ( txt; "Notes "; 1; 1 ) - 1

                    Case ( pos_notes > 0; Trim ( Left ( Text; pos_notes )); Text )


                    • 7. Re: Exclude text if found in a field

                      I added an actual file. You'll have to add more "Notes " to see much more than the 1st two records. You would also have to modify to work with your table and field names. Do not however change the variable(s) names, without changing all instances of change (not many, but the "$field1", "$field2" need to work with the "name & number" routine in the Loop {the number of the loop, being sit as it goes} ).


                      P.S. I added another script to do this. It uses: FieldNames ( Get (FileName) ; Get (LayoutName) ), to get a list of the field names on a layout (with ONLY those fields). It then goes thru them, using GetValue ($field_name; $cnt). It has one other change, which is: Evaluate ( GetValue ( $fields ; $cnt ) ). Notice that it is no longer within quotes.

                      • 8. Re: Exclude text if found in a field

                        It could be possible to load all fields into the same variable and loop through them, but then you'll need to either "delimit" the text from each field with text never found inside any of your fields so that you can adjust the calculation to search only one portion of the variable at a time (separating the values with returns and using the different "value" functions such as GetValue() to access the data from one field at a time is often done.) or you might load a variable's repetitions such that each repetition gets the data from a different field and you then loop through the repetitions to access the data from one field at a time.

                        The main advantage here is that you can generalize a single script to work with different set's of data from different groups of fields simply by loading the variable with data from different sources.

                        • 9. Re: Exclude text if found in a field

                          I just got back to the office, I'll check through all of this and your test file you provided Fenton.  I appreciate all of the response.  I don't imagine we would have any "notes" in a given sentence, other than "noted."

                          I'll have to test it around a bit.  "Noted" could be used but as long as it recognizes the difference, that shouldn't be a problem.  

                          Again, I appreciate all of the help with this!  I'm a newbie to a lot of aspects of FileMaker and text parsing is something incredibly new and over my head at the moment.  I've been trying to play around with it but I'm still utterly lost.

                          • 10. Re: Exclude text if found in a field

                            I actually haven't had a chance to test this as of quite yet.  I apologize.  It has been one crazy day.

                            • 11. Re: Exclude text if found in a field

                              Everything seemed to be working great.  Thank you all for your help. :)