Displaying Document Libraries in other sites

A client recently asked for a modification to Business Portal - Requisitions Management. They wanted to post a library for supporting documentation (quotes, product spec sheets, etc.). The problem is that they wanted it visible in both the Employee Purchase Request entry screen and the Purchasing Requisitions management screen and these pages are in two entirely separate sites.

As you may know, SharePoint is great at showing content from all the libraries you set up....as long as they are within the same site. Site A has no clue that Site B even exists.

The key for this solution came down to using the Page Viewer web part. This web part allows you to display another website within a window on another page.

The next challange is that SharePoint pages all include navigational borders and headers. When you display a SharePoint page within another page, all this flotsam is included, making a really confusing page. Here's how I worked around this issue.

Step 1: Setup your document library. Because I wanted to make sure that all users could get to the library, I set it up in the main Business Portal site. You can add permissions to the library if you need to in order to keep the right people in and the wrong ones out. Add any custom fields you might need to help find your documents more quickly. In this case, I added a mandatory Requisition ID field and an optional description field.

Step 2: Create a view for the document library that only shows the columns you'll want displayed on the other page.

Step 3: Create a new blank page that we'll use to display the document library in a web part. We'll be adding code to hide all of the navigational elements, so we don't want to do this on a page that's needed for something else. In this case, I went to the Business Portal Common Pages library and added the new page there, calling it RequisitionDocs.aspx

Step 4: Add the document library web part to the page.

Modify the web part to your liking. For me, I like to remove the ability of the user to do anything bad, like deleting the web part, moving it around, etc. I put it there for a reason, now you leave it alone! :) You'll also want to either have no title showing or show the title only. Select the view for the document library that you created above.

Step 5: Add another web part - the Content Editor web part. This is where we'll hide everything else on the page like the borders, the header...all that junk. In the content editor page, paste the code found on this page: http://vspug.com/spfromscratch/2007/12/18/blank-page/

NOTE: Take the "*" characters out of the word "java*script and don't forget the colon after the work "java*script"

As soon as you save the content, the page should change dramatically. If you're brave, you can remove the link at the bottom. Just remember though that you need a way to get back into edit mode so DON'T hide all your titlebars!

Step 6: Now that we have the page built that we want to display, we can link to it from our source pages. Edit the page where you'd like to display the library and add the Page Viewer web part. Modify the web part and add the url to your document library to the Link field. In this case, the URL would be http://dynamics-web/BP/Business%20Portal%20Common%20Pages/RequisitionDocs.aspx

Adjust the web part to be the way you want it on the page and Viola!

