Beginners problem

[James Patrick]

Hello

If anyone can help me it would be greatly appreciated. Basically I am trying to redo our companies database(modelmaking company)and I have got a problem. I have 2 tables, a client table and a job sheet table and what I need to be able to do is lookup the company name from the client table then input all the company info, address, telephone numbers etc, into related fields in the Job sheet table. The old database had a pop up menu on job sheet but I don't like it because the list is so long, it takes ages to scroll through.

Another problem is what to do when you have more than one person working for the same company. I wanted to have a separate record for each person at the company but this causes its own problems.

Sorry if this is very long, I hope it makes sense, I am new to all this and finding it very confusing.

Thanks

James

[IdealData]

Value lists (pop up menus) now feature an "Auto complete" which allows the user to start typing an entry, as each character is entered the value shortens accordingly. Even 1000 items in a value list would cascade to only a few after 2 or 3 characters. Please see the dialog box for Field Control > Setup.

More than one person per company - not a problem if the relationship is set up correctly, although you didn't describe what problems this causes.

[LaRetta]

Hi James, and welcome to FM Forums!

... then input all the company info, address, telephone numbers etc, into related fields in the Job sheet table.

This may not be necessary. You may not be aware that, once your Job Sheet is related to a customer, you can place the fields from the Customer table directly onto your Job Sheet layout! The only time you need redundant data is when it is required to keep track of the address of a job even if the Customer moves. This is sometimes necessary for Invoices and such. But when you can, use related fields and eliminate duplicate fields in your Job Sheet.

LaRetta

[James Patrick]

Hello

Thanks for the reply. I have been playing about with auto complete this could be what I am after. The problem with having more than one person for the same company is finding which person you want but I suppose I could make a value list which shows both company name and contact name on the same list, I think this is possible.

Thanks again

[James Patrick]

Hi Laretta,

Thanks for the reply. The old database has a relationship on company name between the tables. When a job comes in a job sheet is filled out which looks up the company details but isn't attached to a specific client with a unique identifier, if that makes sense.

I think this is the problem, I might have to rethink the design of this database to make it more user friendly. I just want to keep it as simple as possible for the user.

Thanks again

[LaRetta]

You may be fairly new but you see your biggest problem ... the relationship shouldn't be based upon name but rather a unique ID in the Customer table. You have come to the right place to learn how to accomplish it.

[James Patrick]

Hello

I have just checked the old database and none of the relationships are based on an id. If I use an id this has problems because wouldn't the person have to lookup the id first to then know what record to go to, to get the information? Unless I use an id that uses maybe a few letters of company name + some letters of client name, for example, but this worries me because it is another process for the user to think about when he is inputting the data.

Sorry if this is difficult to understand but I have been learning all this from books and finding it all quite difficult. There is probably quite a simple solution to all this.

Thanks

[Søren Dyhr]

but this worries me because it is another process for the user to think about when he is inputting the data.

Well not nessersarily:

http://fmforums.com/forum/showpost.php?post/265338/

...take a look a the template, and read Enders objections!

--sd