Archive for April, 2008

Cascading Drop-down Lists in a parent, child relationship

I see this topic come up over and over again in SharePoint. I am going to elaborate on this post when I have a chance.

I am creating this post as a reminder to myself of what I did to make it work.

i started by downloading and installing an add-on from the following site:

http://datacogs.com/datablogs/archive/2007/08/26/641.aspx

Now when I create a new column – I have the option to create a column type of parent drop-down list or child drop-down list.

The selection from the parent will filter the results in the child drop-down

I had to enable Auto Postback on the drop-downs to make it work properly.

If anyone needs this immediately, let me know and I’ll make is a priority.

UPDATE

I am finally able to get around to documenting this procedure. I needed to get it done, because I’m pretty sure that I would have a hard time remembering what I did if I had to start again from scratch. Here is my scenario for the cascading (parent-child) drop-down lists:

I have an application that tracks the progress of programming tickets through the company’s specification review process. The initial programming request contains two drop-downs; one for Project and another for Program. My users wanted the Program list to be filtered to only show programs that belong to the selected Project. The user would select a Project from the first drop-down list and it would filter and refresh the options in the second list.

Setup

After installing the Datacogs program, I created a list containing the Projects. The list contains one column called Project Name.

For Example:

  • Project 1
  • Project 2
  • Project 3
  • etc. 

Next, I created the Programs list. The list contains two columns; one with Program Name and the other a lookup to the Project list. Entries in the Program list are associated to the Project list.

For Example:

  • Program 1 – Project 2
  • Program 2 – Project 2
  • Program 3 – Project 1
  • Program 4 – Project 3
  • etc.

Note: Both of these lists can be created in SharePoint WITHOUT the Datacogs add-in.

Connect the dots

Now comes the fun part. In my new list, the Specification Tracker, I need to perform the cascading drop-down. When I create a new column, with the Datacogs add-in installed, I have an option to create the column with a column type of Parent Drop Down list.

I create a column called Project and set the type to Parent Drop down list. The Parent drop-down list contains a new option:

Parent Drop Down List Details

 
 
 

Enter a SharePoint site URL (in this Farm)then click the Load Lists button. Select a List and Column in the remaining dropdowns
Site URL

List Name

Column For Value

Column For Text

I have to specify the site that contains the list (to perform the lookup on). I really like this feature as I should be able to select lists from any of the SharePoint sites on the same collection. I haven’t tested the feature yet, as both times I’ve used it, the lists were always on the same site.

Once you select the site, click Load Lists. Now you can select the List Name from the drop-down. I selected the Project list I created earlier. Then I have to select the Column for Value (that’s what the selection will pass to the child list). Finally select the column for Text – which is what will display on the screen in the drop-down.

Follow the same steps for the Child drop-down list, except that you have one more selection to make. There is an additional drop-down for Column to Join to Parent. In my case, I selected the Project column in the Program List as the column to join.

Presto, you are done. If you open the newform.aspz for the list, and select a Project, the options available in the Program list will be filtered to only show the ones that belong to that project.

Advertisements

April 30, 2008 at 5:52 pm 10 comments

Create a drop-down list with a lookup to another site

I came across a post in the SharePoint University forum asking for help on creating a lookup to data on another site. I’ve been meaning to create a post on this for a while because this is a very important skill when creating web applications in SharePoint.

Let’s assume that you have a form – editform.aspx. That form already has a Single Item Input Data View setup on it. But now you want to add a drop-down with a lookup AND you want the lookup to pull data from another location.

  1. Start by creating a new row in the table to hold the drop-down.
  2. Click in the empty cell and then click on the datasource you want to perform the lookup on. (Note: you can use any datasource – Connect to another library, Connect to a linked datasource, etc.)
  3. From the drop-down, select Insert as Data Source Control. SPD inserts a grey block in the cell labelled “SQLDataSource – Some weird auto-generated ID”.
  4. On the File Menu, click Insert > ASP.NET Controls> Drop-down list. When SPD inserts the drop-down control it will say “Unbound”.
  5. Hover over the newly inserted drop-down list, and click on the Common Drop Down Lists Tasks flyout.
  6. Select Choose Data Source.
  7. From the Data Source Configuration Wizard dialog, select the datasource we inserted earlier from the Select a datasource drop-down.
  8. Select the Data field to display in the drop-down (what the user will see).
  9. Select the Data field for the value (what sharepoint will store in the field).
  10. Click OK.
  11. The drop-down list should now update to “Bound”.
  12. Save everything.

