Running Total with InfoPath

On a recent project I had a requirement to create a running total in a repeating table in an InfoPath form.  I did a lot of searching and found several people that seem to be confused about what a running total is.  I wanted an extra column in my table that showed the total up to that line.  This turns out to be tricky to find but quite easy to implement.

I found an answer on Stack Overflow by Stas Slabko who describes how to calculate a product with a repeating table.  I used this as a basis for my running sum.

So we need a really simple InfoPath form with a repeating section inRunningTotalDesign

In the table’s footer I have a the TotalValue field that has a simple formula of sum(Value).  The running Total field on the repeating line has a slightly more complex formula as follows:

RunningTotalFormula

../my:Value + concat((count(../preceding-sibling::my:Item[1]) = 0) * 0, 
substring(../preceding-sibling::my:Item[1]/my:RunningTotal, 1, 
(count(../preceding-sibling::my:Item[1]) = 1) * 
string-length(../preceding-sibling::my:Item[1]/my:RunningTotal)))

The RunningTotal field on a line is calculated by adding the Value field to the RunningTotal of the preceding-sibling (i.e., the line above).  If we were to do this on the first row of the repeating table then preceding-sibling would return NaN (Not a Number).  In order to overcome this we check if the count of the preceding sibling is 0 and get either a true or false, we multiply that by a string length to trim out any values that would say NaN.

So how does this look in Preview:

RunningTotalExample

Stas in his article lists the following:

  • Works in a browser form in any sharepoint environment
  • Is not impacted by “16 calculations” threshold
  • The formula is robust to row removal and shifts

Be aware that this solution is impacted by the 16 calculations threshold.  When you update a row then this cascades to all the subsequent rows as cascaded calculations.  Therefore if you change a row that is too far back this will cascade and could hit the limit.  When I implemented I used a Formatting Rule to disable values that were too many rows from the end of the table.  However the way I did that will work only in client InfoPath.

Hope this article helps someone and that my version of a running total matches your version.

Posted in InfoPath | Tagged | Leave a comment

Yet Another round of Microsoft Updates stops SharePoint Services 3 with SQL Embedded from working

Deja Vu with the WSS 3 Updates.  Our support teams tell me of at least three SharePoint failures today. This must now be at least fourth time, that I can think of, a Windows Update has applied a patch to SharePoint Services v3 (there are still a lot of them out there) that it doesn’t recover from.  You seem to get a mixed bag of results dependent upon what Operating System and what SQL version was used and various other factors.

As a general guide we see many SharePoints that just carry on working, several that need Product and Technologies Config Wizard (PSConfigUI) run and a few hardcore ones that need more help.  As a general rule, if SharePoint was installed with SSEE/SQL Embedded/Standalone option it will fall over after some updates.  Running Product and Technologies wizard on its own won’t help as that will fail as well.  If you are in this situation, try following Article ID: 944154 it should get you back and running.

You always need to consider which database engine you are installing with.  SSEE will mean that you don’t have a database size restriction but it comes with a bunch of problems and issues that you also need to be aware of.  Remember with SQL 2008R2 Express Microsoft increased the size limit to 10GB.  Express edition has some of the drawbacks of embedded but it makes several of them go away.  If you are anywhere near that size for your content databases then you really should consider the features of Workgroup or above.

The GroupBoard Workspace template that Microsoft produced for WSS3, really seems to add to these woes

September 2011 last time we had a spate of these

Posted in SharePoint Services v3 | Tagged , , | Leave a comment

Content Database Growth on DB Attach Migration from WSS3 to SharePoint Foundation 2010

Introduction

I’ve done a lot of SharePoint Services 3 to Foundation 2010 migrations now and one thing I’ve learnt is that they all have to be treated individually on their merits.  A recent one was a move from WSS 3 on SBS 2003 using SQL 2005 Embedded Edition to a SharePoint Foundation as part of SBS 2011 Standard Edition using SQL 2008R2 Express Edition.

