Hey guys and gals

I have some strange boxes in my data that I have imported from various old sources. When I copy and past the box into a separate text field then create a calculation of Code(field), it says it is number 11. Another one says it is 12. I look up 11 in ASCII and it says it is a vertical tab and 12 is form feed. Can we even make these characters in FM? I assume not and that is why the square. Does square also mean invalid character instead of the normal invalid question mark?

When I test, many other numbers gives squares too. Why does FM recognize some symbols but not others? If I copy and search for the box, no records are found even if it is the only thing in the field I search. I need to remove these boxes and I would really appreciate ideas. Thank you.

David

I need to remove these boxes

Try:

Substitute ( YourField

; [ Char ( 11 ) ; " " ]

; [ Char ( 12 ) ; " " ]

)

Thank you Raybaudi - you've made my day!

I have never seen calculation look like that. Why do you list your calculation in strange way leading with semicolon?

Because it is easy to add new rows ( and so new substitutions ) by simply copy and pasting them.

Okay I guess whatever makes things easier for you. Seems just as easy to copy [ Char ( 11 ) ; " " ] ; though. Your method would be more confusing for me because it is not like everyone else writes calculations. I admit I am new at filemaker so what do I know?

Thanks again.

I admit I am new at filemaker so what do I know?

I am old at it and though that does not necessarily mean that I know better, I agree with you 100%.

Seems just as easy to copy [ Char ( 11 ) ; " " ] ;

Yes, but you have to remember that, for the last row last char, no semicolon is allowed.

And this not only when you write the calculation but also when you have to modify it, deleting or adding rows.

Hi David and welcome to the FM Forums,

Seems just as easy to copy [ Char ( 11 ) ; " " ] ;

I do not disagree with Raybaudi's format however I put the semicolons behind.

Substitute ( YourField ;

[ Char ( 11 ) ; " " ] ;

[ Char ( 12 ) ; " " ]

)

Since this is a learning forum, I wanted to give you some examples of how you can format a larger substitute calculation (which can be applied to other functions and scripts) for better readability. So, here is a snippet of code from a file I have. It probably looked something like this out-of-the-box.

Left(Substitute(Company; ["," ; ""]; ["." ; ""]; [" " ; ""]); 5)  &  Left(Substitute(Address; ["," ; ""]; ["." ; ""]; [" " ; ""]); 4)  & Left(Substitute(Zip; ["," ; ""]; ["." ; ""]; [" " ; ""]); 5)

It was modified for readability and looks like this in the file.

Left(Substitute(Company; ["," ; ""];

["." ; ""];

[" " ; ""]); 5)  &  Left(Substitute(Address; ["," ; ""];

["." ; ""];

[" " ; ""]); 4)  & Left(Substitute(Zip; ["," ; ""];

["." ; ""];´

[" " ; ""]); 5)

[/code



Here is a different look.



[code]

Left (

Substitute ( Company ; [ "," ; "" ] ; [ "." ; "" ] ; [ " " ; "" ] ) ;

5

) &

Left (

Substitute ( Address ; [ "," ; "" ] ; [ "." ; "" ] ; [ " " ; "" ] ) ;

4

) &

Left (

Substitute ( Zip ; [ "," ; "" ] ; [ "." ; "" ] ; [ " " ; "" ] ) ;

5

)

The point being is to make your code as readable as possible. There are some tools that can help you in that endeavor. One of them is using this site. http://www.aptworks.com/tools/index.html

HTH

Lee

Thank you for the examples and link, Lee. They are very helpful!