You now have a drop-down list that performs a lookup from any datasource you can create. I don’t think this will work across different site collections – someone let me know?

April 25, 2008 at 3:22 pm 4 comments

Defaulting a Hidden Field on a Form

I needed to create a form that automatically populates the values for a user when they click the OK button. It turns out that SharePoint requires at least one editable field in order to save the form (If anyone knows a way around this requirement let me know). The scenario is that an Approver can click the Reviewed button in an email message to say that they have read the documents. They are taken to a modified new item form in a custom list and they click OK and the system records the necessary data. Here’s what I had to do to make it work.

Create the Custom List

The custom list was very basic in the case. We needed to be able to track which Approver was clicking OK and what date and time they clicked it. Luckily, ANY list records the Created and Created By data for an item. I simply changed the Title field to DocumentID and the list was ready to roll.

Because of the weird bug I encountered, I had to create another field called Notes. It’s a simple text field with the default options.

Create the Button

In my scenario, the button was to appear inside of an Outlook email – but you can use this logic for a button placed almost anywhere. The key was that we were using the URL to pass the variables to the form, so the button was a hyperlink to the new item form.

I created a button in photoshop and uploaded the image to a SharePoint picture library and grabbed the URL to the image. I then started by creating the following link in the email (part of a workflow):

<a href=”http://yoursharepointaddress/lists/customlistname/newitem.aspx?DocumentID=

At this point, I inserted a variable from the workflow. The workflow which is sending the email to the Approvers already ‘knows’ which DocumentID we are refering to. Therefore the code continued like this:

<a href=http://yoursharepointaddress/lists/customlistname/newitem.aspx?DocumentID=[DocumentIDVariable] target=”_blank”><img src=http://locationofyourimage /></a>

When the user clicks on the button image, it opens a new browser window and takes them to the specified URL. You can pass any variables that you want through the URL. The URL contains the DocumentID variable which we will make use of in the next step.

Create a New Item Form

I used SharePoint Designer (SPD) to edit the newitem.aspx file for the new custom list. I hid the default web part from view and then I added a new dataview. I linked the new dataview to the new custom list I created and inserted the DocumentID and Notes fields as a New Item Form (I will probably write something more detailed about these steps in the future – but the help files on the Microsoft website are perfect at explaining the basics).

I then changed to the DocumentID field on the new form to be a label and changed the Visible property to be False.

I created a new parameter in the Common Dataview Tasks and named that parameter DocumentID and set it to grab the DocumentID from the URL (Microsoft Help files on Dataviews). Remember the name of this parameter.

Now we get into the nitty-gritty. You MUST set the default value for the field in the actual code for the page and not in the properties for the field. I switched SPD to code view and located the code for the DocumentID field (sometimes it’s easier to set the view to Split mode and then click on the field to highlight the appropriate code). The code I added is highlighted:

<asp:TextBox runat=”server” id=”ff1{$Pos}” __designer:bind=”{ddwrt:DataBind(‘i’,concat(‘ff1′,$Pos),’Text’,’TextChanged’,’ID’,ddwrt:EscapeDelims(string(@ID)),’@Title’)}” text=”{$DocumentID}” Visible=”False” Width=”21px” Height=”16px” />