The WSS 3 had a single site collection in a single content database.  I was a little concerned that this database was 7.2GB and I’d have a 10GB limit on SQL Express that is on the Foundation Server.  However I thought I had plenty of spare space for this to work.

I did the usual steps to complete the Farm configuration on the new SBS box and applied WSS Service Pack 2 to the old SBS 2003 box.  The Pre Upgrade Check in STSADM gives you a special warning to let you know that you have a content database that is over 4GB and you might have to do something when you migrate.  Your options are along the lines of:

 

  1. Deploy SQL Workgroup or above and use that for the content database
  2. Remove content from the database before upgrading
  3. Use a time machine to return to point of installation and change the design to use multiple site collections in separate databases
  4.  Hope that it will fit following the upgrade

 Option 4 may be an attractive one but what if we use a SQL Standard in a test machine to trial the upgrade before we attempt to attach to the live SBS 2011 Server.

 Trial Upgrade

I restored the database to a test machine running SharePoint Foundation 2010 using a SQL Standard and used STSADM –o addcontentdatabase.  I didn’t pre-allocate any space to the database or the log so that I could see the files grow.  The database was set to Simple Logging

The STSADM starts counting its percentages and then stopped at 19.69% going no further.  A check of the upgrade.log file shows that it ends with:

[STSADM] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [5/14/2012 10:13:33 PM]: Populating AllDocStreams Content Column
[STSADM] [SPContentDatabaseSequence] [DEBUG] [5/14/2012 10:13:33 PM]: Executing SQL DDL Script.

SQL is still busy, so I use SQL Activity Monitor to see what it’s doing.   I can see one process of interest on my Content Database that is executing the following:

            INSERT INTO AllDocStreams (
                Id,
                SiteId,
                InternalVersion,
                Content,
                Size,
                RbsId)
            SELECT
                ADV.Id,
                ADV.SiteId,
                ADV.InternalVersion,
        ADV.Content,
              ADV.Size,
                ADV.RbsId
            FROM
                AllDocVersions as ADV
            WITH
                (TABLOCK)

Meanwhile the Log file and the database file both start growing and keep growing until the log file is 5.3GB and the database is 11.7GB.  This happens as the result of a single SQL transaction so, even on simple logging, the transaction log will grow excessively.  You can of course shrink the log afterwards.  If you try this on the live site with a 10GB limit the upgrade will fail on allocating space when the database file gets to 10GB.

Conclusion

In this case the customer archived old data out of the content database and we disabled the Recycle Bin to reclaim space.  The database could then be shrunk to 5.5 GB and upgraded to 9GB.  We now just need to deal with the limited capacity for growth in the content database, could really use that time machine.

Posted in SharePoint 2010, SQL Server | Tagged , , | Leave a comment

CRM 2011 Launch Dialogue from a Button

Introduction

Most of my work is with SharePoint and SQL Server.  However on a recent project I’ve been working with my ConcentrixTSG colleagues on a Dynamics CRM 2011 implementation.  CRM 4.0 was a good product but they’ve certainly added and improved when they released 2011.  On of the new features that mapped well to a customer process was Dialogs.  Currently a prospect may phone up and our customer would talk them through the questions on a New Business Enquiry Form.  These paper forms are then passed around several areas of the business and recorded on some Excel based registers.  These registers are used for tracking and conversion analysis.

This seemed to map well to launching a dialogue that would provide the questions, record the answers and feed them into a new Lead record within CRM.  There are two main issues with CRM to overcome:

  • CRM 2011 Processes, whether workflows or dialogues, are still bound to an instance of an entity.  So you must already have a Lead to run a dialogue to create a lead
  • I wanted to be able to deliver a button on the ribbon that would launch the dialogue in a user friendly way.

It turns out that these two issues take a fair degree of effort to get around. Fortunately I found some help on the Internet.  The articles that I mention here both helped me but I wanted to record my own experience as well.  For my screenshots you’ll see that Lead has been renamed to New Business Enquiry.

Lead Creation Dialogue without a lead to run it against

