Back
Excel

Building an Asset Tracking Application in Excel Services – Part 3a of 5

Today’s author, Dan Parish, continues his discussion on building an Excel Services solution. Part 3 turned out to be a bit longer than we had anticipated so we’ve split it up into two parts: 3a and 3b.

Thus far in this series, I have given an overview of the solution, and detailed how to build the Excel workbook that is the core of how it works.

In today’s post, I will walk through building the SharePoint List that users will input their machine information into.

Overview

In order to collect the information we want about each user’s machine, we will have them enter that information into a SharePoint List. SharePoint Lists can store many different types of data, and can have some amount of data validation applied to them as well.

By using a SharePoint List we also get backup and restore capabilities, permissions, versioning (if enabled), and more, all for free.

To get started, you’ll need a SharePoint site, and you’ll need to be an owner of that site. Note that from here on for the screenshots, I assume you have a basic Team Site, however the same steps apply no matter what type of SharePoint site you are using.

Creating the SharePoint List

To create a SharePoint List, select Site Actions at the top right of the page, and then click Create. If you don’t see a Site Actions button, or if there is no Create in the Site Actions menu, you aren’t an owner on that site.

The next page that appears is a page detailing all of the different types of things you can create. We just want a simple list, so in the Custom Lists column, click on Custom List.

Finally, you’ll be brought to the page on which you actually create your list. Call the list Machine Information, and click Create.

Adding columns to the List

Now that we have our List created, we need to add in the columns for the types of information we care about. Specifically, we want to collect the following information:

  • Asset Number
  • Number of CPUs
  • CPU Speed (Ghz)
  • Amount of RAM (GB)
  • Hard Drive Space (GB)
  • Machine Type (Laptop or Desktop)

To add these columns to your new List, click Settings > List Settings. You’ll be taken to the settings page for your List, and you should notice a Columns section midway down the page. There are three columns in there by default:

  • Title
  • Created By
  • Modified By

What we are going to do is to repurpose Title to be Asset Number, and we’ll then add the other columns we require, leaving Created By and Modified By alone.

To repurpose Title, simply click on its name. You’ll be brought to the Change Column: Machine Information page where you can change the column name. Change it to Asset Number and click OK.

Now we just need to add the remaining columns. To add a column, on the List settings page click Create column. You’ll be taken to the Create Column: Machine Information page. Enter Number of CPUs for the column name, and select Number as the data type. Notice this changes the available settings further down the page.

Set the minimum number of CPUs to 1, the maximum to 8, and click OK. You have now created your first column! The next three are very similar. Go ahead and create the next three columns in the same way with these settings:

Column Name

Data Type

Minimum

Maximum

CPU Speed (Ghz)

Number

1

8

Amount of RAM (GB)

Number

0

16

Hard Drive Space (GB)

Number

1

2000

Once you’ve created those columns, there is only one more column to add, and that is for the Machine Type. Click Create column again, and on the Create Column: Machine Information page, enter Machine Type as the column name.

This time however, select Choice (menu to choose from) as the data type. This makes the available settings further down the page change. For the choices, enter Desktop and Laptop, each on a separate line, and click OK.

You’ve now finished creating your list!

Creating Views on the List

We do want to do one more thing however. In order to ease pulling only the Laptops or only the Desktops into our Excel workbook via the User Defined Function (UDF) we will be building in the next post, we need to create two ‘views’ on the list. Views can be applied to a list and can specify the columns to show (and hence return to our UDF), the order in which the entries should appear, what items to return, and more.

At the very bottom of the settings page, you’ll see the Views section. It has one view in it already called All Items.

To start creating a new view, click Create view. On the next page, select Standard View as the type of view to create. You’ll then be brought to the Create View: Machine Information page. We will create the Desktops view first, so go ahead and enter Desktops as the View Name.

In the Columns section you’ll see three columns: one of checkboxes that specifies if the column should be shown in the view, one of the column names, and one of dropdowns that specifies in which order the columns appear.

Check and uncheck columns, and change the dropdowns on the following items to match the table below:

Display

Column Name

Position from Left

Unchecked

Attachments

N/A (it will change automatically)

Checked

Asset Number (linked to item with edit menu)

2

Checked

Number of CPUs

3

Checked

CPU Speed (Ghz)

4

Checked

Amount of RAM (GB)

5

Checked

Hard Drive Space (GB)

6

Unchecked

Machine Type

N/A (it will change automatically)

Checked

Created By

1

The next thing we want is for the results to be sorted by Created By. This is because we do a VLOOKUP in the Excel workbook on the user’s name, and VLOOKUP requires the names to be in ascending order. So, in the Sort section, select Created By as the first column to sort by, and make sure that Show items in ascending order is selected.

The final thing we want to do is to have this view only return desktops (it is the Desktops view after all). To do this, we will use a filter on the list. So, in the Filter section, select the Show items only when the following is true option. Then, select Machine Type as the column to check, is equal to for the operation to perform, and enter Desktop in the textbox. This means that only items where Machine Type = Desktop will be shown in this view.

Click OK to create this view.

You will be returned to the List with the view you just created active (which you can see at the top right of the List).

We still need to create one more view however, for Laptops. Click the view dropdown that is currently showing Desktops, and select Create View. Now go through exactly the same steps as you did to create the Desktops view, except call the view Laptops, and for the filter you want to match on the word Laptop, not Desktop.

Once you’ve create the Laptops view you are finished! You can try entering sample data by clicking New in the List.

Conclusion

The SharePoint List is the data repository for all of the machine information that the users will be entering. By using a SharePoint List we get all of SharePoint’s backup and restore, permissions, versioning, etc. functionality for free. By adding some data validation and views onto the list, we’ve made it easy for users to fill out the information, and easy for admins to get information out of it and maintain it.

In my next post I will walk through building the User Defined Function (UDF) that will pull this information out of the SharePoint List and into Excel Services.