11 Replies Latest reply on Mar 1, 2012 8:18 PM by philmodjunk

    Multiple omits in a find

    EricNetsultants

      Title

      Multiple omits in a find

      Post

      Hello all,

       

      I'm an old FM guy that is re-entering the FM world and dusting off the memory. Some stuff is coming back naturally... other stuff, not so much.

       

      Quick question:

       

      I have a field that has records with text, numbers in text(in other words, the field is a text field and I don't want the text of 1,2,3,4,etc....), and "-". I want to do a find with all records omitting records that include "-" and/or any amount of numbers.

       

      Can I do that in the simple find box or do I need to run a script? Could you show my the syntax......

       

      Thanks

        • 1. Re: Multiple omits in a find
          philmodjunk

          Note: In my answer, I'm assuming that you have other text than just numbers and hyphens in your field and you want to omit all records where a number or a hyphen exist somewhere in the field.

          If the possible numbers present are a small set of possible values such as only single digits 0 through 9 plus the hyphen, yes you can do this with a simple find. If the range of values is much larger than that, I'd add a calculation field that returns a specific value as a "flag" if any of these characters exist in the text field. Then you can omit all records where the Flag field returns a value.

          Here's an example for a find that omits all records with 0, 1 or - somewhere in the text field.

          Enter find mode

          Type in *-*

          Click the Omit button

          Select new request

          Type in *0*

          Click the Omit button

          Select new request

          Type in *1*

          Click the Omit button

          Perform your find.

          Here's a flag field that will return a 1 if any numeric digit or the hyphen are present in the text field:

          If ( text ≠ Filter ( text ; -9876543210 ) ; 1 )

          Perform an Omit find for 1 in this calculation field or perform a regular find with = in that field (to find all records where the field is empty) and you'll find the records you've specified.

          • 2. Re: Multiple omits in a find
            philmodjunk

            Strange what you forget sometimes....

            Enter find mode and specify *#* in one omit request

            Specify *-* in a second omit request

            and this should find the same set of records as specified in your post.

            • 3. Re: Multiple omits in a find
              EricNetsultants

              Thanks Phil,

              You are correct the numbers can be multiple and random in location. Like  e56gggg7532t

              When I do this

              "

              Enter find mode and specify *#* in one omit request

              Specify *-* in a second omit request"

               

              I still get all the records with numbers in the field. I'm not sure if it is because it's actually a text field and not a number field. Either way, it doesn't work.

              • 4. Re: Multiple omits in a find
                EricNetsultants

                Also, I created a "flag" field and this calculation didn't work : If ( text ≠ Filter ( text ; -9876543210 ) ; 1 )

                It set every record as a "1" in the field.

                 

                Also I changed "text" to the field name so : If ( fieldName ≠ Filter ( fieldnName ; -9876543210 ) ; 1 )

                • 5. Re: Multiple omits in a find
                  philmodjunk

                  Did you click the omit button?

                  just did a test:

                  I created the following records:

                  1aa
                  aa4
                  FFF-
                  55aa
                  bbbb

                  If I enter *#* and click the Omit button, then perform a find, I get

                  FFF-
                  bbbb

                  What failed for me was using *-*. That failed to drop out the FFF- record.

                  But if I just used - in one omit request and *#* in the other, I could narrow the found set to just:

                  bbbb

                  But make sure to click the OMIT button for both requests.

                  • 6. Re: Multiple omits in a find
                    EricNetsultants

                    Yep I put omit and then just "#" and it did remove some. It seemed to only remove the records that contained 1 digit not next to another. So, 4ttttt, but 44tttt still remained.

                    If I put "##" then the record with two numbers next to each other were removed but not the single or more than two digit numbers.

                    • 7. Re: Multiple omits in a find
                      philmodjunk

                      Note that one of my test values was 55aa and that value was correctly omitted.

                      Your last post mentions using # and ##.

                      What I am using is *#*

                      The asterisks are wild cards that represent "0 to many" characters of any type.

                      • 8. Re: Multiple omits in a find
                        EricNetsultants

                        Ahh... my bad. I wasn't using the wildcards. I thought you were just highlighting #.

                         

                        That works much better. It removed all the records with numbers.

                         

                        Thanks again.

                         

                        It would be helpful getting that flag field working.

                        My fieldName for that field is "Flag"

                        My fieldName for the field with data to search is "DiscTest"

                        • 9. Re: Multiple omits in a find
                          philmodjunk

                          Already posted. Just have to subsitute your field name for text:

                          If ( DiscTest ≠ Filter ( DiscTest ; -9876543210 ) ; 1 )

                          and it can be simplified to just:

                          DiscTest ≠ Filter ( DiscTest ; -9876543210 )

                          • 10. Re: Multiple omits in a find
                            EricNetsultants

                            Thanks again....

                             

                            I did try it that way and had an issue. I'll try again in the morning and report back if I have issue.

                             

                            I really appreciate the help.

                             

                            -Eric

                            • 11. Re: Multiple omits in a find
                              philmodjunk

                              Using the the two find requests with operators and wild cards should make this calculation field unecessary.