To take it further, the list view that I created had title that you could click on which then loaded the document library in my web part...bringing back all the navigational nastiness. To get around this, change the titleurl field in the document library on the display page (requisitiondocs.aspx). Instead of pointing to the document library, use the hash character instead (#).

Another problem is that in the default views for the document library, there's an "Add new document link" that again loads a full page for uploading documents. Sheesh, I try to get out and they keep pulling me back in! To get around this, edit the document library web part on your display page and change the toolbar type to "No Toolbar".

I have quite a few sources to thank for this, most of all is the code for hiding the nav elements. I found it posted on this page - http://vspug.com/spfromscratch/2007/12/18/blank-page/

The tip for hiding the Title URL was found here - http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/ac235b36-b516-40d3-bd2d-ea7dccd8eef1 and the tip on hiding the "Add new document" link was found here - http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/9561ff37-bd33-44ec-be45-82f0fcd868db

Sharepoint or Sharepoint Central Admin Returns Error “The File Exists (Exception from HRESULT: 0x80070050)

One of my clients is a financial institution. As such, they have pretty stringent controls on user accounts for vendors that may need to work on their system. Several months ago, I was brought in to do an install of Business Portal, which requires SharePoint.

I was given a temporary admin level userid to use during the setup, which I used to install and configure SharePoint and Business Portal.

Today I was called back to resolve some issues with Business Portal. When I tried to log back into SharePoint, my access was denied. I tried to get to Central Admin and received the error – The file exists. (Exception from HRESULT: 0x80070050).

Basically, what has happened is that the user account I was given initially was deleted after the initial install was completed and then recreated when I was asked to come back and repair Business Portal. They also never created another Admin login for the Central Admin and SharePoint sites. When the new account was created, AD issued a new SID (Security ID) which is the string of characters that REALLY defines your account. Your username is just a descriptive field. SharePoint provides access based on the SID, not just the username field. So, my username is the same, but the SID is different, preventing me from getting to SharePoint. The problem is that my account is the ONLY one that has rights – not a good situation for the client to be in.

Fixing this problem….well, it’s not a simple procedure. I’ve spent nearly two hours now researching and testing ways of getting the SID updated in the SharePoint database.

Here’s the procedure to go through to get the SID changed for a user in SharePoint. You’ll definitely earn your Geek badge for this one.

WARNING: This is a low-level solution, requiring hackery and other risky behaviors. USE AT YOUR OWN RISK ONLY AFTER YOU HAVE A BACKUP.

Procedure:

• Get the SID listed in SharePoint
• Get your current SID from Active Directory
• Convert your current SID from binary SID to HEX
• Update the database with the new HEX value

Simple sounding, not so much in real life. After much searching and trial and error, here are the source files you’ll need to accomplish this.

1) Get the SID listed in Sharepoint

Connect to your SQL Server and run the following query on the AdminContent database for your SharePoint install:

SELECT Sites.Id, Webs.FullUrl FROM Sites inner join Webs on Sites.RootWebId = Webs.Id

This will give you a list of the sites and associates URL’s that SharePoint knows about. Copy the Site ID to your clipboard.

You’ll now run this script that will get you the other information you need – including the user’s SID which is in the tp_SystemID field. Paste your Site ID from above into the XXXX space below.

SELECT * FROM UserInfo WHERE tp_SiteID='XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'

If you don’t mind filtering through a long list manually, you can just skip the 1st step and run this query -

SELECT * FROM UserInfo

This will get you a list of the usernames for that site.

2) Get your current SID from AD and convert to HEX

This is the part that took a long time to figure out. Fortunately, I came across a Visual Basic Script that will accomplish the task in one fell swoop.

You’ll want to copy the following text into NotePad and then save it as getSID.vbs. Note the portion in yellow below, you’ll need to replace it with the user’s AD context information. If you don’t know what the context is, use the dsquery command listed below the script to retrieve it.

GETSID.VBS

Option Explicit
Dim objUser, arrSid, strSidHex, objTrans, strUserDN, strSidDec
' Constants for the NameTranslate object.
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_1779 = 1
Const ADS_NAME_TYPE_SID_OR_SID_HISTORY_NAME = 12
' Bind to object. REPLACE WITH YOUR AD INFO 
Set objUser = GetObject("LDAP://cn=Test,ou=Sales,dc=MyDomain,dc=com")
' Retrieve SID and convert to hex string, then to decimal string.
arrSid = objUser.objectSid
strSidHex = OctetToHexStr(arrSid)
Wscript.Echo strSidHex
strSidDec = HexStrToDecStr(strSidHex)
Wscript.Echo strSidDec
' Use the NameTranslate object to convert objectSid to
' Distinguished Name.
Set objTrans = CreateObject("NameTranslate")
' Initialize NameTranslate by locating the Global Catalog.
objTrans.Init ADS_NAME_INITTYPE_GC, ""
' Use the Set method to specify the SID format of the object name.
objTrans.Set ADS_NAME_TYPE_SID_OR_SID_HISTORY_NAME, strSidDec
' Use the Get method to retrieve the Distinguished Name of the user object.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
Wscript.Echo strUserDN
Wscript.Quit
Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr _
& Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function
Function HexStrToDecStr(strSid)
' Function to convert hex Sid to decimal (SDDL) Sid.
Dim arrbytSid, lngTemp, j
ReDim arrbytSid(Len(strSid)/2 - 1)
For j = 0 To UBound(arrbytSid)
arrbytSid(j) = CInt("&H" & Mid(strSid, 2*j + 1, 2))
Next
HexStrToDecStr = "S-" & arrbytSid(0) & "-" _
& arrbytSid(1) & "-" & arrbytSid(8)
lngTemp = arrbytSid(15)
lngTemp = lngTemp * 256 + arrbytSid(14)
lngTemp = lngTemp * 256 + arrbytSid(13)
lngTemp = lngTemp * 256 + arrbytSid(12)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(19)
lngTemp = lngTemp * 256 + arrbytSid(18)
lngTemp = lngTemp * 256 + arrbytSid(17)
lngTemp = lngTemp * 256 + arrbytSid(16)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(23)
lngTemp = lngTemp * 256 + arrbytSid(22)
lngTemp = lngTemp * 256 + arrbytSid(21)
lngTemp = lngTemp * 256 + arrbytSid(20)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(25)
lngTemp = lngTemp * 256 + arrbytSid(24)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
End Function

