There is a way to bind the two pieces of the single name together using ctrl-space instead of regular space. However, it causes more grief than it is worth because 1) you cannot see that it is bound this way, 2) it breaks when trying to use calculations such as RightWords () (which would see both words then as one). You can search this invisble Code(16) by searching for *ctrl-space* for removal if needed.
It can also mess with your value lists since FM sees the first entry and replicates it (including the ctrl-space). I don't find it worth using but you may. I just want you to be aware of the potential issues with it. A good point is that it DOES work in relationships although if the 'bound' word is on the child side (and it is set to unicode), the relationship will break even if the capitalization is same). Exact() will not see Van(space)Doren and Van(ctrl-space)Doren as exact either. Code() recognizes the difference.
UPDATE: BTW, this is referenced in Help as:
You can enter two or more words in a field and have them treated as one word by the index. For example, you may want a name like Jean Louis to appear as one word in the View Index dialog box, even when Show individual words is selected. To have two or more words treated as one, enter the space between words by pressing Ctrl+Space bar (Windows) or Option+Space bar (Mac OS) instead of the Space bar alone.
Thank you for your post.
The answer from LaRetta is correct (Thank you!).
Here is another option you may want to consider. Create a Calculation field that returns the first word of the Last Name field. That is, RightWords (Last Name; 1)
You could then perform a search on this calculated field for A..H, and it will exclude those unwanted names.
Let me know if you need clarification.
to get the first word, shouldn't you use Leftwords instead of RightWords? :smileywink:
Good catch, and Thank you! I don't know what I was thinking at the time... :-)
Yes, the correct calculation should be:
LeftWords ( Last Name ; 1 )
hansie - I apologize if I caused any confusion.
I have tried to find a simple way to remove some text in a field for 2000 products. The database was built using importing folders and the folder structure varied. I want to be able to remove some text which always is the same plus whatever comes after it.
Here is a file content: Basketball Times/items/product/basketballtimes.jpg What I want is to remove "/items/product/basketballtimes" where the lst word will always be different and sometimes several words but "/items/product/" will always be the same (that was the folder holding the images I need to use but there where other folders and subfolders with other paths which I do not want to change). I do want to keep the ".jpg" ending though.
I am not a scripter and the find/replace function does not seem to be able to do what I need it to do in this case. Do you have any ideas?
I would start by looking into the Left, Middle, Position, and PAtternCount functions. You should be able to parse out the text that you want/dont want. I would play around with them using a calculation or if you have advanced, then the data viewer. Once you have your calcuation, then you can use a Replace Field Contents to replace the field value. ( save a copy of the file first as there is no undo )
I'm a bit unsure of what you want to remove so I suggest using two global text fields to practice it. For example,
gStart could hold: /items/product/
gEnd could hold: .jpg
Then create this calculation to view if you are getting the correct results. If so, loop through your records or use Replace Field Contents. Calc would be:
Let ( [
start = Position ( text ; gStart; 1 ; 1 ) ;
end = Position ( text ; gEnd ; 1 ; 1 )
Substitute (text ; Middle ( text ; start ; end - start ) ; "" )
There are other ways as well.
UPDATE: Hello there! You're ahead of me, John! :smileyvery-happy:
That sounds good. I do have the advanced version.
Could you please guide a novice to make a script to try out?
I will try this! If I keep using FileMaker as intensely as lately I will have to learn to script - it seems to be a truly fantastic tool to do almost anything in a adatabse!
Do you have any recommended place to go to learn scripting for FileMaker?
I used your calculation (with modifications to fit my database) and tried it out using Data Viewer and it worked really well. Thanks for your help!