Finding matching records

[stac_1]

I am trying to automate a find in one database of one field from many records in another database. For example. I have a list of job numbers that when searched for if records are found, these found records need to be moved (archived) to another database. This list of numbers can be a bit long and entering find mode and either typing or copying and pasting each number every time becomes a problem. I have tried separating the numbers with quotation marks, commas and the words "and" "&" and "or". Does anyone have any suggestions on searching one field for many possibilities?

[Barry F-H]

If I understand your question correctly, the solution is as follows:

1. In Find mode, click the field that you want to use for your search.

2. Click the Symbols popup menu button (i.e., the triangle) that is located just above the Find button in the Status Area of the database's window.

3. In the popup menu, select the appropriate symbol, type the appropriate value, and click the Find button (or press the Return key on your keyboard).

So, for example, if you want to find all records with a job number greater than 104, select the "greater than" option in the Symbols popup menu, then type "104" (without the quotation marks), and press Return.

Hope this helps!

[LiveOak]

The really tricky way is to use a global field and a relationship. Create a global field:

gJobNumber (text)

and a relationship (this could be to the same or a different file) called JOBS

gJobNumber matches Job Number

Enter a list of job numbers to be found into the field gJobNumber separated by carriage returns:

10047 <cr>

10054 <cr>

10055 <cr>

10056 <cr>

Create a script that contains the script step:

Go to related record [JOBS, show only related records]

This will do the trick. If the related records are in a different file, you must move to that file to see the new found set.

In addition, if this is a two file operation, you can get tricky about how you create the list in gJobNumber. If you have a found set of records in File 1 (say closed jobs) that you want to make the found set in File 2:

1) create a list layout in File 1 with ONLY the job number (no column headings, headers, footers, etc.)

2) select to that layout under script control

3) perform a Copy All Records operation

4) paste to the gJobNumber field

5) perform the Goto Related Record [JOBS, show only related records] script step.

-bd

[stac_1]

Barry, Thanks for your help.... sometimes the answer is too close... ;-) - anyway, that worked great but, how would I find records with numbers that are not in any sort of order or range (ie. 1000300, 1000355, 5000544, 5000643 & 7000799)?

[Barry F-H]

According to the FMP User's Guide (P. 3-2), you should be able to use the symbol "@" (without quote marks) as a wild card.

So, in Find mode with your cursor in the field of interest, you should be able to type (for example) @00030@ to find the values 1000300 and 1000355 plus any others meeting this search criterion.

Based upon my read of P. 3-2 and 3-3, I think you will not be able to enter a single search command to find the five specific job numbers that you gave as examples. It might be doable via an AppleScript or FMP's ScriptMaker, but if so that's beyond my current skills. -(

Hope this helps!

[stac_1]

Thanks for the help. It worked like a charm! Thank you thank you thank you thank you thank you.....

[George]

Stac, I don't know how the Mac version works but in the PC FM5, I have the ability to perform a 2nd, 3rd, 4th etc request in the find mode.

You first goto the find mode and enter in your criteria, next I have "Requests" at the top on the menu bar and in it there is "Add New Request". If I select add new request I get a new empty find form where I can add additional find criteria. So in your case if you were looking for item numbers 1033554, 1020778 and 1188456, you would have a total of 3 find requests. Look up find requests in your documentation or FM help.

Good Luck,

George