DDH Software, LLC Making a big world smaller

The following tutorial will show you how to set up HanDBase Sync Exchange for ODBC to Synchronize with a Filemaker Pro database on your Windows computer.

Due to Filemaker's limited support of ODBC there are some steps that need to be taken to be able to Synchronize with Filemaker Pro using the HanDBase Sync Exchange Conduit. While the Mac version of FileMaker does have support for ODBC the steps here will only outline how to set this up on Windows since we do not have a native Mac conduit. However it should be possible to set up a FileMaker database on a Mac as an ODBC Data Source and share that over your network so you can sync through a Windows computer. Please consult your FileMaker documentation for infomation on how to set up your Mac Filemaker database as a Data Source.

Setting up your FileMaker Database as an ODBC Data Source

• FileMaker 7 ODBC Installation Guide (Driver Installation)
• FileMaker 7 ODBC Developer's Guide (Data Source Configuration)
• FileMaker 8 ODBC Installation Guide (Driver Installation)
• FileMaker 8 ODBC Developer's Guide (Data Source Configuration)
• FileMaker 9 ODBC Guide (Driver Installation and Data Source Configuration)
• FileMaker 10 ODBC Guide (Driver Installation and Data Source Configuration)

If you have difficulty with setting up your FileMaker database as an ODBC Data Source you will need to contact FileMaker for further assistance. Beyond providing these instructions, which are taken from their Documentation, DDH Software cannot assist with any problems encountered during this part of the setup. Additionally, if you cannot find the ODBC Driver installation file listed below you will need to contact FileMaker to obtain that. We do not have a license from FileMaker to re-distribute that software.

1. On your FileMaker CD or in the folder where the files were extracted from the download locate the xDBC folder and open it.
   
2. In the xDBC folder open the ODBC Client Driver Installer folder.
   
3. Within that folder double click on the DataDirect SequeLink 5.5 Client for ODBC.msi file to begin the installation. Note: Based on our testing launching the setup.exe file, as one might expect, does not properly install the FileMaker ODBC Driver.
   
4. Open the FileMaker database you plan to synchronize with.
5. You will need to check the Privileges to make sure that the User(s) you plan to allow access to syncing have the proper privileges. From the File Menu choose Manage and then choose Accounts and Privileges
   
6. On the Accounts tab, make a note of the Privilege Sets that the user(s) who will be syncing with this FileMaker database belong to.
   
7. Click on the Extended Privileges tab and make sure that the Used by Privilege Sets column lists those sets you made a note of in the row that shows a Keyword of fmxodbc and the Description of Access via ODBC/JDBC
   
8. If the Privilege Set is not listed click on the Privilege Sets tab, select the set you want to allow access and click the Edit button. Then in the Extended Privileges box check the Access via ODBC/JDBC (fmxodbc) option and click OK to save those changes.
   
9. Click OK to save all changes to your accounts and privileges.
10. Next you will need to allow this database to be Shared via ODBC. From the File menu choose Sharing and then choose ODBC/JDBC.
    
11. Click the On selector to enable sharing. Then under ODBC/JDBC access to file choose the selector next to All Users. If you wish to only allow selected users click the selector next to Specify users by privilege set and then click the Specify button to choose those sets.
    
12. Click OK to save the Sharing settings.
13. Next you will need to launch the ODBC Administrator. From your Start Menu select Control Panel.
14. In the Control Panel open Administrative Tools.
15. Double click on Data Sources (ODBC) to open the ODBC Administrator.
16. Click the Add button to add a new Data Source.
    Note: HanDBase Sync Exchange will work with either a User DSN or a System DSN.
17. Highlight the DataDirect 32-BIT SequeLink 5.5 option in the list. This is the name of the ODBC Driver that will be able to access your FileMaker database.
    Note:You will not see FileMaker listed anywhere during this process.
    
18. Click the Finish button to create the Data Source.
19. The configuration window for the Data Source will appear.
    