The answer here to to run the dialogue off of an entity instance that you know will already exist, collect your answers and then use the dialogue to create a new lead.  CRM Trick Bag gave me the answer here.  When you create the Dialogue Process in CRM 2011 and you have to pick an entity, choose the User entity.  The currently logged in user can always select their user record and press the Start Dialog button.  They can then choose to launch the Lead Data Capture Dialogue, fill in the fields and on completion the dialog will create a new Lead based on the data captured.

For our Ribbon button we are going to put the URL required to call the dialogue directly onto the ribbon.  We thus need to capture that URL somehow.  Once your dialogue has been created and activated, select a user, press the Start Dialog button and choose your dialogue.  On the Dialogue’s first screen that comes up press CTRL+N.  This will launch a new screen.  From this screen you can look at the URL and capture the GUID for the process.  You’ll see this on the Trick Bag, you are looking for the first bit after %7b and up to %7d, these are the escaped versions of { and } used to surround a GUID.  Copy that into a notepad window and keep it for later.

Add a button to the Ribbon

In CRM 4.0 this was a little bit tricky, in 2011 in much harder.  Most of my guidance for this comes from CRM Mongrel so you’ll maybe want to look there as well.  However the first thing you want to do is create some Web Resources so that the button will have something to do.

Create some Web Resources

Go into Settings/Customisations and open the default Solution or one of your own solutions, click into Web Resources and create a new Script file web resource.  You can see that my function constructs a URL using Xrm.Page.context.getServerUrl() to find where you are and Xrm.Page.context.getUserId() to find who you are.  The function then does a window.open to launch the dialog in a new window.  This will pretty much work as-is in most situations as long as it has the correct URL that you got in the previous section.

Web Resource
Web Resource LaunchNBEF

image
The Web Resource editor.  This one shows the function that launches the Dialog, GUID has been obscured but your GUID will be different to mine.

function callDialog() {
    var url = Xrm.Page.context.getServerUrl();
    url += "/cs/dialog/rundialog.aspx?DialogId=%7bED03DEEA-6AD6-4BE9-92C7-2C831071419D%7d&EntityName=systemuser&ObjectId=" + Xrm.Page.context.getUserId();
    window.open(url, "", "status=no,scrollbars=no,toolbars=no,menubar=no,location=no");
}

For the time being, although the resource needs to have a function defined, it can just be a simple alert(“you pressed a button”).  You can then get the button in and working before pasting in the correct function.

Our button is also going need an icon.  for my example I reused the one from CRM Mongrel you need to add this as another web resource

Add the Ribbon Button

We are then going to edit the ribbon and

  • Add a command that calls the function in our first web resource
  • Add a button that has the picture from our second resources and when pressed calls the command

Buttons on the ribbon can only be added in valid places on the ribbon.  In order to find a valid place you need to look at the SDK, find the ribbon for your entity and look what position numbers the existing buttons have been given.  In the SDK you need to look in: \resources\exportedribbonxml\.  You’ll find that there is a separate file with the ribbon xml for each of the built in ribbons.  Open the one for the entity that you are interested in and look through it.

To put a button onto the main application you are going to be in the sections about: Mscrm.HomepageGrid.  A Lead form would be: Mscrm.Form.lead.MainTab.  The thing that you need to pay attention to is the Sequence numbers so that you can position your own button.

There are several ways to edit the actual ribbon, my approach was to use the Ribbon Editor for Microsoft Dynamics CRM from Codeplex.  This doesn’t (or at least didn’t) work with Claims Auth enabled; my scenario was pre-live so I turned claims off for the work and back on after.

The first thing is to add a Command Definition to the Lead entity (renamed in this scenario).

Command Definition

I gave it one rule to always enable the button.  This means that anyone who can get to Leads can press the button.  This could be made more complex so that only people with rights to create Leads can have the button.

image

Then define the Action for the Command Definition that calls the function in the web resource that we defined earlier.

image

