[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

FM - Using Filemaker to Merge a Word Document

Posted on 2011-05-02
19
Medium Priority
?
1,505 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:rvfowler2
  • 9
  • 9
19 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 35508270
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.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35508438
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.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35508601
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Paul Sauvé
ID: 35509025
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?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35515217
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)"
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35516141
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.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35516424
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.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35516542
Do you have FileMaker set as a DSN in the Windows ODBC  utility?
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35516548
And do you have ODBC sharing turned on in the Sharing preferences in FileMaker?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35701689
Sorry Will, things have been crazy around here.  Yes, it is turned on.  See screen print.
-OCBD-Sharing.JPG
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35701776
Open up your files and see if ODBC/JCBD is set to be on for "All Users" for each file.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35702134
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
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35702264
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.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35702481
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
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35702543
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.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35703847
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
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 2000 total points
ID: 35706011
In order to use FileMaker Server as an ODBC source you must be using FileMaker Server Advanced. That is the key difference in FMS 11 between the regular and the Advanced versions. Assuming you have FMSA, you must go into the FMS Admin Console and make sure FMS is set to allow connections via ODBC/JDBC.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35706141
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.com:1433
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.

0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 35721582
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question