but when I select this Company I want to fill in the rest of the address etc details.
This will take a number of steps to accomplish.
The first step is to add a table occurrence of the table in your Contacts file to Manage | Database | Relationships in your Invoices file.
Open Manage | Database | Relationships in your Invoice file and click the misnamed "add a table" button in the far bottom left corner.
In the dialog that appears, select "add fileMaker data source" from the Data Source drop down, find your contacts file, select it, and then select the contacts table in it.
This creates a table occurrence to the table in your contacts file that you can now link to the other table occurrence boxes in your relationships graph.
With the right relationship in place using your value list formatted field for selecting a company, you can then either copy the data over from this table into fields in the invoice file or you can simply add fields from this contacts table directly to your layout.
Yes I have already done that. How do I copy the fields. I tried "Set Field" in the script but this did not work. Do I need to point to the contacts file first. Attached is the script I wrote for this. but this only seems to work if the fields are within the file you are working on.
What you show will only work if you have a valid relationship linking one record in contacts to one record in customers. This will not add new records, but update existing records instead.
If you are going to copy the data into your customers table, why do you need the contacts table? Import records can copy records from a table in one file into a table in another, but this is a one way trip like any attempt to copy data. Once you have the data in Customers, any changes to the data in customers does not appear in contacts and any new changes in contacts will not appear in customers.
It seems that you need to merge your data into a single table in a single file and either use that single table in both solutions or to add the contacts manager features to your invoice solution so that the contacts manager file is no longer needed.
What is the precise end result that you want here?
This is probably a temp measure until I rebuild the database (it is 10 years old and handed down from one person to another). But until then I don't want to interfere with either file. Surely there is a script that I can use that will bring in the related data from the Contacts file to the Customer file. Well I am hoping this is the case.
There is, but I wanted to be sure you were aware of the ramifications.
Why not just use Import records to copy this data from Contacts in to Customers? This can be done manually or in a script. (do this on a copy of your file so that if the results are not what you expected, you have an unmodified copy to fall back on.)
OK I did that, but what if one of our staff create a new record or change address details?.
Is it possible to update the data, or import one record only?
This is the inherent problem to having two tables for the same data and why you need to change your design to use only one table.
That scenario will be a problem no matter which exact method you use to copy data from one table to another.
It is possible to use Import Records with the "match records in found set" option to update an existing record instead of adding a new one, IF there is a field in both tables that uniquely and correctly identifies the same record in both tables.
Modification date fields can be used to identify which fields have been modified since the last "synch" import.
And there are third party tools (mainly for use in FM GO but some, maybe all can also be used with FM Pro) for synchronizing the data in two tables. You may want to look at the products offered by SeedCode and 360Works to see if they are a possible tool to use for this if your budget can handle the cost of setting it up.