Value list - Company/Customer

[MagusJosh]

I hope I'm posting this question in the right place...I'm trying to adjust an existing database to fit the following desired functionality:

The user I'm working with wants to have an auto-filling field (Company Name) also display a name associated with the company (Full Name). These two fields are pulling their information from an external contact database where each client (Full Name) is a separate record (with the same Company Name in many cases).

The problem I'm running into is this: When I teach it to show both the Company Name and the Full Name in the Company Name field, it only shows the first entry from the contact database. When I tell it to sort the entries by the Full Name (the second field, in this case), it shows me all of them but still only uses the first entry from the contact database no matter which I select within the company range.

I know I've missed some step, but I'm not sure what. Can anyone either point me towards what I need to change, or suggest a better way to do it?

Thanks!

[bcooney]

How did you relate the files? By full name? What is this external contact database? Are you expected to keep the FM database in sync with this external contact database? If so, how do you plan to do this?

Are there any key fields that you could use, besides matching on text values?

[MagusJosh]

I've tried it with the files related by Company Name, Full Name, and both at the same time. It hasn't made any difference.

The external contact database is just another FMPro database. I'm not trying to sync data to it, just draw address info from it.

I'm not sure what you mean by key fields...if I understand the question right, it's just those two fields, which are text values.

[bcooney]

There is no serial number assigned to a Contact?

If the other file is a FM file, will the two systems share the Contacts file?

Edited June 17, 2008 by Guest

[MagusJosh]

Not at this time and yes respectively. I suppose I could add an ID number to the customer database, but I don't think that will help my client much, since they want to be able to search by company and choose which customer name gets filled in.

[bcooney]

I think that the problem goes beyond the lack of key fields. The FM file from which you are trying to get data is not modeled correctly. There should be a table for Companies, and a table for Contacts. They should be related by CompanyID.

Then, in your system, when the user selected the Company they wanted*, they would be presented with a related value list of Contacts for that Company.

*There are several techniques for how a user selects a Company. They could Find in the Company table, and then your script would capture that ID and create whatever child record needed. They could be in the child record and select a Company from a popup window (list of companies, or filtered portal).