Jump to content

Finding Text with periods and slashes


APost
 Share

This topic is 4385 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4385 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.