Finding/Adding between related databases problem

[RayM]

I have related two files:

(1) An order entry database (master file)

(2) An address database (related file)

I have been able to get addresses out of the related file (address db) into my master file (order entry db) by creating links between the two files. That's fine. Here is my problem -- or perhaps these are two distinct problems -- when I am entering a new record in my order entry db (I use the company name as the "trigger" field) FMP5 will only copy the company address info into my order entry (master) file if I type in an exact match of the company name I have in my address (related) file. But I would like to be able to type several letters of a company name and get some kind of list of near matches found in the address (related) db to select from in my order entry (master) db either when

(a) entering a new record in my order entry (master) db; or

(: finding an address in the address (related) db FROM the order entry (master) db (that is, searching in one file [the address database] from another [the order entry database]).

One solution I attempted and successfully got to work was to create a dropdown list in my order entry database to display the company names from my address database; unfortunately, however, this solution proved to be impractical as I have hundreds of names to scroll through. So I am back to square one. Any suggestions? Please!

Ray

[ April 09, 2001: Message edited by: RayMatt ]

[ April 09, 2001: Message edited by: RayMatt ]

[LiveOak]

You have hit upon the reason we NEVER use fields with data that means something to the user to connect files via relationship. If the name is altered (not uncommon), all the links will be broken. Use an auto entered serial number field (Customer No.) to connect the files.

To make searches easier, you can create a portal from orders to customers. Combine this with buttons "A", "B", "C" or "A-D", "E-H", etc. above the portal. Create a field in customer that calculates to the first letter of the customer name or one of server alpha groups they are in (group "1" could be "A-D", etc.). Create a relationship from a global field in Orders to this calculated field in Customers. You can now control what is displayed in the portal by having the button set the global field to "A" or "1" (for first alpha group). They click on the desired line in the portal (customer) and have an invisible button set the customer number for the original relationship to the correct field.

-bd

[RayM]

Thank you for your reply, but I'm afraid it is largely over my head as I am only a FMP5 beginner. I'll need time to unpack it. However, I thought I would mention that after searching the forum, I changed my approach to this problem. I want to outline my new approach for (perhaps) the benefit of other beginners. Perhaps, if you would, you might point out some of the pitfalls of my new approach. This explanation will probably sound convoluted, so bear with me (if you're so inclined).

First, I created a serialized CLIENT ID field in my address (related) db, a corresponding CLIENT ID field in my master db, and I've now linked my two files using this field. I then added a button on my order entry (master) db, that calls a script which in turn calls a script (subscript) I've created in my address db. The script allows the user to enter search parameters in my address database, searches the db, and if it finds a single match it copies the client ID into my master db (and thus all the address data). If it finds multiple matches (which is more often the case), it displays a list of hits in the address db. The user then clicks the client id of the correct customer, clicks continue, and my script copies the selected client ID into my order entry db (and thus the address data).

This not-too-elegant solution seems, for now, to work for me, but I'm open to other suggestions. I'm still debugging my script in order to trap user aborts and so on. One thing I'm trying to figure out is how to set some sort of flag (so that I can test whether a user has entered data or not). I'm sure I'll discover other problems as I go along; but so far, this approach seems to work acceptably.

Any other thoughts (explained for an obtuse beginner of course) or suggestions would be appreciated.

Ray

[ April 10, 2001: Message edited by: RayMatt ]

[ April 10, 2001: Message edited by: RayMatt ]

[markpro]

Check out your value list option one more time. If you begin to type the name of a company when you open the drop down list, FM will scroll to the names that match that "criteria". If you pause for too long (test it) FM thinks you have begun a new "search" and scolls to that section of the value list.

I.E.

Customers named:

Barker

Becker

Bellow

Typing BE as your value list appears would take you to Becker.

[RayM]

Thanks for the tip. It works as you said. However, I'm finding that the Client ID approach with my find script in fact is a better solution because in some cases I have to deal with a single company with several locations. But your tip is going to prove useful in other situations.

Thanks again,

Ray