4 Replies Latest reply on Jun 1, 2012 11:53 AM by philmodjunk

    Finding on a field with optional hyphens

    DavidVallance

      Title

      Finding on a field with optional hyphens

      Post

      I have a database which contains model numbers. Sometimes these model numbers have been entered with hyphens in them and sometimes without. I need to be able to find matching model numbers with and without eg. I want to find both SVT7 and SVT-7 with a single find. Logically I would assume that SVT*7 should work but it doesn't - it only finds the SVT7. Wildcards seem to work for every other variant except the hyphen so what am I doing wrong?

        • 1. Re: Finding on a field with optional hyphens
          philmodjunk

          Interesting....

          I suspect that the fact that the hyphen does double duty as both a hyphen and a minus sign is what is keeping this from working. NOte that your hyphen precedes a numeric digit here. (FileMaker can be a bit weird that way.)

          You may need to use one of these two methods:

          Option 1:

          Define a calculation field that uses Filter to strip out the hyphens.

          Filter(SeriaNumberField ; "ABCDEFGHIJKLMNOPQRSTUVWXY&0123456789")

          for example, will strip out everthing but uppercase letters and the digits 0...9. If you need some additional characters such as the period, you can include them in the quoted text.

          You, or a script, would then perform finds on this field instead of the one that might contain hyphens.

          Note that this option makes it possible to set up a relationship that matches on the "hyphen filtered" value--though I do not recommend using it as a primary key.

          Option 2:

          Perform a find where one request includes the hyphens and one request does not. This can be scripted and controlled with script triggers or a custom menu such that the user never knows that both versions were used as find criteria and it won't matter if the user includes or excludes the hyphens in their entered criteria.

          Note that a predictable pattern of where the hyphens would located when used is needed to make this option practical.

          • 2. Re: Finding on a field with optional hyphens
            Sorbsbuster

            While you're at it, why not strip out (or put in, whichever is Correct Company Policy) the hypens, now, and then add a Validation rule (the same calculation as Phil gives) to stop (or always insert) the hypens?  Then you wouldn't have the search problem.

            • 3. Re: Finding on a field with optional hyphens
              DavidVallance

              Thanks for the helpful replies. Phil's option 1 is the best for my situation. I'm very new to FM and was curious to know whether it was me or the software! Option 2 is not really applicable as the model numbers (in my own workshop jobs database) are very diverse. The example was just one of many possibles. Sorbsbuster's advice is also well heeded - if I hadn't been so slack inputting the data in the first place I wouldn't have had the problem. Actually I am transferring a database from Open Office via Excel files so before I go live I might just have a clean-up at the same time.

              Thanks

              Dave

              • 4. Re: Finding on a field with optional hyphens
                philmodjunk

                It would be pretty easy to clean up this data. Take a look at Replace Field Contents and its calculation option. Do the clean up on a copy of the database so if you mess things up you can toss the copy and try again.