12/18/2007
I was testing out a workflow I created that pauses for a duration of time before sending a reminder. Although I intend to set my pausing duration to 14 days, I set it to 1 minute to test it. I was surprised to find that "1 minute" took 7 minutes the first time and 20 minutes the second time. I wonder why this happens. Does anyone know?
11/13/2007 UPDATE: For whatever reason, the mdb file I had uploaded wouldn't work. I zipped up the file and the new link is reflected below. Thanks to Itay for pointing this out to me! I really like how easy it is to create alerts in SharePoint Designer 2007, but I found a few aspects of it's "send e-mail" feature rather annoying: - Every carriage return in the alerts is processed as a <BR> or a <P> (HTML code) when the e-mail is sent. This looks really bad.
- You can add style sheets to the e-mail, but because of the above problem, you have to create it as if it were a single line without line breaks. This is annoying.
- You can't copy the entire contents of an e-mail that has workflow lookups inserted to the body. This makes for a tough process of trying to copy code from one e-mail body to another.
I wasn't content with plain text e-mail alerts, so I copied the HTML out of one of SharePoint's default alerts and used that as a starting point. I cut up the contents of the file and put snippets of code in a Microsoft Access Database. I then made a form that helps generate the final formatting of the e-mail alert. You can insert "placeholders" for where you intend to add workflow data lookups to your e-mail alert. After you specify what fields you want, you can preview the alert in Internet Explorer (sorry Firefox users) and copy the code (without line breaks) that you'll need to paste into SharePoint Designer. Here's a look at what the application looks like: 
Check out what a "Preview Alert" looks like: The idea is that you let the application create the code for you and then you copy the code and paste that line into SharePoint Designer. If you added lookup placeholders to your code, you then go through and delete the placeholders and put the actual lookup in its place. Here's what SharePoint Designer looks like after you've added the lookups to your code: 
I've included a link the the Access application below, but you should note a few things: - The file is available as is with no warranties or support, expressed or implied.
- The preview is set up to work only with Internet Explorer (unless someone wants to write a module for me that calls the application for the default Windows browser).
- You'll need to manually copy the contents of the box at the top of the window. The contents of this box are generated when you click "Process Alert!"
- After copying the code into SharePoint designer, you'll need to replace the placeholders with actual lookups.
I'm open to comments and suggestions regarding this tool, but I can't promise to be able to support it. I'll try to fix bugs if I have time. Download it here:
I originally learned how to do this from a post in Ian Morrish's blog:
I decided to do my conditional formatting by row, since I was showing or hiding the contents of an entire row of data based on whether or not the user had the correct security rights. to select a row, click in the area you want to select and then click <tr> at the top of SharePoint Designer:
To open conditional formatting, right click on the selected area and click "Conditional Formatting."
After you create the rule to show the contents of the selelection (you'll need to click the "advanced" option), add the text ddwrt:IfHasRights([permission mask value]). You can find the values for this field at the link to Ian Morrish's blog that I listed above.
You can see in the above example that I used the value 2048 for those who have permission to manage the list. I believe this applies to people set up as "owners" of the list.
This is a quick and dirty way to trim your pages based on a user's security privileges. If anyone knows how to do it based on whether the user belongs to a specific security group, that would be helpful, so let me know.
Fairly soon, I plan on posting soon my tool that helps create e-mail alerts from workflows in SharePoint Designer 2007. 11/12/2007It's been nearly a month since my last post. Things have been busy here at United Supply, leaving me little time to do any quality blogging. However, because I have to run a report every Monday morning that requires me to download oodles of data (it takes quite a while) I figure I'll try to produce something substantive while I bide my time, rather than check out the latest low-priced Zune offering.
Managing Freight Claims at United Supply
At United Supply, we ship many of our orders using third party freight carriers such as UPS or FedEx. When the goods arrive damaged or a package is lost in transit, we as the shipper of the goods are usually responsible for filing a freight claim with the carrier.
The way our phone system is set up, a customer could call customer service and get a representative from any one of our branches to file a claim. In this set-up, a person a customer service representative (CSR) could likely take a call from a customer where the goods were shipped out of our Charlotte branch.
In the past, when a claim was called in to customer service, the CSR would fill out a form (by hand) and then fax that form to a person in Charlotte, who would file the claim with the freight carrier. On occasion, the CSR would call the carrier and file the claim over the phone, which, because it was filed over the phone instead of online, prevented it from being tracked over the internet.
If the customer called in to check the status of their order, the CSR would have to call the freight claim manager in Charlotte, who would then check her Excel spreadsheet to see the status of that claim based whether or not the background of that row in Excel was highlighted.
If the claim was approved, our freight claim manager would put her paperwork together and then submit it to a person in the accounts receivable department who would use that information to create a credit for the customer.
This was a lot of manual work!
Enter SharePoint
Another person in the company was trying to create a form in InfoPath that customer service could fill out and then e-mail to our Freight Claim manager in Charlotte. This might have been great, except for the fact that we don't have InfoPath here at United Supply but for that one person's computer. However, I looked at the scenario and saw immediately that SharePoint would be a great solution.
Here's why I found SharePoint to be ideal in this situation:
- SharePoint gives us one list that can be centrally managed
- SharePoint can be set up so that adding an item to a list has the look and feel of filling out a form.
- Because it's managed on a web site, there's no need to call someone else to check an Excel file managed on their local computer.
- Automated workflows can be set up to alert various people when claims reach a certain status.
With much excitement, I went to work immediately to see what I could turn out. Because I knew I would be training many people who are not entirely familiar with navigating the world wide web, I decided to create custom web part pages in SharePoint Designer that would be used to facilitate the process.
The first step in the process was to create what I called the "Freight Claim Home Page." I wanted to put both look-up and entry features in one place so that customer service would have one point of reference to begin their freight claim pursuits. I used three different SharePoint lists and a Wiki manual combined with custom web part pages and workflows to make the entire process come together.
Here's what the Freight Claim Home Page looks like:
An explanation of the Freight Claim Home Page:
- Freight Claim Links
This part of the page is a Data View web part that links to a list called "Freight Claim Links." Each item in this list is security trimmed so that users only see the links that pertain to them. Take a look at the following image to see how the links list differs based on the security privaleges of the person who logs in:
- Search
Although SharePoint has a fantastic search engine built in, I added a search feature to the Freight Claim Home Page that would filter results by a specific parameter of their choosing. This is a simple data form web part linked to the "Freight Claims from Search" web part.
- Freight Claims from Search
This section is fairly self explanatory, as it displays the results of the Search web part. Users can click on the Work Order Number in this results list to see details for that claim below.
- Freight Claim Info, Package Info, Freight Claim History
The information in these web parts all come from the same SharePoint list and are all linked to the "Freight Claims from Search" web part. I layed it out in this way because I thought it gave a better "dashboard" feel to the page.
You'll see in the image that some fields and links are marked as visible only to Freight Claim Managers. Because SharePoint doesn't have security trimming built in to each individual field in a list, you can't easily limit the visibility and editability of fields in a SharePoint list. However, if you use conditional formatting in a data view web part, you can hide fields based on a user's security credentials. Update: Check out my followup post where I discuss conditional formatting and security trimming.
In addition to security trimming the Freight Claim Home Page, I also trimmed the Freight Claim Entry Form:
You'll see a section on that page that says "Administrative Fields Only." The fields only appear to those set up as "Freight Claim Managers."
Another list I use is Freight Claim Roles:
This list is set up so that the workflow e-mail alerts that I set up in SharePoint designer will e-mail the appropriate person in that role. That way, I don't have to make changes in SharePoint designer if personnel change at a particular branch. Update: Check out the followup post I made about creating custom e-mail alerts.
One of the workflows that I wrote is that when a claim reaches a "closed" status, an e-mail will automatically be sent to the receiving person at the particular warehouse if the package is being staged at that warehouse. It's not uncommon that a package that has to be staged for possible further inspection stays on the shelf for months after the claim has been closed because no one notifies the warehouse staff that the goods can now be destroyed. The workflow in SharePoint designer looks up the person in the "Freight Claim Roles" list that matches the branch where the package is located in the freight claim list item. Here's an example of the alert that I created:
I created a little application in Microsoft Access to help me automate the creation of these e-mails, which mimic the automated alerts that SharePoint creates. I'll post a write-up on that little tool at a later time because I believe a lot of people will find it incredibly useful as SharePoint Designer is lacking in its ability to make CSS html e-mail alerts.
Since we implemented this solution, I have received an overwhelmingly positive response to this new way of handling freight claims. We've virtually cut out all paperwork in the process and now have a way that any employee can reference the status of this list without having to flood our Freight Claim manager's voicemail box with status update requests.
I'm very much interested to know what people think about this SharePoint-based solution. Also, let me know if you have any follow-up questions. 10/16/2007I was listening on my new Zune to a podcast at Michael Gannotti's blog which was entitled WSS vs MOSS for Internet ( MP3 link). In this podcast, Michael shares some of his thoughts on what appears to him to be shortcomings of Windows SharePoint Services 3.0 (WSS) in comparison to the more complete Microsoft Office SharePoint Server 2007 (MOSS).
I must say that after having followed Michael's SharePoint blog for some time, I relished a little bit in his difficulties with the core SharePoint platform. Not that I'm rejoicing in some of the difficulties he experienced; rather, I can tell he realizes first hand what it feels like not to have certain things that those who use MOSS sometimes take for granted. I always felt that the professionals who use MOSS sometimes forget about the things WSS is missing. Ok, I'll stop with my grinning.
The reason Michael was using WSS to begin with was because he was doing some charitable work for a youth group at his Church and designing for them a site on a hosted SharePoint platform. Although he works with MOSS 2007 for his corporate clients, this group obviously needed just a simple site, so WSS seemed the appropriate fit. Here are a few of the things he discussed:
- No RSS aggregator in WSS
He was quick to point out that there's not an RSS web part in WSS that allows you to pull content from other sites in a quick and dirty way. You can use SharePoint Designer 2007, but then you have to have SharePoint Designer, which isn't a cheap product. Thus this kind of limits the ease with which you're able to give the content management aspect of the site to those who will be using it, at least when it comes to aggregating RSS information from other sites.
- No Content By Query (CBQ) Web Part in WSS
Thank you for noticing how frustrating this is! I'm sure the WSS and MOSS design teams at Microsoft had a good reason for leaving the CBQ web part out of WSS, but it sure is frustrating to not be able to mashup data from one list alongside that of another. I haven't even figured out if this is possible using SharePoint Designer 2007. I'm stuck using web part pages, which sort of give me what I'm looking for.
As a self-tought Microsoft Access developer, I've been trying to find ways to things in SharePoint that I'm able to do in Access. Simple queries are one of those things, and it's really hard to imitate without the Content by Query web part.
- No "News" Function in WSS
I've never seen the "News" function in MOSS, but apparently it gives you the functionality to create an announcement and then assign a date on which it will go public. Apparently Michael found this a little frustrating, but I can't imagine it would be too hard to imitate by using SharePoint Designer, but then again, you would need to have SharePoint designer.
At the very least, you could just have the item require Content Approval, and then manually publish the information by approving the content to make it public.
- No [insert something else here] in WSS
Alright, so I can't remember exactly what his fourth point was, but I think it had something to do with page layouts. I've never had the time to really think about needing that or how I would use it, so I didn't pay much attention to that part.
I'm really interested to see where Microsoft is headed strategically as far as shared WSS hosting is concerned. MOSS 2007 no doubts has taken off through the roof, but you're starting to see (or maybe I should say that you're increasingly seeing) those of use who exclusively the simpler WSS, often times in a hosted environment. Small organizations such as church youth groups would never need the full-blown MOSS 2007 product, but more and more people are starting to find ways to make SharePoint work for them in hosted environments, so maybe we'll see some more changes with specifcally that aspect in mind. 9/25/2007I want to thank Brian Hunsaker for pointing this out to me. I've posted several times about the difficulties I've had in the past with displaying text fields in custom web parts where the "Append Changes to Existing Text" option is turned on. An example of this is the comments field in the test issues list:
I still don't know how to get this to show up in a custom Data View Web Part, but at least, with Brian's help, I've found a line of code that will allow you to display comments with a custom list form. Here's how you add a custom list form and display the comments field within SharePoint Designer 2007. I created a blank Web Part Page as my starting point.
After selecting the correct web part zone, go to Insert > SharePoint Controls > Custom List Form:
For this example, I'm going to use the following options:
Find the line of code that references your Comments field (the text field with "Append Changes to Existing Text" turned on). If you're viewing your site in split mode, all you have to do is click on the comments field in the design area of your site.
Replace the line that begins <xsl:value-of … and replace it with the following line of code:
<SharePoint:AppendOnlyHistory runat="server" FieldName="V3Comments" ControlMode="Display"/>
FieldName is the name of your field and ControlMode is the type of mode you're using to display the data (so if your form is in edit mode, you may want to use "Edit.").
Now you will see this history of this field with user and date/time stamped!
Here's a link to an MSDN article that explains some of this (although I had trouble understanding it):
Lastly, if anyone knows how to display comments such as this in a data view web part, I'm all ears. This solution is a good start. though, and you can even filter it via web part connections.
9/20/2007
Update: Solution Found! See bottom of original post for solution.
2nd Update: See Below
This one really has me stumped. I've been working for a few weeks on a list in SharePoint that will allow users from our various branches to enter freight claims they receive from the customer. This list allows us to centrally manage all freight claims from across our six branches so that customer service can easily reference the status of their freight claim. I've been working hard on setting up the list and building custom web part pages to simply the add and lookup processes, but now I'm at the point where I want to take our Freight Claim manager's current freight claims, which she had been keeping track of in an Excel list, and transfer all that data into SharePoint.
I imported her Excel list into Access 2007 and have been trying to use an Append Query to insert the data into the SharePoint list. However, I keep getting the same error message:
"You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule. Please correct and try again."
(NOTE: There's also a link for that message box that says "Was this information helpful?" I made sure to click "No.")
I wish it would tell me how I'm violating data integrity rules. I've tried everything to clean up my query, but can't seem to figure out why it won't work. I'm thinking it may have to do with the fact that I'm not assigning a new ID to each item I'm trying to insert; does SharePoint not automatically assign new IDs to items that you try to insert from Access?
Also, I tried trimming my list to one item and running the query from that, but that didn't work either.
Does anyone out there have any ideas?
Update: Solution Found (9/20/2007 3:35 PM)
After reading Mark's comments, I decided to create a new append query and add fields from my source table one at a time. To my surprise, this worked. I was able to add quite a few fields and run the query successfully before receiving the error message again.
The error message was thrown when I added a "Choice" field that was limited to only the items in that list. I had already previously added a different choice field which had worked fine, so I couldn't figure out what was wrong with this one. I made sure the value I was trying to add matched exactly those found in the list, and it did. I was about to throw my hands up when I decided to look at the design of the SharePoint list from within Access.
When I looked at my SharePoint List table in Design view, then looked at the "lookup" aspect of the field that was throwing the error, I discovered that the choices in the lookup list weren't the same as those listed on the SharePoint site. As it turns out, after I had added the list to Access, I made changes to the available choices in the SharePoint list. Even though I had hit "Refresh" to the SharePoint list several times since making those changes, they still didn't show up. Finally, I reconnected to the SharePoint list from within Access and this someone corrected everything and I was successfully able to run my Append Query. (2nd Update: I e-mailed the guys at the Access 2007 team blog, and Clinton from their team told me to right-click on the SharePoint table from within Access and then go to "SharePoint List Options." That brings up an option called "Refresh List." Apparently, this "refresh list" option refreshes the list definition schema as well as the data, whereas the "Refresh All" button on the ribbon only refreshes the recordset.)
In Short: If you're trying to run an Append Query to a SharePoint list from within Access, make sure you connect to the list after making changes to any choice fields in your SharePoint list. Otherwise, your query may not work because it could violate data integrity rules. 9/18/2007
I posted this same question a little while back but never received an answer. I had forgotten about it until someone left a note at the bottom of that post asking if I had found a solution. So here it is again:
Now for my question...
When you have "Append changes to existing text" selected for multi-line text boxes in lists for which versioning is enabled (think of the comments field in the Issues list), how in the world do you display all of the comments for that list item in a custom web part? When you go into the default view for a particular item, you see all of the comments time-stamped and user-stamped nice and neat at the bottom of the item. I can't for the life of me figure out how to display comments in that fashion in a custom web part. Any help that doesn't involve Visual Studio would be much appreciated, but if VS is the only way to go then let me know as well. Thanks! 9/12/2007
I've been having fun playing around with custom Workflows in SharePoint Designer 2007 lately, but I wanted to caution people against a funny mistake that I made.
I'm working on building a list and corresponding web part pages to help organize how we manage freight claims from within our company. I created one workflow that starts when I new item is created. It assigns the freight claim to the freight claim manager and e-mails her that it's ready to be processed.
I tried creating an additional workflow that's initiated every time the item is changed. The workflow checks to see if the item has reached a certain status which requires the credit department to apply a credit. If so, the workflow will re-assign the item to an employee in the credit department and then send them an e-mail letting them know that they have work to do. It sounds wonderful. However, each time that workflow is run, it makes a changes to the original workflow when it changes the "assigned to" field. This of course changes the list item and re-initiates the workflow, thus creating an infinite loop for as long as the criteria are met. I had received about 50 e-mails before I noticed something was wrong. Deleting the workflow stopped the problem, but not until about 250 versions of the list item had been created! So be sure that if your workflow starts from a change and could potentially make changes to a list item, that you give it an out by narrowing down your "If" conditional statements.
Now for my question...
When you have "Append changes to existing text" selected for multi-line text boxes in lists for which versioning is enabled (think of the comments field in the Issues list), how in the world do you display all of the comments for that list item in a custom web part? When you go into the default view for a particular item, you see all of the comments time-stamped and user-stamped nice and neat at the bottom of the item. I can't for the life of me figure out how to display comments in that fashion in a custom web part. Any help that doesn't involve Visual Studio would be much appreciated, but if VS is the only way to go then let me know as well. Thanks! 9/6/2007Robert Bogue has published another article that I wanted to bring to your attention. I think I like his writing style because it's somewhat philosophical in nature, and given my background in philosophy, that's right down my alley! This particle article, Understanding the SharePoint, ASP.NET Relationship, is something I don't quite understand because I'm not an ASP.NET developer and not very familiar with Visual Studio, but there may be some of you out there who are familiar with these technologies and could aprpeciate his perspective. Happy reading! |
|
|
|
|