Show lines of Text Based on delmited String

[Pip''''n]

I have a mail merge that needs to display various lines of text based on a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop through the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n

 

[Peter Jamieson]

In this case I think you will need to modify your data source (i.e. create
the data source you really need), and you may find that when you do so, it
will make sense to use a report generator such as the one in Access to
produce your output rather than Word. If you still need or want to use Word,
you need to think about the overall merge (e.g. is it a "one-to-many" merge,
which Word is not really designed to do?)

What is the data source at the moment? Which version of Word?

Peter Jamieson

 

[Pip''''n]

Currently the Data Source is Microsoft SQL Server 2000 and we're using Word
2007.

The query that i'm using is built dynamically to single out a single record
by a Deal ID. The database is set up in a way that a Deal can have multiple
products, but those products are stored as a comma delimted string in a
single field. (Dont ask me why they did this)

The field that contanis the string is dbo.tblDeals.ProductLookup

The document is called from a Classic ASP page that generates the database
view which is called by the word document. In the page they choose the
account and deal that they want to create the contract for.

Here is the SQL:
SELECT dbo.tblDeals.DealID, dbo.tblAccounts.Type,
dbo.tblAccounts.AccountName, dbo.tblAccounts.Address,
dbo.tblAccounts.Address2, dbo.tblAccounts.City,
dbo.tblAccounts.Province, dbo.tblAccounts.PostalCode,
dbo.tblAccounts.Country, dbo.tblProvinces.Name AS ProvinceName,
dbo.tblDeals.DecisionMakerContact, DATEPART(dd, { fn
NOW() }) AS SignupDay, { fn MONTHNAME({ fn NOW() }) } AS SignupMonth,
DATEPART(yyyy,
{ fn NOW() }) AS SignupYear,
dbo.tblContacts.ContactName, dbo.tblContacts.ContactTitle,
dbo.tblContacts.ContactFirstName,
dbo.tblContacts.ContactSuffix,
dbo.tblDeals.ProductLookup FROM dbo.tblDeals INNER JOIN
dbo.tblAccounts ON dbo.tblDeals.AccountLookup =
dbo.tblAccounts.AccountID INNER JOIN
dbo.tblProvinces ON dbo.tblAccounts.Province =
dbo.tblProvinces.Abbreviation LEFT OUTER JOIN
dbo.tblContacts ON dbo.tblDeals.DecisionMakerContact =
dbo.tblContacts.ContactID
WHERE (dbo.tblDeals.DealID = 1827)

 

[Peter Jamieson]

Something I forgot to ask before: even if you were able to split the
individual ProductIDs out from the ProductLookup field, how would you get
the corresponding Product descriptions during the merge? If the list of
product IDs is small and stable, you could perhaps do it using IF fields and
testing for each ProductID in turn. Otherwise, there's a problem, because
a. the number of Products varies (is there a known limit?)
b. you might be able to do the lookup using a DATABASE field, but these
days, DATABASE fields are more badly behaved than they used to be because
they insert an extra carriage return after the result, even when the result
is a single field

What I would do depends on what technologies I could make use of.
For example, if in this case you are in a position to write a Transact-SQL
procedure/function, I am fairly sure it would be possible to write one that
would take the ProductLookup field as input, and output either a multiline
text field containing "ProductID: Product Description" for each product, or
(not so sure with SQL Server 2000) return a table containing one row with
Product ID and Descriptions. What I'm not at all sure about is whether you
could use the results of those procedures/functions easily in a merge.
Or if you can use Word VBA, then you could for example
c. use Word MailMerge Events to inspect ProductLookup, do the necessary
lookups using ADO, and insert the results into the mail merge main document
pre-merge
d. avoid Word MailMerge altogether and just use ADO to get all the data you
need and build your document using that.

Depending on feasibility etfc. I would have a look at the Transact-SQL
approach first, but in the end there's a lot to be said for heading straight
for (d).

If all that stuff is beyond your scope, I don't know what else to suggest...

Peter Jamieson

 

[Pip''''n]

Currently the Descriptions are written for this specific document. They were
Hard Coded into the Contract document and the Salesmen would just remove the
ones that they didn't need. This happens in about 4 different locations in
the document.

There are 22 products which correspond to 13 different descriptions (some
products use the same description)

Right now I have it set up with If statements which would work if there was
only one product chosen however there can be multiple.

 

[Peter Jamieson]

Well, assuming you are trying to stick to using fields, and assuming you
therefore have a maxmum of 22 different product codes, you may be able to do
it this way - at the moment I'm not in a position to test:
a. enable the Database toolbar in Word, and click the Insert database
button.
b. follow the steps to select your SQL Server view and insert it as a
field. (I am assuming that your mail merge data source is a .odc - you will
probably need to use the same one, or create a suitable .odc and use that.
If you are using an ODBC DSN things may be rather harder, but see how far
you get). Make sure you can update the DATABASE field successfully
c. use Alt-F9 to look at the { DATABASE } field you inserted. It should be
something like this:

{ DATABASE \d "pathname of your .odc" \c "an OLE DB connection string" \s
"SELECT * FROM ""your view name""" \h }
d. Let's suppose the first possible ProductID is "1". Change the SELECT so
that it reads

SELECT PATINDEX('1',dbo.tblDeals.ProductLookup) +
PATINDEX('1,%',dbo.tblDeals.ProductLookup) +
PATINDEX('%,1,%',dbo.tblDeals.ProductLookup) +
PATINDEX('%,1',dbo.tblDeals.ProductLookup) FROM ""your view name""

Change the \h to \#0

e. try executing that - you may have to adjust the syntax. However, the
general idea is that if the string is neither "1", starts with "1,",
contains ",1," nor ends with ",1" the result will be "0"

If you manage to get that to work, what you would then need to do is nest it
inside an IF field, e.g.

{ IF "{ DATABASE all that stuff }" = "0" "" "Product 1: product 1
description" }

f. then, you need to repeat that dfor all the other possible values of
product ID.

Perhaps you can see why I was a little bit reluctant to go down this route,
when it's probably easier to achieve in VBA. But there's no harm in
trying...

Peter Jamieson

 

[Pip''''n]

That's great, your idea worked. I just had to change the If statemnt to be >
0 rather than = 0.

One error that I have found however, is that if i have a productlookup
string that contains 33 that the product 3 description will also show.

I have modified the productLookup field to be ', ' + ProductLookup + ','
This removes the requirements for the multiple PatIndexes and leaves me with
only
"SELECT PATINDEX('%, 23,%', ', ' + ProductLookup + ',') FROM
\"mailMerge_Service\""

And the problem is fixed.
Thanks for all your help,

Pipn

 

[Peter Jamieson]

Glad you managed to find a way through that - I wasn't at all sure it could
be made to work.

Peter Jamieson

 

[Pip''''n]

I have encountered a new issue. I am now told that I need to display a cost
field for these products which is held in a tblProducts field.

I created a new Database field with the new select statement however when
the merge is run an error box comes up saying that the required merge fields
are not available. I assume this is because the FROM \"tblProducts\" is not
the same as the original merge source of FROM \"mailMerge_Service\"" which
is used in all of the other database fields.

Is there a way to have this new connection to the cost fields without losing
the connection to the mailMerge_Service view?

 

[Peter Jamieson]

Well, there is sometimes a problem where Word gets confused about what the
data source is when you have both a regular data source and a DATABASE
field - however it doesn't seem to be quite as simple as "it doesn't work if
the tables in the data sources are different", as here I have merges that
work with a mail merge data source and two different DATABASE data sources.
I'll see if I can find out anything more about precisely what makes it fail.
Meanwhile
- I have to say you are beginning to push Word quite hard, and your
customer appears to require you to work with some rather poor data
structures. Do you have to do this using Word? Are you in a position to use
Word VBA if you need to?
- it might help if you could post the text of the dtaabase field (or is it
fields?) here.

Peter Jamieson

 

[Pip''''n]

I spoke with the users of the document generation that i'm creating and we
dont have to worry about populating these extra fields.

Thanks for all your time and effort.