Anyway, here’s some code written in LotusScript to demonstrate Create, Read and Update of Excel data. Unfortunately Delete is not supported.

The following code is used to read a XLS file. It will only read the first Worksheet, but you could read through all of them.

On Error Goto processError

	Const filename = "c:\users\ian\my documents\writexls.xls"
	Const adSchemaTables = 20

	Dim adodbConnection As Variant
	Dim connectionString As String
	Dim adodbRecordSet As Variant
	Dim worksheet As String

	connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	filename & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

	Set adodbConnection = CreateObject("adodb.connection")
	adodbConnection.Open(connectionString)	

	Set adodbRecordSet = adodbConnection.OpenSchema(adSchemaTables)
	worksheet = adodbRecordSet.Fields("table_name").Value

	adodbRecordSet.Close
	adodbRecordSet.Open "SELECT * FROM " + worksheet, adodbConnection
	While Not adodbRecordSet.EOF
		For i = 0 To (adodbRecordSet.Fields.Count - 1)
			Msgbox adodbRecordSet.Fields(i).Value
		Next
		adodbRecordSet.MoveNext
	Wend

	adodbRecordSet.Close
	adodbConnection.Close

finally:

	Set adodbConnection = Nothing
	Set adodbCommand = Nothing
	Exit Sub

processError:

	Print "Error: " & Error & ": " + Error$ + " on line " & Erl
	Goto finally

Now, here’s the code to create, insert and update data in the file:

On Error Goto processError

	Const filename = "c:\users\ian\my documents\writexls.xls"

	Dim adodbConnection As Variant
	Dim connectionString As String
	Dim adodbCommand As Variant

	Kill filename

	connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	filename & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

	Set adodbConnection = CreateObject("adodb.connection")
	adodbConnection.Open(connectionString)	

	Set adodbCommand = CreateObject("adodb.command")
	Set adodbCommand.ActiveConnection = adodbConnection

	adodbCommand.CommandText = "CREATE TABLE [Contacts] ([Id] number, [Name] string)"
	adodbCommand.Execute

	adodbCommand.CommandText = "INSERT INTO [Contacts] ([Id], [Name]) VALUES ('1', 'Ian')"
	adodbCommand.Execute
	adodbCommand.CommandText = "INSERT INTO [Contacts] ([Id], [Name]) VALUES ('2', 'Tom')"
	adodbCommand.Execute

	adodbCommand.CommandText = "UPDATE [Contacts] SET [Name] = 'Paul' WHERE [Id] = 1"
	adodbCommand.Execute

	adodbConnection.Close

finally:

	Set adodbConnection = Nothing
	Set adodbCommand = Nothing
	Exit Sub

processError:

	Print "Error: " & Error & ": " + Error$ + " on line " & Erl
	Goto finally

Function getAttachmentData

	Dim doc As NotesDocument
	Dim stream As NotesStream
	Dim export As NotesDXLExporter
	Dim mime As NotesMimeEntity
	Dim txt As String
	Dim Start As Double
	Dim Finish As Double
	Dim fileData As String

	On Error Goto processError

	If Not dataDictView Is Nothing  Then Set doc = dataDictView.getDocumentByKey(|ARCHIVE CSV FILE| , True ) 

	If Not doc Is Nothing Then
		Set stream = session.CreateStream
		Set export = session.CreateDXLExporter
		'Convert the document to DXL
		export.setInput doc
		export.setOutput stream
		export.process
		'Process the export into a string file (as long as its less than 2GB)
		txt=stream.ReadText
		txt = Replace(txt, Chr(10), ||)
		'Find the <filedata> tags and read what's between them
		Start=Instr(1,txt,|<filedata>|)
		Finish=Instr(1,txt,|</filedata>|)
		fileData=Mid(txt,Start +10, Finish-Start-11)

		'Truncate the stream and write the filedata into it
		stream.Truncate
		stream.WriteText fileData

		'Setup a NotesMIMEEntity to decode the Base64 string
		Set mime = doc.CreateMIMEEntity(|MIMEEntity|)
		mime.SetContentFromText stream, |text/plain;charset=UTF-8|, ENC_BASE64
		mime.DecodeContent
		getAttachmentData = mime.ContentAsText
	End If

	Exit Function

