FM - Using Filemaker to Merge a Word Document
My boss wants me to use Filemaker fields in order to merge with a Word document. I can do this on the Word side with a macro (VBA), which imports the info from a tab delimited text file, but just wanted to check with you all if there was a better way.
This seems a bit cumbersome; yet, I can understand why he wants to have information recorded in the FM database for access at a later time. Thank you.
This seems a bit cumbersome; yet, I can understand why he wants to have information recorded in the FM database for access at a later time. Thank you.
Will Loving
Using Word's Mail Merge Manager, you should see an option to select "FileMaker Pro" as a data source. The wizard should walk you through selecting a layout in FileMaker that has the fields on it that you want to merge with. If you don't already have such a layout you may wish to create one.
ASKER
Would have been great, but possibly because we're a PC shop, or because we use Word 2003, the choice for Filemaker Pro was not there.
I believe there may be a way to do more manually using ODBC, but the simpler route is to use FileMaker's "Merge" export. The Merge export is simply CSV (comma-separated values) with a header record that contains the fields names. Having the field names in the first record allows you to then create a merge template in Word that uses those field names as placeholders. With the template using placeholder field names you can then re-export (to Merge format) whenever necessary and simply select the export as a new "data source" document, and have Word recognized the field names.
yet, I can understand why he wants to have information recorded in the FM database for access at a later
You want Word docs in the database?
You want Word docs in the database?
ASKER
Word docs in the database is a related question and yet, I am teaching myself how to use either the container field or the web portal to show documents.
Regarding the ODBC issue, based on a previous question I asked, I had looked in FM Help and it seems that FM Pro is not one of the supported dbs for ODBC (seems Microsoft wants to give Access the edge). See below:
-----
"Sorry, I had looked on the wrong FM Help page for my answer. Looks like Filemaker does not support an ODBC connection to either Excel or a txt file. See below. However, Microsoft did provide the ODBC drive - I went into Control Panel:Admin tools and was able to add a systemDSN driver for Excel and one for text files. However, when going through FM, it didn't list either, probably because of the limitation below:
Data sources supported in FileMaker 11.0
You can use the following data sources in the FileMaker Pro relationships graph. If you are importing ODBC data, or using the Import Records or the Execute SQL script step, you can use additional data sources, such as IBM DB2 or Informix ODBC.
• Oracle 9i
• Oracle 10g
• Oracle 11g
• SQL Server 2000
• SQL Server 2005
• SQL Server 2008
• MySQL 5.0 Community Edition (free)
• MySQL 5.1 Community Edition (free)"
Regarding the ODBC issue, based on a previous question I asked, I had looked in FM Help and it seems that FM Pro is not one of the supported dbs for ODBC (seems Microsoft wants to give Access the edge). See below:
-----
"Sorry, I had looked on the wrong FM Help page for my answer. Looks like Filemaker does not support an ODBC connection to either Excel or a txt file. See below. However, Microsoft did provide the ODBC drive - I went into Control Panel:Admin tools and was able to add a systemDSN driver for Excel and one for text files. However, when going through FM, it didn't list either, probably because of the limitation below:
Data sources supported in FileMaker 11.0
You can use the following data sources in the FileMaker Pro relationships graph. If you are importing ODBC data, or using the Import Records or the Execute SQL script step, you can use additional data sources, such as IBM DB2 or Informix ODBC.
• Oracle 9i
• Oracle 10g
• Oracle 11g
• SQL Server 2000
• SQL Server 2005
• SQL Server 2008
• MySQL 5.0 Community Edition (free)
• MySQL 5.1 Community Edition (free)"
I think you are thinking about this backwards, at least if I understand your original goal being: to connect a Word document to an FM database via ODBC in order to do mail merge. The Help info you quote above is for connecting FM to other database sources using ODBC, for the purposes of importing or interacting with the ODBC source, not for the other way around where FM is the ODBC source for something like Word or Excel.
I think the simplest solution is to upgrade to a more recent version of Word that supports FM as an ODBC data source. The version of Word you are using is 8 years and two versions old and while there may be a work-around - I seem to recall some methods with flakey ODBC drivers using FM 6 - it's probably time to pony up. You may be able to download a demo version of a more recent version of Word and test it but I'm pretty sure it works.
I think the simplest solution is to upgrade to a more recent version of Word that supports FM as an ODBC data source. The version of Word you are using is 8 years and two versions old and while there may be a work-around - I seem to recall some methods with flakey ODBC drivers using FM 6 - it's probably time to pony up. You may be able to download a demo version of a more recent version of Word and test it but I'm pretty sure it works.
ASKER
Maybe I did something wrong, but I went into Word 2007 on our new computer and it didn't have Filemaker Pro as an option either.
Do you have FileMaker set as a DSN in the Windows ODBC utility?
And do you have ODBC sharing turned on in the Sharing preferences in FileMaker?
ASKER
Sorry Will, things have been crazy around here. Yes, it is turned on. See screen print.
-OCBD-Sharing.JPG
-OCBD-Sharing.JPG
Open up your files and see if ODBC/JCBD is set to be on for "All Users" for each file.
ASKER
All were already set to open, but the "All Users" was grayed out. Then went back to MailMerge in Word and FM Pro was not listed in the drop-down or when I clicked on "New Source." See pics.
-ODBC2.JPG
-ODBC3.JPG
-ODBC2.JPG
-ODBC3.JPG
If you have it setup correctly the Type would presumably be "ODBC DSN", but you need to have added FileMaker as an ODBC source in the ODBC Source setup utility.
ASKER
Tried connecting as in the attachment, but it looks like "My Data Sources" is pointing to the wrong place. Couldn't find where to change that, but right now have to dash.
-ODBC.doc
-ODBC.doc
At least under Win XP, it is found under Start > Administrative Tools > Data Sources (ODBC) This opens the ODBC Data Source Administrator where you would set up a Data Source Name (DSN) for FileMaker.
ASKER
I did not find Administrative Tools on my local PC, so assuming you mean our SBS2003 server. Found it and tried unsuccessfully to add a system DSN for FM Pro, but FM Pro still was not an option.
I then Googled ODBC Filemaker Pro 11 and found the following. However, when I tried the instructions, I did not get very far because they were not files listed that I could select to open (see screenprint).
---
To share databases using ODBC or JDBC:
1. Open the database files.
To enable or change a file’s sharing status, you must open it with an account that has Manage extended privileges access privileges. See About accounts, privilege sets, and extended privileges for more information.
2. Choose File menu > Sharing > ODBC/JDBC.
The ODBC/JDBC Sharing Settings dialog box appears.
3. For ODBC/JDBC Sharing, click On.
4. Select the filename(s) to share from the list of open files.
Each FileMaker Pro database file can have one or more tables. FileMaker Pro fields are represented as columns. The complete field name, including any non-alphanumeric characters, displays as the column name.
Avoid using spaces in the field names of database files you intend to share via ODBC and JDBC, because some SQL query tools don’t allow spaces in column names.
5. Choose which users can access the file:
Choose this To provide access to these users
All users Users from any account.
Specify users by privilege set Users with accounts assigned one of the selected privilege sets. Click Specify to select the privileges that accounts must have to access the shared database file. Accounts that need access to the database file require the fmxdbc extended privilege Access via ODBC/JDBC. Users must enter their account name and password defined in Accounts and Privileges.
No users This default setting prevents any user from accessing the database using ODBC/JDBC.
6. Repeat steps 3 and 4 for each database you want to publish.
7. Click OK or choose additional settings.
Each FileMaker Pro database file that is open and set up for access is a separate data source (create a DSN for each individual FileMaker database file you want to access).
For details about supported SQL statements, expressions, Catalog functions, and client drivers, see the FileMaker ODBC and JDBC Guide. You can view this manual by choosing Help menu > Product Documentation > ODBC and JDBC Guide.
-ODBC5.JPG
I then Googled ODBC Filemaker Pro 11 and found the following. However, when I tried the instructions, I did not get very far because they were not files listed that I could select to open (see screenprint).
---
To share databases using ODBC or JDBC:
1. Open the database files.
To enable or change a file’s sharing status, you must open it with an account that has Manage extended privileges access privileges. See About accounts, privilege sets, and extended privileges for more information.
2. Choose File menu > Sharing > ODBC/JDBC.
The ODBC/JDBC Sharing Settings dialog box appears.
3. For ODBC/JDBC Sharing, click On.
4. Select the filename(s) to share from the list of open files.
Each FileMaker Pro database file can have one or more tables. FileMaker Pro fields are represented as columns. The complete field name, including any non-alphanumeric characters, displays as the column name.
Avoid using spaces in the field names of database files you intend to share via ODBC and JDBC, because some SQL query tools don’t allow spaces in column names.
5. Choose which users can access the file:
Choose this To provide access to these users
All users Users from any account.
Specify users by privilege set Users with accounts assigned one of the selected privilege sets. Click Specify to select the privileges that accounts must have to access the shared database file. Accounts that need access to the database file require the fmxdbc extended privilege Access via ODBC/JDBC. Users must enter their account name and password defined in Accounts and Privileges.
No users This default setting prevents any user from accessing the database using ODBC/JDBC.
6. Repeat steps 3 and 4 for each database you want to publish.
7. Click OK or choose additional settings.
Each FileMaker Pro database file that is open and set up for access is a separate data source (create a DSN for each individual FileMaker database file you want to access).
For details about supported SQL statements, expressions, Catalog functions, and client drivers, see the FileMaker ODBC and JDBC Guide. You can view this manual by choosing Help menu > Product Documentation > ODBC and JDBC Guide.
-ODBC5.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tried to but it was grayed out. Sorry, thought I mentioned that, but maybe it was in a previous post. Searched FM Help for enablingODBC and found the following; however, it seems to only apply if you want single source authentication and not my problem, or am I wrong?
---
Enabling ODBC data source single sign-on (Windows only)
If you work with database files hosted by FileMaker Pro or FileMaker Server that access ODBC data from Microsoft SQL Server, you can configure the host computer to enable single sign-on (SSO). ODBC data source single sign-on permits a client to use one Windows-authenticated login to access ODBC data in shared files.
Important Before you can enable ODBC data source single sign-on, your Windows domain administrator must:
• configure the Account is trusted for delegation security setting for your Windows user account on the client.
• configure the Trust this user for delegation and Use Kerberos only security settings for your Windows user account on the host computer.
• enable the Impersonate a client after authentication privilege for your Windows user account on the host computer.
• configure the ODBC DSN to use Windows authentication on the host computer.
• configure Microsoft SQL Server to use Windows authentication.
To enable ODBC data source single sign-on:
1. With the database open, chose File menu > Manage > External Data Sources.
The Manage External Data Sources dialog box appears.
2. Select an ODBC data source from the list and click Edit.
The Edit Data Source dialog box appears.
3. For Authentication, select Use Windows Authentication (Single Sign-on), enter the SPN (Service Principal Name), and click OK.
Note Your Windows domain administrator must provide the SPN, which is generally in this format: MSSQLSvc/<fully qualified domain name>:<port>
For example: MSSQLSvc/sql2005.filemaker
Notes
• In order to open a hosted database, the administrator must configure the FileMaker Server service to log in as the privileged user account. For more information, see FileMaker Server Getting Started Guide.
• ODBC data source single sign-on is not supported:
• on Macintosh.
• for ODBC data accessed from MySQL or Oracle.
• in versions of FileMaker Pro and FileMaker Server prior to version 10.
• in web publishing.
---
Enabling ODBC data source single sign-on (Windows only)
If you work with database files hosted by FileMaker Pro or FileMaker Server that access ODBC data from Microsoft SQL Server, you can configure the host computer to enable single sign-on (SSO). ODBC data source single sign-on permits a client to use one Windows-authenticated login to access ODBC data in shared files.
Important Before you can enable ODBC data source single sign-on, your Windows domain administrator must:
• configure the Account is trusted for delegation security setting for your Windows user account on the client.
• configure the Trust this user for delegation and Use Kerberos only security settings for your Windows user account on the host computer.
• enable the Impersonate a client after authentication privilege for your Windows user account on the host computer.
• configure the ODBC DSN to use Windows authentication on the host computer.
• configure Microsoft SQL Server to use Windows authentication.
To enable ODBC data source single sign-on:
1. With the database open, chose File menu > Manage > External Data Sources.
The Manage External Data Sources dialog box appears.
2. Select an ODBC data source from the list and click Edit.
The Edit Data Source dialog box appears.
3. For Authentication, select Use Windows Authentication (Single Sign-on), enter the SPN (Service Principal Name), and click OK.
Note Your Windows domain administrator must provide the SPN, which is generally in this format: MSSQLSvc/<fully qualified domain name>:<port>
For example: MSSQLSvc/sql2005.filemaker
Notes
• In order to open a hosted database, the administrator must configure the FileMaker Server service to log in as the privileged user account. For more information, see FileMaker Server Getting Started Guide.
• ODBC data source single sign-on is not supported:
• on Macintosh.
• for ODBC data accessed from MySQL or Oracle.
• in versions of FileMaker Pro and FileMaker Server prior to version 10.
• in web publishing.
ASKER
Will, so sorry. Since I have FM advanced as a client, it never occurred to me that we would have advanced on the server and we don't. Sorry for the lengthy interaction on something so simple.