Write a Junxure Custom Application in Visual Studio

Creating a custom  Junxure Application with VB.net

Problem.

Download the Code for this project

This office manages accounts for advisors who do not work directly for them.  The people who own the accounts are not clients of this firm, but rather are cleints of the external Adivsory firm.  They need to be able to see the following information:

How much each individual Investor has in each account
How much each external advisor has under management with the main firm.
How much revenue was generated via these accounts.

For our example we will use the following people.
Steve Findlay – An external Advisor that the firm works with.
Alan Anderson – A client of the External Firm

Solution

The solution to this issue was a 2 part item.
First we needed to figure out a way to use Junxure to track this information.  To do this we did the following.

Added a user to Junxure with the same lastname and first name as the external advisor.
Created an Employee Position to indicate that a contact record in Junxure has an External Advisor.  The Employee Postion is called “External Advisor”
Go the the clients of the External Firm and add an employee position to them and mark their External Advisor as “Steve Findlay”.

2

Writing the custom Application.

In this article it is not my aim to show you how to use visual studio to write a custom application, I am assuming that if you are reading this, then you have some level of experience in using visual studio, or another programming language.  I am just trying to show you the possibilities of what you can do using Visual studio to enhance Junxure.  I am however including the source code for this project, so you can explore the solution further.

What we now need to do, is to find all of the Contacts in Junxure, that have an External Advisor, and present a grid that shows their accounts and the revenue for each one.
We will use two views that already existing in Junxure, vqryAccountTotals and vqryRevenueFeesPaid, since these views totals up the details of the accounts and clients revenue.
Here is a graphical representation of what we are trying to do.

We can choose whatever fields we want from the above tables, so to
create the sql for this, we will use the following fields.  We
will exclude the Junxure Manually added accounts since they would not
have revenue

and we would not want them in the results.

