finds with 'bad' characters

[ptook1]

My first post! I'm newish to FMP and we have 8.5Adv

I have an inventory database - By nature of the business the we use a lot of the "special" characters in the descriptions for which we need to search. The boss wanted the drop down list for searching. For example when I use the drop down list and locate "Ladel #4" or 'Banner 24"'it finds it in the drop down box but will not "find it" during the search. I understand the use of special characters causes this, even though the drop down list shows the item. The boss can not simply understand why it can't find it. Go figure.

I was playing with a script that was in an example DB that works with a find.

Set Error Capture [On]

If [ Get(WindowMode) = 0]

Enter Find Mode [Pause]

Else

End If

Perform Find [ ]

If [ Get(FoundCount)=0]

Show Custom Dialog [Title: "Message"; Message: "No records match this request."; Buttons: “OK”]

Show All Records

Else (script continues)

I was hoping that I would be able to maintain the drop down list and modify the script to manipulate the search text to remove the special characters or have the script insert the "" char at the right spots in to the 'thing' they used to search and then resubmit the find.

This future code would change the search from Banner 24" to Banner 24" for example.

Does anyone see a way to accomplish this? Is there a "get" function that returns the search criteria that was used in a drop-down list so I can manipulate it? Is ther just another better way to do it?

thanks in advance,

-ptook.

[bcooney]

Hello and welcome to the Forum.

My question to you is why are you searching on a desc and not on an ID? Don't you have IDs assigned to each item in your inventory? Or perhaps a CategoryID?

What fields comprise the value list?

[ptook1]

Hello and thanks!

There is a "group number" that things could be looked up by, but that is just a bunch of meaningless numbers and letters (and some with the special characters too!). But for the most part if they are searching for a 12" green cafeteria tray they would search by 'tray green 12"'. I'm afraid I don't know what you mean by "value list" -- I'm new so sorry.

[David Jondreau]

Setting the indexing of the field to Unicode may help.

[ptook1]

Thank you! But that did not help. A good try anyway.

[bcooney]

Let's go with Group Number. What is this number and how is it created? It sounds as if it's a Category ID.

For example, in a database of Cafeteria supplies, all Trays have the GroupID of "1000." Each individual tray record has a GroupID and an ItemID.

When you create a new item record, you assign a GroupID. The system automatically assigns the ItemID.

Then, if you are Finding for an item, you could look for all Items of GroupID = 1000. However, who remembers IDs. Therefore, you create a value list that consists of GroupID and GroupName from the table Groups.

It seems that you have lots of info stored in one field. Tray green 12" might be worthy of three fields: GroupID (tray), Color, Size.

[ptook1]

I like the train of thought. However, I'm actually tied. What I have written is a purchase history for inventory items. No or little information is hand written to the database. It is imported! There are two main tables, an item table and a purchase record table. The purchase record table is populated by importing text from another program -- the main sales and inventory program – something non-FMP. This program did not collect purchase histories in an adequate manner. So, I imported all the master items, one for one, matching the item number and the group number. Then they export the purchase orders from received orders on a daily basis (again created in the "mother" program) by writing a report to a text file. So the item number and group number are from the "mother" and I can not really change them. I need them to match for the one to many relationship to work. I then create this purchase history, where the owners can pull up an item (or group number -- which as you guessed long ago stopped being used as a group) by using a portal window of all the purchases. I hope that has made sense. I could bother you with how no delimited import would work. I had to import the purchase records containing all those nasty characters by basically reading each line in to a single text field and then processing each line by “punching out” the real fields by using a list of Middle() functions. (Sorry, I’m starting to whine!)

I’m stuck with what I have. If one were starting a completely new application what you are suggesting would be wonderful! So I may be treading over deep waters if there is not a nice way to just make the pull down work. I do appreciate the thoughts however!

Edited March 16, 2009 by Guest

[comment]

I would suggest you enter your search criteria into global field/s, then run a script that enters find mode and sets the field/s to be searched to the global values, while escaping special characters.

[LaRetta]

One might also take advantge of FMs innate ability to remove special characters because it considers them word separators, ie, create a calculation with something simple like:

MiddleWords ( OriginalText ; 1 ; 1 )

& " " &

MiddleWords ( OriginalText ; 2 ; 1 )

& " " &

MiddleWords ( OriginalText ; 3 ; 1 )

and search on that (or use that in the drop-down for the value list). Or in Find, count the words then reset the find entry word by word via similar calculation which uses xWords.

UPDATE: I was being lazy ... of course there are better calcs. But that shows the vision of my meaning more clearly.

Edited March 17, 2009 by Guest
Added update

[IdealData]

If your drop down list is based on the content of the field they are searching on then you could make a 'parallel' calculation field that replaces " with ".

Use the new field as the drop down list.

[ptook1]

If your drop down list is based on the content of the field they are searching on then you could make a 'parallel' calculation field that replaces " with ".

Use the new field as the drop down list.

Thanks for all the ideas everyone. The one above I think I understand. It's clever and yet straight forward. I think I will try that!

would I use a substitue function and if I do how does noe replace the quote mark itself?

Edited March 17, 2009 by Guest