Open the excel spreedsheet in Filemaker. Importing from a FMP database is much easier.
Two imports will be necessary (working with test copies to get it right before actually importing.
There has to be a field in the destination for each field in the source.
Im not sure if I'm following you. I know how to import records, my problem is I need all my old data to link up with the current Client Id. Thank You
You need a field in both tables that uniquely identifies each client, but only using data from your original file. This may be simple or very hard to set up as it depends on the consistency of the data in your old file. You might match by name fields, for example, but then two different clients can have the same exact name. They can even have the same name and address....
But if you can set up such a field, you can import the data twice, once into each table. Use a unique values validation on this "temporary match" field in the client table to automatically filter out duplicates. The Client table should have a unique ID field--ideally an auto-entered serial number. Once the data is imported, you use a temporary relationship based on this "temporary match field" to copy over the ID from clients to matching records in projects. Once that's done you can remove the temporary relationship and set up an ID field based relationship.
This may be the best method for what i am trying to accomplish. But once again im not able to follow. not your fault its mine. Can you please walk me through this one. I have another post as well that is trying to accomplish the same thing, just in a different manor.
You'll need to examine your data carefully to see if the following will work:
For simplicity, let's assume that you have a FullName field that uniquely identifies each client. (You might actually need to combine several fields such as first name, last name and an address or phone number.)
You can use a Query in Access to produce such a value before exporting the data or you can import all your data into a FileMaker table where a calculation field combines the data to create the identifier. Any combination of the above might work as long as you get a field/column where the text in it identifies each client.
Once you have that, you can start importing data.
Include a text field for the above described identifier field. Set a field validation on this field: Unique Values, Validate always. When you import into this table, the duplicates will be omitted. When mapping fields specify the identifier field plus any fields that hold client data. Do not map any fields to project data. Also define an auto-entered serial number field and when you import, be sure to select the check box that enables auto-enter operations.
Import from the same source again, but map only to projects fields and include that same identifier field. Do not set up any validations on the identifer field in this table. (Another serial number field to identify each project would not be a bad idea.) And finally, add a number field to serve as the client ID field for linking to the Clients table.
Now define a relationship matching the identifier field in clients to the identifier field in projects. After all data has been imported, go to the Projects layout, click in the blank Projects::ClientID field and select Replace Field contents. Select the calculation option and set it up to refer to the clientID field in Clients.
Now that you have the ID number copied over, you can remove the original relationship that matches records by the calculated identifier and replace it with one that matches by ClientID.
Your Sir are brilliant. I have spent 20Hrs watching videos on lydia.com and you have solved this problem like it was no big deal. Thank You So So So Much. I owe you big time.
I wish by brain worked like yours