Auto-enter fields upon entry of a field.

[Dick van der Velde]

OK, here's the situation.

I've got a database with 2 tables. One is called forms and the other is called clients.

In clients I have the fields name, contact1, contact2 and contact3.

In forms I have Date, Name, Contact1, Contact2, Contact3 and Remarks.

In forms the date is automatically netered. Then Name is entered from a value list from the table clients.

What I want is that when I have selected the Forms::Name, that it automatically enters contact1, 2 and 3 from the table clients where clients:name equals the entered forms::name.

How do I do that?

I hope I'm clear.

P.S. I know this looks inefficient as things seem redundant. But Sometimes the contacts change at the client, and these changes must only be done in new forms, the old one must remain unmodified.

Edited November 11, 2005 by Guest

[flapjacks]

What you want is the same as pricing for line items in an invoice detail file. Make the "Contact1,2,3" lookups baised on the client relationship. The older records remain unchanged unless you do a relookup. The new ones show the current info from clients.

John

[Dick van der Velde]

Thanks for the speedy answer but I'm a beginner and not quite sure what you mean by that.

Should I relate forms::contact1 to client::contact1 and then 2 tot 2 and 3 to 3?

There can be more than one relation between 2 tables?

And how do the fields in forms automatically get filled in after I choose the forms::name?

[aaa]

Create relation by "name" from forms to client.

Then in form make fields contact1,contact2,contact3 as lookup from client.

1. Wiil be better if you for any cient have Cliend_Id field and have this field in forms, because two clients can have the same name.

2. You can dont create contacts field in forms file. In the list of "forms" you can show related fields from clients.

Sorry for my english!? If i explained not clear, i can attach you example.

Edited November 11, 2005 by Guest

[Dick van der Velde]

yes please, an example would be very welcome.

[aaa]

Hi, Dick!

I attached you example. Where in the forms by choosing Contact_Id you see Name,Contact1,Contact2,Contact3 fields from Clients.

That fields we have not in the Forms. I think that will be better if you dont have this fields in the forms. But if you want, you can create this fields in the forms too and use lookup. At that case, if you change any field in clients, you shoud not see that changes in Forms.If is nessesary second way i can attach for it an example too. Sorry if i am not clear.

Contacts.zip

[Dick van der Velde]

First off, thanks for your answer, it gets close to what I want, but yes I need the fields in forms too, because I need to keep the original data in forms. So if I get a new contact, in forms the old contact must remain the same.

Also I like to know how you did it?

In this case how do you display the fields from clients on forms. Is it just a matter of selecting a client ID and having the clients: fields on the from? Or do you have to change something in the properties somewehere?

As said already; thanks so far!

[aaa]

Simply you must use lookup.

As in new attachment.

Contacts.zip