Having got the Command defined we can now add a Custom Action that will be represented by a new button:

image

The Location box has a text string that contains the information we read in the ribbon xml.  In my example:

Mscrm.HomepageGrid.lead.MainTab.Workflow.Controls._children

We then need to define all the properties for the buttonimage

Sequence:60 – identifies the place we chose for our button in ribbonxml
Template:o1 – identifies a big square button

The rest is labels, image, tooltips and a pointer at the command we defined earlier.

CONCLUSION

There’s no getting away from it this is a long and complex process but I think the results of following it through create a good user experience for custom functionality.

Posted in Uncategorized | Leave a comment

External Data List Open in Dialog Missing

On a recent project I have an external data list showing some data from a SQL database that is part of an LOB system.  When I click any of the items in the list I’m getting the list’s display form but it launches in a dialogue.  I really want it to be a full-page so List Tools/List/List Settings but Advanced settings isn’t there because it’s an external data list.

One of the things that would often work in a previous version if a setting got trimmed out of the UI is just construct the URL to the setting.  My list is on the Admin subsite of my web application, so I can use:

http://sharepoint/Admin/_layouts/advsetng.aspx?List=

and then put the GUID of my list on the end.  I get the advanced settings with one option Launch Forms in a Dialog.  If I put this to No and press OK, I can turn dialogues off on the list, just like any other.  There really was no need to trim this setting out of the interface

Posted in SharePoint 2010 | Tagged , , | Leave a comment

Logging in the SSIS Script Task and Script Component

Recently I had some fun with logging in the SSIS script component and I thought I’d share it. I have an SSIS package that uses scripts in a data flow to write to Dynamics CRM Web Services. I wanted to add some logging to record the records that got updated. I found the MSDN article Logging in the Script Task but was unable to follow it. The article says that I should be able to use:

Dts.Log(“Rows processed: ” + rowsProcessed.ToString(), 0, emptyBytes);

However when I tried it there was no Dts.Log to be found. Well the answer is this.

Control Flows have Script Tasks and Data Flows have Script Components. Logging is separate and the way it is handled is different.

