I'm working with a MySQL database hooked up to FileMaker via External SQL Sources. I have layouts that are displaying data from the MySQL database and this is all working just fine.
What I've discovered, though, is that if I do a find on a layout based on MySQL data it doesn't act the way I expect based on finds with actual FM data layouts.
For example, on a MySQL layout let's say I have a field with the following number in it: 1234567
If I do a find for 4567 I get zero results. If I do a find for 123, though, then it does find the record correctly. It seems that as long as I do a find using the first characters in the value then it works, but if I try to skip to the middle or anything without the first characters it always returns zero.
Another example would be something like 0000206063. If I do a find for just 206063 it won't find it. I have to include the 0's at the beginning, but as long as I do, I don't have to use the entire value. I could just use 000020 and it would find it. Using a wildcard like *206063 doesn't work either.
The name Fred will find as expected, but ed will not. The name Jones would come back with a find on Jon, but not with a find on ones.
This is becoming confusing for my client as they don't always know when data is coming from MySQL and when it's not, so when they're trying to perform finds it's causing them to get a little lost in the system.
Is there any way to fix the MySQL layouts so they perform finds the same way other FM data does? Any information on this would be greatly appreciated. Thanks!
I've never found that FM natively finds strings in the middle of words unless I start he search criteria with an *. I almost always use a scripted find and insert the * at the beginning for them. That works in both FM and ESS.