3 Replies Latest reply on Mar 28, 2010 12:03 AM by comment_1

    Performing a find on a formatted field

    RickWhitelaw

      Title

      Performing a find on a formatted field

      Post

      In my solution I need to store GST and S.I.N. numbers (Canada). To ease data entry for the user I use a custom function on a number field. Let's say GST . . . always a nine-digit number. 

      I want it to show up as "123 456 789" whether or not the user enters the spaces. So I use the function (as auto-enter):

       

      Left ( Filter ( Input; "0123456789" ); 3)&" "&Middle (Filter ( Input ; "0123456789" ) ; 4; 3)&" "&Right ( Filter ( Input ; "0123456789" ); 3 )

       

      I call the function with (self) as the input. So: "SIN_GST_FORMAT(self)"

       

      This works fine. However if I perform a find on "123 456 789" (with the spaces) the find comes up blank. If I perform a find on "123456789" all goes as expected. This leads me to believe that the field, despite the auto-enter working as expected, is being indexed without the spaces. I know there's a simple work around by scripting the find while using the Substitute function (hardly worth it) but I found this a bit odd. The auto-formatting is only operating on the display of the field. Perhaps I should be storing it as a text field.

       

      Ideas?