processError:

	currentlog.LogError Err, |Error: | &amp; Err &amp; |: | &amp; Error &amp; | on line | &amp; Erl &amp; | in Agent: getAttachmentData|
	Exit Function

End Function

Here’s a simple view in Notes:

Here is what the JSON looks like:

{
  “@timestamp”: “20090303T123231,91Z”,
  “@toplevelentries”: “3″,
  “viewentry”: [
    {
      "@position": "1",
      "@noteid": "8000000C",
      "@children": "4",
      "@descendants": "4",
      "@siblings": "3",
      "entrydata": [
        {
          "@columnnumber": "0",
          "@name": "Index",
          "@category": "true",
          "text": {
            "0": "Option A"
          }
        }
      ]
    },
    {
      “@position”: “1.1″,
      “@unid”: “84FE562575501BE88025755F0031E1B1″,
      “@noteid”: “8F6″,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice A1"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “1.2″,
      “@unid”: “84FE562575501BE88025755F0031E1B1″,
      “@noteid”: “8F6″,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice A2"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “1.3″,
      “@unid”: “84FE562575501BE88025755F0031E1B1″,
      “@noteid”: “8F6″,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice A3"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “1.4″,
      “@unid”: “84FE562575501BE88025755F0031E1B1″,
      “@noteid”: “8F6″,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice A4"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “2″,
      “@noteid”: “80000010″,
      “@children”: “4″,
      “@descendants”: “4″,
      “@siblings”: “3″,
      “entrydata”: [
        {
          "@columnnumber": "0",
          "@name": "Index",
          "@category": "true",
          "text": {
            "0": "Option B"
          }
        }
      ]
    },
    {
      “@position”: “2.1″,
      “@unid”: “866C6FA4AEA492158025755F00328A60″,
      “@noteid”: “8FA”,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice B1"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “2.2″,
      “@unid”: “866C6FA4AEA492158025755F00328A60″,
      “@noteid”: “8FA”,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice B2"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “2.3″,
      “@unid”: “866C6FA4AEA492158025755F00328A60″,
      “@noteid”: “8FA”,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice B3"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “2.4″,
      “@unid”: “866C6FA4AEA492158025755F00328A60″,
      “@noteid”: “8FA”,
      “@siblings”: “4″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "textlist": {
            "text": [
              {
                "0": "Choice B4"
              }
            ]
          }
        }
      ]
    },
    {
      “@position”: “3″,
      “@noteid”: “80000014″,
      “@children”: “1″,
      “@descendants”: “1″,
      “@siblings”: “3″,
      “entrydata”: [
        {
          "@columnnumber": "0",
          "@name": "Index",
          "@category": "true",
          "text": {
            "0": "Option C"
          }
        }
      ]
    },
    {
      “@position”: “3.1″,
      “@unid”: “B6C86E0C587E08CB802575620031CA0F”,
      “@noteid”: “8FE”,
      “@siblings”: “1″,
      “entrydata”: [
        {
          "@columnnumber": "1",
          "@name": "Choice",
          "text": {
            "0": "Choice C1"
          }
        }
      ]
    }
  ]
}

Using jQuery and its incumbent JSON AJAX methods we can return data from the view and process it.

Below is a simple form which has 2 select option drop downs on it:

When the User chooses an Option from the ‘Option’ drop down, a set of choices specific to the selected option need to be offered in the ‘Choice’ field.

Here is the code from the JSHeader

function Option_changed()