To Enable Logging for the Script Task:

  • On the SSIS Menu select Logging.
  • Add a Provider (I’m using the Log Provider for SQL Server and under its configuration have selected an OLEDB connection manager that points at the database that I want to record the logs in
  • Enable Logging for the Package in the Containers section
  • Click the grey tick for the script task that you want to log (sequence containers that house it/them)
  • Add log code as per the link above to your scripts
Log Provider for SQL Server Image

Log Provider for SQL Server

Script Task Log Settings Image

Script Task Log Settings

To enable a Script Component of a Data Flow task to be logged, select the data flow task and enable the appropriate logging:

Script Component Log Image

Script Component Log

Once the logging is enabled then you just need to add a few lines of code to the script component and/or the script task.

To test and demonstrate I created a new, simple package on a test system.  My simple package looks like:

Control Flow

Control Flow for simple package

In my single data flow I have an XML source that takes its XML from a string variable with a manually typed two lines and then a script component:

Script Component in Data Flow Task

Script Component in Data Flow Task

In my Script Task I can use code from the article linked at the beginning:

//script task
 public void Main()
 {
   byte[] emptyBytes = new byte[0];
   Dts.Log("Log from a Script Task", 0, emptyBytes);
   Dts.TaskResult = (int)ScriptResults.Success;
 }

There is an example of the code to use in a script component on a data flow task. Logging in the Script Component. The example uses VB.Net. I had a go at converting that to C# and came up with

//script component
public override void PreExecute()
{
  base.PreExecute();
  byte[] emptyBytes = new byte[0];
  this.Log("Script Task in Dataflow Component startup", 0, emptyBytes);
}

What are the results of this then, my SYSSSISLog table shows the following

SYSSSISLog

SYSSSISLog

Posted in Uncategorized | 1 Comment

PortalSuperUser and broken EditProfile.aspx

Sometimes support is just about figuring out what someone did wrong when configuring an application. I recently did a new install of SharePoint Server 2010 with Enterprise features that was to be used for a migration from an older MOSS 2007 farm. I set my new Server up with SP1 and created mysite and intranet web applications. The Intranet just had a test Team Site ready to be replaced with the migrated content. I set up the User Profile Service and imported objects from Active Directory. The profiles were there, mysites were set up and user search was working. I though everything was fine but then I clicked the Edit my Profile link on the Profile page of a mysite and got an unexpected error.

OK so I’m sure that the tracelogs will help me out here.  I used ULSViewer (http://archive.msdn.microsoft.com/ULSViewer) to capture the trace logs at the point of the error.  Immediately after the unexpected error in the tracelog was:

Application error when access /_layouts/EditProfile.aspx, Error=Server Out Of Memory. There is no memory on the server to run your program. Please contact your administrator with this problem. at Microsoft.SharePoint.Library.SPRequestInternalClass.GetUserToken(String bstrUrl, String bstrLogin) at Microsoft.SharePoint.Library.SPRequest.GetUserToken(String bstrUrl, String bstrLogin) at Microsoft.SharePoint.SPWeb.GetUserToken(String userName) at Microsoft.SharePoint.Publishing.CacheManager.<.ctor>b__0(SPSite newSite) at….

Server out of memory that must be absurd.  After a long spell of troubleshooting with a very good  Microsoft PSS engineer I suddenly realised that if I accessed EditProfile.aspx.aspx from another web app then it worked fine.  Something must be wrong with my mysite web app,

What I eventually worked out was that In order to get rid of the Event Log warning:

Object Cache: The super user account utilized by the cache is not configured. This can
 increase the number of cache misses, which causes the page requests to consume
 unneccesary system resources.To configure the account use the following command 'stsadm -o setproperty -propertyname portalsuperuseraccount -propertyvalue account -url webappurl'. The account should be any account that has Full Control access to the SharePoint databases but is not an application pool account.

On the http://mysite web app (and other web apps), I follow Mirjams article: http://www.sharepointchick.com/archive/2010/10/06/resolving-the-super-user-account-utilized-by-the-cache-is.aspx.  I checked again that I had the accounts in the User Policy:

This all looked right.  So in Powershell then, had I set these correctly?

PS C:\Users\administrator.xxx> $wa=Get-SPWebApplication http://mysite:80
PS C:\Users\administrator.xxx> $wa.properties["portalsuperreaderaccount"]
i:0#.w|xxx\SPsuperreader
PS C:\Users\administrator.xxx> $wa.properties["portalsuperuseraccount"]
i:0#.w|xxx\SPsuperuser

Oh, that’s not right, these are the settings for a Web App that is running in Claims authentication.  The MySites web app is running in Classic authentication so these needed to be re-set.

PS C:\Users\administrator.xxx> $wa.properties["portalsuperuseraccount"] = "xxx\spsuperuser"
PS C:\Users\administrator.xxx> $wa.properties["portalsuperreaderaccount"] = "xxx\spsuperreader"
PS C:\Users\administrator.xxx> $wa.update()
PS C:\Users\administrator.xxx>IISRESET

I’m sure that with a “normal” content Web App when you get these wrong you just get an access denied whenever you try to browse the web app.  It is strange that with a MySite host and SSSC enabled everything works until you get an Unexpected Error/Server out of memory when you try to edit the user profile.

There is now a Technet article linked from Mirjam’s (http://technet.microsoft.com/en-us/library/ff758656.aspx)

The object cache stores properties about items in Microsoft SharePoint Server 2010. Items in this cache are used by the publishing feature when it renders Web pages

Well, if the warning relates tot he Publishing Infrastructure and the object cache I recon it has very little to do with the MySite site collections anyway.  Perhaps the warning should only appear in the event log when Publishing Infrastructure feature is enabled within site collections in a given web app.

So it really is important to get these settings right, if you are going to apply them.

Posted in SharePoint 2010 | Tagged , , | 1 Comment