20. Enter a Data Source Name and Description. These can be anything you want but generally you'll want to give them the same name as your FileMaker Database. Both are required.
21. In the SequeLink Server Host: box enter either localhost or 127.0.0.1 if the database is on the computer where you are going to be syncing with. If the database is on a different computer accessible on your network you will need to enter the IP address of that computer. You may need to consult your IT staff to determine what that address is.
22. In the SequeLink Server Port box enter 2399.
23. Next click the [...] button to the right of the SequeLink Data Source box.
24. A pop up window will appear and show Default and the name of the FileMaker database you currently have open. Highlight the name of your database and click OK.
    
25. Now all the necessary fields should be populated. We recommend you click the Test Connect button to make sure you can establish a connection before continuing.
    
26. Generally a box will pop up prompting you to enter a valid Username and Password for this Database. Enter those and click OK.
27. If the connection was successful you will see another popup confirming this. Click OK to close that box.
    Note: If the connection test was not successful start at the beginning of this tutorial and recheck all the settings, particularly in your database. If you continue to have problems please consult the FileMaker ODBC Guide or contact FileMaker's Technical Support for assistance. DDH Software will not be able to assist with troubleshooting any of these steps as up to this point the only software involved is what is provided by FileMaker.
28. If the connection test was successful Click Apply and then Click OK to save these changes.
29. Doing that will bring you back to the ODBC Administrator where you will now see the Data Source you created listed.
    

Before you continue you will want to determine if only a single user will be synchronizing with the FileMaker database or if multiple users will be synchronizing with the database. This will help determine where you will store the HanDBase database folder during the following steps.

If you will be syncing with just a single user then you'll want to select that user's individual synchronization folder. For BlackBerry, iPhone and iPod touch and Windows Mobile users this should be in your Documents or My Documents folder within a HanDBase sub-folder there. That is where you will find the user's Sync folder. For Palm OS users please refer to Knowledgebase Article 273 to determine this location.

If you will be syncing with multiple users you will want to choose a more generic location to avoid any possible issues with databases in a users folder that are not being shared. We recommend somewhere like ..My Documents\HanDBase\SharedDBs\. However be sure that if your users log onto the computer with different usernames that this file is accessible to all users for reading and writing. You can also store this file on a Shared network drive that all the users have access to.

Start with an existing Filemaker database

1. You will first need to add two Text fields to your database in order for the Synchronization to work.
   rhdrinfo_
   rhdrid_
   IMPORTANT: Please be sure to enter these EXACTLY as indicated above, being sure to include the underscore ( _ ).
   Note: Once these fields are added you will NOT want to alter them in any way. The Sync Exchange conduit will automatically add data to these fields. Do Not alter this data or add any values to these fields. If you use a Form view to enter your data we recommend you just do not add these two fields to your Form, keeping them hidden.
2. Access the Sync Exchange settings for your platform. These steps vary depending on whether you are using a device with Palm OS, Windows Mobile, BlackBerry or an iPhone or iPod touch.
   Knowledgebase Article 232 has the general steps for accessing the Sync Exchange settings for these different platforms. You can also follow the steps for accessing these options in the HanDBase Documentation Manuals which are located in the Documentation sub-folder of the HanDBase 4 folder on your Start Menu under Programs or All Programs. You can also find this documentation in our Knowledgebase at the following link:
   http://www.ddhsoftware.com/knowledgebase.html?UID=2019032407305134.236.145.124&read=198
3. BlackBerry and iPhone and iPod touch users should follow these steps.
   1. From the Conduit window click the Edit Sync Settings button.
   2. Click the Create New Shared Database button.
   3. Navigate to the folder where you wish to store the HanDBase database, based on the information above regarding a single user or multiple users.
   4. Once you have selected the folder you want this HanDBase database to reside in then type in the name you want to give this HanDBase Database followed by .PDB. This will also translate into the name of the database on your handheld device(s) therefore it must be 19, or less, characters. For example ABCInventory.PDB.
   5. Click Open.
   6. This new database should now be listed in the Shared Databases box. Highlight this database.
      
   7. On the right hand side if the Use Default Settings for this Database box is checked uncheck it.
   8. At the bottom of the right hand side check the Synchronize with Access/ODBC Database box and then click the Settings button to the Right of that. This will open the Sync Exchange Settings Window.
      
