Using MERGEFIELD "If-then-else" with part of a string

[Michelle Craig]

I have a question that may have an answer already, but I'm not sure if it's
the same thing. I am extracting a file from an Oracle database that contains
a number of fields. This file is a csv with a .txt extension (as the extract
process does not give the option of creating a file with a .csv extension).
One of the fields is CHKL, which includes a concatenated list of checklist
items that is separated by spaces. For example, the checklist items for a
particular person might include the text "$30 Application Fee(space)GRE
Scores(space)Letter of Reference", etc.

What the end-user wants is to have text show in the merged document that
specifically references the application fee ONLY IF the record has that text
in the CHKL field. It is easy to do this in SQL with a "contains" in a WHERE
clause. Unfortunately, Word 2003 doesn't let you do this.

If anyone has any ideas on how I might accomplish this, I would greatly
appreciate it. At this point, the merge document just has text pertaining to
the application fee whether or not the CHKL field contains that item or not.
They can live with this for the "go-live", but it would be nice and elegant
to be able to do it right.

Thanks so much,

 

[Peter Jamieson]

There are a number of possible approaches to this kind of problem, e.g.
1. If you know in advance the precise criteria you need to be looking for,
you can include an additional column in the SQL or View you use to extract
data - I'd have to look up the correct Oracle SQL for that myself, but
perhaps something like

SELECT ...., contains(CHKL, 'Application Fee') as 'appfee', ... FROM ...

Then in Word you should be able to use a nested IF field such as

{ IF { MERGEFIELD appfee } > 0 "the text and fields you want if there is an
appfee" "the text and fields you want if there isn't an appfee" }

2. Word's IF field can use wildcards in comparisons, but they are limited,
particulary in the sense that you can't do a contains by matching
"*Application Fee*" (the limitation is that you can't have the
multicharacter wildcard "*" at both ends of the expression). Also, there are
text/string length limitations in these comparisons. However, if you know
that the Application Fee string is always at the beginning of the list, and
if you know that the text before the term "Application Fee" can only be
certain lengths - say, 4 or 5 characters, you may be able to use

{ IF { MERGEFIELD CHKL } = "????Application Fee*"
"{ SET appfee 1 }"
"{ IF { MERGEFIELD CHKL } = "?????Application Fee*"
"{ SET appfee 1 }"
"{ SET appfee 0 }" }"
"{ SET appfee 0 }" }{ IF { REF appfee } > 0 "the text and fields you want if
there is an appfee" "the text and fields you want if there isn't an
appfee" }

3. If your data source is a text file with 254 columns or fewer, you may be
able to open it as a data source using the OLE DB provider, which uses the
Jet (Access) database engine. In that case, you can use VBA and WOrd's
OpenDataSource method to specify an SQL query that does much the same as
option (1), i.e. creates a new column that makes it possible to use a Word
IF field to do what you want. But let us know if you want o go that route,
because unless you are using your .txt file for a number of different
merges, I suspect you would be better off doing the query in Oracle.

And don't forget that you can connect a Word Mail Merge Main document
directly to an Oracle Table or View, or - if you are willing to use VBA - by
specifying an Oracle SQL query in OpenDataSource (but the query length is
limited to either 255 or 511 characters).

Peter Jamieson