Import records--but not a recurring import--can be used to copy data from the found set of one table into another. You may find it easiest to simply do one import records for each target table, importing from the same source table, but mapping different sets of fields from that one large table. Please note that you can also import directly from the CSV file in this form rather than the all in one table you have set up for this purpose.
There may be details to this process, however were some additional processing of the data is needed in order to move the data. This might be the case, for example, if you need to combine data from two fields in the temp table and put the combined data in a single field in one of the target tables. If that's the case, you may need to use a script that loops through your records to copy data from the temp table into a target table on a record by record basis. Typically, you either use a set of variables to move all the data or you use just one variable to move a value from a Primary key field and then use a relationship with a looked up value or auto-entered calculation to move the rest of the data.
Thanks Phil, I'll probably end up running the Import again and again over the next week or so and fixing any problems as they occur. I thought that one large Table preconfigured with the correct Field names would make things easier if I keep having to do the same recurring import.
So if I understand you correctly, I would create several Imports to copy certain data into other tables? Could these be scripted so I can just click a button maybe?
There's an import records script step you can use and so you can put all the needed Import Records step in a single script to do this.
You can set these steps to import from either the FileMaker Table or the CSV file--whichever you find works best for you.
You'll want to pay carefull attention to any serial number fields if you need to do this more than once. The next serial value settings for any such fields will need to be updated so that a new record doesn't accidentally duplicate a serial number of an existing record. You'll also need to be sure any newly imported records are correctly assigned such serial numbers--that might be best done by enabling auto enter options during import or possibly through using Replace Field Contents to update them immediately after the import. Which option is best can depend on the design of your target tables.
Ok great, just looking at the Import Records script step now, how do I define an FM Table as the source?
Please ignore my last post, I think I've sussed it :)
For anyone else that might read this thread, the trick is to use the same open file dialog for selecting a different file to select the very same file where you are defining your import script.
If Access can save your records to an Excel file, you might find that useful after you resolve a few minor issues with the Filemaker import. Plus I think Filemaker and Excel can be bound together...
...and if the Acess files are dbf files, you can import that directly.
Both options work for most data, but both have issues that can cause problems. CSV may be safer.
These are a few of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.
It can also be downloaded as a database file from: https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip
Please note that two of these issues are yet to be check out in FileMaker 12 (If you test them let me know), so they may or may not apply in that version.
Hmm, that reminds me...
I remember when 4th Dimension announced it would now import DBF files back before television was invented.
I was very happy since I had a solution in dbf files I wanted to move to 4th Dimension.
First import, tragedy... lots of records that were marked for deletion. Contacted support. Ex database developer gave me complex ideas to solve problem. Instead I thought about it and decided that before importing I should pack the database. You see dbf marks records as deleted but doesn't delete them until you pack them and just doesn't let those deleted records get involved in things.
So, I posted this simple and correct and uncomplicated solution and received at least $400,000 in Paypal donations...not...
I don't know how Filemaker handles dbf files since I haven't done this in years but I suspect packing first will save some time. The delete field flag will import.
I have to wonder if you read the issue report that link points to. It makes no reference to a delete field flag but does refer to issues with memo fields--a field type also used in Access.
Keep in mind that you can just export to CSV or Tab and not need to deal with these issues.
I'm trying to manipulate some imported data and would like to do this before running the individual Import scripts.
The 'recurring import' is grabbing "000107" from the CSV file and I'm trying to change it to "107" by using:
Right ( MyField; 3)
The values don't change though, any suggestions?
Right ( myfield ; 3 ) if defined in a calculation field, should do that, but if you just define the field as a number field, the leading zeroes should drop out automatically.
Hmm, something isn't right then as the field has that calculation and is also set as a Number field too. Just noticed that the imported text is now in a grey font instead of black?