Archiving Data Using FM10 Server Importing and Exporting

Do you have a client that has a huge database and a hard time letting go of old data?

Archiving decrease the database size (therefore increasing the performance) and allow the client to access old data when needed. You can archive easily and quickly using FileMaker Server 10 by simply exporting data into a temporary .csv file in the documents folder, and then importing the same data into a separate archive database.

Before I show you the 4 steps to server-side archiving, you should know a few things:

• Filemaker Server 10 can only import and export from the documents folder ([MachineName]/Library/FileMaker Server/ Data/Documents/) or a temporary folder. It can import and export any usual file type except the server cannot export to .fp7. I recommend using .csv because every computer has text edit or some similar application. You can open the file in excel and see the data nicely.
• You’ll want to use the Get( DocumentsPath ) to construct your importing and exporting file path. When you run your archiving script from a FileMaker client using Get(DocumentsPath), your files write to your User/ Documents folder (nice for testing). When the script runs from FileMaker Server, the files will write to the FileMaker Server/ Data/ Documents folder.
• If you write the files into a subfolder of the documents folder, make sure the FileMaker Server Admin has read & write access to that folder. Otherwise, the export files cannot be written. You don’t need a folder inside the documents folder, but it does provide organization.

How to Archive Data Using FileMaker Server 10

Take the following steps to archive data using FileMaker Server 10:

1. Allow the user to designate which data can be archived. In my case, I gave the user a checkbox for nightly archiving. I also allow the user to choose an age that will prompt archiving. For example, if a data group is over X number of days old, it will be archived.
2. Create an external data source from your main database to your archive database. Create a layout in your main database for each table that will store the archived information. These layouts are based on your external data source (your archive database).
3. Create an archive script that completes the following steps:
   1. Do a search on the data to be archived.
   2. Create an export file path variable and set it as Get ( DocumentsPath ) & “[Archive Folder]/[ExportFileName].csv” The archive folder is optional.
   3. Export the records to the variable file path you have created. Be sure to select the correct file type.
   4. Go to the archive layout based on your external data source. The data you just exported will be imported into this table.
   5. Create an import file path, which will just be your export file path with a “filemac:” or “filewin:” preceding it. For example: “filemac:” & “$$exportFilePath”
   6. Import the records with your data source file path set to your import variable (choose the right file type) and your target table is set to the archive table your current layout is based on. You may need to export an actual csv with real data first, and use it to set up the importing correctly. Select the newly created CSV file as the import file path, set up the import order correctly ( Be sure “Arrange By:” is set to last order or custom import order and NOT matching name), and then remove the hard file path and replace it with the variable import file path you created in the step above. See FileMaker 10 FTS Module 6 for more information on how to complete this step.
   7. If you’re not deleting the data that has been archived, it’s a good idea to let the user know that the archive was successful. I created a field for the user to see the archive timestamp. Also, you want to be sure to uncheck the archive checkbox so the data does not get archived again the next night.
   8. Now repeat this process for each table to be archived.
   9. Here is an example of what the code looks like:

Set Variable [$exportTestPath; Value:Get( DocumentsPath ) &
      "QA_Archive/ArchiveTestExport.csv"]
Export Records [No dialog; "$exportTestPath"; Macintosh]
Go to Layout ["z_Archive_Test"(Archive_TES__Test)]
Set Variable [$importTestPath; Value:"filemac:" & $exportTestPath]
Import Records [No dialog; "ArchiveTestExport.csv"; Add; Mac Roman]

4. From the Server Admin Console, create a new schedule, and choose Run Script. Select the Archive script you have created to run nightly. Do a test run by highlighting your script and selecting “Run a Schedule Now.” If the script did not run as expected, check out the Event.log file in Library/FileMaker Server/Logs for errors.

Next Steps

Have questions? You can contact my team directly for more insights. Or, check out our other FileMaker posts to learn more about customizing your solution.