SELECT   dbo.tblClientEmpJobPositions.ClientID,
dbo.tblEmployees.FName,
dbo.tblEmployees.LName,
dbo.tblClients.LastName,
dbo.tblClients.FirstName,
dbo.vqryAccountTotals.AccTotal,
dbo.ContactsAccountsXref.Account#,
dbo.vqryRevenueFeesPaid.Inception,
dbo.vqryRevenueFeesPaid.YTD,
dbo.vqryRevenueFeesPaid.Last12,
dbo.vqryRevenueFeesPaid.Last3
FROM         dbo.tblEmployees RIGHT OUTER JOIN
dbo.vqryRevenueFeesPaid RIGHT OUTER JOIN
dbo.tblClients ON dbo.vqryRevenueFeesPaid.ClientID = dbo.tblClients.ID RIGHT OUTER JOIN
dbo.tblClientEmpJobPositions LEFT OUTER JOIN
dbo.tblLookJobPositions ON dbo.tblClientEmpJobPositions.JobPositionID = dbo.tblLookJobPositions.ID ON
dbo.tblClients.ID = dbo.tblClientEmpJobPositions.ClientID ON dbo.tblEmployees.EmpID = dbo.tblClientEmpJobPositions.EmpID LEFT OUTER JOIN
dbo.vqryAccountTotals RIGHT OUTER JOIN
dbo.ContactsAccountsXref ON dbo.vqryAccountTotals.CLTNO = dbo.ContactsAccountsXref.Account# ON dbo.tblClients.ID = dbo.ContactsAccountsXref.ContactID
WHERE     (dbo.tblLookJobPositions.JobPosition = N’External Advisor’) AND (NOT (dbo.ContactsAccountsXref.Account# LIKE N’Junxure%’))

This will return all of the records in Junxure that have an Employee

position of External Advisor, along with the totals in the accounts and
the revenue paid from those clients.

Now we need to make sure that when we are looking at the Junxure
Contact Record for the External Advisor of Steve Findlay, we are only
seeing the contacts that he is the External Advisor for.

Since our custom program will be launching in the context of the
current client in Junxure, we will not be able to open the custom query
showing the records for the Employee Steve Findlay.

In order to do that, we will need to convert his Junxure Client ID that
is show on the top of the client form, into the Junxure Employee ID for
the records that you added for Steve Findlay.

When we setup the Steve Findlay record we were careful to enter the
lastname and firstname the same in the Employee form as it was in the
Client form.  So we will have to do a lookup to find the Employee
id, like is shown in this diagram.

We will pass the ClientID into the new program and lookup in the
employees table for the same lastname, firstname, returning the
employee ID.  We will then filter the above sql statement to only
show the people where Steve Findlay is and External Advisor.

Junxure has the ability to launch custom programs and pass in context
sensitive information as a command line argument.  We will setup a
custom launch command to launch our CustomAdvisorReport.exe and pass in
the argument of <ID> for the current client.

This will launch our CustomAdvisorReport.exe program and it will also
pass in the ID as an argument.

It will be up to our program to read the argument and do everything
else.

So on the form load we read the arguments, and since there should only
be one, we will assign that argument to the ClientID variable.

We will use the Environment.GetCommandLineArgs to get this
value.   The GetCommandLineArgs always includes the program
name as the first element, so we will check if there are 2 arguments
and if so, we will stuff the second argument into the client ID

Dim arr() As String
arr = Environment.GetCommandLineArgs
If arr.Length <> 2 Then
MsgBox(“You need to supply a ClientID to report on”)
End If
clientID = arr(1)

Once we have the client ID, we will need to get the firstname and
lastname so we can do a lookup in the clients table.

We will use a bit of SQL to get the Employee ID.

SELECT     dbo.tblEmployees.EmpID
FROM
dbo.tblEmployees INNER JOIN
dbo.tblClients ON dbo.tblEmployees.FName = dbo.tblClients.FirstName AND dbo.tblEmployees.LName = dbo.tblClients.LastName
WHERE     (dbo.tblClients.ID = 2620)

This will return the EmpID for the user in Junxure that we
entered to match this employee.  We will now apply that Employee
crtieria to only show the accounts that match that Employee.

You can download the sample code for this application here.
Download the Code for this project

There are a few things that you will need to be aware of when using
this sample code.

We have a class that is called SqlServer.vb and it uses the connection
string that is defined in the external.config.  If you are going
to use this application as a framework for additional custom
programming, you will need to copy your external.config from
c:\junxure\codedn into the debug folder so it will know how to connect
to your database.  Or you can just edit the one that comes with
this zip file.  The line you want to change is the line that looks
like this.

<add
name=”Junxure” connectionString=”Data Source=i7\SQLExpress;Initial Catalog=Junxure;Integrated Security=True” providerName=”System.Data.SqlClient” />

You will have to change the Highligted portion to reflect your server
and instance name as it is on your network.

Once we get the data to display we are just loading it into a grid.

Additional Implementation.

We added another button to display all of the externally managed
clients, instead of the one that you were on in Junxure.  It is
basically the same code, but we skip the filtering by client ID.

Once we get the data to display we are just loading it into a grid.

We added some code to open the client when you double click the row.

We added a button to open the Advisor when you click that button.

Adding a Button to the Custom Database

 

Hooking up the custom database to the Junxure Client Form

In Junxure, you have the ability to hook up a custom Access database to the
client form.  Once it is hooked up, you will have a new button on the top
left of the client form.

Clicking this button will open the custom database to the form that you have
specified in the options settings.  The beauty of this, is that you can
then build the form in your custom database to open for the client that you are
on in Junxure, allowing you to extend Junxure in a way that is seamless to the
users.

I will give you step by step instructions for completing this whole process. 
So let’s get started.

Setup the Options.

You will need to turn on the option to display this button, and then you will
need to specify a form to open in the custom access database.  In this
example, I am going to use the frmClients as the form that I want to open, but
that form actually does not exist in the access database.  We are going to
create it later.  For now, just turn on the option by going to Maintain
System, System Options, Global Options , custom and checking the box and adding
“frmClients” to the form name.

No that you have turned on this option, Junxure will open the custom database
and then open the frmClients.  Since you probably don’t have a frmClients,
you will probably get an error if you were to try this right now.  You will
first need to configure your custom database.

Custom Database Location

The custom database is located in

 

 

c:\junxure\code and it is called customJunxure.mdb.  If you have a
newer version of Junxure, you may not have either that folder or that file. 
If it is there, you can open it now.  If you don’t have it I have provided
the file that I am building in this example, so you can download it and it will
already have the form that I am showing you how to build.  You can download
a copy of one here.  This is a self extracting zip file that will extract
to the proper folder.
CustomJunxure.exe

 

 Once you have the c:\junxure\code\customjunxure.mdb file on your system, double-click on it to
open it and you will see this screen. 

 

 

This is the custom database screen that I have created, but you are not forced
to use this database.  You can actually use any access.mdb file in place of
this file as long as it is named customjunxure.mdb.

In this blog entry, I am going to assume that you are using this file. 
Before you will be able to “read” any of the Junxure data, you will need to be
sure you have configured and ODBC Connection to your Junxure database. 
Here is a link that has directions for setting up and ODBC connection. 

http://www.junxure.com/kb/ViewArticle.aspx?ArticleID=151

Now lets get started setting up the frmClients in the custom database. 
The first thing you will notice in this custom database, is that you are
basically “Locked” out of the design of the menu and you probably cannot figure
out how to get to the “database Container” in the database.  We hide it
when this opens, and to see the database container, you will click “F11″. 
That will open the database container and you will see something like this, if
you are using Access 2003, and something a bit different if you are using
another version of access.  It all works the same but the interface is a
bit different.  Go ahead and hit “F11″ with the open database.

Here you will see a list on the left that allows you to select the different
object types in the database.  In this demo, we will be working with
Tables, queries, forms and Modules.  Don’t worry about the modules, there
is only one thing that we need to do and I will walk you thru it.

Refresh Table Links

The next thing that we will want to do is to refresh the table links. 
This requires that you have the link table manager installed in your access
version.  If you did not install the link table manager, you can drop and
reattach all the tables, but I would recommend that you install the link table
manager when prompted if you do not have it installed.

Click on Tools |  Database Utilities | Linked Table Manager and that
will open the linked table manager. 

If you are using Access 2007 it is on the Ribbon on the Database Tools Tab

It will look like this. 

Click Select all, then click OK.  If your ODBC connection is properly
setup, it will refresh all of the links and this database will now be able to
view the data in Junxure. 

 

 

Caution: When you are looking at “DATA” in the custom database, you are actually looking at data in Junxure’s SQL database and any changes or deletions made here, will also be made in Junxure.

 

 

 

 

 

 

 In order to assist you in really getting the most benefit out of the custom
database, we have build a “hook” into Junxure to allow you to see the what
client your are on in Junxure and what employee you are logged in as.  In
order to take advantage of this, you will need to create a module that will call
some functions that we have created.  Don’t worry if you don’t understand
this, just follow these steps.

 

1.  Click on the Modules entry in the database container.

2 Click the New button on the top

This will open a module window and it will be called Module 1.  It is in
this module that we will want to “Paste” in some code. 

3.  Copy this code below and paste it into the window, below the “Option
Compare Database” text that appears in the module windows.  The code is
between the horizontal lines, not including the lines.

---

 

Function getCurrentID()    Dim o As Object     Set o = CreateObject("JxPublicObject.clsPublicObject")     Dim msg As String     msg = o.GetCurrentInfo     Dim ClientID     Dim emp     If msg & "" = "" Then 'added for testing, if clientform is not open, I return 1187, you can change this for your favorite client     getCurrentID = 1187     Exit Function   End If         If InStr(msg, ",") >= 0 Then      ClientID = Right(msg, Len(msg) - InStr(msg, ","))      EmpID = Left(msg, InStr(msg, ",") - 1)      Else      ClientID = 0     End If     getCurrentID = ClientID End Function Function getCurrentEMPID()    Dim o As Object     Set o = CreateObject("JxPublicObject.clsPublicObject")         'I am not sure why this is not regestring     Dim msg As String     msg = o.GetCurrentInfo     Dim ClientID     Dim emp     If InStr(msg, ",") >= 0 Then      ClientID = Right(msg, Len(msg) - InStr(msg, ","))      EmpID = Left(msg, InStr(msg, ",") - 1)      Else      ClientID = 0     End If     getCurrentEMPID = EmpID End Function

 

 

---

 

 

 

 

When you paste in that code it should look something like this. 

If you only see the first function, don’t worry, you may have the editor
setup to only show one function at a time.

 We are going to test it in a second so just continue along.

Testing the Functions

Open Junxure and Go to a client in Junxure.  With the client form open,
we want to get the ID of the client that you are on.

Leave Junxure open and go back to the code windows.  Look for the window
that is labeled Immediate Window. 

If you do not see it in the code window you can click on “View”
Immediate
Window

In the immediate window, type the following.  A question mark followed
by getCurrentID() and hit enter. 

You should see the current ID from Junxure below what you typed.  If
that works, you now have the ability to get the current client ID.  Now
lets test it for the current logged on employee.

Type ?GetCurrentEMPID() and hit return

You should see the employee ID.  If you get any errors, you need to
check the following.

1. Junxure is started and Running.

2. The client form is open and on a client.

3. You have installed the Junxure Public Object.  This is found in

C:\Junxure\codeDN\JxPublicObject\SetupJxPublicObject.msi.  If you have
done number 1 and 2, then run this MSI and try again.  If it still does not
work, then you have a problem and you should call support, they can help you
with getting the public object installed.

Save The Module.

Click the save Icon and then follow the prompts to save the module, then
close the module window by clicking the X in the top right of the window.

Build a query to return the current Client’s Information

1. Return to the database container, and click on Queries.

2. Click the new button like you did for module.  This will open a query
designer dialog.

Select Design view and click OK, this will open a table selection dialog.

Select tblClients and click OK and Close.  This will take you to the
query designer with tblClients at the top of the designer.

Double click on the any fields that you think you may want to use, but for
this demo, we are going to use these fields.

ID
Lastname
Firstname

This will give you something that looks like this.

Now what we want to do, is filter this query to only
return the client that we have open in Junxure.  To do that we are going to
add criteria to the ID field.

In the cell under ID and to the right of Criteria, you
are going to type =getcurrentID() and that will call the function that we made
in previous steps.

Once you are done, it will look like this.

To test this, you can click on the menu bar “View” and
“Datasheet View’ and you should see the client that you have open in Junuxre.

Now click the save Icon and save this query as “qryClients”

close the query by clicking the X in the top right
corner.

Building the Form

From the database container, click on forms and click
new

Click Autoform:Columnar and put qryClient in the box at
the bottom, then click OK.  This will create a form that shows the fields
that you selected in your query.

The newly designed form should look like this.

You now have a form that will open to your open client
in Junxure.  We will do a few things to this form and you will be well on
your way to being able to use the custom database.

Click on View | Design View from the menu. This will put
the form in Design View.

I am going to do the following.

1. Make the form a bit bigger.

Drag the bottom right corner of the form to make it a
bit bigger.  I would make it about an inch taller and an inch wider for
now.  You can always design it to your hearts content later.

2. Resize the white text boxes to be the same size

Select the three white boxes and use the menu “Format”
size To Shortest

This will make them all the same size.

3. put a button on the form to close the application.

Find the toolbox and select a button from the toolbox. 
If you don’t see the toolbox, you can click View Toolbox from the menu and it
will toggle it on and off.  Find the button that will make a button on the
toolbox, click it and then draw a button on your form.  It does not matter
where you draw it, just put it on the form somewhere. 

that will pop up a dialog box, that will help you tell
Access what you want that button to do when it is clicked.

Select Application, Quit Application, and then click
Finish, you will then have a button on your form, that when clicked will close
the application, returning you back to Junxure.

4. Save the form as frmClients.

Click the same save button you used above to save the
form.  It will want to save it as qryClients, since that is what you based
the form off of, but you want to change that to frmClients to match what you
have in Junxure on your options page.

5. Put some code on the form to maximize it when it
opens.

Find the box in the top right of the form designer and
double click it. Select the event tab, then click in the On Open cell to reveal
the ellipsis button then click the ellipsis button to open the code builder.

Double click the code builder item in the list box.

This will open the code editor with a new OnOpen event
for the form.  Type the following in that event so it looks like this

That will cause the form to maximize when you open it. 
Now close the code editor by clicking the X at the top.

Close the toolbox and the properties windows and then 
close the form by clicking the X at the top of it. 

Be sure to SAVE everything when you are prompted.

Be sure to close the Access Custom Database and exit
Access or it will not be able to open the form when you click the button to test
it.

Testing the database from Within Junxure

Return to Junxure and open the client form to a client
of your choosing.  I choose to open the form for Ken Golding

Now click the button for that the arrow is pointing to
and it will open the custom database to the client that you have open in Junxure

Now you click the stop sign button and you will return
to Junxure.

Summary

This gives the user a seamless hook into your custom
database.  While this demo does not give you much in terms of
functionality, it does demonstrate that you can easily hook into Junxure to open
your Access database to the form that you decide, and if you write reports in
that custom database, and those reports are for the displayed client, you can
really extend Junxure allowing you to design your own reports and you need.

For more information on creating reports in Access, you
can see my previous blog posts, or just google microsoft access report designer
tutorial for a list of resources.
Click
here for Link

 

Code to find the current client in Junxure

Using SharePoint Web Services WSS

First time foray into Sharepoint

First time foray into SharePoint Web services.

We have been getting more and more clients asking us about interfacing with SharePoint but none of our clients were using it.  Finally we have a client that depends on SharePoint and needed us to write an interface.  With the demands on our programming pool, we always wait for demand before writing something.  Now was the time.  I decided to write this blog entry to help any other developers wade thru working with SharePoint Services.

I hope this information helps others who find themselves having to wade thru the SharePoint Web Services.  I cannot take much credit for the information in this article without first acknowledging the fact that I scoured the web looking for samples and tidbits of information.  I also hired Shervin Shabiki, from Computer Ways to help me get started.  Additionally, I attended the Orlando code camp and sat thru most of the SharePoint sessions so I could get a feel for what I was in for.

Getting Started

Install SharePoint.  I used WSS, (Windows SharePoint Services) a free download that runs on Windows 2003 Server.  SharePoint comes with an awesome object model that makes programming to it pretty easy, but we had a few restrictions that prevented us from using the object model.  We wanted to support interfacing with a hosted SharePoint site, and many of the hosted SharePoint sites do not allow access to the server, so we needed to be sure our interface would work without accessing the server by any method other than the browser interface and the web services.  This restriction alone is what prevented us from using the object model.  So for the purpose of this post,  be aware that there are easier ways to do this, but we are only going to use the web services.

Explore

Spend some time learning how SharePoint works.  Before the code camp, I had not even seen SharePoint, and was only vaguely familiar with what it actually was.  I had listened to a couple of DotNetRocks podcasts about it and I was under the impression that is was a product like Dot Net Nuke.  What an underestimation that was.  While Dot Net Nuke and SharePoint both allow you to create a portal, I think that SharePoint is much more than that.  I have heard it described in a number of ways, and when I ask people who know, what it is, I have heard a variety of descriptions.  Sort of like when several eye witnesses report the same event, they all see if a bit differently.  I guess that is because SharePoint is so big, that is allows you to do so much, it kind of depends on what you want it to be.  I am by no means an expert, but after about a month of total immersion, I still don’t feel like I fully know what it is. So keep that in mind as you read thru this stuff.

I have played around with some of the templates and it is a pretty neat tool that can solve a lot of problems when you need people to collaborate on things, over the web.  The easiest way to get started is to sign up for a hosting account and mess around with it.  I used www.apps4rent.com and it cost $8.95 a month to setup an account with no contract.  Within an hour or so I was using SharePoint without having to install it, or even have a Windows 2003 server.

While that worked for getting me familiar with SharePoint from a users point of view, their hosting plan did not give me access to the Admin site, so I could not fully use the Admin web services.  To get around that I installed windows 2003 server on a box and then installed SharePoint.  All development was done from a separate box, running windows xp and VS 2005

Dive in

There are many sites on the web that describe all of the different web services, so I won’t get into them here, I just want to show you what I learned thru this journey.  Here are a couple of links that I found useful.

MSDN Microsoft Documentation	http://msdn.microsoft.com/en-us/library/cc752745.aspx
Steve Pietrek	http://stevepietrek.com/
Zac Smith	http://www.trinkit.co.nz/blog/archive/2008/09/10/tech-ed-2008-session-content.aspx
John Holliday	http://www.johnholliday.net/products.aspx
Article by Klaus Salchner	http://www.csharphelp.com/archives4/archive602.html
Code Project	http://www.codeproject.com/KB/SharePoint/

Objectives

Our interface needed to do a few things.

1. Build a Sub Site that was separate from the clients main SharePoint site

2. Build a 3 document libraries in this sub site.

3. Build a predefined list of folders in these libraries

4. Add custom columns to these libraries

5. Upload a file into these folders

6. Set the values for the custom columns on the uploaded document

5. Retrieve a list of all files in a specific folder

6. Open a file from one of these folders.

Pretty easy on the surface, but for the first entry into this is was a bit challenging.  So here we go.

Before we can do anything we need to be sure that we can connect to the SharePoint server.  In order to do that we need a few pieces of information.  I am going to create a class called clsSharePoint and in that class I have some variables that will hold the site specific information

Public password As String = “password”
Public username As String = “Administrator”
Public domain As String = “http://2003Server/default.aspx” Public SharePointURL As String = “http://2003Server”
Public AdminURL As String = “http://2003server:46487/default.aspx”
Public AdminEmail As String = “ken@kengolding.com”
Public sitename As String = “Junxure”
Public companyFiles As String = “Company Files” Public Docs As String = “Docs”
Public AdminPort As String = “46487″

Variable Name	Description
password	Password for the user with rights
username	A user with full permissions on that SharePoint site,  They will need full permissions because we will be adding subsites and other admin functions
domain	The URL to the main SharePoint site.
SharePointURL	The URL to the main site without the page name.
AdminURL	The URL to the admin site,  this can be found by starting the Central SharePoint Administrator on the server (Administrative Tools) and clicking site settings.  Each SharePoint installation will probably have a different port number so you have to see what yours is.
AdminEmail	Email address for the action user, only used as an argument when building a site.
sitename	Name of the sub site that you want to build
Docs	We build several document libraries, but in this example we are only going to build one.  I gave the users the ability to call it whatever they like, by changing the variable.
AdminPort	This is used when we build the URL for the web services.

Setting up your project.

Create a new project in VS and add 6 web references.

Web service name	Web Service URL	Name of Reference (You can call it whatever you want, but I called it this)
Admin	_vti_adm/admin.asmx	WSPAdmin
Lists	_vti_bin/lists.asmx	WSPLists
Document Workspace	_vti_bin/DWS.asmx	WSPDocumentWorkspace
Webs	_vti_bin/Webs.asmx	WSPWebs
Site Data	_vti_bin/sitedata.asmx	WSPSitedata

Calling your first web service

I like to create a method called test in any project like this, just so I can know if things are working.  Because we are using web references, and we plan on distributing this application, we will need to do a few things so that our web references while initially hard coded to the location of our development server, will work on our clients machines.    Here is the initial test method.  I have removed all of the try catch blocks to save typing and space, but I will discuss errors towards the end of this article.

Public Sub TestAdmin()

Dim WSPAdmin As WSPAdmin ‘ we are creating an object based off of our above web reference

Dim nc As New System.Net.NetworkCredential(username, password) ‘ we need to set our credentials

Dim xn As System.Xml.XmlNode = m_admin.GetLanguages() ‘ now we will just query the service for the languages

‘that returns an xml node, we will display the outerxml just to see if we were successful in connecting

MsgBox(xn.OuterXml)

End Sub

I created a form in this project to test this class, and on this form I have this code.

Dim sp and new clsSharePoint
sp.TestAdmin()

When that is called it produces this messagebox showing the outerxml that is returned in that webservice method.

Success, we asked the admin web service to let us know what languages are supported and it returned 1033 in an xml node.  Most of what is returned will be in xml nodes.  Now we will discuss a implementation items.

Dynamic Web Services

As you move your application from one client to another, we want to be able to dynamically change the URLs and credentials for the web services.  I attacked that this way.  First I made some private web services that were based on the web references that we hooked up in the previous steps.  This will give us some available services and we won’t have to instantiate them for every call

Dim m_admin As New WSPAdmin.Admin

Dim m_folder As New WSPDocumentWorkspace.Dws

Dim m_list As New WSPLists.Lists

Dim m_web As New WSPWebs.Webs

Dim m_sitedata As New WSPSiteData.SiteData

I then created a function that will return the network credentials so I am only having to get credentials in one place.  This will return a valid network credential

Public Function getcredentials() As System.Net.NetworkCredential
Dim nc As New System.Net.NetworkCredential(username, password)
Return nc
End Function

Now I created a function that will return a valid URL for the webserver depending on the inital setting of the variables with the server information.

Public Function getWebserviceURL(ByVal Service As String)
Select Case Service
Case “Admin”
Return SharePointURL & “:” & AdminPort & “/_vti_adm/admin.asmx”
Case “Sites”
Return SharePointURL & “:” & AdminPort & “/_vti_adm/admin.asmx”
Case “Lists”
Return SharePointURL & “/sites/” & sitename & “/_vti_bin/lists.asmx”
Case “DWS”
Return SharePointURL & “/sites/” & sitename & “/_vti_bin/DWS.asmx”
Case “Webs”
Return SharePointURL & “/sites/” & sitename & “/_vti_bin/Webs.asmx”
Case “SiteData”
Return SharePointURL & “/sites/” & sitename & “/_vti_bin/sitedata.asmx”
End Select
End Function

Now we need to create a new method that will initialize the above web services, so that we don’t have to deal with this in the remaining function.  Now when you instantiate this class, it will change the URLs, Credentials and Timeouts on the 6 private web services

Public Sub New()

‘set the urls for each web service to the localized URL
m_admin.Url = getWebserviceURL(“Admin“)
m_admin.Credentials = getcredentials()
m_admin.Timeout = 60000
m_folder.Url = getWebserviceURL(“DWS“)
m_folder.Credentials = getcredentials()
m_folder.Timeout = 60000

‘set the credentials and timeout for each webservice
m_list.Credentials = getcredentials()
m_list.Url = getWebserviceURL(“Lists”)
m_list.Timeout = 60000
m_web.Credentials = getcredentials()
m_web.Url = getWebserviceURL(“Webs“)
m_web.Timeout = 60000
m_sitedata.Credentials = getcredentials()
m_sitedata.Url = getWebserviceURL(“SiteData“)
m_sitedata.Timeout = 60000
‘They are now all ready to use

End Sub

Now that we have a class that has some web services instantiated and hooked up, lets use them to do some stuff.

Get a list of Sites

This function will return a dataset that contains a list of sites on the server.  It uses a helper function that is described later in the article.  xmlNodeToDS that takes an xml node and converts it into a dataset.

This is handy to check if a site exists before you add it.

Public Function getSitesDS() As DataSet
Dim ds As New DataSet
Dim node As Xml.XmlNode
Try
node = m_web.GetAllSubWebCollection()
ds = xmlNodeToDs(node)
Return ds
Catch ex As Exception
Return ds
End Try
End Function

Add A new site

The documentation for this method is found here.  http://msdn.microsoft.com/en-us/library/administration.admin.createsite.aspx

Here is the signature for this method.

CreateSite(Url, Title, Description, Lcid, WebTemplate, OwnerLogin, OwnerName, OwnerEmail, PortalUrl, PortalName)
We are going to Create a site with the local of English 1033 and the Standard new site of STS

Public Sub AddSite()
Try
m_admin.CreateSite(SharePointURL & “/sites/” & sitename, “Junxure SharePoint Site”, “This site is used for document management in Junxure”, 1033, “STS”, username, username, AdminEmail, “”, “”)
Catch soapex As System.Web.Services.Protocols.SoapException
If soapex.Detail.InnerText.StartsWith(“Another site already exists at”) Then
Else
msgbox (soapex.detail.innertext)
End If
End Try
End Sub

In the above code, I introduced the System.Web.Services.Protocols.SoapException.  This object will allow you to see why your web service calls fail.  You use the familiar try catch block, but instead of catching an exception, you catch a SoapException.  The soapexception has a detail property and it has an innertext property that will tell you what went wrong.

Add a list to a site

Pretty straight forward code, just adds a list to the site, takes a name of the list, a desc and a type.

Public Sub addList(ByVal listname As String, ByVal description As String)
’101=shared documents  use this link below to see the other types of lists to use
‘http://msdn.microsoft.com/en-us/library/lists.lists.addlist.aspx
Try
m_list.AddList(listname, description, 101)
Catch ex As System.Web.Services.Protocols.SoapException
msgbox (soapex.detail.innertext)
End Try
End Sub

Add A Folder to a list

Again pretty straight forward.  The trick to this is providing the rootstring to the folder that you want to add.  We want to add a folder called A to the list we added above.  If the above list was docs, it would be at

http://2003server/sites/junxure/docs then I would call it like this

sp.AddFolder(“docs”,”A”)

That would create http://2003server/sites/junxure/docs

sp.addFolder(“docs/A”, “New Folder”) would create

http://2003server/sites/junxure/docs/A/New Folder

Private Sub AddFolder(ByVal rootstring As String, ByVal infolder As String)
Try
m_folder.CreateFolder(rootstring & “/” & infolder)
Catch soapex As System.Web.Services.Protocols.SoapException
MsgBox(soapex.Detail.InnerText)
End Try
End Sub

Retrieve all the lists in a site.

This function will return an xml node that represents all of the lists on a site. I have written it so that it will return a dataset of all of the lists on the site.  It uses a helper function, that takes an xml node and returns a dataset with the nodes contents.

Public Function getListSDS() As DataSet
Dim ds As New DataSet
Dim node As Xml.XmlNode
Try
node = m_list.GetListCollection()
ds = xmlNodeToDs(node)
Return ds
Catch ex As Exception
MsgBox(soapex.Detail.InnerText)
Return ds
End Try
End Function

Here is the helper function It takes the xml node returned from many of the web service methods, and converts it into a dataset.  The one trick that you may notice is that I prepend the xml from the node with <?xml version=”1.0″ encoding=’UTF-8′ ?> .  Before I prepended it, I would only get errors trying to read the xml into the record set.  This function is really handy if you want to see what is in the nodes that are returned.

Public Function xmlNodeToDs(ByVal node As Xml.XmlNode) As DataSet
Dim ds As New DataSet
Try
Dim result As New System.IO.MemoryStream
Dim sw As New StreamWriter(result, System.Text.Encoding.ASCII)
Dim xml As String = “”
xml = “<?xml version=’1.0′ encoding=’UTF-8′ ?>” & node.OuterXml.ToString
sw.Write(xml)
sw.Flush()
result.Seek(0, SeekOrigin.Begin)
ds.ReadXml(result, XmlReadMode.Auto)
Return ds
Catch ex As Exception
MsgBox(soapex.Detail.InnerText)
Return ds
End Try

End Function

Get all items in a list (Recursively)

If there are folders in a list, the standard calls do not work for getting the items in the list.  You will have to use some CAML to query recursively.    in this example we are going to query the list that is passed in and we are going to get a list of all of the items in that list, even if they are in subfolders in the list.  It will be recursive as deep as the list is.  This is written to return a datatable.

It uses the method GetListItems

GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, webID)

Listname the name of the lsit

Viewname is the name of the view and an empty string will use the default view for that list.  Any fields that are not in the view will not be returned.

query – this is an xml node that defines the query for the method call.  In our case, we set it up by creating an xmlDoc and then using the doc to create an xmlNode.  We then set an element of the node to “Query”

Viewfields is optional and we are just passing in an xmlNode that is equal to nothing

nodeQueryOptions is where you tell it what folder and what attributes to search on. Again we use the xmlDoc to create a queryoptions node, and then from that we set the innerxml to the folder that we want to query by using <Folder></Folder>  that will search from the root folder.  If we want to specify a folder, then we would put the folder path inbetween the <folder> tags like this <folder>FOLDERNAME\SUBFOLDERNAME</Folder>.  That entry would search for all items in the Subfoldername folder

We can then put some attributes on the Node.  The trick in this one is to use the <ViewAttributes Scope=”recursive”/>  With this attribute set, it will search recursively thru the whole list.

When we take the resulting node, and use our helper function to put it into a dataset, we can inspect the dataset and see that it is the second table that contains the items for the list, so we return the second table.  ds.tables(1)

Public Function getListItems(ByVal Listname As String) As DataTable

‘this is a recursive search for all items in the list
Dim ds As New DataSet
Try
Dim innerxml As String

Dim nodeView As Xml.XmlNode = Nothing
Dim nodeResult As Xml.XmlNode
Dim xmldoc As New System.Xml.XmlDocument()
Dim nodeQuery As XmlNode = xmldoc.CreateNode(XmlNodeType.Element, “Query”, “”)
Dim nodequeryOptions As XmlNode = xmldoc.CreateNode(XmlNodeType.Element, “QueryOptions”, “”)
innerxml = “<Folder></Folder><ViewAttributes Scope=” & Chr(34) & “Recursive” & Chr(34) & “/>”
nodequeryOptions.InnerXml = innerxml
nodeResult = m_list.GetListItems(Listname, “”, nodeQuery, nodeView, String.Empty, nodequeryOptions, “”)
ds = xmlNodeToDs(nodeResult)
If ds.Tables.Count = 1 Then
Dim dt As New DataTable
Return dt
Else
Return (ds.Tables(1))
End If

Catch soapex As System.Web.Services.Protocols.SoapException
msgbox (soapex.detail.innertext)
Dim dt As New DataTable
dt.TableName = “No Data”
ds.Tables.Add(dt)

End Try

End Function

Below is a partial screen shot of the above datatable loaded into a grid.  You can see the field names and the contents of the xmlnode

Get the info for an item given a listname and a URL

Using this function, you can retrieve just the information for one item, rather than all the items in a list.  We will query the list based on the ows_FileRef that is the URL for the items.

If you have a document with a URL of  http://2003Server/sites/Junxure/Company Files/A/Anderson-Allen-1187/Financial Plan/Add.bmp you can all this function with the name of the list and this URL and you will receive a datatable with one row, containing all of the information for that one item.  we use the same code as above, but we are going to change the query node to include a where clause, and that where clause will specify that we are looking for the FieldRef field to be equal to the passed in search URL.

Public Function getListItemsRecursiveByURL(ByVal Listname As String, ByVal fileURL As String) As DataTable
‘this is a recursive search for an item in the list with that URL
Dim searchURL As String = fileURL
Dim ds As New DataSet
Dim dt As New DataTable
Try
searchURL = URLDeEncode(searchURL)
searchURL = searchURL.ToLower.Replace(SharePointURL.ToLower & “/”, “”)
Dim innerxml As String = “”
Dim nodeView As Xml.XmlNode
Dim nodeResult As Xml.XmlNode
Dim xmldoc As New System.Xml.XmlDocument()
Dim nodeQuery As XmlNode = xmldoc.CreateNode(XmlNodeType.Element, “Query”, “”)
innerxml = “<Where>”
innerxml += “<Eq>”
innerxml += “<FieldRef Name=’FileRef’/>”
innerxml += “<Value Type=’Text’>” & searchURL & “</Value>”
innerxml += “</Eq>”
innerxml += “</Where>”
nodeQuery.InnerXml = innerxml
Dim nodequeryOptions As XmlNode = xmldoc.CreateNode(XmlNodeType.Element, “QueryOptions”, “”)
innerxml = “<Folder></Folder><ViewAttributes Scope=” & Chr(34) & “Recursive” & Chr(34) & “/>”

nodequeryOptions.InnerXml = innerxml
nodeResult = m_list.GetListItems(Listname, “”, nodeQuery, nodeView, String.Empty, nodequeryOptions, “”)
ds = xmlNodeToDs(nodeResult)
If ds.Tables.Count > 1 Then
Return (ds.Tables(1))
Else
Return dt
End If

Catch soapex As System.Web.Services.Protocols.SoapException
msgbox (soapex.detail.innertext)
Return dt
End Try

End Function

The trick to the above code is getting node query xml entered properly.  If you look above you will see that we use a helper function to URLDeEncode the url that is passed in.  We also have an URLencode fuction.  These simply take URLS and make them safe to pass around, adding or replacing unsafe characters. Here are the two function

Public Function URLEncode(ByVal inUrl As String) As String
Try
‘reserved
inUrl = inUrl.Replace(“%”, “%25″)
inUrl = inUrl.Replace(“/”, “%2f”)
inUrl = inUrl.Replace(” “, “%20″)
inUrl = inUrl.Replace(“$”, “%24″)
inUrl = inUrl.Replace(“&”, “%26″)
inUrl = inUrl.Replace(“+”, “%2B”)
inUrl = inUrl.Replace(“,”, “%2C”)
inUrl = inUrl.Replace(“:”, “%3A”)
inUrl = inUrl.Replace(“;”, “%3B”)
inUrl = inUrl.Replace(“=”, “%3D”)
inUrl = inUrl.Replace(“?”, “%3F”)
inUrl = inUrl.Replace(“@”, “%40″)
inUrl = inUrl.Replace(Chr(34), “%22″)
inUrl = inUrl.Replace(“<”, “%3C”)
inUrl = inUrl.Replace(“>”, “%3E”)
inUrl = inUrl.Replace(“#”, “%23″)
inUrl = inUrl.Replace(“{“, “%7B”)
inUrl = inUrl.Replace(“}”, “%7D”)
inUrl = inUrl.Replace(“\”, “%5C”)
inUrl = inUrl.Replace(“|”, “%7C”)
inUrl = inUrl.Replace(“^”, “%5E”)
inUrl = inUrl.Replace(“~”, “%7E”)
inUrl = inUrl.Replace(“[", "%5B")
inUrl = inUrl.Replace("]“, “%5D”)
inUrl = inUrl.Replace(“`”, “%60″)
Return inUrl
Catch ex As Exception
msgbox (ex.message)
Return inUrl
End Try

End Function

Public Function URLDeEncode(ByVal inUrl As String) As String
Try
‘reserved
inUrl = inUrl.Replace(“%25″, “%”)
inUrl = inUrl.Replace(“%2f”, “/”)
inUrl = inUrl.Replace(“%20″, ” “)
inUrl = inUrl.Replace(“%24″, “$”)
inUrl = inUrl.Replace(“&”, “%26″)
inUrl = inUrl.Replace(“%2B”, “+”)
inUrl = inUrl.Replace(“%2C”, “,”)
inUrl = inUrl.Replace(“%3A”, “:”)
inUrl = inUrl.Replace(“%3B”, “;”)
inUrl = inUrl.Replace(“%3D”, “=”)
inUrl = inUrl.Replace(“%3F”, “?”)
inUrl = inUrl.Replace(“%40″, “@”)
inUrl = inUrl.Replace(“%22″, Chr(34))
inUrl = inUrl.Replace(“%3C”, “<”)
inUrl = inUrl.Replace(“%3E”, “>”)
inUrl = inUrl.Replace(“%23″, “#”)
inUrl = inUrl.Replace(“%7B”, “{“)
inUrl = inUrl.Replace(“%7D”, “}”)
inUrl = inUrl.Replace(“%5C”, “\”)
inUrl = inUrl.Replace(“%7C”, “|”)
inUrl = inUrl.Replace(“%5E”, “^”)
inUrl = inUrl.Replace(“%7E”, “~”)
inUrl = inUrl.Replace(“%5B”, “[")
inUrl = inUrl.Replace("%5D", "]“)
inUrl = inUrl.Replace(“%60″, “`”)
Return inUrl
Catch ex As Exception
msgbox (ex.message)
Return inUrl
End Try
End Function

Building the Query

There is a great tool out there called U2UCamlCreator.exe and with this tool you can create and execute CAML queries and see how they work.

Here are a couple of links that discuss that tool

http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1252

http://www.u2u.be/res/Tools/CamlQueryBuilder.aspx

This tool was very useful for helping figure out the required CAML queries.

Add A field to a list

If you go to the SharePoint site via a browser, you can see that it is very easy to add columns and set their properties.  We need to do this programatically.  I wrote a couple of functions to accomplish this.

Before I add a field, I want to see if that field already exists.  I pass in a listname and a fieldname, and return true or false depending if that field exists.   This function uses some classes in the Site Data Web services.

Public Function FieldExists(ByVal listname As String, ByVal Fieldname As String) As Boolean
Dim exists As Boolean = False
Try
Dim lstMetaData As New WSPSiteData._sListMetadata
Dim lstFields() As WSPSiteData._sProperty
m_sitedata.GetList(listname, lstMetaData, lstFields)

Dim field As WSPSiteData._sProperty
For Each field In lstFields
If Fieldname.ToLower = field.Title.ToLower Then
exists = True
Exit For
End If
Next field
Return exists
Catch soapex As System.Web.Services.Protocols.SoapException
msgbox (soapex.detail.innertext)
Return exists
End Try
End Function

This function is the fuction that adds the field to the list.  It takes a listname, a fieldname, a datatype and a description.  If the field is already there, it exits otherwise it adds the field to the list.

Public Sub AddField(ByVal listname As String, ByVal fieldname As String, ByVal datatype As String, ByVal description As String)
If FieldExists(listname, fieldname) Then Exit Sub
Dim innerxml As String = “”

Dim ndList As XmlNode = m_list.GetList(listname)
Dim ndVersion As XmlNode = ndList.Attributes(“Version“)
Dim xmlDoc = New System.Xml.XmlDocument()
Dim ndDeleteFields As XmlNode = Nothing
Dim ndProperties As XmlNode = xmlDoc.CreateNode(XmlNodeType.Element, “List”, “”)
Dim ndTitleAttrib As XmlAttribute = Nothing ‘ CType(xmlDoc.CreateNode(XmlNodeType.Attribute, “Title”, “”), XmlAttribute)
Dim ndDescriptionAttrib As XmlAttribute = Nothing ‘CType(xmlDoc.CreateNode(XmlNodeType.Attribute, “Description”, “”), XmlAttribute)
Dim ndNewFields As XmlNode = xmlDoc.CreateNode(XmlNodeType.Element, “Fields”, “”)
Dim ndUpdateFields As XmlNode = Nothing ‘xmlDoc.CreateNode(XmlNodeType.Element, “Fields”, “”)

innerxml = “<Method ID=’1′>”
Select Case datatype
Case “DateTime”
innerxml += “<Field Type=’DateTime’ DateOnly=’TRUE’ DisplayName=’” & fieldname & “‘ FromBaseType=’TRUE’/>”
Case “Text”
innerxml += “<Field Type=’Text’ DisplayName=’” & fieldname & “‘ Required=’FALSE’ FromBaseType=’TRUE’ Description=’” & description & “‘/>”
Case “Number”
innerxml += “<Field Type=’Number’ Name=’” & fieldname & “‘ DisplayName=’” & fieldname & “‘ Required=’FALSE’ FromBaseType=’TRUE’ Description=’” & description & “‘/>”
End Select
innerxml += “</Method>”
ndNewFields.InnerXml = innerxml

Try

‘code to actually add the field
Dim ndReturn As XmlNode = m_list.UpdateList(listname, ndProperties, ndNewFields, ndUpdateFields, ndDeleteFields, ndVersion.Value)

Catch soapex As System.Web.Services.Protocols.SoapException
msgbox (soapex.detail.innertext)
End Try
End Sub

This is a function that will get a datatable that contains all of the fields in a list

Public Function GetFieldsDT(ByVal listname As String) As DataTable
Dim dt As New DataTable
dt.Columns.Add(“FieldTitle“)
dt.Columns.Add(“FieldName“)
dt.Columns.Add(“DataType“)
Try
Dim lstMetaData As New WSPSiteData._sListMetadata
Dim lstFields() As WSPSiteData._sProperty
m_sitedata.GetList(listname, lstMetaData, lstFields)
Dim msg As String = lstMetaData.Title + ” :: “ + lstMetaData.DefaultViewUrl + ControlChars.Lf
Dim field As WSPSiteData._sProperty

For Each field In lstFields
dt.Rows.Add(field.Title, field.Name, field.Type)
Next field
Return dt
Catch soapex As System.Web.Services.Protocols.SoapException
msgbox (soapex.detail.innertext)
Return dt
End Try
End Function

Update a field for a specific item in a list.

You will pass in the listname, the URL of the item, the fieldname and the value to set it to.

Public Sub updateItem(ByVal Listname As String, ByVal fileURL As String, ByVal Fieldname As String, ByVal fieldValue As String)
Try

‘we need to get the ID of the item
Dim itemID As String
Dim ds1 As New DataSet
Dim dtItem As New DataTable
dtItem = getListItemsRecursiveByURL(Listname, fileURL)
If dtItem.Rows.Count = 0 Then
Exit Sub
Else
itemID = dtItem.Rows(0)(“ows_ID”)
End If

‘now we Get Name attribute values (GUIDs) for list and view.
Dim ndListView As System.Xml.XmlNode = m_list.GetListAndView(Listname, “”)

‘Create an XmlDocument object and construct a Batch element and its ‘attributes. Note that an empty ViewName parameter causes the method ‘to use the default view.
Dim doc As New System.Xml.XmlDocument()
Dim batchElement As System.Xml.XmlElement = doc.CreateElement(“Batch“)
batchElement.SetAttribute(“OnError“, “Continue”)
batchElement.SetAttribute(“ListVersion“, “1″)
batchElement.SetAttribute(“RootFolder“, Listname)
Dim innerxml As String = “”

‘You will see similar code to this all over, but not too many explanations.  Here is where you pass the method in.
‘you can actually batch as many methods as you want together, and each method will have it’s own ID, starting with 1 and incrementing up
‘as you add addtional methods.  In this case we are only adding one method.
‘see the notes below for information on this xml
innerxml = “<Method ID=’1′ Cmd=’Update’>”
innerxml += “<Field Name=’ID’>” & itemID & “</Field>”
innerxml += “<Field Name=’FileRef’>” & URLDeEncode(fileURL) & “</Field> “
innerxml += “<Field Name=’” & Fieldname & “‘>” & fieldValue & “</Field>”
innerxml += “</Method>”
batchElement.InnerXml = innerxml

Dim resultNode As XmlNode
resultNode = m_list.UpdateListItems(Listname, batchElement)
ds1 = xmlNodeToDs(resultNode)

Catch ex As System.Web.Services.Protocols.SoapException
SPpex(ex, “WSSupdate”)
End Try
End Sub

////////////// NOTE ON XML////////////////////////////////////////
innerxml = “<Method ID=’1′ Cmd=’Update’>”
innerxml += “<Field Name=’ID’>” & itemID & “</Field>”
innerxml += “<Field Name=’FileRef’>” & URLDeEncode(fileURL) & “</Field> “
innerxml += “<Field Name=’” & Fieldname & “‘>” & fieldValue & “</Field>”
innerxml += “</Method>”
batchElement.InnerXml = innerxml
While you will see this all over, it was a bear to make it work.  You pass in the method ID as described above, it just increments for each command that you want to execute.
You then pass in the FieldName and that is the ItemID that we got from the ows_ID in the top part of this function
You also need to pass in the DEEncoded URL for the item
Then you pass in the value that you want to update it to.  If you do not pass in the DeEncoded URL it will not update the value.
///////////////////////////////////////////////////////////////////////////////////////

Finally we upload a Document

After searching all over the place I could not find out how to upload a document into a list using the web services.  In talking with Shervin Shabiki, he told me that you cannot upload a document using the web services.  You must use another method.  Here is what I came up with, borrowing from this post on CodeProject and adding some of my own ideas.

In using this function on my site, we have already added the appropriate lists, and each list has some custom columns added to them.  I will pass in the clientname and since we added the clientname to our list in our custom program, it will update the clientname column with the passed in value

Public Function UploadDocument( ByVal localFile As String, ByVal remoteFile As String, ByVal Listname As String, ByVal folder As String, Optional ByVal Clientname As String = “”) As String

‘// Read in the local file
Dim sRemoteFileURL As String = “”
Dim attemptNumber As Integer = 0
Dim maxAttempts As Integer = 5
Try

Dim r As Byte()
Dim sDocLib As String = “”
Dim Strm As System.IO.FileStream = New System.IO.FileStream(localFile, System.IO.FileMode.Open, System.IO.FileAccess.Read)
Dim reader As System.IO.BinaryReader = New System.IO.BinaryReader(Strm)
Dim filecontents As Byte() = reader.ReadBytes(CInt(Strm.Length))
reader.Close()
Strm.Close()
Dim sSPURL As String = SharePointURL & “/sites/” & sitename
sDocLib = Listname & “/” & folder

Dim NC As System.Net.NetworkCredential = New System.Net.NetworkCredential(username, password, domain)
sRemoteFileURL = sSPURL & “/” & sDocLib & “/” & Trim(LTrim(RTrim(remoteFile)))
sRemoteFileURL = Replace(sRemoteFileURL, ” “, “%20″)
sRemoteFileURL = Replace(sRemoteFileURL, “\”, “/”)
Dim m_WC As WebClient = New WebClient
m_WC.Credentials = NC
Dim dtcheck As New DataTable

Do Until dtcheck.Rows.Count > 0
attemptNumber = attemptNumber + 1
‘we want to try the upload for as many times as we specified in maxAttempts variable.

r = m_WC.UploadData(sRemoteFileURL, “PUT”, filecontents)
‘After we attempt to upload it, we then use our getListItems function to see if the item made it up there, if not we try again.

dtcheck = getListItemsRecursiveByURL(Listname, sRemoteFileURL)
‘if the datatable dtcheck has rows, then the item was uploaded successfully
‘Here we have some custom code that will process a failure if the attemp number is = the max attempts meaning that we did not get the file uploaded so we
‘need to log it and move on.  I do not show the code for logging the failure as it does not pertain to this post.
If attemptNumber = maxAttempts And dtcheck.Rows.Count = 0 Then
MsgBox(“Error Loading file into SharePoint” & vbCrLf & “The files as been placed into the Failed Bin”)
processfailure(localFile, remoteFile, Listname, folder, clientname)
Return localFile
End If
Loop

‘We use the update item code we wrote earlier to update the clientname field with the passed in clientname
If clientname <> “” Then updateItem(Listname, sRemoteFileURL, “ClientName”, Clientname)
Return sRemoteFileURL

Catch ex As Exception
msgbox (ex.message)

Return “Failed”
End Try
End Function

Hopefully these functions will help others as then embark on the SharePoint webservices programming tour.

If you would like me to post the code to this post, please leave a comment.

Creating a custom report (Part 3) Where’s the data.

As you probably already know, Junxure keeps all of the data inside of a Microsoft SQL server database. This database contains a number of tables, and if you know where to look and how to ask for the data, you can pull anything and everything out of the database for your own custom reports. I will try to show you the tables and the relationships for the main parts of the program. Here is what I will cover.  While I am not going to cover all of the tables below, you will see that the clients tables are prefaced with tblclients so you should be able to figure them out pretty easily

Clients – tblClients
Phones -tblClientPhones
Addresses -tblClientAddresses
Emails – tblClientEmailAdds
Classifications -tblClientClassifications
Keywords – tblClientkeywords

Actions -tblClientActions
Emails – tblClientActionsEmails
Attachments – tblClientActionsEmailAttachments
Documents – tblClientActionsDocuments

Accounts – tblClientDBCamIDs
Owners -ContactAccountXref
Assets – FAS-Assets

Hopefully this will help you find where the data is for your custom reports.

Clients

The main data for the clients is stored in tblClients.  In that table you will find all of the fields that pertain to any contact in your database.  Since Junxure is a product that has been evolving since since 1995, there are a few things that you should not about this table.  There are some fields that we no longer use, those fields were left in the table, because many of our clients have written custom reports that use that table, and if we removed the fields, many of their reports would have been broken.  We decided to leave all of the phone, email and address fields in the table, even though we no longer use them.    All of that data was moved to the appropriate tables as show in the photo below.

If you look in the center of the picture, you will see the clients table.  The Primary key and main identifier for each client is the ID fields.  Each client can have unlimited records in each of the tables that the arrows are pointing to.  The 3 tables highlighted in blue are the tables that hold the phone, email and addresses on the contact info tab in Junxure.  The tables highlighted in Yellow, are the tables that hold the data in the bottom part of the profile tab.

Here is a diagram that shows the relationships between employees and the clients.

Actions

The actions are contained in a main table, and the emails and documents are located in additional tables.  Here is a diagram that shows the relationships for the actions.

tblClientActions -In the diagram below, you can see the white box in the center is the main action table.   It contains the main part of the actions.

tblClientActionKeywords -The pink table is the action keywords table, and you can have many records in this table for each action.

tblClients -The purple table is the clients table, and it is related to the actions via the client ID field.

tblAlerts -The darker yellow table is the alerts, and if an action has a task assigned to it, then an alert is placed into this table.  Once alerts are completed, they are deleted from this table

tblClientActionsFYi -The light yellow table at the top left is where we store the FYI’s for this actions.

tblClientActionsEmails and tblClientActionsEmailAttachments -The Green table are the emails and the email attachments.  Each action can have multiple emails, and each email can have multiple attachments.

tblClientActionsDocuments -Finally the blue table is the table where we store the documents.  We do not actually store the document in the database, but we store the path to the document in the filename fields.

Also on actions you may be interested in seeing what employees are related to the action.  Here is the diagram for that.

Accounts and Assets

Accounts and Assets are a bit harder to comprehend and because of the way Junxure has evolved, the naming on these tables is a bit strange.  One thing to remember is that Junxure, in it’s evolution, now allows multiple clients to own an account, so the accounts table is not really associated with the clients at all, that is where we use the cross reference table.  To help you understand how it is all related, it would help if you think of these 4 entities, and then look at the names of the tables that hold these entities.

Clients  – tblClients

Accounts – tblClientDBCamIds

Owners – ContactsAccountsXREF

Assets – FAS-ASSETS

Looking at the picture below you can see that each assets belongs to an account.  Each account has owners, and each owner is a record on the clients table.

Asset Fields

The field names on the asset form do not match the field names in the tables.  Here is a screen shot that shows what the actual field names are for the asset form.

This should answer many questions you may have about the location of the data and the how the data is related.  In my next post, I will try to show you how you can “Hook” the custom database to the Junxure database using the Junxure Public Object.

Hanselman of the East

I am sure that some of our readers will see the title of this post and think that it is a bit strange.  There actually is a story behind it.   A lot of people do not understand the world that computer programmers live in.   For those of you who use Junxure, it is pretty easy to use the program and I hope we have done a pretty good job at hiding the complexity.  If you could peek into our world, you would see that Junxure is actually a blend of technologies, all working together to allow you to run your practice in the most efficient manner.

All of these technologies allow us to do some incredible things, but at the same time they are very complicated and forever evolving.  The challenge for me and our development staff is how do we keep up with the technology and it’s ever changing status.  The problem of keeping up is not unique to the programmers who develop Junxure,  it is a universal problem.  To help developers keep up with the changes, Microsoft has put together a team of people whose main purpose is to spread the news of what is new, what is coming, and how do you use these technologies.  Additionally,  just like we at Junxure listen to our users to find out what is working and what need to be added or changed, they listen and meet with many of their users to do the same thing.

They do this via a variety of ways, and one of them is something that is called “Code Camp”.  This weekend Microsoft, Infragistics and a host of other companies hosted a “Code Camp” and they were able to have it at a local community college.  Orlando Code Camp This Saturday, they put together 50 speakers, and 65 sessions designed to help developers use the technology.  There we sessions on Dot Net Nuke, Sharepoint, Data Services, Sql Programming, Programmer related management and development stuff, The Dotnet Framework, MVC and a whole bunch of other stuff.    You can click the link above if you are really interested in the details.

So if you have gotten this far in the post,  then you are probably asking yourself what the title “Hanselman of the East” has to do with all of this.  We it all goes back to a maintenance man at a theater in Fort Lauderdale.  You see, about 4 or 5 years ago, I went to another event that was hosted by Microsoft,  and this event was at a theater.  I arrived a a bit early and got a seat near the front.  While they were setting up it looked like there where having trouble with the audio visual system and in walks a maintenance man wearing a hard had and a old leather tool belt.

I have to admit, I thought it was a bit funny seeing all of these highly trained Microsoft employees standing around while this maintenance man was called in to make it all work.  He fiddled with some cables  and settings a low and behold, as if a by magic, the Microsoft logo appears on the huge theater screen.  Internally I had a bit of a chuckle thinking how the maintenance man saved the day.  Then they said that they were going to get started.  Much to my surprise, the same maintenance man goes to the front of the room and introduces himself as Russ Fustino, host of the, now world famous Russ’Tool Shed.  Russ gave a fantastic presentation on how to program on what was then the new Visual Studio and some of it’s new features.  Russ is one of the guys who helps us learn all of this new stuff.  His discussion and demonstrations of the tools that are available to developers is top notch.

So again you are probably asking again “What does this have to do with Hanselman of the East”.   Well there is another person who has devoted lot’s of his time to helping developers learn about technology, and his name is Scott Hanselman.  Scott is very well known but mostly hangs out in the West,  but he has a great reputation in this field.   Many people when they think of great leaders in the area of Microsoft development they are can’t help but immediately think of these two guys, along with a slew of many others.

To give you an idea of how many people Russ has helped over the years, I’ll share a story about a session this weekend.  Ryan Morgan was giving a presentation on Jquery, and a lady walked into the room and said she had some extra tickets to Russ’ TV show.   There were about 40-50 people in that room, with many standing in the back of the room because of  Ryan is an awesome presenter and everyone wanted to hear him.  Anyway, when she said she had a few tickets to the show, one poor guy asks “Who is Russ Fustino?”  The whole room went silent and almost in unison whole room cried out “WHO’S RUSS FUSTINO?”  like they were so surprised that someone in this field, would not know who Russ is.  The poor guy then had to listen as people began to say “EVERYONE KNOWS WHO RUSS IS.”  He as probably feeling pretty bad by then, but that shows the impact that Russ has made on the developer community.

Overall it was a great weekend for the Junxure developers.  We were able to fan out and cover a huge variety of topics and the knowledge that we gained will help us to make Junxure, Clientview and Junxure Mobile much better products.

So this is the end of the weekend and the Junxure developers have spent a couple of days reviewing our code, looking at things that we are working on and refining our road map.  We topped it all off with a whole day at Code Camp, and now we are going to be heading back to work, fully charged, loaded with new ideas,  and excited to get started using them.

Hopefully you now have a better appreciation of what we do behind the scenes to keep up to date on all of the current technology.