*--Create two tables and put 3 records in each linked by ct_id field
*--Query both tables and create a single merge table
*--Send to a text file to be used in the word merge process

CLOSE ALL
SET TALK OFF
SET SAFETY OFF

*--Create parent table with name and address and salutation
CREATE TABLE table1 (ct_id N(5,0), po_name c(20), po_addr1 c(20), po_greetin c(20))
SELE table1
APPEND BLANK
REPLACE ct_id WITH 1
REPLACE po_name WITH "John Smith"
REPLACE po_addr1 WITH "PO BOX 1234"
REPLACE po_greetin WITH "Mr. Smith"
APPEND BLANK
REPLACE ct_id WITH 2
REPLACE po_name WITH "Cary Jones"
REPLACE po_addr1 WITH "PO BOX 5678"
REPLACE po_greetin WITH "Ms. Jones"
APPEND BLANK
REPLACE ct_id WITH 3
REPLACE po_name WITH "Sally Hanson"
REPLACE po_addr1 WITH "123 Main Street"
REPLACE po_greetin WITH "Miss Hanson"

*--Create child table with account number linked by ct_id field
SELE 0
CREATE TABLE table2 (caccount c(15), ct_id N(5,0))
SELE table2
APPEND BLANK
REPLACE caccount WITH "12345"
REPLACE ct_id WITH 1
APPEND BLANK
REPLACE caccount WITH "23456"
REPLACE ct_id WITH 2
APPEND BLANK
REPLACE caccount WITH "345676"
REPLACE ct_id WITH 3

PRIVATE lcTemp,lnFields,lcHeader
PRIVATE loWord,llToPrinter,lcSourceDoc
PRIVATE cAlias,cTemplateDoc

lcAlias = "MergeThese"
lcTemplateDoc = ""

*--Create a query of the two tables containing the data we need for the merge doc
SELECT talias1.ct_id, talias1.po_name AS po_name, talias1.po_addr1 AS po_addr1, ;
   IIF(EMPTY(talias1.po_greetin),talias1.po_name,talias1.po_greetin) AS po_greetin, ;
   talias2.caccount AS po_acct, talias2.ct_id FROM table1 talias1, table2 talias2 ;
   WHERE talias1.ct_id = talias2.ct_id INTO CURSOR mergethese

SELECT (lcAlias)
lcTemp = SYS(2015)+'.tmp'
lnFields = FCOUNT()
COPY TO (lcTemp) TYPE DELIMITED WITH "" WITH TAB
lcHeader = ''
FOR ix = 1 TO FCOUNT()
   lcHeader = lcHeader + ;
      IIF(EMPTY(m.lcHeader),'',CHR(9)) + ;
      FIELD(m.ix)
ENDFOR
lcSourceDoc = SYS(5)+CURDIR()+'MergeSource.txt'
STRTOFILE(m.lcHeader+CHR(13)+CHR(10)+FILETOSTR(m.lcTemp),m.lcSourceDoc)
ERASE (m.lcTemp)

loWord=CREATEOBJECT("word.application")
WITH loWord
   IF EMPTY(m.lcTemplateDoc)
      .documents.ADD()
   ELSE
      .documents.ADD(m.lcTemplateDoc)
   ENDIF
   *--Create our own template on the fly
   WITH .Activedocument.Mailmerge
      .OpenDataSource(m.lcSourceDoc)
      .EditMainDocument
      .APPLICATION.SELECTION.TypeText("Insured : ")
      .FIELDS.ADD(.APPLICATION.SELECTION.RANGE,'po_name')
      .APPLICATION.SELECTION.TypeText(CHR(13)+"Address: ")
      .FIELDS.ADD(.APPLICATION.SELECTION.RANGE,'po_addr1')
      .APPLICATION.SELECTION.TypeText(CHR(13)+"Account: ")
      .FIELDS.ADD(.APPLICATION.SELECTION.RANGE,'po_acct')
      .APPLICATION.SELECTION.TypeText(CHR(13)+"Dear: ")
      .FIELDS.ADD(.APPLICATION.SELECTION.RANGE,'po_greetin')
      .APPLICATION.SELECTION.TypeText(CHR(13)+"Hello ...."+CHR(13))
   ENDWITH
   .VISIBLE = .T.
   .ACTIVATE
ENDWITH

CLOSE ALL
RETURN