Alexei posted some feedback this week asking about the Poor Man’s CRM.  I am so sorry that I haven’t been able to get a download out yet.  I still don’t have a download available, but since I’m stranded in Toronto without internet access, I’d compose a post or two on how I’ve created Poor Man’s CRM.  Considering that I don’t have internet access, I’m not going to provide a lot of details as I don’t have access to a Sharepoint site to check details.  (Note to self:  load up a Win2k3 server with WSS in a Virtual PC for the laptop J )  For the purpose of these posts, I’m going to be discussing the InfoPath demo site, as I really like the Dashboards (and think you might as well J).

First, let’s talk about design considerations for something like this.  I have been playing with relational databases for years, mostly Access / Jet or Access / SQL set ups.  I personally view Sharepoint as a sort of web-based Access.  True, we don’t have a lot of the more advanced functionality – but there are a lot of small businesses using basic Access databases that could be handled with WSS.  So for design considerations, let’s think of each Sharepoint list as a table in Access.  As a result, Poor Man’s CRM has lists for Customers, Contacts, Locations, Equipment, Customer Links, Customer Documents, & Help Desk.  Note that I’m using the term ‘list’ rather generically.  These don’t necessary have to be a list as Sharepoint defines them, any of these could be a List, Document Library, Forms Library or Fax Library (if you’re using SBS). 

Once you’ve created your lists, we’ll want to add a Lookup field to each list that we want to associate with another.  For example, since we want to have our Customer Contacts associated with a customer, we’ll add a Lookup field to the Customer Contacts list that looks up the name field from the Customer list.  Then you can repeat for each additional list that you want to link – for our example, we’ll add a customer lookup field to the Help Desk list, the Locations list, the Customer Links list and the Customer Documents list.

Now to build our Customer Dashboard.  This is actually fairly straight-forward.  Each dashboard is a web part page.  Web part pages have to live within a Document Library.  As a result, I strongly recommend creating a new Document Library for the sole purpose of holding your web part pages.  The first step to creating the Customer Dashboard is to create a new web part page, name it accordingly, and select the layout you want.  You’ll notice that the sample Dashboards are using a layout with a full LH column. 

Once your new web part page is created, add the web parts you want.  In the Customer Dashboard example, the Customers list is in the LH column, the Customer Contacts are at the top of the body section, followed below by the Help Desk, followed below by Locations, with Equipment to the right, and at the bottom is the Customer Links with Customer Documents on the right.

Now that we have our layout set, you want to customize each web part view as necessary.  For example, we normally would only want the Name field to be visible for the Customers web part, and for those names to be sorted ascending.  Likewise, we may only want the Contact Name, Phone, Fax & Email visible for Customer Contacts.  Customize each web part view however you want. 

Once you have your web parts designed as you want, the only thing left is to link the web parts so that the web parts in the body (Customer Contacts, Help Desk, Locations, Customer Links & Customer Documents) only show records related to the customer selected in the Customers web part.  In order to do this, we need to open our Dashboard web part page in FrontPage 2003.  Once the page is open in FrontPage, right click on your Customers web part and select to change to XLST DataView Part (or something along those lines J).  Repeat for each web part.  Once the web parts are all converted to DataView Web Parts, you’ll want to save the page.  Next, while still in FrontPage, right-click on the Customers web part and select the Data Connections wizard.  Step through the wizard, specifying that we want to provide values to another web part, select the Customer Contacts web part to,  for our action, we want to filter the web part on the data value supplied, and we want the Customer name value to be a hyperlink with the first record selected by default.  (IIRC, most if not all of these are the default options.)  Now repeat this process – each time specifying a different web part to receive the value.

When you’re finished, save the page and close FrontPage.  Refresh your page and voila! – your dashboard should be working as intended, with the body web parts only showing those records that relate to the customer selected in the Customer web part.

Your last task will be to place the necessary link on the Sharepoint main page (or where ever you want it) pointing to the dashboard.