This script will give you pop-up messages with the HEX value of the userid you’ve searched for. Unfortunately, you can’t copy and paste. So, VERY CAREFULLY transcribe it into notepad and then copy to your clipboard.

Future upgrade: I’ll try and see if I can revise the script to export to a text file instead.

DSQuery:

This command line will have to be run from a server that is AD aware and has the DSQuery utility. Easiest option is to run it from your AD Controller. In this example, we return any userid that starts with the letters JE. So for me, where my userid is RYoder, I’d use RY*

dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=je*))" -attr sAMAccountName distinguishedName ObjectSid -Limit 0

 

3) Update the database with the new HEX value

Run the following script against the Content database. NOTE: You’ll need to append the 0x to the front of the HEX string you found above.

Replace the X with the tp_ID of the user you are trying to update.

UPDATE UserInfo SET tp_SystemID = 0x010500000000000000000986BD9EA976E44036C3F5D3F04040000 WHERE tp_ID = 'X'

You should now have access to the content database. Note,  you’ll also want to run this update against any other content databases that are out there – usually one exists for each web application, otherwise you’ll get an Access Denied error when trying to hit the site.

 

That’s it! Enough for now, my brain hurts just typing this!

GP Technical Airlift Notes - Day 1 - GP11 ?

Keynote tidbits

Direction

• Product will be increasingly 'role-based', mapping the product to actual business processes.
• Product will be integrated more tightly with Office product line.
• Product design will be handled through Visual Studio. Standardized customization means increased supportability.

Add and extend:

• Role based => Enhanced user experience
• Portals and worflow => process-centric design
• BI => ad hoc, self service informationg athering
• web service => simplified / standardized accessibility

Direction

(A) Check out "Statement of Direction"

MS will spend $7B in R&D this year

GP looking to increase value to customers who subscribe to Enhancements by releasing Feature Packs between product releases. Next Feature Pack will be a step-in to GP 11, which will be built around Office 14.

16,000 QuickBooks customers last year moved from QuickBooks Pro to QuickBooks Enterprise. They should be GP customers. GP will begin aggressively marketing to QB customers.

New VPC is out.

Feature Pack Features

• Excel report builder - Additional data sources available. Preview columns before export. Add restrictions and calculations. Summarize a Excel Report for multiple companies into a single workbook.
• Forcasting enhancements
• Reporting
  • Personal BI - Report Writer, Excel Reports, Smartlists
  • Team BI - Excel Services, SQL Reporting Services, Sharepoint
  • Org BI - PerformancePoint Server, FRx
  • (A) - Check on PerformancePoint

Scope

(A) Check out Pinnacle Award winner stories to find out what cutting edge solutions are being done.

Demos

Upgrade to GP from QuickBooks. Old vs New

!! - GP 10 Feature pack includes rapid migration tool which allows you to bring in ALL data (including transactions !) in one single export / import routine.

