Hmmm ... didn't work ...
I first ran it, and nothing changed. Then I noticed the code required if vLEName = "DBStaff", which is actually "DBColl#", and when I changed that, the headers started showing up, but it didn't populate from the record. So there must be something wrong with the lookup subroutine?
I tried fiddling a bit, but I can't get it to populate the record ...
Here's what my DataMergeDB.txt file looks like ...
DBID^DBName^DBAdd1^DBAdd2^DBCity^DBProv^DBPCod^DBCnt^DBList^DBRef#^DBColl#^Staff
Name^StaffTitle^StaffEmail^
326023.000000^ABC Company^123 Test Drive #7^^Hamilton^ON^L5T 1K4^Fred^525.000000^290310-3^BW^^^^
// This is pasted from Inside Sesame 2005-04 & Modified - 2005-06-08 BW
var vFileHandle as Int // For writing merge data source file for Word
var vHeader as String // Data source file header line
var vData as String // Data source file data line
var n as Int // General purpose counter var
var vSelectedDoc as String // The selected Word doc
var vOneOrAll as String // Include record(s) option
var vGoPrint as String // Printing flag
var vNames as string // Form LE names from vNamesPath text file
var vStartingRec = @ResultSetCurrentPosition()
var vOK as Int // added var for new @Shell to do a DOS Dir, 11 Jan 2005
var vLttrInfo as string // letter data for DBLttrLog0 field
var vLttr as string // selected document name without the .doc ext
Var vNewNoteNo as String
var vNewNote as String
var vCount as Int
var vNewFileID as Int
var vLEs as String = @StringArrayElementList() // All LE's on current form
var vDocList as string // The document list converted to stringarray if nec.
var vDataPath as String = "C:\Sesame\WordMrg\DataMergeDB.txt"
var vNamesPath as String = "C:\Sesame\WordMrg\LEListDB.txt"
var vDocsPath as String = "C:\Sesame\WordMrg\"
var vDocListPath as String = "C:\Sesame\WordMrg\DocListDB.txt"
// Command Line for Directory command // this does not need to be modified.
var vDir as string = "dir /b /o:n " + vDocsPath + "*.doc >" + vDocListPath
// Ray ADDED 2010-12-24
Var vStaffLEs as String = "StaffName;StaffTitle;StaffEmail"
Function AddStaffInfo(vDBStaffID as String) as String
Var vVals as String
Var vReturn as String
vVals = @XLookupSourceList(@FN, vDBStaffID, "Staff!StaffRef", vStaffLEs)
If vVals <> ""
{
vReturn = vReturn + @AccessStringArray(vVals, 1) + "^"
+ @AccessStringArray(vVals, 2) + "^"
+ @AccessStringArray(vVals, 3) + "^"
}
Else
{
vReturn = vReturn + "^^^"
}
Return(vReturn)
End Function
SUBROUTINE PrintCurrentRecordOnly()
var n as Int
Var i as Int
Var vLEName as String
vGoPrint = "Yes"
For n = 1 to @CountStringArray(vNames)
vLEName = @AccessStringArray(vNames,n)
vHeader = vHeader + vLEName + "^"
vData = vData + @(vLEName) + "^"
If vLEName = "DBColl#" Then
{
For i = 1 to @CountStringArray(vStaffLEs)
vHeader += @AccessStringArray(vStaffLEs, i) + "^"
Next
vData = vData + AddStaffInfo(@(vLEName))
}
Next
vData = @Replace(vData, @Newline(), " ")
vFileHandle = fileOpen(vDataPath)
fileSeek(vFileHandle, 0)
fileWriteLn(vFileHandle, vHeader + @NewLine() + vData)
fileClose(vFileHandle)
END SUBROUTINE
SUBROUTINE PrintAllRetrievedRecords()
var n as Int
var i as Int
Var vLEName as String
If @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
Then
{
vGoPrint = "Yes"
For n = 1 to @CountStringArray(vNames)
vHeader += @AccessStringArray(vNames,n) + "^"
Next
For n = 1 to @CountStringArray(vStaffLEs)
vHeader += @AccessStringArray(vStaffLEs,n) + "^"
Next
vFileHandle = fileOpen(vDataPath)
fileSeek(vFileHandle, 0)
fileWriteLn(vFileHandle, vHeader)
For n = 1 to @ResultSetTotal()
ResultSetCurrentPosition(n)
For i = 1 to @CountStringArray(vNames)
vLEName = @AccessStringArray(vNames,i)
vData += @(vLEName) + "^"
If vLEName = "DBColl#" Then
{
For i = 1 to @CountStringArray(vStaffLEs)
vHeader += @AccessStringArray(vStaffLEs, i) + "^"
Next
vData = vData + AddStaffInfo(@(vLEName))
}
Next
vData = @Replace(vData, @Newline(), " ")
FileWriteLn(vFileHandle, vData)
@Msg("Processed Record " + n + " of " + @ResultSetTotal())
vData = ""
Next
fileClose(vFileHandle)
// Rewind to record where it all started
ResultSetCurrentPosition(vStartingRec)
}
END SUBROUTINE
/*
// THESE lines are commented out,
// but can be activated if you need to DEBUG.
WriteLn("vDataPath = " + vDataPath)
WriteLn("vNamesPath = " + vNamesPath)
WriteLn("vDocsPath = " + vDocsPath)
WriteLn("vDocListPath = " + vDocListPath)
WriteLn("vDir = " + vDir)
*/
// BEGIN MAIN PROGRAM
// Get the list of specified LE's on this form (for the data source file).
// Turn it into String Array by replacing C/R's with semicolons
vNames = @Insert(vNamesPath)
vNames = @Replace(vNames, @NewLine(), ";")
// If file missing or empty, bring everything to grinding halt.
If vNames = "" Then
@Msgbox(vNamesPath + " file missing!","LE Names for merge file unavailable.","Can't proceed.")
Else
{
// Added by TJM 8 Jan 05 to report any LE names in
// vNames that are not represented on current form
For n = 1 to @CountStringArray(vNames)
If @Instr(vLEs, @AccessStringArray(vNames, n)) = 0 Then
{
WriteLn("LE name '" + @AccessStringArray(vNames, n) + "' not in current form.")
WriteLn("(Ref. File: " + vNamesPath + ")" + @NewLine())
}
Next
// Build Word doc selection menu
// Set position of picklist to the left of and above the command button
PopupSelectPosition(4,@Xpos(ThisElement)-125,@Ypos(ThisElement)-50)
// generate current list of docs
vOK=@shell(vDir)
// Convert the document list from one-per-line to semi-colon separated if nec
vDocList = @insert(vDocListPath)
vDocList = @Replace(vDocList, @NewLine(), ";")
vSelectedDoc = @PopupMenu(vDocList, "SELECT MERGE DOCUMENT" )
If vSelectedDoc <> "" Then
{
// Build which record(s) menu
// Prevents Users from printing batch letters -- 2005/10/31 BW
vOneOrAll = @PopupMenu("THIS Record Only;ALL Retrieved Records","SELECT RECORD(S) TO MERGE PRINT")
if vOneOrAll = "All Retrieved Records" and @group = "Users" then
{
@msgbox("You do not have access to batch letter printing!","","")
vOneOrAll = ""
}
// Reset Popup position
PopupSelectPosition(0,99,99)
If vSelectedDoc <> "" and vOneOrAll <> "" Then
{
// Delete exsting data file. New one will be generated.
If FileExists(vDocsPath + vSelectedDoc) Then
{
fileDelete(vDataPath)
If vOneOrAll = "THIS Record Only" Then
PrintCurrentRecordOnly()
Else
PrintAllRetrievedRecords()
If vGoPrint = "Yes" Then
// n = @Shell("START Winword /mMerge_to_New_Doc " + @Chr(34) + vDocsPath + vselectedDoc + @Chr(34))
n = @ASynchShell(vDocsPath + vselectedDoc)
}
Else
@Msgbox(vDocsPath + vSelectedDoc + " doesn't exist.","","Aw shucks!")
}
}
}
// Log Letter sent
if vOneOrAll = "THIS Record Only" then
{
vLttr = @replace(vSelectedDoc,".doc","")
vLttrInfo = @Serverdate() + " -- " + vLttr + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
if @Len(VLttrInfo) >60 then vLttrInfo = @left(vLttrInfo,60) + " ... "
DBLttrLog0 = vLttrInfo + @NewLine() + DBLttrLog0
DBNote0 = "Sent " + vLttr + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
}
vCount = @FormResultSetTotal("Note Data")
If DBNote0 <> "" then
{ If vCount = 1 Then
{ If @FormFieldValue("Note Data", "Note", 1) <> "" Then
{ vNewNoteNo = @FormNewRecord("Note Data")
}
Else
{ vNewNoteNo = 1
}
}
Else
{ vNewNoteNo = @FormNewRecord("Note Data")
}
}
Else
{ If DBNote0 = "" THEN @MsgBox ("", "You Cannot Enter a Blank Note!","")
}
gFileID = @ToNumber(@GlobalValue("gFileID")) +1
vNewFileID = gFileID
GlobalValue("gFileID", vNewFileID)
FormFieldValue("Note Data","NoteRef",vNewNoteNo,DBID)
FormFieldValue("Note Data","NoteDate",vNewNoteNo,@ServerDate())
FormFieldValue("Note Data","NoteTime",vNewNoteNo,@ServerTime())
FormFieldValue("Note Data","Note",vNewNoteNo,DBNote0)
FormFieldValue("Note Data","NoteID",vNewNoteNo,vNewFileID)
FormFieldValue("Note Data","NoteSig",vNewNoteNo,@UserID)
vNewNote = @FormFieldValue("Note Data","Note",vNewNoteNo)
ForceRedraw()
gPosted = 1
DBNote0 = ""
Pages: [1] 2 
Send Topic 
Print
MailMerge Fetching Info (Read 2948 times)