4. Palm OS and Windows Mobile users should follow these steps.
   1. When you have access to the Sync Exchange settings click the Add button.
   2. Navigate to the folder where you wish to store the HanDBase database, based on the information above regarding a single user or multiple users.
   3. Once you have selected the folder you want this HanDBase database to reside in then type in the name you want to give this HanDBase Database followed by .PDB. This will also translate into the name of the database on your handheld device(s) therefore it must be 19, or less, characters. For example ABCInventory.PDB.
   4. After typing in the file/database name click the Open button to save this setting.
   5. You will next need to click the Configure ODBC/Access Sync button toward the bottom right. This will open the Sync Exchange Settings Window.
5. You will find the first option, Enter a HanDBase Database, already filled in. Do not change that setting.
6. You should still have your Filemaker database open but if you do not then at this time re-open it.
7. The Merge with an ODBC data source: option should already be selected so then proceed to click the drop down list under where it indicates Select an ODBC data source. From that list select the Filemaker ODBC Data Source you created in the above section. Note: If you have a Personal Firewall you may be asked if the HanDBase Sync/Data Exchange application can have permission to access the Local Network. You will want to enable this.
8. Then from the drop down list under Table Name: select the database that is currently opened, which at this point should be the only one.
9. The only other options you might want to consider setting are the Data Source User ID and Password. If you click the Options button you'll see the following screen where you can enter the User ID and Password for the FileMaker database.
   Note: The settings file that stores these Sync Exchange settings is stored as a Plain Text file and your User ID and Password will be visible if someone were to find this file and figure out how to open it. So please check your company policies before doing this. However, be aware that if you do not enter this someone will have to enter it during the Sync process. Also note, there appears to be a bug in the ODBC Driver where if your database has a username but no password you will be prompted for the Username for every transaction during the sync.
   
10. For now ignore the rest of the options below as they only apply after you have this set up.
11. Click the Save button to save these settings.
12. Palm OS and Windows Mobile users Click the Ok button to close the Sync Exchange Options window.
13. BlackBerry and iPhone and iPod touch users click the [X] to close the Sync Settings Window.
14. Then close any other open windows.

You are now finished setting up this Filemaker database to Sync with HanDBase. You can now Synchronize your handheld and when finished you will find the HanDBase version of the database on your handheld device. You can then begin making changes to the database and they will synchronize with Filemaker.

Note: Due to limitations in Filemaker's ODBC support the Filemaker database MUST be open when you synchronize. Otherwise you will encounter errors during Synchronization. Also Note: If your text fields have been left at the default settings and you have not limited the number of characters allowed in those fields then when the HanDBase database is created during this process all your Text fields will show as Note fields in HanDBase. You can either change your FileMaker field settings to limit the number of characters in your text fields to 254 or after your first sync you can modify the structure of the HanDBase database to make those fields Text fields.

Start with an existing HanDBase database

1. At this time Filemaker Pro does not support the Create Table ODBC statement. Therefore you will first need to create a Filemaker database that has the same fields, with the appropriate field types. It will be best to give the fields the same names as you have in HanDBase, so that matching them up later will be easier.
2. Once you have created your Filemaker database you will then need to add two Text fields to your database in order for the Synchronization to work.
   rhdrinfo_
   rhdrid_
   IMPORTANT: Please be sure to enter these EXACTLY as indicated above, being sure to include the underscore ( _ ).
   Note: Once these fields are added you will NOT want to alter them in any way. The Sync Exchange conduit will automatically add data to these fields. Do Not alter this data or add any values to these fields. If you use a Form view to enter your data we recommend you just do not add these two fields to your Form, keeping them hidden.
