SQL History Table – Copy row to another table before update

This is great! I was looking for something like this, but could not find anything: how does that saying go – ‘necessity is the mother of invention’…???

This script builds a trigger that will copy a row as it was before an update to a history table that it will build if such a table does not exist.

CREATE TRIGGER [dbo].[<TriggerName,,>]
ON [dbo].[<YourTableName,,>]
FOR UPDATE
AS
DECLARE @TableName varchar(50), @KeyColName varchar(50)
----------------------------------------------------------
-- Warren Rapson Jan 2009
--
-- This trigger is designed to copy a record from a table
-- into its history table as it was before a change was
-- made.
--
-- The trigger should create a History table it the form
-- MyExistingTableName_History.
--
-- You must enter the name of the table to be used and its
-- key column in the below variables as well as the
-- trigger details at the top - press Ctrl + Shift + M
---------------------------------------------------------
   SET @TableName = '<YourTableName,,>'
   SET @KeyColName = '<YourTableKeyColName,,>'
---------------------------------------------------------

DECLARE
   @ColName nvarchar(4000)
   ,@AllCols nvarchar(4000)
   ,@OldValCols nvarchar(4000)
   ,@HistoryTableName nvarchar(50)
   ,@SQL nvarchar(1000)
   ,@SetStatement nvarchar(4000)
   ,@InsertHisoryStatement nvarchar(4000)

SET @AllCols = ''
SET @OldValCols = ''
SET @SetStatement = ''
SET @HistoryTableName = @TableName + '_History'

-- make history table if it does not exist
IF Object_ID(@HistoryTableName) IS NULL
BEGIN
   SELECT @SQL = 'SELECT * INTO ' + @HistoryTableName + ' FROM ' + @TableName + ' Where 1 = 2'
   EXEC (@SQL)
   -- add history columns
   SELECT @SQL = 'ALTER TABLE ' + @HistoryTableName
               + ' ADD HistoryDateTime datetime DEFAULT GETDATE(), HistoryUsername varchar(50) DEFAULT suser_sname()'
   EXEC (@SQL)
END

-- get coloum names from your table
DECLARE mycursor CURSOR FOR
   SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION
OPEN mycursor
   FETCH NEXT FROM mycursor INTO @ColName
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @AllCols = @AllCols + @ColName + ', '
      SET @OldValCols = @OldValCols + 'Old.' + @ColName + ', '
      SET @SetStatement = @SetStatement + @ColName + ' = Ins.' + @ColName + ', '
      FETCH NEXT FROM mycursor INTO @ColName
   END
CLOSE mycursor
DEALLOCATE mycursor 

-- remove last commas from statements
SET @AllCols = SUBSTRING(@AllCols, 1, LEN(@AllCols)-1)
SET @OldValCols = SUBSTRING(@OldValCols, 1, LEN(@OldValCols)-1)
SET @SetStatement = SUBSTRING(@SetStatement, 1, LEN(@SetStatement)-1)
 
-- this stops the 'invalid object name' error re the 'deleted' table - i don't know why...
IF Object_ID('tempdb..#tmp_OldVals') IS NOT NULL DROP TABLE #tmp_OldVals
SELECT * INTO #tmp_OldVals FROM deleted
 
SELECT @InsertHisoryStatement = 'INSERT ' + @TableName + '_HISTORY (' + @AllCols + ')
                                 SELECT ' + @OldValCols + '
                                 FROM #tmp_OldVals as Old, ' + @TableName + '
                                 WHERE Old.' + @KeyColName + ' = ' + @TableName + '.' + @KeyColName
EXEC(@InsertHisoryStatement)

How to highlight or change styles of fields/cells in SharePoint Lists

In this handy little trick, I have List of projects with a field called Status that contains values of either Green, Amber or Red.

Highlighted column

The below code, inserted into a Content Editor Web Part on the same page as the list, simply hunts for <TD> tags that contain Green, Amber or Red then alters their style!