{    

    //URL to the view with the restricttocategory set to the selected choice

    var url = dbpath + ‘/keywords?readviewentries&outputformat=json&restricttocategory=’ + $(‘#Option option:selected’).text()

    //Select object

    select = $(‘#Choice’)                    

    //Default option

    options = ‘<option value=”">– Please select –</option>’

    //jQuery AJAX call to return JSON

    $.getJSON(url,function(data) {    

        //Check if we have any rows

        if(data.viewentry){

            //Loop through the rows

            $.each(data.viewentry, function(i, item){        

                $.each(item.entrydata, function(i, item){

                    //If the column contains multi-value

                    if(item.textlist){

                        //Loop through the multi-value column data                    

                        $.each(item.textlist, function(i, item){

                            //Loop through the text values

                            $.each(item, function(i, item){    

                                options += ‘<option>’ + item[0] + ‘</option>’

                            })                        

                        })                        

                    } else {

                        //No multi-value so just grab the text

                        $.each(item.text, function(i, item){

                            options += ‘<option>’ + item + ‘</option>’

                        })

                    }

                })

            })

            //Loop through the columns

            select.html(options)

        }

    })

    //

    $(‘#Choice’).html(options)        

}

You can download a copy of the Demo database here.

main 


In the example below I will create a data connection to the http://moss/_vti_bin/usergroup.asmx web service to receive data to use in a Drop Down List to pick from the members of a Sharepoint Group.

1. Create the Data Connection

2. Select Web Service

3. Enter the URL to the UserGroup web service, don’t forget to include your site in the URL if appropriate.

4. For this example I’m using the GetUserCollectionFromGroup method

5. Provide InfoPath with an example Group Name

6. Provide InfoPath with the actual Group Name you want this Data Connection to query

7. Choose if you want to store a copy of the data in the form template for use in Offline mode. Since I’m using the Forms Service the form will never be used offline, so I’m leaving the box unchecked

8. Give your new Data Connection a name and Finish the wizard

Next we need to save the Form so we can manually edit the XSD for the Data Connection. From the File drop down manu choose Save as Source Files…

Open the folder where you saved the Form and you should see a Developers1.xsd file, edit this file with your favourite XML Editor (or notepad). Update the file so it looks like the snippet below, I’ve put in a comment at the start and end of the modification.

<?xml version="1.0"?>
<s:schema elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:s="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:tns="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:http="http://schemas.xmlsoap.org/wsdl/http/">
	<s:import namespace="http://www.w3.org/2001/XMLSchema"></s:import>
	<!-- Beginning of insert -->
	<s:complexType name="GetUserCollectionFromGroupType">
		<s:sequence>
			<s:element minOccurs="0" maxOccurs="1" name="Users">
				<s:complexType>
					<s:sequence>
						<s:element maxOccurs="unbounded" name="User" >
							<s:complexType>
								<s:attribute name="Notes" type="s:string"></s:attribute>
								<s:attribute name="Name" type="s:string"></s:attribute>
								<s:attribute name="IsSiteAdmin" type="s:string"></s:attribute>
								<s:attribute name="Sid" type="s:string"></s:attribute>
								<s:attribute name="ID" type="s:string"></s:attribute>
								<s:attribute name="LoginName" type="s:string"></s:attribute>
								<s:attribute name="Email" type="s:string"></s:attribute>
								<s:attribute name="IsDomainGroup" type="s:string"></s:attribute>
							</s:complexType>
						</s:element>
					</s:sequence>
				</s:complexType>
			</s:element>
		</s:sequence>
	</s:complexType>
	<!-- End of Insert -->
	<s:element name="GetUserCollectionFromSite">
		<s:complexType></s:complexType>
	</s:element>

We need to change another part of this file, replacing a s:element block.

<!--<s:element name="GetUserCollectionFromGroup">
  <s:complexType>
    <s:sequence>
      <s:element minOccurs="0" maxOccurs="1" name="groupName" type="s:string"></s:element>
    </s:sequence>
  </s:complexType>
</s:element>-->
<!-- Beginning of insert -->
<s:element name="GetUserCollectionFromGroup" type="tns:GetUserCollectionFromGroupType" />
<!-- End of insert -->

Now save the Developers1.xsd file, right click on the Manifest.xsf file and choose Design.

Add a Drop Down List Control and set its Data Source to Developers, you should now be able to select the correct attributes returned from the web service.

You can Save the Form as a standard XSN file, but DO NOT edit the Developers Data Source in InfoPath or you will overwrite the changes made to the Developers1.xsd file.

To do this we need to add some Javascript to the page.  Edit the page and add a Content Editor Web Part, hide it by clearing its Layout / Visible on Page checkbox.  Edit the Content Editor Web Part Source and insert the following Javascript:

<script language="javascript">

function Custom_AddDocLibMenuItems(m, ctx)
{
var strDisplayText = "Send for Review";
var strAction = "http://sharepointserver/Workflows/Send%20for%20Review/Send%20for%20Review.aspx?List=14987b66-90be-4693-aafd-94bc6ba8f18e&amp;amp;ID=24&amp;amp;TemplateID={dcbbce95-46dc-4b58-b69e-a99b9ea6a698}&amp;amp;Source=http%3A%2F%2Fsharepoint%2Edomain%2Ecom%2FWIP%2FForms%2FOpen%2Easpx%3FPageView%3DShared";

var strImagePath = "";

var start = strAction.indexOf("&amp;amp;ID=")+4
var prefix = strAction.substr(0, start)
var suffix = strAction.substr(strAction.indexOf("&amp;amp;", start), strAction.length)

strAction = "location.href = '" + prefix + currentItemID + suffix + "'"

// Add our new menu item
CAMOpt(m, strDisplayText, strAction, strImagePath);

// add a separator to the menu
CAMSep(m);

return false;
}
</script>

If you update the workflow you will need to edit the strAction property as the URL will change. Also if you try to start a workflow using the shortcut on an item where the workflow is already running the workflow will error. If there is a way to check if workflow is running on an item I’d be interested to know how it’s done!

Built on robust SQL Server database technology, these services provide high availability and security and support standards-based web protocols and interfaces (SOAP, REST) for rapid provisioning and ease of programming.

Businesses can store and access all types of data from birth to archival, using Microsoft SQL Server Data Services. Users can access information on any device, from the desktop to a mobile device.

Read on to learn how SQL Server Data Services deliver on the Microsoft Data Platform vision and meets the needs of the next generation of data-driven applications.

http://www.microsoft.com/sql/dataservices/default.mspx

name = "ian"

Set objDomain = GetObject ("LDAP://rootDSE")
Set conn = CreateObject("ADODB.Connection")
conn.provider ="ADsDSOObject"
conn.open "Active Directory Provider"
Set Comm = CreateObject("ADODB.Command")
Set Comm.ActiveConnection = conn
Comm.CommandText = "<LDAP://" & objDomain.get("defaultnamingcontext") & ">;(&(objectCategory=User)(anr=" + name + "));displayName,AdsPath;subtree"

Set rs = Comm.Execute
Set objUser = GetObject(rs.Fields("ADsPath").Value)

msgbox objUser.Get("mail")

When copying and pasting design elements from one database to another, if the source database is defined as a template then the following dialog box will appear:

If you answer yes to this prompt then the design element will be set to inherit from the source database.

To clear the property from all design elements use the following Agent code, which uses DXL to export, update and overwrite the design element.

Sub Initialize

Dim session As New NotesSession
Dim db As NotesDatabase
Dim note As NotesDocument
Dim noteCollection As NotesNoteCollection
Dim stream As NotesStream
Dim domParser As NotesDOMParser
Dim exporter As NotesDXLExporter
Dim importer As NotesDXLImporter
Dim attrib As NotesDOMAttributeNode
Dim nid As String
Dim found As Boolean
Dim i As Integer

Set db = session.CurrentDatabase

'Get a note collection of all the design elements
Set noteCollection = db.CreateNoteCollection(False)
noteCollection.SelectAllFormatElements False
noteCollection.SelectAllDesignElements True
noteCollection.BuildCollection

'Loop through the note collection
nid = noteCollection.GetFirstNoteId
For i = 1 To noteCollection.Count
Set note = db.GetDocumentByID(nid)

'Create a stream to store the DXL export output
Set stream = session.CreateStream
Set exporter = session.CreateDXLExporter
'Create a DOMParser to manage the DXL
Set domParser = session.CreateDOMParser(exporter, stream)
'Export the design element to the DOMParser
exporter.SetInput note
exporter.SetOutput domParser
exporter.Process

'Get the fromtemplate attribute from the exported XML
Set attrib = domParser.Document.DocumentElement.GetAttributeNode("fromtemplate")

'If the fromtemplate attribute exists and is not blank we need to blank it and import the DXL
If attrib.isNull = False Then
If attrib.AttributeValue <> "" Then
attrib.AttributeValue = ""
'Serialize the DXL so the importer has data to work with
domParser.Serialize
Set importer = session.CreateDXLimporter(stream, db)
importer.DesignImportOption = DXLIMPORTOPTION_REPLACE_ELSE_CREATE
importer.Import
End If
End If

nid = noteCollection.GetNextNoteId(nid)
Next

End Sub