Finding Text with periods and slashes

[APost]

I'm trying to export stock and options symbols to excel where I can lookup current values and then update values in filemaker. I've got it working pretty well accept for the options symbols.

An example options symbol is .ZUMZ\11E21\32.5 I have yet to do a successfull search for this value. I've even tried going to the record, copying the value, entering find mode, pasting the value right back in the field it just came out of, and performing the find. I always get "No records match this find criteria".

I'm assuming it's the presence of the "\"'s. However I have to have them in there in order to find the current values in excel.

Do I have to do some weird thing like parse the string and find records that have .ZUMZ AND 11E21 AND 32.5 or is there something I don't know about?

[Raybaudi]

Try to double each back-slash

.ZUMZ\\11E21\\32.5

[APost]

Yes and no. Using the \\ still doesn't work in a simple find, but it will work in a "Position" Script Step where a single \ won't. If I try to have the "Position" function search for "\" I'm just told "This text constant does not end with a quotation mark" when it clearly does.

I've tried Position( $Symb;"\";0;1) Filemaker won't accept the arguments

Position( $Symb;"\*\";0;1) to see if I can find the start position of a block of text, but it won't accept that either

Position( $Symb;"*\*";0;1) but it just returns Zero

Position( $Symb;"?\?";0;1) But Filemaker won't accept that

Position( $Symb;"?\*";0;1) Returns zero

If I replace the \ with a \\ then I have to run a search and replace before I put it out to excel and when I bring it back in before I try to use the updated values.

All things considered, it looks like that's the only solution thus far. Thanks for the idea.

If anyone has a better one, I'm keen to try it as well.

Thanks thrill seekers!

[comment]

I am afraid you have missed Daniele's point: you don't need to change your data; you only need to escape the backslashes when entering the criteria in Find mode.

See:

http://www.filemaker.com/11help/html/find_sort.5.6.html#1027771

I don't see what the Position() function has to do with it.

[Russell Barlow]

Yes and no. Using the \\ still doesn't work in a simple find, but it will work in a "Position" Script Step where a single \ won't. If I try to have the "Position" function search for "\" I'm just told "This text constant does not end with a quotation mark" when it clearly does.

I've tried Position( $Symb;"\";0;1) Filemaker won't accept the arguments

Position( $Symb;"\*\";0;1) to see if I can find the start position of a block of text, but it won't accept that either

Position( $Symb;"*\*";0;1) but it just returns Zero

Position( $Symb;"?\?";0;1) But Filemaker won't accept that

Position( $Symb;"?\*";0;1) Returns zero

If I replace the \ with a \\ then I have to run a search and replace before I put it out to excel and when I bring it back in before I try to use the updated values.

All things considered, it looks like that's the only solution thus far. Thanks for the idea.

If anyone has a better one, I'm keen to try it as well.

Thanks thrill seekers!

Left Side Info:

Left(Field1 ; Position(Field1 ; "\" ; 1 ; 1)-1)

Middle Info:

Let ( [

start = Position (Field1 ; "\" ; 1 ; 1 ) + Length ( "\" ) ;

end = Position ( Field1 ; "\" ; start ; 1 )

] ;

Middle (Field1 ; start ; end - start )

)

Right Side Info:

Right (Field1; Length(Field1) - (Position(Field1; "\"; 1; 2))

The above would parse out the info from your combined field if that would make searching easier for you to have the information in its own fields.

[APost]

I am afraid you have missed Daniele's point: you don't need to change your data; you only need to escape the backslashes when entering the criteria in Find mode.

See:

http://www.filemaker.com/11help/html/find_sort.5.6.html#1027771

I don't see what the Position() function has to do with it.

[bruceR]

Yes and no. Using the \\ still doesn't work in a simple find

Yes it does.

FindSlash.fp7.zip

[APost]

Left Side Info:

Left(Field1 ; Position(Field1 ; "\" ; 1 ; 1)-1)

Middle Info:

Let ( [

start = Position (Field1 ; "\" ; 1 ; 1 ) + Length ( "\" ) ;

end = Position ( Field1 ; "\" ; start ; 1 )

] ;

Middle (Field1 ; start ; end - start )

)

Right Side Info:

Right (Field1; Length(Field1) - (Position(Field1; "\"; 1; 2))

The above would parse out the info from your combined field if that would make searching easier for you to have the information in its own fields.

Thank you, I now see Daniele's point. I'm trying to have my script find a value formated like .ABCD\12E3\45.6 The \'s are part of the string, and unfortunately, they are also a special character that Filemaker uses to find other special characters.

If I just search using that value, Filemaker will not find anything. I now understand that Daniele suggested that I edit the value in the field when I'm trying to find it, and add the extra \'s. That works great (thank you).

And that brings me to the reason for the Position statements, as you see in the other reply. I'm trying to find a way to do this without manually entering the extra \'s.

I was trying to parse the string into three separate values like the formulas above, so that I could search for a string that had .ABCD AND 12E3 AND 45.6 However when I try the formulas above the "Specify Calculation" window returns "This text constant does not end with a quotation mark"

The calculation I am trying to enter is

Left(Legs::Symb ; Position(legs::Symb ; "\" ; 1 ; 1)-1)

The text that filemaker highlights with the 'does not end with a quotation mark' warning is

"\" ; 1 ; 1)-1)

[Russell Barlow]

Thank you, I now see Daniele's point. I'm trying to have my script find a value formated like .ABCD\12E3\45.6 The \'s are part of the string, and unfortunately, they are also a special character that Filemaker uses to find other special characters.

If I just search using that value, Filemaker will not find anything. I now understand that Daniele suggested that I edit the value in the field when I'm trying to find it, and add the extra \'s. That works great (thank you).

And that brings me to the reason for the Position statements, as you see in the other reply. I'm trying to find a way to do this without manually entering the extra \'s.

I was trying to parse the string into three separate values like the formulas above, so that I could search for a string that had .ABCD AND 12E3 AND 45.6 However when I try the formulas above the "Specify Calculation" window returns "This text constant does not end with a quotation mark"

The calculation I am trying to enter is

Left(Legs::Symb ; Position(legs::Symb ; "\" ; 1 ; 1)-1)

The text that filemaker highlights with the 'does not end with a quotation mark' warning is

"\" ; 1 ; 1)-1)

You probably have to escape that as well. I stay away from using an escape character for any type of data separator.

The formula above that I posted works with the "-" and "/" characters as a separator.

[APost]

Yes it does.

FindSlash.fp7.zip

Sorry, I mean using the single \ in a simple find isn't working

[bruceR]

Sorry, I mean using the single \ in a simple find isn't working

That is correct. It won't work. You need to script the substitutions.

[APost]

Aha.

Now I've grasped the escape character. If I change the calculation from

Left(Field1 ; Position(Field1 ; "\" ; 1 ; 1)-1)

to

Left(Field1 ; Position(Field1 ; "\\" ; 1 ; 1)-1)

it works like a charm. I think I'm on the road now.

Thanks for your help and patience.

[APost]

As an aside. The other thought was to add an extra \ to the string so the script could find it, update the values associated, then have it take out the extras so they'd work in excel for the next round of updates.

Edit>find/replace will do the trick....if you don't mind doing it by hand. But I ran into the same problem trying to script the step; it just doesn't like the single \.

But thanks to you-all's help, there's a solution. Thanks again.