Performing a find on a formatted field
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.