The Roadmap

Big...Huge...Gigantic...

I’ve been asked for an expanded vision for ESSC I.T.

I get the understanding that the gloves are off and we need to push forward under the assumption that the resources can be found, likely using grants and such. Considering the mess that C.A.D.D.I.S and the boys from Oracle left behind it is both exciting and a bit frightening to consider that little old me might be allowed to take a shot where all the old gods failed…

The key, in my own mind, is to decouple the different parts into something far more flexible than tradition allows, balancing the rigor of the ‘reported’ data entry with the ad-hoc nature of InfoPath forms services.

Drop Dead Drop Downs

When consuming a dataset created from a table with a key and description, by selecting at the top element and splitting the value and display, we can get a return value in the xmlDocument to post the foreign key and bind the joins together.

In this case the segment of xml is like:

->editregion
id->1
Desc->North Los Angeles
vpid->1227
vpname->Kolenda, Kathleen
vpstring->Kolenda, Kathleen
->/editregion

Then if the default value is -1, the value returned can be handled by determining if the result sent is a -1, in which case the source for VPName is the value (or in this case the VP’s Employee ID Number). What is funky is that the VPName will be the Display Name rather than the value if it’s an edit but if left untouched the VPName will be the Display Value. My solution is to load the table with a VPString field that doesn’t show and has the original name so that if they don’t match I know to use the VPName field but if they do I can use the VPID field.

With this code segment to load the repeating group in the main data section, I can get around the funkyness…

function LoadESSCRegions()
{ // Get the list of Services. var
regions = Document.GetDOM("AsRegionsList").selectNodes("//REGIONS");

// If nothing was found, then return; there will be no items to
insert.
if(regions == null)
{

XDocument.UI.Alert("Error-> Unable to load Regions List");
return;
}

var reg;

while
(reg = regions.nextNode())
{
var row =
XDocument.DOM.selectSingleNode("//my:myFields/my:RegionsTable/my:EditRegions");
row = row.cloneNode(true);
row.selectSingleNode("my:ESSCRegion_ID").text
= reg.selectSingleNode("ESSCRegion_ID").text;
row.selectSingleNode("my:RegionDescription").text =
reg.selectSingleNode("Description").text;
row.selectSingleNode("my:RegionIsActive").text =
reg.selectSingleNode("IsActive").text;
if (reg.selectSingleNode("RVP") ==
null)
row.selectSingleNode("my:RVPID").text = "-1"
else
row.selectSingleNode("my:RVPID").text = reg.selectSingleNode("RVP").text;

if (reg.selectSingleNode("NAME") == null)
row.selectSingleNode("my:RVPString").text = ""
else
row.selectSingleNode("my:RVPString").text =
reg.selectSingleNode("NAME").text;

var parent =
XDocument.DOM.selectSingleNode("//my:myFields/my:RegionsTable");
parent.appendChild(row);
}

}

Recursion of the mummy

Recursion of the mummy

It’s Halloween, and its recursion which often makes me curse but it is the only realistic way to parse an xmldocument. It is critical for us, using Infopath, to get the functionality of the form libraries along with the aggregation type of goodies I can grab out of a traditional DBMS. So our submit logic often starts with a submit to DBMS and then save the form to the library, while the associated onopen event for the form updates the fields from a secondary datasource grabbed from the DBMS by a webservice.

So, to parse a whole InfoPath form I first create an object for each repeating group in the form like so…
TLocationRecord = class(System.Object)
private
function isNewRecord(thisNode : xmlNode) : Boolean;
function isComplete : Boolean;
procedure ParseNode(thisNode : xmlNode;
thisRecord : TLocationRecord);
procedure RecurseChildNodes(ThisNode : xmlNode;
thisRecord : TLocationRecord);
public
Location_ID : Integer;
Name : String;
Address : String;
City : String;
Zip : String;
Phone : String;
Fax : String;
isActive : String;

Next_Location : TLocationRecord;

procedure LoadFromXML(RootNode : TLocationRecord;
xml : xmlDocument);
end;

The only tricky part is the recursion of course, so in this case after tossing the whole document to the LoadFromXML procedure I grab the first element of the form in a xmlNode object and feed into the recursion.

The recursion looks for the specific part of the form I want to parse, in this case the locations repeating table named my:EditLocations, which will return true on the NewRecord function, at which point I add another node to then end of my own object and recurse. Otherwise I just toss it to the parser until I run out of document.

