Dailycode.info

Short solution for short problems

Export data from Lotus Notes to Excel

The following code lets you choose a view and all the data from the view is exported to Excel automatically.
Very useful and completely free!

Place this code as an Agent in Lotus Notes Designer, and run the code from the Actions-menu.

Sub Initialize
      'Create an Excel Spreadsheet from any view
     '11/3/2000 Art Yates
 Dim Session As New NotesSession ,db As NotesDatabase
 Dim sourceview As NotesView,sourcedoc As NotesDocument
 Dim dataview As NotesView, dc As NotesDocumentCollection
 Dim datadoc As NotesDocument, maxcols As Integer
 Dim WS As New Notesuiworkspace
 Dim ViewString As String, Scope As String, GetField As Variant
 Dim C As NotesViewColumn, FieldName As String, K As Integer,N As Integer
 Dim xlApp As Variant, xlsheet As Variant, rows As Integer, cols As Integer
 Dim nitem As NotesItem , entry  As NotesViewEntry, vwNav As NotesViewNavigator
 Dim ShowView()  As Variant, i As Integer, VList As Variant, ColVals As Variant
 
 Set db = session.CurrentDatabase   'link to current database
 
 'fetch then display a list of views in the database
 Vlist= db.views
 K=Ubound(Vlist)  'get size of list
 Redim Preserve ShowView(K)
 N=-1
 For i = 0 To K
  If Len(Vlist(i).Name) >0 Then 
   FieldName=Trim(Vlist(i).Name)
   If Mid(Fieldname,1,1) <>"(" Then  'do not show hidden views
    N=N+1    
    ShowView(N) = FieldName
   End If
  End If 
 Next i 
 Redim Preserve ShowView(N)
     'now sort the list - by default views are listing in the order that they were created
 For i=0 To N
  For K=i To N
   If  ShowView(i) > ShowView(k) Then
    FieldName=ShowView(i) 
    ShowView(i) = ShowView(k)
    ShowView(k)=FieldName
   End If
  Next k
 Next i 
 
 viewstring= ws.Prompt(PROMPT_OKCANCELLIST,"List of Views","Choose a View","",ShowView )
 If Len(viewstring)=0 Then Exit Sub
  'ViewString ="Dan's View"
 
 Set dataview = db.getview(ViewString)  'get selected view
 
 Set vwnav= dataview.createViewnav()
 
 rows = 1
 cols = 1
 maxcols=dataview.ColumnCount  'how many columns?
 
 Set xlApp = CreateObject("Excel.Application")  
'start Excel with OLE Automation
 xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
 xlApp.Visible = True
 xlApp.Workbooks.Add
 xlApp.ReferenceStyle = 2
 Set xlsheet = xlApp.Workbooks(1).Worksheets(1)  
 'select first worksheet
 
 'worksheet title
 xlsheet.Cells(rows,cols).Value ="View: " + ViewString + ", from Database: " 
+  db.title +",  Extract created on: " +  Format(Now,"mm/dd/yyyy HH:MM")
 
 xlApp.StatusBar = "Creating Column Heading. Please be patient..."
 
 rows=2  'column headings starts in row 2
 For K=1 To maxcols
  Set c=dataview.columns(K-1)
  xlsheet.Cells(rows,cols).Value = c.title
  cols = cols + 1
 Next K
 
 Set entry=vwnav.GetFirstDocument
 rows=3   'data starts in third row
 Do While Not (entry Is Nothing)
  
  For cols=1 To maxcols 
   colvals=entry.ColumnValues(cols-1) 'subscript =0
   scope=Typename(colvals)
   Select Case scope
   Case "STRING"
    xlsheet.Cells(rows,cols).Value ="'" +  colvals
   Case Else 
    xlsheet.Cells(rows,cols).Value = colvals
   End Select   
  Next cols  
  xlApp.StatusBar = "Importing Notes Data   -    Document " & (rows-1) 
  rows=rows+1
  Set entry = vwnav.getnextdocument(entry)  
 Loop
 
 xlApp.Rows("1:1").Select
 xlApp.Selection.Font.Bold = True
 xlApp.Selection.Font.Underline = True
 xlApp.Range(xlsheet.Cells(2,1), xlsheet.Cells(rows,maxcols)).Select
 xlApp.Selection.Font.Name = "Arial"
 xlApp.Selection.Font.Size = 9
 xlApp.Selection.Columns.AutoFit
 With xlApp.Worksheets(1)
  .PageSetup.Orientation = 2
  .PageSetup.centerheader = "Report - Confidential"
  .Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
  .Pagesetup.CenterFooter = ""
 End With
 xlApp.ReferenceStyle = 1
 xlApp.Range("A1").Select
 xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
 'xlapp.ActiveWorkbook.saveas "c:VX" + Trim(Format(Now,"yyy"))   'save with generated name
 dataview.clear 
 
 Set xlapp=Nothing   'stop OLE
 Set db=Nothing
