Data from multiple tables as ODBC data source e.g. layout as data source

[Ashlin]

Hi there, am new to Filemaker and these forums. I've moved from working in MS Access and am loving FM so far.

Problem:

I am using FM as ODBC data-source for MS Word mail merge.

I would like the merge source to have data from multiple related tables. In Access this would have just required using a query as the data source. However, FM as ODBC only appears to allow use of tables and not layouts.

Is there a way to setup a layout as an ODBC or do I have to create a behemoth table which has all the data I need?

Alternatively, am open to any other suggestions.

I don't think using Form letters inside FM is suitable as I need to be able to edit comprehensively (Hence using MS Word) before sending.

[Vaughan]

Create calculated fields that bring the related data in to the main table, then merge these calc fields.

I don't think using Form letters inside FM is suitable as I need to be able to edit comprehensively (Hence using MS Word) before sending.

I've seen solutions done both ways. The solution that used Word had a real Rube Goldberg feel about it, because templates had to be in Just the right place and macros had to be enabled, the right version of Word had to be used, and even so the user was prompted with various messages from Word along the way.

I prefer doing everything in FMP because if it's done in Word then the actual message that was sent is not known to the database, and there is no way to get it back in to the database (at least not without some equally Goldberg-esque method).

[Ashlin]

Thanks Vaughan,

I've made a separate 'merge' table that just requires a primary key and then a bunch of lookups to get the data I wanted into that table.

I didn't want to clog up my main table with too many fields. I also have the advantage that my naming schema for merge fields will always be constant as it relies on merge table not source tables where I might decide to change field names etc. at some point. I know that's bad but you always end up seeing things more clearly once you start building rather than on a paper plan.

I have only briefly tried to make a form letter and found it very difficult to get everything the way I wanted. I'll play around some more as I agree it would be nice to do everything from FM.