How do I get a field to autofill....

[chester122]

Hi All!

I am a newbie, and currently having some trouble. I have a database with two tables, Clients and Orders, which have relationships defined upon the fields name and address. In the orders table I have a clients field, which operates via a dropdown menu with the values from the clients table within it (I have done this via a value list). Each client has a single address, which I would like to appear automatically in the delivery address box on the order form. How can I get filemaker to do this?

Apologies if I've missed something really simple.

Many thanks

Adam

[Ender]

Welcome Adam.

You didn't specify whether your client selects a Client ID or a Client Name from the value list, but I would recommend using a Client ID (showing the Client Name too.) There are some speed issues you may run into with custom value lists, but that's another discussion.

There are two ways to show related field data; with field lookups or by putting the related field on the layout. Lookups would store a copy of the Client address data in the Orders table. Putting related fields on the layout will show whatever the current information is from the related table. If address data could change in the Client file, but you need the Orders file to show the address data as it was when the order was created, then lookups are best.

To create lookups on your address fields, just go into the field options for each address field in Orders, in the Auto Enter section, select Looked-up value. Use the Orders as the starting table, and Client as the Looked up table. Set the correct address field as the copy from field.

To place related address fields on a layout, simply drop a new field on the layout, select the source table as Client, and choose the client address field.

[chester122]

Many thanks for your help. However I have set up a lookup but it will not work. I was able to set up the lookup exactly as you suggested. If I specify the client as adam for example, the field address in the order database remains empty? Any suggestions on how I can get FileMaker to lookup the client entered and place their address fields in the address fields of the order?

Thanks in advance,

Adam

[Ender]

Lookups only get the data at the time when data is entered in the field that the lookup's relationship is based on. So when you you enter the client the first time in an Order, the lookups should populate.

If you have a bunch of existing records you need to populate, click into the Client field and perform a Relookup field contents from the Records menu.

[chester122]

Using a lookup I can get the field to autofill only if I select that if no exact match is found print "whoops". In each case it selects whoops immediately. Any ideas on why this is happening?

Many thanks,

Adam

[Ender]

If the lookup is not working, then there may be a problem with the relationship or with the field types of the keys mismatching.

You can check the relationship by dropping a portal on an Orders layout showing related Client records. Put the Client ID and Name on the portal. This portal will show the possible related records from Client (There should be exactly one portal row if the Client IDs are unique and setup properly.) The related record will be the source of the lookup. If the record in the portal is correct, then the lookup settings may be the problem.

[chester122]

I discovered I'd set too many constraints in the relationships. Simplifying them has solved the problem and lookups are working fine. Many thanks for your help Ender.

Cheers

Adam