During migration, all master records can be exported to Excel and vetted before importing into GP.

(20 minute upgrade)

Start a new company from scratch

Demo of Rapid Configuration Tool

Using Excel templates, allows for extremely rapid initial setup. End of setup, brings up list of all errors which link directly to cells in original Excel documents

Shipping 13 templates out of box for verticals.

Rapid Config tool hits ~78 windows and over 1300 Fields during setup.

(20 minute setup)

Fun Moments

Pissed off ISV barely controls his fury that his product feature will be built into GP 11 as a native feature - almost blowing his NDA.

MOSS Implementation

Eric Gjerdevig - egjerdevig@summitgroupsoftware.com

Joe Tews - jtews@summitgroupsoftware.com

Business Data Catalog - Map to back-end data that allows that data to be presented in Sharepoint as a list.

Method 1 - Business Data Catalog:

• Imported into SSP in MOSS Central Admin
• After Import, create new site. Add new web part Business Data List / Business Data Related List
• Modify to select source for data for each.
• After data resolves, Edit, Connections, Get data from...

Method 2 - Excel Web Access:

• MOSS Central Admin / Operations / Services on Server / enable Excel Calc Services
• Open SSP page
• Edit Excel Services Settings
• Fill in unattended service account
• Back to SSP / Enter trusted file location
• Upload Excel doc to MOSS library. Open document and publish to excel services (Office key / publish)
• Select what items get published
• Save to publish
• Open page, add Excel Web Access Web Part
• Select workbook created

KPI's

Search

Sources:

• Internal MOSS sites
• External websites
• File Shares
• Line of Business applications
• Exchange public folders

Workflow

Approval process that defines how documents move through an organization.

• Consistent processes
• Automatic notification
• Accessible through MOSS / Outlook
• Reporting

Participant Roles:

• Administrate
• Manage
• Originate
• Approve
• Delegate
• Carbon Copy

Customizing Workflow

Christina Phillips - christinag@theknastergroup.com

Tim Oines - tim.oines@microsoft.com

General Notes

• Requires MOSS
• MDW MOSS Site
• Documents originate in GP (NOT like requisition mgt in Business Portal where documents originate outside of the system)
• Approvals via GP, Outlook or MOSS
• Flexible approval structure
  • Multiple approvers
  • Routing based on document criteria
• Workflow SDK now available on partnersource
  • Integration Guide
  • Web Service Reference
  • 2 Samples (new + extending)
• Creates SQL role DYNWORKFLOWGRP to allow security via MOSS site. However, authentication happens via WSS Timer Service account

Terms

• Workflow Contract - Definition of the workflow. What components are available.
• Workflow Contract Registration - registration of assemblies with the GAC
• Workflow events - events that occur which need to be acted upon
• Dynamics Security Store - stores web services permissions for operations and tasks using auth mgr tool
• Form Controller - responds to gp client events, manages communications with web services, adds message bars, controls and history bar to dynamics window
• Form Factory - .NET code that runs before dexterity windows are opened to determined whether information is affected by a workflow

Out of the Box Workflows (6)

• Batch Approvals
  • GL
  • Payables M
  • Rreceivables Mgt
• Sales Order Processing
  • Credit Limit Override
  • Quote Approval
• Purchase Order Processing
  • Purchase Order Approval

Beyond Out of the Box

• Create a new workflow
  • Payables Check Approval
  • Sales Credit Memo Approval
• Extend existing
  • Add custom fields to standard workflow
  • Integrate 3rd party products

Building a new workflow

1. Make app dictionary changes
2. Install data and config changes
3. Add workflow columns to database
4. Add a web service
5. Create a client workflow assembly
6. Create a server workflow assembly
7. Create a document viewer
8. Update the Dynamics Security Service
9. Update Microsoft SQL Server Security
10. Deploy the Server Workflow Assembly
11. Deploy the Web Service
12. Deploy the Client Workflow Assembly

