Next up in my series of summer shorts is an interesting lesson learned while completing a “tiny” SharePoint Designer workflow. This started over on my AIIM blog where I was explaining how I was fixing a contact list that had been built backwards. Instead of setting all the contact and address components, people were simply entering a mailing address in complete form. That approach works well for preparing a label, but not so well for writing the letter, unless you like starting out by saying “Dear customer” or “Hey you.”
Fixing the contact list was supposed to be an easy task. A guy on my team wrote a small program to snag the address block and bust it into all the little address components. We pasted those into the contact list (after adding back in the missing component columns) and to keep the mailing label users happy, a short workflow would reassemble the multi-line column continent. In addition, I was demonstrating SharePoint and SharePoint Designer to a woman who will be a pinch-hitter on our team. Of course, this is SharePoint so everything didn’t go according to plan, but that’s a learning experience too. Below is a quick summary of the things we learned along the way:
Microsoft is in Washington – I used to live in Seattle, and there are people on the left coast who are surprised by the fact that Zip codes in the northeast begin with zero. When we parsed the composite addresses into components, we saved them as a CSV file. When I double-clicked to open that file, 06033, the Zip code string for our office turned into 6033, the number. Pasting that into SharePoint failed because the column was looking for text.
There’s an interesting twist to this. If you open Excel first, you can tweak the text settings and then import the CSV. That works well for the zip codes, but we discovered a different problem. The multi-line text fields export from SharePoint well, and import nicely if you double-click the CSV file. If you open the CSV file as an import target, expect a few problems with the multi-line fields. Fortunately, we don’t need the multi-line fields, but we are researching this for the day that we do, but I digress.
String Builder is a Tease – Building a composite string from component text variables is pretty easy in String Builder. For example, you can type:
[City], [State] [Zip]
which will result in a single line, with comma and nicely spaced. You can also type:
[Address line 1]
[Address line 2]
[Address line 3]
[Address line 4]
which will also result in a (rather unreadable) single line string.
No problem, Google and all the wonderful members of the SharePoint community came to the rescue. There were a couple of solutions offered for adding line breaks to multi-line text but the one I liked was simply adding the html break <br/> at the end of the line. There’s an interesting and understandable twist to this too – this only works if the multi-line text column is configured for Rich Text. That compounds the earlier twist, because the import problem doesn’t affect plain-text multi-line columns, only rich text.
I Can’t Run This Workflow on Every Item in the List – Actually, I can but only after applying a little client-side script magic. If you want to learn how to do that, jump over to the king of how-to-make-SharePoint-do-the-hard-stuff blogs and read one of Marc Anderson’s SharePoint Services Stories (clever name) – actually, it’s the very first SharePoint Services Story. Marc’s blog is my go-to resource, and his solution is elegant and reliable, but I didn’t really need it today. As you may recall, I already had the composite columns. I just had to make sure that those fields would get created and updated when new entries are created and old entries are changed – I think that’s called testing.
In case you’re wondering, we wrapped each of those address line thingies in an If-statement so we didn’t end up with blank lines.
Have a great weekend!