 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / July 2007Show lines of Text Based on delmited String
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 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 >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 [quoted text clipped - 21 lines] > > 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) > 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 [quoted text clipped - 31 lines] > > > > Pip'n 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 > Currently the Data Source is Microsoft SQL Server 2000 and we're using > Word [quoted text clipped - 77 lines] >> > >> > 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. > 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 [quoted text clipped - 110 lines] > >> > > >> > Pip'n 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 > Currently the Descriptions are written for this specific document. They > were [quoted text clipped - 142 lines] >> >> > >> >> > 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 > 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 [quoted text clipped - 186 lines] > >> >> > > >> >> > Pip'n Glad you managed to find a way through that - I wasn't at all sure it could be made to work. Peter Jamieson > That's great, your idea worked. I just had to change the If statemnt to be > > [quoted text clipped - 234 lines] >> >> >> > >> >> >> > 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? > Glad you managed to find a way through that - I wasn't at all sure it could > be made to work. [quoted text clipped - 238 lines] > >> >> >> > > >> >> >> > Pip'n 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 >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. [quoted text clipped - 289 lines] >> >> >> >> > >> >> >> >> > 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. > 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 [quoted text clipped - 277 lines] > >> >> >> >> >on > >> >> >> >> >a
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.