The code I added sets the value of the ‘text’ to be the DocumentID parameter we created earlier (make sure you include the punctuation exactly as I have above. Save your new form and you are ready to test it.

Verify what you did

If you followed the previous steps you should now have the following:

  1. A hyperlink button you can place anywhere (on a site, in an email, etc.) that passes values through the URL.
  2. A customised new item form that takes the variable from the URL and populates a hidden field. You can apply these steps to any list you have.
  3. When the user clicks the button, it opens the custom form. The user then clicks OK on the custom form and the new item is created in the list.

As you may have realised by now, this is a truly powerful tool for getting information back from your users. I hope to come back at some point an elaborate on some of the earlier steps, but in the meantime, I recommend reading the Microsoft helpfiles – specifically those on the SharePoint Dataviews.

Thanks for reading.

Due to some questions raised in the comments, here are the detailed steps for defaulting a field on a form:

Defaulting a field on a form

1.       Open a SharePoint ASPX page in SharePoint Designer. My page is called edit_lead.aspx and is based on my masterpage.

2.       Use SPLIT view to view the code and the design at the same time.

3.       Insert a <div> container for your form, inside of the content placeholder (I place all of my content within individual DIVS so I can control them through CSS).

4.       Save the page.

5.       Click in the div and select Data View > Insert Data View from the SPD menu bar.

6.       Click on the WebPartPages:DataFormWebPart in design view and select a data source.

7.       Select the fields you want to include and choose Insert Selected Fields As… Single Item Form (I am using the form to edit an existing list item).

8.       When the form appears in your page, click anywhere in the form (that is not a field), and select DataView > Parameters from the menu bar.

9.       Click New Parameter

10.   Name the Parameter (URLLeadID).

11.   Select Query String from the Parameter Source drop-down.

12.   Enter the variable you will use in the URL in the Query String Variable (LeadID).

13.   Click OK.

14.   Select the field you want to populate.

15.   Choose DataView > Format Item As> Text Box.

16.   Click Save.

17.   Now select the field again and look in the code view, it should look similar to this:

<td width=”75%” class=”ms-vb”>

        <asp:TextBox runat=”server” id=”ff1{$Pos}” text=”{@LeadID}” __designer:bind=”{ddwrt:DataBind(‘u’,concat(‘ff1′,$Pos),’Text’,’TextChanged’,’ID’,ddwrt:EscapeDelims(string(@ID)),’@LeadID’)}” />

        <SharePoint:FieldDescription runat=”server” id=”ff1description{$Pos}” FieldName=”LeadID” ControlMode=”Edit”/>

</td>

18.   Locate the text=”{@LeadID}” and replace the @LeadID with your parameter: $URLLeadID (note the $ not the @ symbol).

19.   Click Save.

20.   The field will now autopopulate with the parameter passed from the URL.

Note: If you are create a NEW ITEM FORM, the steps are similar except that you won’t have the text=”{@LeadID}” already in the text box. You will have to add the entire parameter: text=”{$URLLeadID}”

April 22, 2008 at 1:09 pm 21 comments

Auto-Increment a Number in a New List Item

The Problem

The web application I am designing requires several different auto-incrementing numbers. The customer account numbers, support ticket numbers, etc. must auto-matically increment when a new item is created. A quick web search will show a lot of people recommending the ID field (created by SharePoint) as the solution.

Disclaimer: I found this idea on a website or forum somewhere. I can’t locate the original source. If anyone knows the author of the original concept, let me know so I can give them credit and link to them.

Edit: Here is a link to the original forum post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2232817&SiteID=1

I had two problems with this solution:

  1. The number needs to start at 1000 not 1.
  2. I need to be able to reset the number or change it if needed without loosing the existing data.

Other considerations:

  1. Users can’t change the number.
  2. The number has to be unique.

The solution I found seems to meet all of my criteria. This process is still in the design stage and has not been tested by users.

The Theory

A user creates a new item in a list. The system looks up a table of numbers and assigns the next available number to the new item. The system then updates the table of numbers.

In Practice

NOTE: I don’t use spaces in my list or column names and then I change the labels in the actual forms (I’ll explain that in a future post)

  1. Start by creating a custom list. I called mine “SystemCounters”. This list will store all of the counters for my site.
  2. Create a column called “Counter”, column type: Single Line of Text.
  3. Create a column called “LastNumber”, column type: Number. Configure the column settings as Required and having 0 decimal places.
  4. Create a column called “NextNumber”, column type: Calculated. Configure the column settings as Formula: “=LastNumber+1” (without the quotes), Data type returned as: Number, and having 0 decimal places.
  5. Configure other list settings as needed. I like to disable attachments and make sure the list is not included in searches (advanced settings).
  6. Open the list and create some new items. I created a new item as ClientID and set the LastNumber to 999.

You now have a table which will hold as many counters as you would like. You can create counters for almost any list, for example create a counter for issue numbers or support tickets.

How do you use the new counters?

Putting the counters into play will require that you have access to SharePoint Designer or Visual Studio. If you are attempting to create a web application without one of those two programs, you are in for a lot of pain and suffering. SharePoint Designer (SPD) will allow you to easily create workflows and edit forms for your application. I highly recommend the investment in purchasing the license.

  1. Create a new custom list. For this example, we will create a list to track new clients. NOTE: if you create the list as a Contact list you will be able to easily sync it up with Outlook.
  2. Create a new column called “ClientID”, column type: Single Line of Text or Number.
  3. Create the rest of the columns you need to track for new clients. For example you could create FirstName, LastName, Company, etc. 
  4. Open SPD and create a new workflow. Set the workflow to run in the Client Database and run everytime something is created.
  5. Create a variable for the Client ID Number.
  6. Set the workflow to populate the new variable with the contents of the NextNumber field from the SystemCounters list, where the Counter Name equals ClientID. NOTE: I have summarized this into one line of text, but it could be very complicated to new users. I would recommend reading the online help tutorial for creating workflows. I will try to create an article on workflow design in the future.
  7. Tell the workflow to set the ClientID on the current item to equal the Client ID variable.
  8. Now tell the workflow to set the LastNumber field in the SystemCounter list to equal the Client ID variable.
  9. Save the workflow and we are rocking!

What happens?

NOTE: Personally, I use SPD to open the new item form for the Client Data list and set the Client ID field to type of label (or remove it completely). This prevents users from entering their own numbers in the Client ID field. Any number they entered would be overwritten by your workflow, but it will save you support emails like: “Where is my customer numbered 9999?”

When a new client is added to the database (list), the user enters the name and contact info and clicks save. The workflow kicks into action when the new item is created. It looks up the NextNumber field in the System Counters and finds the number 1000 (LastNumber was 999, NextNumber is LastNumber+1). It sets the variable to be 1000 and then populates the ClientID on the new item to be 1000. The workflow now sets the LastNumber in the SystemCounters to be 1000, which makes the NextNumber field automatically recalculate to 1001.

You now have a fully functioning Counter system and Auto-Incrementing number.

Those of you who pay attention might have realised that this doesn’t guarantee that the Client Database contains only unique numbers. I could create a client with a ClientID of 1000, set the counter back manually, and create another client with a ClientID of 1000. I’ll show you how I tackled that in another post.

Thanks for reading.

April 11, 2008 at 12:15 pm 15 comments

Introduction to my Weblog

Hello All,

I swore to myself that I wouldn’t start a weblog. I told all my colleagues that I wouldn’t start a weblog. Here I am, starting a weblog. I’ve been wrestling with this beast called SharePoint for the last year and I’ve finally started to make some headway. The problem is, I have so many tips & tricks jammed into so many places that I can’t find the important ones when I need them: weblog to the rescue.

I think that the learning curve for Microsoft SharePoint Technologies has been quite steep. I am well versed in HTML, CSS and PHP and I found the whole process to be a nightmare. This blog will contain the information I have gathered while learning to design web applications in SharePoint. If I can save even one person the suffering – this will all be worth it.

Thanks for reading. I would love to hear your feedback as the site progresses.

April 10, 2008 at 5:01 pm 2 comments