Link Tables

[amberrdw]

Ok, I have two tables, one for the customer database, and one for the invoice. I have linked a hidden field called customer code (in the invoice) with the customers information (in the customer table) and when I type in that code their information is supposed to automatically filled in. My problem is that their information isn't automatically filled in. Some other people have looked at the database and can't figure out why that field isn't working. Any ideas? and thanks in advance.

[Databasic]

. I have linked a hidden field called customer code (in the invoice) with the customers information (in the customer table) and when I type in that code their information is supposed to automatically filled in.

Will need more information - linking field type/setup/ for both tables.

[amberrdw]

I have a database that is an invoice. One of the fields is the customer name, mailing address, city, state & zip. I am trying to get this to automatically fill in, based on a separate database that I have linked (relationship). In my separate database, I have the customer information set-up as an excel type layout, in which the contact code is in one field, customer name is on one field, the mailing address in another field, the city, state and zip are all in their own as well (columns).

 

I also have the customer information set-up in one of FileMaker's generic databases, in which it looks somewhat like a rolodex. I have tried to link both of them.

 

I have it formatted as a text field. I have tried a calculation field (although for the life of me I can't figure out what calculation I would use).

 

When I tried a drop-down list, it entered the customer names, but not any addresses or city, state, zip. Then I became excited and tried other items, in which now I have the company name twice, right next to each-other.

 

I can live with a drop-down list, if I can't get it to search by contact code and automatically fill in. But I need the mailing address, city, state & zip to show with the correct address.

 

If you can't tell by this message, this is my first attempt at databases in FileMaker. I was pretty excited that I was able to successfully link the other databases (13 total) to drop down lists in their field, and to provide an automatic invoice number.

 

This customer auto-fill in is providing so many troubles to me. There is a spot in the manual that talks about multi-criteria relationships, but does not give me enough detail on how to set-it up.

 

Any help you can provide would be greatly appreciated.

 

Thanks.

[bruns]

I have a similiar problem, I have two tables. Custmers with name and address and stuff, invoice with Bill to name and address and stuff.

 

When I get a customer, I fill in all the name and address stuff.

Then I create an invoice and how can I get the Bill to name, address and phone to fill in auto. I am think I will have to use a customer id # which I alreadt have, but now I am stuck as amberrdw is, it sounds like.

 

Thanks for any help.

[Databasic]

Ok this involves what is often referred to as primary key fields and foreign key fields. You need a primary key field in the customer table- usually a serial number field - say pk_Cust_CustID. Then you can create a value list on that field. Then you need to create a fk_inv_CustID filed in the invoice table and create a relationship between the two fields. Once related you can include fields from the customer table on the invoice layout ( such as name , address ect ) Then when you enter the correct Customer ID value in the invoice field the related data will fill in automatically. Generally you use the value list created from the customer table pk_Cust_CustIDvalues to enter date( as a drop down list ) in the invoice fk_inv_CustID field to ensure consistency . While they are different fields in different tables they have the same data thus establishing the relationship between the customer and invoice records and then allowing data to be interchanged between the two. Cheers

[LingoJango]

One of the fields is the customer name, mailing address, city, state & zip.

 

Just one field for mailto in Invoices, right?

 

Click on the field in Define Database. Click on the Options button at the bottom. Make sure you're in the Auto-Enter tab. Click on the Specify button for "Calculated value".

 

In the calc box, use

 

Customer::Name & ¶ &

Customer::MailingAddress & ¶ &

Customer::City & ", " & Customer::State & " " & Customer::Zip

 

but instead of the field names I'm giving you, make sure you put in the names of the fields in the related Customer table. Have you actually linked the Invoices table to the Customer table? This is a necessity.

[bruns]

Yes, only one field for mail to in invoices called bill to

I made field "Bill to " a calc field and the clicked on options and put the calc in you posted

 

Is this right so far

 

I did not see an auto enter tab, not sure about that

 

Where to now?

[bruns]

Also as far as being linked, I have a field called idcustomer in customer table and idcustomer in invoice table, I linked these to and also linked bill to and full name.

 

Did I go wrong?

[bruns]

DataBasic, I have done most of what you said. I made the pk key and the fk key in the appropriate tables. I linked them through a relationship and when I enter the customer number on the invoices layout it populates Name and address and stuff. I did not do the value list you mentioned because I got confused with the rest of it. Is there more I need to do or that will make it better. Thanks

[bruns]

Ok, I have found a problem, when I click new in customer, it adds one and I start filling out and the customer number is auto. That works but at the same time it makes a blank with the same customer #. Same in invoices. Any idea?

[Databasic]

Looks like you are 90% there. Your blank may be related to the "Bill" field which is redundant and that relationship - delete the relationship and see if that helps. The mail to data is placed in the invoice layout BUT are customer table fields. The value list is relatively easy - select make value list and take the "list from field" radio button and enter customerID from the customer table. Once this is done you can format the customerID:invoice field to select from that drop down list. The benefit is the consistency of data so that customerId:Customer is exactly the same as CustomerID:Invoice. Once that works you can add a second field such as surname to allow easy recognition of the customer. You could also place a portal in the customer layout ( based on the invoice table) to show all the invoices pertaining to a particular customer

[Jack Rodgers]

At the bottom of the field definition dialog is a button for Lookup.

 

Once you have linked the two tables, when you enter the customer number in the invoice, a lookup field will pull over the information from the other table. You have to do one lookup for each field you want filled in.

 

Or you can script the data transfer.

 

New record

set field customerid to xxxx

commit record

 

after the commit record the lookup data will be transfered.

 

Or you can set it via script:

 

New record

set field customerid to xxxx

commit record

set field custname to cust::name

set field address to cust::address

etc.

commit record.

[amberrdw]

Ok, I tried to make the field a calculation but whenever I try to finish it up and click Ok, I get a message saying "The specified table cannot be found" but I have the table chosen and checked to see if it was valid (and it is). What am I doing wrong?

[bruns]

LingoJango, I have tried and I am missing something. Can yu help?

[bruns]

LingoJango, I tried what you put below but could not get it to populate. I made a primary key "pk_cust_custID" for customers and a foreign key fk_inv_custID for table invoices. I click new in my customer layout and I fill out name and address, I had the Primary key an serial number which I call customer # on my layout. When I go to invoices and I put my customer # on that layout, it does not populate name and address, however when I go back to customer layout it make it also makes a blank record after the one I created. Any ideas??

 

Thanks

 

 

Just one field for mailto in Invoices, right?

 

Click on the field in Define Database. Click on the Options button at the bottom. Make sure you're in the Auto-Enter tab. Click on the Specify button for "Calculated value".

 

In the calc box, use

 

Customer::Name & ¶ &

Customer::MailingAddress & ¶ &

Customer::City & ", " & Customer::State & " " & Customer::Zip

 

but instead of the field names I'm giving you, make sure you put in the names of the fields in the related Customer table. Have you actually linked the Invoices table to the Customer table? This is a necessity.

[LingoJango]

Hi, Bruns,

 

I've been away.

 

The field on your layout should be fk_inv_custID, and you shouldn't be entering the number manually but using a value list. For the value list, use values from field pk_cust_custID and also display values from the customer name field (or perhaps a calculated firstnamelastname field) so you know what you're selecting.

[touchMe]

Ok, I have two tables, one for the customer database, and one for the invoice. I have linked a hidden field called customer code (in the invoice) with the customers information (in the customer table) and when I type in that code their information is supposed to automatically filled in. My problem is that their information isn't automatically filled in. Some other people have looked at the database and can't figure out why that field isn't working. Any ideas? and thanks in advance.

 

The reason that the information isn't automatically filling-in is because the customer information fields in your invoice are not set as lookups

 

When you create a new invoice and stick that magic customer code into the new record, the moment you tab out of the field your record should be updated with the customer information, it does this by lookup(s)

 

define fields (invoice) - options - lookup... for every field you wish to 'automate' in invoice - customer name, address, town, state, zip etc.,

 

this must be done by a lookup and not a regular relationship, it is done once (when creating the invoice) and therefore not updated or 'maintained' if information changes later in the customer record, you should set these lookup fields in invoices (from layout) so they cannot be edited... you are never going to 'touch' any of these fields, they will be fully automated

 

upon completing your invoice it is also a good idea to have a script create a PDF of the 'authorized' document and for that script to stick it into an invoice PDF-file/log

 

...trust me, your accountant will love you!

 

PS: try automating the entire function from a script in your customer record, create the invoice THROUGH the customer