Business Intelligence

• Jennifer Harwood, Microsoft
• Brian Meier, Microsoft
• Gary Tronson, Microsoft

Excel Report Builder

• Users create or modify data connections and refreshable excel based reports
• Combine data from multiple companies into a single workbook
• Allow publishing to MOSS or other network locations

Setup:

• Install Smartlist Builder
• Tools / Smartlist Builder / Refresh Cache
• Grant Security to tables, views. Tools / Smartlist Builder / Security
  • Update cache every time new views are created and assign security, otherwise they will not be exposed to SLB
  • Tools / Setup / System / Security Tasks / SLB window
• Open Excel Report Builder
• Begin building your report
• If you publish the report, grant security to the report wherever you publish it.
• If you can see the report, but get errors about access - grant access for the data connection in SQL. SQL Mgt Studio / Security / Logins / Make sure the user's Windows account has rights to the databases.

SQL Reporting Services (Jennifer)

Partnersource / Product Releases / Download reports that can be opened in Visual Studio for exploration or modification

Make sure to rename reports if modifying existing reports so that they are not overwritten by service packs or upgrades

Model - a diagram of data that includes tables and views which is used as the foundation of the report. automatically creates aggregates of data (date becomes day, week, month, etc.)

Rest of presentation canceled due to trying to run the demo via Terminal Services over a wifi connection that was anemic.

Integrating BI into Dynamics GP

Demo - create a list in purchasing using Business Data as the source to pull in vendor list and purchase order list. list tracks purchasing disputes. then build a kpi that tracks the number of disputes to show red/yellow/green.

Windows Live Accounts in Sharepoint

Hold your hands up, everyone. Now put them down if you've never used a browser. OK, lost one.

Now, put them down if you've never used a browser to access a Sharepoint site. Wow, lost a lot there.

M'kay, now put them down if you've ever built or administered a sharepoint site. Anyone left? Oh, there's a few in the corner.

Now, put them down if you've never thought - "Gee, I wish I could authenticate users on my Sharepoint site without having to give them an account on my domain". Where'd the hands go. Woohoo, still got one. This article is for you - yes, you in the shirt.

Codeplex.com now has, in the Community Kit for SharePoint, a Windows Live Authentication feature that you can install and enable the use of Windows Live ID's to your sharepoint site. Kinda cool, huh?

Link here.

SPS Designer Class Notes and Links - Thursday

More useful notes and links from the SPS Designer class in Atlanta...

Infopath and forms

- form is a CAB file with an .XSN extension - Trust levels - Restricted - Domain - Full Trust

- To create forms, Infopath must be installed in designer mode.
- There is a framework from MS for importing Word and Excel documents
- To run forms services, enable both Office SharePoint Server Standard Site features and Office SharePoint Server Enterprise Site features in Site Features. Then, enable the same features under Site Collection Features.

- To force forms to be edited in browser, Advanced settings in the library, display as web page.

- If a form is going to be used in multiple locations, publish it as a content type. Then, when the form is updated, all locations where the form is used will also be updated.

Workflow

- Automates business processes

- Waits until something happens then proceeds to next step - Build on windows workflow foundation
- WF Program (template and forms)

- Installed as a feature into farm

- Workflow instance

- Activities

- .NET Framework Community Workflow site

http://netfx3.com/content/WFHome.aspx

- Microsoft Windows Workflow Foundation Step by Step by Kenn Scribner

- Shelton Blog - Workflow documents and videos

http://sheltonblog.com/

- El Blanco's Office 2007 Blog

http://chrissyblanco.blogspot.com/

- MOSS provides pre-built infrastructure for workflow. Possible with WSS but very difficult, have to build the infrastructure from scratch.

- Workflow templates (built from scratch) are deployed as features

- In Visual Studio (CTRL-K-B) to bring up snippets for sharepoint

- c:\program files\visual studio 8\xml\1033\snippets\sharepoint server workflow\snippets