<script>
   var strX;
   strX = document.getElementsByTagName("td");
   for(var i = 0; i < strX.length; i++) {
      if(strX[i].innerText == 'Green') { strX[i].style.background = "#00FF00"; }
      if(strX[i].innerText == 'Amber') { strX[i].style.background = "#FFD800"; }
      if(strX[i].innerText == 'Red') { strX[i].style.background = "#FF2222"; strX[i].style.fontWeight = 'bold'; }
   }
</script>

SharePoint Survey – Randomise questions

My last post was about my disappointment in finding out that workflows cannot be used with surveys… I was trying to build a quiz that would display random questions from a pool and then automatically grade the quiz using a workflow. At this point, the grading won’t happen but I thought I’d share my code that I used to randomise the questions in the survey.

The code uses javascript to hide (display: none) the TDs that hold the questions and answers on the NewForm.aspx then display a set number of those Q&As randomly.

I haven’t done extensive testing, but it seems okay. I pasted the below after ‘</WebPartPages:WebPartZone>’ on my survey’s NewForm.aspx.

<script type="text/javascript">
//----------------------------------
//NUMBER OF QUESTIONS IN YOUR SURVEY
var numQs = 3;
//----------------------------------

//inArray function - thankyou http://itcave.net/javascript/inarray
Array.prototype.inArray = function ( search_phrase ) {
for( var i = 0; i < this.length; i++ ) {
if( search_phrase == this[i] ) {
return i;
}
}
return false;
}

//get TDs and hide all the questions and answers
var strX = document.getElementsByTagName("td");
var QAsetno = 1; //var for sequential question numbers
for(var i = 0; i < strX.length; i++) {
if(strX[i].className == "ms-formlabel") {
strX[i].id = "question"+QAsetno;
document.getElementById(strX[i].id).style.display = 'none';
i++;
if (strX[i].className == "ms-formbodysurvey") {
strX[i].id = "answerset"+QAsetno;
document.getElementById(strX[i].id).style.display = 'none';
}
QAsetno++;
}
}
QAsetno--;

var proposedQno;
var randQno=new Array();
for(var x = 1; x <= numQs; x++) {
//propose a random number and check if it has been used
do {
proposedQno = Math.ceil(Math.random()*QAsetno);
} while (randQno.inArray(proposedQno)!=false);

//at this point, the proposedQno will be unique so we can add it to the list of used numbers
randQno[x] = proposedQno;
//make the Q&A visible using the unique proposedQno
document.getElementById("question"+proposedQno).style.display = '';
document.getElementById("answerset"+proposedQno).style.display = '';
}

</script>

I was hoping I could do something similar (building a auto-graded quiz) using a custom list, but the layout on a list NewForm.aspx seems a lot more complicated for a one-fits-all solution such as this.

Anyway – I hope this helps somebody – enjoy!

SharePoint Survery Workflow “failed to start”

What the hell is this!?

I’m building a workflow to be started after a user responds to a survey and I keep getting an error “failed to start”  in the workflow column in the ‘All Responses’ view. Thinking it was one of our apps servers playing up again I tried a simple workflow on a list – I only used a list because they’re a little quicker to create for a test like this… Glad I did use a list because my workflow worked as it should without error.

After Google-ing “sharepoint survey workflow” I found this at Microsoft http://support.microsoft.com/kb/926370. It turns out that you can’t use workflows with surveys!!! So I ask, why the hell can you attach a workflow to a survey in SharePoint Designer!!!

Thanks – I feel better for venting.

My first post!

Hi,

This is my very first post!!! I’m very new to this whole ‘wordpress’ thing, but I thought I should get myself a reputable blog at some stage so that I can post my scripts and thoughts related to my job with SharePoint, VB, MSSQL & suite, and my hobby with PHP, MYSQL, CSS and Javascript.

Nothing else to blog right now so – stay tuned!