Need Help

[dmack]

New position at work... Took over FMPro "STUFF"...

Right off the get go I have run into script problems due to Non-generic names throughout the systems... (None of the finds work due to everything being labelled wrong...) We have exported and Pivotted the names and there is a "HUGE" amount of change from one convention to the next... Is there a way to run a find and have it Omit all those that match a Value list? I would run the finds by hand (will in a few days if no other options arise...) but I am working on a file maker database's that is larger than any others I have ever seen and there are alot of them...

We wnat to run a script in them that Omits and than compares and adjusts the data... I figure we can get it to at least Omitt the data and then we can toss the stuff into excel and make the changes there one product at a time... else we find them one type at a time and use replace all???

Any other suggestions?

------------------

Thanks,

Ken

[Chuck]

How big is your value list?

Performing a find that omits values is fairly easy, but if you have many items in your value list that you want to omit, then it might be tedious to do, and there may be a better way.

Just as when you are in browse mode you can create new records, when you are in find mode, you can create new find requests. These multiple find requests act as "ors" when you perform the search.

Let's say you have three items in your value list, and you want to find all the records that have something besides those three items.

Enter find mode. In the field you want to search on, enter one of the items in the value list. Click the Omit button in the status area. Create a new find request, and perform the same actions for the second item in the value list, and again for the third.

This sets up a find request that finds all of the records that do not have the first, second or third item in the search field.

Once you create such a find, you can save it in a script by creating a new script and placing the Perform Find [ Restore ] step in it.

Now, as you can see, if you have many value list items, creating this find is going to be tedious. In such a case, I would recommend creating a flag field that is a number calculation. Assuming that the three items in my value list are "A", "B" and "C", the flag field would look like this:

( not Search_Field = "A" ) and ( not Search_Field = "B" ) and ( not Search_Field = "C" )

So if the Search_Field has A, B or C as the contents, the flag will result in a 0, and if it has none of them, it will result in a 1. From here you can just search the flag field for all records that have a 0.

Chuck

[dmack]

Chuck,

Thought of that. But your thinking along the right lines with "if your value lists are to big"... They are frightfully huge... Any other ideas??? There are over 100 in each list...:

I need to do this on multiple databases hence looking for a better way...:

Best that I have come up with is Pivot tables and then mass replacement one at a time...

eg:

Fine-short-cooled (is the proper one)

short/Fine/Cold (has been put in instead)

Find (short*Fine*Cold*) and replace all..

This is due to there also being Fine-long and Fine-cut etc...

I am looking at 20-40 thousand records per database times about 7 databases...

I think I took on a project out of my time capabilities....

------------------

Thanks,

Ken

[spragueg]

Why not use your value list in a second database and relate it to the problem database. Then you can export the data that matches via the relationship.

Does that help?

G

[BobWeaver]

I had almost exactly the same problem with a database that I inherited. No value lists had been set up. So, the users could enter anything they wanted in some fields. The result was that there was no way to do a proper find based on those fields unless you created a find request with every possible permutation of user entries including misspellings. I reduced the problem by creating a related file to lookup all of the variations that could be entered, and return the one official legal one. My solution was to generate some calculated fields to parse the offending field into separate fields containing one word each. These fields were linked to my dictionary db which looked up the offending word and returned a word with the proper spelling and a description of whether the word was color, size temperature etc. Then I could re-assemble the words back into a proper phrase using another calculated field. It worked quite well, and eliminated about 99% of manual correction. My files had about 15,000 records. So this took a while to run, but saved a lot of manual work.

By the way, I don't know if this is generally known but if you separate individual words in a field with returns, then you will get a match if any of the words matches the field contents of the linked file. For example, if the dictionary file has two fields called INCORRECT and CORRECT and one of the records is as follows:

INCORRECT: purple<CR>prple<CR>mauve<CR>violet<CR>violent<CR>reddishblue

CORRECT: Violet

and the main db is linked via the INCORRECT field, then 'mauve' in the main database will select this record in the related database and will return 'Violet'. This way you only have to create one record for ALL misspellings of a word rather than a record for EACH misspelling. Hope this helps.

[This message has been edited by BobWeaver (edited November 23, 2000).]