procedure TLocationRecord.RecurseChildNodes(ThisNode : xmlNode;
thisRecord : TLocationRecord);
var
wrkNode : xmlNode;
NextRecord : TLocationRecord;
wrkRecord : TLocationRecord;
begin
while ThisNode <> nil do
begin
if isNewRecord(thisNode) then
begin
NextRecord := TLocationRecord.Create;
NextRecord.Next_Location := nil;

wrkRecord := thisRecord;
wrkRecord.IsWellFormed := self.isComplete;

while wrkrecord.Next_Location <> nil do
wrkRecord := wrkRecord.Next_Location;

wrkRecord.Next_Location := NextRecord;
RecurseChildNodes(ThisNode.FirstChild,NextRecord);
end
else
if ThisNode.HasChildNodes then
begin
wrkNode := thisNode.FirstChild;
ParseNode(thisNode,thisRecord);
RecurseChildNodes(wrkNode,thisRecord);
end;
thisNode := thisNode.NextSibling;
end;
end;

procedure TLocationRecord.ParseNode(thisNode : xmlNode;
thisRecord : TLocationRecord);
var
wrkNode : xmlNode;
begin
If ThisNode.Name = 'my:Name' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.Name := wrkNode.Value;
end
else
If ThisNode.Name = 'my:Address' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.Address := wrkNode.Value;
end
else
If ThisNode.Name = 'my:City' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.City := wrkNode.Value;
end
else
If ThisNode.Name = 'my:Zip' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.Zip := wrkNode.Value;
end
else
If ThisNode.Name = 'my:Phone' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.Phone := wrkNode.Value;
end
else
If ThisNode.Name = 'my:Fax' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.Fax := wrkNode.Value;
end
else
If ThisNode.Name = 'my:isActive' then
begin
wrkNode := ThisNode.FirstChild;
if wrkNode.NodeType = xmlNodeType.Text then
thisRecord.isActive := wrkNode.Value;
end;
end;

function TWebService1.maintUpdateLocations(IIRForm : xmlDocument) : integer;
var
insstr : String;
editStr : String;
inscmd : sqlCommand;
editcmd : sqlcommand;
Root : TLocationRecord;
wrkRecord : TLocationRecord;
RVal : Integer;
begin
Root := TLocationRecord.Create;
Root.Next_Location := nil;

Root.LoadFromXML(Root,IIRForm);

IIR_SQL_CONNECTION.Open;

insstr := 'INSERT INTO LOCATIONS(NAME,ADDRESS,CITY,ZIP,PHONE,FAX) '+
'VALUES (@NAMEPARAM,@ADDRPARAM,@CITYPARAM,@ZIPPARAM,@PHONEPARAM,@FAXPARAM)';

inscmd := sqlCommand.Create;
inscmd.Connection := IIR_SQL_CONNECTION;
inscmd.CommandText := insstr;

editStr := 'UPDATE LOCATIONS SET(NAME,ADDRESS,CITY,ZIP,PHONE,FAX) '+
'VALUES (@NAMEPARAM,@ADDRPARAM,@CITYPARAM,@ZIPPARAM,@PHONEPARAM,@FAXPARAM) '+
'WHERE LOCATION_ID = @LOCPARAM';

editcmd := sqlCommand.Create;
editcmd.Connection := IIR_SQL_CONNECTION;
editcmd.CommandText := editstr;

inscmd.Parameters.Add('@NAMEPARAM',sqldbType.NVarChar,50);
inscmd.Parameters.Add('@ADDRPARAM',sqldbType.NVarChar,75);
inscmd.Parameters.Add('@CITYPARAM',sqldbType.NVarChar,50);
inscmd.Parameters.Add('@ZIPPARAM',sqldbType.NVarChar,10);
inscmd.Parameters.Add('@PHONEPARAM',sqldbType.NVarChar,14);
inscmd.Parameters.Add('@FAXPARAM',sqldbType.NVarChar,14);


// Walk the list and either update or insert.....
wrkRecord := Root.Next_Location;

