Do you have a client that has a huge database and a hard time letting go of old data? Archiving may be a good solution to decrease the database size (therefore increasing the performance) and allow the client to access old data when needed. Using FileMaker Server 10, archiving is made easy and fast 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, there are a few things you should know:

• 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, and 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. The nice thing about using Get(DocumentsPath) is when you run your archiving script from a FileMaker client, your files write to your User/ Documents folder (which is nice for testing) and when the script runs from FileMaker Server, the files will write to the FileMaker Server/ Data/ Documents folder.
• If you are writing the files into a subfolder of the documents folder, be sure that the FileMaker Server Admin has read & write access to that folder, otherwise the export files cannot be written. A folder inside the documents folder is not necessary, but it provides organization.

Here are the steps needed 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 check box 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:
   • Do a search on the data to be archived.
   • Create an export file path variable and set it as Get ( DocumentsPath ) & “[Archive Folder]/[ExportFileName].csv” The archive folder is optional.
   • Export the records to the variable file path you have created. Be sure to select the correct file type.
   • Go to the archive layout based on your external data source. The data you just exported will be imported into this table.
   • Create an import file path, which will just be your export filepath with a “filemac:” or “filewin:” preceding it. For example: “filemac:” & “$$exportFilePath”
   • 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.
   • 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 check box so the data does not get archived again the next night.
   • Now repeat this process for each table to be archived.
   • 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, be sure to check out the Event.log file in Library/FileMaker Server/Logs for errors.

Related posts:

1. Use System Level Scripts to Archive Backups or FTP Import with FileMaker Server System level scripts can be very helpful to use...
2. More Examples We have created a Flex Project called “FlexFMSnippets,” which contains...
3. Manage Your Contacts: Import vCards into FileMaker Pro This file demonstrates a process by which you can import...
4. SuperContainer and mounted volumes On a Mac, if you configure SuperContainer to store images...
5. Data Viewer won’t display script variables if opened after script start The other day I was unit testing a FileMaker script...

Related posts brought to you by Yet Another Related Posts Plugin.