End Sub 

Reference calculated fields

 When we use advanced functions, sometimes we need to reference one calculated field to an other. In Sharepoint this is possible.
And when we accidently make circular references we get this wonderful error:

The formula contains a circular reference (the calculated column refers to itself).  You must remove or change the circular reference.

 

Here is an example of a formula based on an other calculated field:

=IF(ISNUMBER(FIND("\",[calcfield])),RIGHT([calcfield],LEN([calcfield])-FIND("\",[calcfield])),"") 

 

This fomula checks if the field contains a "\", and if it does, it takes the substring on the right of the "\",
else it displays and empty string: "".

You can find everything on functions that you can use in calculated fields on: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx


Using advanced functions in calculated fields

Recently I had a nice challange in Sharepiont 2007.
I had to group a view by a field, but not just group it on the text but on part of the value.
Eg. The field contained a value like this: A chapter\a subchapter.
The view had to be grouped by chapter and secondly by subchapter.This is the simple solution to the problem: 

I created 2 new calculated fields in the sharepoint list.
The first field called chapter, the second subchapter.
The chapter field had to contain the part of the title field before the "\". And if there was no "\" in the text,
then it had to be the complete string. This is the code I used to create this field: 

=IF(ISNUMBER(FIND("\",Title)),LEFT(Title,FIND("\",Title)-1),Title) 

 

First we check if the "\" is present in the string, if not we just use title as it is. If "\" is present, then we take the left of the "\".

Here are some examples:

A testing code\b subtest 2     --> becomes: A testing code

A testing code                      --> becomes: A testing code

 

The second field had to contain the part of the string after the "\" and in case there was no "\" it had to be empty. Here the code I used:

=IF(ISNUMBER(FIND("\",Title)),RIGHT(Title,LEN(Title)-FIND("\",Title)),"") 

 

First we check if the "\" is present in the string, if not we return "". If "\" is present, then we take the right of the "\".

Here are some examples:

A testing code\b subtest 2     --> becomes: b subtest 2

A testing code                      --> becomes:

 

Then I grouped the view on these 2 fields. 

Since I'm a programmer myself, I know that examples are the best way to learn a code. So here's one more.

This time I had to get some values out of a text field called Soort with this format: text1\text2\text3 .
I had to show text2 and text3 in different columns. The first calculated field called Project contains the string text2.
The code to get text2 out of the string text1\text2\text3 is the following:

=LEFT(RIGHT(Soort,LEN(Soort)-FIND("\",Soort)),FIND("\",RIGHT(Soort,LEN(Soort)-FIND("\",Soort)))-1) 

 

So this code return: text2. The next calculated field called Subproject had to contain the string that is in place of text3.
Since Sharepoint code does not have the possibility to split or use loops we had to come up with the following code:

=RIGHT(RIGHT(Soort,LEN(Soort) - FIND("\",Soort)),LEN(RIGHT(Soort,LEN(Soort)-FIND("\",Soort))) - 
FIND("\",(RIGHT(Soort,LEN(Soort) - FIND("\",Soort))))) 

 

I have not reviewed these functions, I just know that they work.
Maybe it is possible to get the results in an easier way. Feel free to share it with us.

 


Using substring in a column in Sharepoint

A client asked me if we could group items in a list on a part of a textcolumn.

The text in the column looked like this:

categories per pc/ 106.032
categories per pc/ 106.033
 

I was supposed to make a view that showed that was grouped by pc name, only the pc name was not to be found seperate in any column. Instead it was a piece of the text in an existing field.It was the last 7 characters of the column.

So I googled and quickly and ended up on this website: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

There are all functions and operators on fomulas documented. I created a calculated field called PC and inserted this funcion:

=RIGHT(Categories,7) 

In named this new field PC and grouped my view on this field. The possibilities in calculated fields have more possibilities then I thaught.  


Redirecting pages with html

If you are looking for code to redirect your site to another site or subfolder, here it is: 

This script leads the old index page to the new web site.

<head> 	
	<script language="JavaScript">window.location="http://www.newwebsite.be/"; </script>
<head> 

 

It is also possible to reload the page after some time as e new page.

Use this script:

<head> 
	<meta http-equiv="REFRESH" content="10;URL=newpage.html"> 
<head> 

Workflow in Moss 2007

Workflow possibilities with Microsoft

 

 

Microsoft offers default 3 possibilities:

VS 2005, Sharepoint designer and out of the box solutions. 

v     Out of the box solutions: By default there are 5 workflows present after installation of MOSS: Approval, Collect Feedback, Collect Signatures, Disposition Approval, Translation Management.

v     Sharepoint Designer: This graphical design allows you to create different actions. This can be used by web designers, there's no need to posses any .Net knowledge.

v     VS 2005: The possibilities in VS 2005 are huge. You can accomplish almost everything, but it has to be developed. These workflows are usable throughout the entire portal. When it is not possible with Sharepoint designer, we have to use VS .net 2005. You'll need in dept .Net and VS2005 knowledge to create workflows.

Comparing Workflow Development Processes

The figure below illustrates the various steps that need to be performed to create, deploy, associate, and run a workflow using each of the authoring tools. In general, the largest difference between the two tools is this:

v   Workflow authoring in the Visual Studio 2005 Designer for Windows Workflow Foundation is performed by a professional developer, who is creating a workflow template that can be deployed across multiple sites, and contains custom code and activities. The developer then turns the workflow template over to a server administrator for actual deployment and association.  

v   Workflow authoring in Office SharePoint Designer 2007 is done by someone other than a professional developer, such as a web designer or knowledge worker, who wants to create a workflow for a specific list or document library. In this case, the designer is limited to the workflow activities on their ‘safe list’, and the workflow cannot include custom code. The workflow author deploys the workflow template directly to the list or document library as part of the workflow authoring process.

 
   

For a more detailed comparison of the capabilities and advantages of each tool, see Workflow Development Tools Comparison.

Extra info:

 


Retrieving DataItems on IndexChanged event

Recently I was looking for a way to retrieve a particular column based on it's column name (instead of fixed cellnumber) when a row in a GridView has been selected. I came up with the following which does the trick:

protected void gvGeneratedReports_SelectedIndexChanged(object sender, EventArgs e)
{
    GridView gv = (GridView)sender;
    DataRowView rowView = (DataRowView)gv.SelectedRow.DataItem;
    string outputType = rowView["OutputType"].ToString();
} 

Copying rows from DataTable A to DataTable B

It's amazing how often we need to copy data from one DataTable to another. This is how you can the job done:

// PRESUPPOSITION: Already instantiated dataTableA
 
DataTable dataTableB = dataTableA.Clone();
dataTableB.TableName = "CloneOfA";
 
// Copy records from dataTableA to dataTableB
foreach (DataRow dr in dataTableA.Rows){    dataTableB.ImportRow(dr);}

MOSS: Group list by month using calculated fields

How to group a list or library by month if you have a field containing Dates?

 

Simple, create a calculated field in your list or library that returns a string.

Use this code: =TEXT(Date,"yyyy - ")&TEXT(Date,"mm")&TEXT(Date," (mmmm")&TEXT(Date," yyyy)")

Now group by this field and that's it!

You can find everything on functions that you can use in calculated fields on: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx


Country or postal code list box

In some enterprise applications you need to show geographical data such as countries and postcodes. Most of the time you need it for a registration page, where the user need to fill in the country and postcode/area.

Geonames is a free geographical database that contains over 8 million geographical names and it can be accessed through a number of webservices. For example the url http://ws.geonames.org/countryInfo? gives an xml with all countries, whereas the following request http://ws.geonames.org/postalCodeSearch?placename=be gives us all postcodes for a particular country (e.g. Belgium).

Most likely you need two dropdown lists, one for countries and one for postcodes, where the postcode dropdown is dependent from the country dropdown list. This is a very good example to introduce AJAX by using the CascadingDropdown that is included in ASP.NET AJAX.

To implement this functionality we need to implement two methods on a webservice, namely GetCountries and GetPostalCodesByCountry.

Source url = http://www.delarou.net/weblog/PermaLink,guid,34dab161-3d1d-4e1a-b786-d79a9cc04447.aspx