Hard Find

[Walter-MainFrame]

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

 

 

 

[Kris M]

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

[eos]

Wrap the string into quotes.

[Walter-MainFrame]

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.

 

 

 

 

[doughemi]

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

[comment]

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

[Walter-MainFrame]

 

 

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

[comment]

Wrap the string into quotes.

This is not the best idea, because then the search does not use the index.

[Walter-MainFrame]

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 ?

 

 

 

 

[comment]

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 ; 
[ "#" ; "\#" ] ;
[ "!" ; "\!" ] 
)

 

[Walter-MainFrame]

 

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

[eos]

 

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".

[comment]

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.