while wrkRecord <> nil do
begin
if (wrkRecord.Location_ID > 0) then
begin
// edit existing record
if wrkRecord.isComplete then
begin
editcmd.Parameters.Item['@NAMEPARAM'].Value := wrkRecord.Name;
editcmd.Parameters.Item['@ADDRPARAM'].Value := (wrkRecord.Address as TObject);
editcmd.Parameters.Item['@CITYPARAM'].Value := (wrkRecord.City as TObject);
editcmd.Parameters.Item['@ZIPPARAM'].Value := (wrkRecord.Zip as TObject);
editcmd.Parameters.Item['@PHONEPARAM'].Value := (wrkRecord.Phone as TObject);
editcmd.Parameters.Item['@FAXPARAM'].Value := (wrkRecord.Fax as TObject);
editcmd.Parameters.Item['@ISACTIVE'].Value := (wrkRecord.IsActive as TObject);
editcmd.Parameters.Item['@LOCPARAM'].Value := (wrkRecord.Location_ID as TObject);

RVal := RVal + inscmd.ExecuteNonQuery;
end;
end
else
begin
// Insert new record
// Validate Record for completeness...
If wrkRecord.isComplete then
begin
inscmd.Parameters.Item['@NAMEPARAM'].Value := wrkRecord.Name;
inscmd.Parameters.Item['@ADDRPARAM'].Value := (wrkRecord.Address as TObject);
inscmd.Parameters.Item['@CITYPARAM'].Value := (wrkRecord.City as TObject);
inscmd.Parameters.Item['@ZIPPARAM'].Value := (wrkRecord.Zip as TObject);
inscmd.Parameters.Item['@PHONEPARAM'].Value := (wrkRecord.Phone as TObject);
inscmd.Parameters.Item['@FAXPARAM'].Value := (wrkRecord.Fax as TObject);

RVal := RVal + inscmd.ExecuteNonQuery;
end;
end;
wrkRecord := wrkRecord.Next_Location;
end;
Result := RVal;
end;

Then it is just a matter of walking the TLocation Object and editing or inserting the rows.

JScript birth date madness

I had an earlier post on calculating a birth date in VBScript, but the IIR form was already set in java script which lacks a “datediff” function to calculate an age. It shouldn’t have been all that difficult to calculate just using date math, but there is a conversion issue when trying to use a date field in place of a string as the birth date source.

The issue is the use of ISO8601 format of YYYY-MM-DD versus the entry format of MM-DD-YYYY. The solution is to parse the string before doing the math. A complete "age" functions is as follows:

if (eventObj.IsUnDoRedo)
{
return;
}

//==============================================
//= On date of birth changed calculate age in years and set age field
//==============================================

var DOB = XDocument.DOM.selectSingleNode("my:myFields/my:DateOfBirth");

if ((DOB != null) && (DOB.text != ""))
{
var myStringList = DOB.text.split('-');
var DOBText = myStringList[1]+'/'+myStringList[2]+'/'+myStringList[0];

var dt1 = new Date();
var dt2 = new Date(DOBText);

var years = dt1.getFullYear() - dt2.getFullYear();

// Check to see if it's before or after this years birthday
dt2.setYear(dt1.getFullYear());

if (dt1 <>
{
years--;
}
SetField("Age",years);
}
}

Picking a single node from an eventObj

While we have already done code for a walkthru of all of the notes, after a column of buttons to our Daily Activity Worksheet Form it is important for us to be able to select values associated with that row, and only that row.

The object orientation of the JSCript code makes that a snap.

The button that was pressed, in particular, is the eventObj passed to the event handler. The "Source" Property is a reference to the other items in the row.

If we reference the form as seen, then the particular participant bound to the button in the row pressed is referenced as eventObj.Source.selectSingleNode("my:Participant").text



One important item of note, while doing the walkthru we used the path starting at the root... i.e. "//my:Participant". Since the button is already below the parent you need to reference it as "my:Participant" or at best, you'll always get the first child node.

Clearing some underbrush

One mistake I've noticed with the budget developer is the tendancy for webservie functions to grow like weeds. With a week left before deployment it's time to take a crack at seperating out those functions that are no longer required...

Next Up

I'm on the SharePoint Tour, friday, in Torrance. Hoping that will go well while at the same time I'm going back to the Budget Developer. So I'll be spending time in this space organizing my thoughts, with a special concentration on "What-If" additions to budgeting.

So here is a very early list of what if's

• Income impact of program income by percentage
• Line item, across the board or by entity tree percentage changes
• Reducing staff work days
• Ratio tools for employment related costs

But I do need some basis for better study...