Walter-MainFrame Posted July 28, 2015 Share Posted July 28, 2015 Hi All I Would need some help with this search I have a database a database full off records that are imported from GOLDMINE CRM but the key field look like this IBBFFN9#{!XL:-M IL2FFN9#{!XL:-P IBBFF#N9{!LL:-O I23BFFN9#{!XL:-M IB%FFN9#{!XL:-M And I can´t find a simple way off searching these because it has Operators in the middle. And filemaker always says no record found Please Help Link to comment Share on other sites More sharing options...
Kris M Posted July 28, 2015 Share Posted July 28, 2015 look at the line on how to use the \ escape char http://help.filemaker.com/app/answers/detail/a_id/13715/~/refining-find-requests-in-filemaker-pro-using-find-operators Link to comment Share on other sites More sharing options...
eos Posted July 28, 2015 Share Posted July 28, 2015 Wrap the string into quotes. Link to comment Share on other sites More sharing options...
Walter-MainFrame Posted July 28, 2015 Author Share Posted July 28, 2015 look at the line on how to use the \ escape char http://help.filemaker.com/app/answers/detail/a_id/13715/~/refining-find-requests-in-filemaker-pro-using-find-operators Hi Kris Well I Know about the escape char but the problem is that I dont have a patern off the position off the operators in the id Field also It can have many operators in the same field too on different positions. And if I take them off In the search, I might end Up finding duplicatas because this is a Unique Id for the record. Link to comment Share on other sites More sharing options...
doughemi Posted July 28, 2015 Share Posted July 28, 2015 (edited) You just have to escape the operators with a backslash: Set Field[YourField; "IBBFFN9\#{\!XL:\-M"] You can use the Substitute() function to accomplish this: Substitute("IBBFFN9#{!XL:-M"; ["#"; "\#"]; ["!"; "\!"]; ["-"; "\-"]) Edited July 28, 2015 by doughemi Link to comment Share on other sites More sharing options...
comment Posted July 28, 2015 Share Posted July 28, 2015 (edited) Know about the escape char but the problem is that I dont have a patern off the position off the operators Use the Substitute() function to escape them, as shown in the attached file. Add more substitution pairs as needed. find.fp7 Edited July 28, 2015 by comment Link to comment Share on other sites More sharing options...
Walter-MainFrame Posted July 28, 2015 Author Share Posted July 28, 2015 (edited) Hi EOS In the Script when I try this I get syntax error !! $$OPID = IBBFFN9\#{\!XL:\-M ....... Enter Find Mode Set Field[ID_Field; """ & $$OPID & """] Perform Find How do I write the Set Field with the $$OPID Edited July 28, 2015 by Walter-MainFrame Link to comment Share on other sites More sharing options...
comment Posted July 28, 2015 Share Posted July 28, 2015 Wrap the string into quotes. This is not the best idea, because then the search does not use the index. Link to comment Share on other sites More sharing options...
Walter-MainFrame Posted July 28, 2015 Author Share Posted July 28, 2015 Use the Substitute() function to escape them, as shown in the attached file. Add more substitution pairs as needed. find.fp7 Hi Comment Good Idea But if i have 100.000 records in the database is there a Risk off getting duplicates this is a Unique record since In some cases It is possible to escape many characters in one record isnt this a risk ? Link to comment Share on other sites More sharing options...
comment Posted July 28, 2015 Share Posted July 28, 2015 But if i have 100.000 records in the database is there a Risk off getting duplicates this is a Unique record since In some cases It doesn't matter how many records you have - you're only escaping the search criteria, so that the special symbols are interpreted as characters, not operators. However, there is the possibility of getting duplicates - if you have a record whose key contains a key of another record. To prevent this, use the == exact match operator - i.e. set the searched field to: "==" & Substitute ( Table::gSearchValue ; [ "#" ; "\#" ] ; [ "!" ; "\!" ] ) Link to comment Share on other sites More sharing options...
Walter-MainFrame Posted August 4, 2015 Author Share Posted August 4, 2015 (edited) Hi Comment I Was trying to implement the file you send and it works in most cases but today I Found that it failed when I looked in the debuger it fails with these ids ICUF7MC#\N-?:-M ICU23MC#\P-?:-O IKL20MC##\1-:12 I can´t find a way to add \ this to be a substitution pair. HELP ? Wrap the string into quotes. Hi EOS In the Script when I try this I get syntax error !! $$OPID = IBBFFN9\#{\!XL:\-M ....... Enter Find Mode Set Field[ID_Field; """ & $$OPID & """] Perform Find How do I write the Set Field with the $$OPID Do you think it is possible even if i losse the índex ... Thanks in Advanced Edited August 4, 2015 by Walter-MainFrame Link to comment Share on other sites More sharing options...
eos Posted August 4, 2015 Share Posted August 4, 2015 In the Script when I try this I get syntax error ! $$OPID = IBBFFN9\#{\!XL:\-M ....... Set Field[ID_Field; """ & $$OPID & """] I assume this $$OPID = IBBFFN9\#{\!XL:\-M means Set Variable [ $OPID ; IBBFFN9\#{\!XL:\-M ] which gives you a syntax error because Filemaker does not know the expression IBBFFN9\#{\!XL:\-M (it's not a number, a string, a field reference, a variable reference, a function, a Custom Function …); you need to pass it as a string, i.e. Set Variable [ $OPID ; "IBBFFN9\#{\!XL:\-M" ] which is what I meant when I wrote "wrap the string in quotes". Link to comment Share on other sites More sharing options...
comment Posted August 4, 2015 Share Posted August 4, 2015 I can´t find a way to add \ this to be a substitution pair. [ "\\" ; "\\\\" ] ; Important:Be sure to make this the first substitution pair - otherwise it will also affect the backslashes that the other substitution pairs are adding. Don't forget to add another pair for the ? question mark. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3160 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 accountSign in
Already have an account? Sign in here.
Sign In Now