3. Access the Sync Exchange settings for your platform. These steps vary depending on whether you are using a device with Palm OS, Windows Mobile, BlackBerry or an iPhone or iPod touch.
   Knowledgebase Article 232 has the general steps for accessing the Sync Exchange settings for these different platforms. You can also follow the steps for accessing these options in the HanDBase Documentation Manuals which are located in the Documentation sub-folder of the HanDBase 4 folder on your Start Menu under Programs or All Programs. You can also find this documentation in our Knowledgebase at the following link:
   http://www.ddhsoftware.com/knowledgebase.html?UID=2019032407305134.236.145.124&read=198
4. BlackBerry and iPhone and iPod touch users should follow these steps.
   1. From the Conduit window click the Edit Sync Settings button.
   2. If you are the only user who will be syncing, Highlight the database you wish to sync with FileMaker in the list on the left hand side.
   3. If you are not going to be the only user click the Create New Shared Database button.
   4. Navigate to the folder where the HanDBase database is stored, based on the information above regarding a single user or multiple users.
   5. Select the database name and click Open.
   6. On the right hand side if the Use Default Settings for this Database box is checked uncheck it.
   7. At the bottom of the right hand side check the Synchronize with Access/ODBC Database box and then click the Settings button to the Right of that. This will open the Sync Exchange Settings Window.
      
5. Palm OS and Windows Mobile users should follow these steps.
   1. When you have access to the Sync Exchange settings click the Add button.
   2. Navigate to the folder where your HanDBase database is stored, based on the information above regarding a single user or multiple users.
   3. Select the HanDBase PDB file you want to Synchronize and click the Open button.
   4. After typing in the file/database name click the Open button to save this setting.
   5. You will next need to click the Configure ODBC/Access Sync button toward the bottom right. This will open the Sync Exchange Settings Window.
6. You will find the first option, Enter a HanDBase Database, already filled in. Do not change that setting.
7. You should still have your Filemaker database open but if you do not then at this time re-open it.
8. The Merge with an ODBC data source: option should already be selected so then proceed to click the drop down list under where it indicates Select an ODBC data source. From that list select the Filemaker ODBC Data Source you created in the above section. Note: If you have a Personal Firewall you may be asked if the HanDBase Sync/Data Exchange application can have permission to access the Local Network. You will want to enable this.
9. Then from the drop down list under Table Name: select the database that is currently opened, which at this point should be the only one.
   
10. You will then need to select the Merge Mapped Columns option in the lower section.
11. Click the Map Columns button to begin mapping your columns.
12. In the Map PDB Columns to ODBC Columns window select the column/field from the Left side and then select the corresponding field from the Right side. Click the Map Columns button.
13. Repeat the above step for each field in your database.
14. When finished mapping the fields/columns click the OK button to save these changes.
15. The only other options you might want to consider setting are the Data Source User ID and Password. If you click the Options button you'll see the following screen where you can enter the User ID and Password for the FileMaker database.
    Note: The settings file that stores these Sync Exchange settings is stored as a Plain Text file and your User ID and Password will be visible if someone were to find this file and figure out how to open it. So please check your company policies before doing this. However, be aware that if you do not enter this someone will have to enter it during the Sync process. Also note, there appears to be a bug in the ODBC Driver where if your database has a username but no password you will be prompted for the Username for every transaction during the sync.
    
16. Click the Save button to save all the Sync Exchange settings.
17. Palm OS and Windows Mobile users Click the Ok button to close the Sync Exchange Options window.
18. BlackBerry and iPhone and iPod touch users click the [X] to close the Sync Settings Window.
19. Then close any other open windows.

Note: Due to limitations in Filemaker's ODBC support the Filemaker database MUST be open when you synchronize. Otherwise you will encounter errors during Synchronization.