GP Note Index

I have collected this for my reference from following two references and I thank you both for putting this out.

Ref:
	from msdn blog
	From mbsguru, blogspot

The Note Index (NOTEINDX) field in each record of SY_Company_MSTR (SY1500) table store the next Note Index to be used for that company. Every Master and Transaction record is assigned a Note Index when created. This Note Index is used as a foreign key when looking note against SY_Record_Notes_MSTR (SY03900).

To get Note Index use the following:
For Dexterity: call Get_Next_Note_Index, 'Note Index';
For SQL Scripting, call procedure: smGetNextNoteIndex

Yous SQL script as following:
declare @companyId smallint,
@noteIndex numeric (19, 5)
@errorState int;

-- get company id
select @companyId = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--get and increment the next note index
exec DYNAMICS..smGetNextNoteIndex @companyId, 1, @noteIndex output, @errorState output


print @noteIndex
print @errorState

Displaying PPT in SharePoint

Follow this link. Follow the instructions, they are straight forward, but make sure that you choose MULTPLE UPLOAD when uploading the file, the web version of ppt has more than one file so, SP informs you that you are uploading xx files even when you just selected 1 web version of PPT file. Thanks Sherri!

SQL Server login character length

Run sp_server_info procedure to find the limitation of the server with other information.

e.g. EXEC sp_server_info

Ref:
http://awesomesql.wordpress.com/2009/08/21/maximum-length-of-loginuser-name-and-password-in-sql-server-2005/
http://msdn.microsoft.com/en-us/library/ms176094.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/7b28762b-5e7e-40c7-b765-3eb6c2681ba5

Other limitations:
http://weblogs.sqlteam.com/mladenp/archive/2007/07/24/60267.aspx

SQL Server Error: 10061

Check if the SQL Server (MSSQLSERVER) Service is running. This can throw that error.
(Ref)

Relational Database Design

A database is a collection of data. Related data are grouped together in a tabular format so that it makes sense. For instance, salary data would better completement work related data than address data.
A relational database is acollection of tables of such data which can be linked with some common culumns, primary key and foreign key in database term. For instance:

EmpId	FName	LName
1	John	Q
2	Mary	James
3	Henry	Dew

EmpId	Type	Desc
1	Primary	This is primary description
1	Secondary	This is secondary description
1	Tertiary	This is tertiary description
3	Primary	This is primary description

Here, these two tables can be connected with the common field EmpId. Also notice that employee 2 does not has any entry in second table. This way information can be separated without loosing its integrity. There would have been duplication and few blanks if these two tables were combined. This is a form of a relational database.

In above, the first table could be called Employee and the second, Description. In database term it would be written as:

Employee (EmpId, FName, LName)

Description (EmpId, Type, Desc)

Employee and Description are called "entity" and those in parenthesis are called attributes.

As with every design, one needs to decide what needs to be put into the table as shown above. How detailed do you want to be will decide on how many tables and how to group them.


Once this has been decided, linking those tables with common field would produce ERD and the documentation something like (for first table above):

Table	Field	Data Type	Length	Description	Constraints
Employee	EmpId	Integer	Unique number that identifies an employee	Primary Key, Identity
	FName	varchar 50	first name of an employee
	LName	varchar 50	last name of an employee

Would be data dictionary where one defines data: name, type, length etc.

Installing SSMS 2008 in Windows 7

What?
I wanted to install SQL Server Management Studio 2008 in Windows 7 machine

Problem:
Whenever I tried to install it failed in preliminary run saying it could not restart the computer and continued even after restarting computer (not just one or two restarts)

How I solved:
Before running the initial run, I went to registry in:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Mananger
and cleared the value of "PendingFileRenameOperations"
and run the setup. mz! it worked.

Making Contact Us Forms in SharePoint with SharePoint Designer (SPD)

What?
I am creating a "Contact Us" form in SharePoint (SP) without coding (only little in SPD)

What do I need?
i. SPD
ii. Access in SP to add/delete/edit a list

How?
Concept:
1. Create a list in SP with required fields
2. Create a workflow which triggers on create
2.1 will email the content of the item (our Contact Us form content)
2.2 will delete the current item, it will just send email and not save the content
3. Tweak a little so that user fills as if they are filling out normal form

Procedre:
1. Set up a Custom List

2. Give the name "Contact Us"

3. Add column "Body", there already is "Title" and this can be used as 'subject'

4. Go to Settings->Advance Setting and disable attachment

5. Go to SPD and create workflow that triggers automatically on create.

5.1 Build email

5.2 Send email

5.3 Delete the current Item

7. Few tweaks

7.1 Go to Site Actions-> Site Settings

7.2 Click on "Navigation"

7.3 "Edit" navigation of "Contact Us" to /mysite/Lists/Contact Us/NewForm.aspx

This will take users directly to the form instead of list items.

7.4 Now change verbiage in NewForm.aspx in SPD so that it reads "Contact Us: Please fill out the form" instead of "Contact Us: New Item," the default one.

7.5 Also change verbiage in AllItems.aspx, mine says "Thank you for . . . " instead of the default one. This will act as a confirmation page and will appear when "Ok" button is presse in NewForm.aspx.

So,

This is an easy way to do "Contact Us" or similar kind of form. I'd appreciate if someone could add more to this one.

Happy F'day!

Making Contact Us Forms in SharePoint (SP) with SharePoint Designer (SPD)

What?
I am making a "Contact Us" from in SharePoint without coding (only little changes in SPD)

What do I need?
i. SPD
ii. Access in SP to add/delete/edit list

How?
Concept:

1. Create a list in SP with required fields

2. Create a workflow that will send email to required recipient and is triggred when new item is created.

3. Have a workflow delete the "current item" after the email has been sent

4. Tweak the SP ways to give impression to user as if they are working in normal form

Procedure:

1. Set up a Custom List

InfoPath: Enable rendering on a mobile device

I created a form and just to see what difference it would make, I check the option: "Enable rendering on a mobile device." Well I did not see any difference until I tried to publis it. While doing so, it had a notice that it should be approved by administrator. But when removed it worked just fine. See detail here.

Adobe LiveCycle Designer: cannot start, need reinstall

Adobe LiveCycle Designer has something to do with some of Microsoft packages. I was having the problem: the designer would not start how ever you start it.
I am using Windows 7. Then I found this post where it mentions removing Visual Studio distribution pacakge solved it. So, I searched through my installed application and alas! I found SQL Server 2008 setup files. My SQL Server 2008 was unsuccessful recently but setup files were there. I removed/uninstalled it and the ALCD starts working. I don't know the link but hey it worked and its Friday :)

Word and Fields

Go to this page if you are looking for formatting word fields.

Embedded Image in Email

To do:
1. Collect data from a database
2. Group records by employee
3. Create a personalized email with images

Solution I persued:
I have a report in SSRS with all content and when printed it is ready to be "snail" mailed but I need to email. I did not know how to email part of report pertaining to each employee. So, I used C# to do so. Here too, I did not use the report viewer to email as email address would be in report data and I don't know how to extract the data from report viewer.

Instead I invisioned a large data set, a table, containing everything. This is result from the query, as in SSRS, I would then do grouping etc as required. I would then Separate/group dataset per emp; create an email; then send it. I would repeat it for all the employees.

1. Collect data from database: Well there might be number of ways but I used SQLDataAdapter to collect my data.

using (SqlConnection conn = new SqlConnection (ConfigurationSettings.AppSettings["ConnectionString"]))
{
conn.Open();

using (SqlDataAdapter da = new SqlDataAdapter (sqlQuery, conn)){
DataTable dt = new DataTable();
da.Fill(dt);

Now I have my big dataset, I now need to separate them for each employee. My data had EmpId in the set so collected distinct empid from the set using:

DataTable empId = dt.DefaultView.ToTable(true, "EMPLOYID");

2. I have now set of employee in empId data table, pick one employee at a time, filter dt for that employee: I used,

DataRow[] dRow = dt.Select("EMPLOYID=" + empId.Rows[loopIndex][0].ToString());

This is the way to filter data table and it will return the array or data rows that matches the selection criteria.

Now I have the group, I created another data table to store information. This may have been extra steps but this is how I did:

foreach (DataRow dr in dRow)
{
DataRow newRow = EmpTable.NewRow();
newRow.ItemArray = dr.ItemArray;
EmpTable.Rows.Add(newRow);
}

3. Now email needs to be created. I choose html body for, email would contain text and images. I used StringBuilder for this purpose. In fact I created whole HTML document with tables to format the content and again tables to add grid for records for the employee. Important parts were:

StringBuilder emailBody = new StringBuilder();
emailBody.AppendLine("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\">
<html>
<head>
<meta http-equiv=Content-Type content='text/html; charset=iso-8859-1'>
</head>
<body>
<table border='0' cellpadding='0' cellspacing='0'>
<tr>
<td>
<img src='cid:companyLogo' align='right' />
</td>
</tr>");
for (int i = 0; i <>
{
//create table using <table> tag and put my data
}
emailBody.AppendLine ("</table>
</body>
</html>");

Email email = new Email("sender", "receiver", "subject", emailBody);
email.Send();

Email Class:
class Email
{
private string from, to, subject;
private StringBuilder body;
SmtpClient smtp;
LinkedResource lrCompanyLogo;

public Email(string from, string to, string subject, StringBuilder body)
{
this.from = from;
this.to = to;
this.subject = subject;
this.body = body;
lrCompanyLogo = new LinkedResource("path to file system", new ContentType("image/jpeg"));
lrCompanyLogo.ContentId = "companyLogo"; //same as used in 'cid' in img tag
}

public void Send()
{
mail = new MailMessage();
mail.To.Add(to);
mail.From = new MailAddress(from);
mail.Subject = subject.ToString();
mail.BodyEncoding = Encoding.UTF8;
mail.IsBodyHtml = true;

ContentType mimeType = new ContentType ("text/html");
AlternateView htmlBody = AlternateView.CreateAlternateViewFromString(this.body.ToString(), mimeType);

htmlBody.LinkedResources.Add(rlCompanyLogo);
mail.AlternateViews.Add(htmlBody);

smtp = new SmtpClient();
smtp.Host = ConfigurationSettings.AppSettings["EmailHost"];
smtp.Send(mail);
}


Important concept:
DataRow[] dRow = dt.Select(boolExpression);
DataTable empId = dt.DefaultView.ToTable (true, "EMPLOYID")
equiv: "SELECT DISTINCT EMPLOYID FROM dt"

<img src='cid:uniquId' />
LinkedResources lr = new LinkedResources ("path to file", "image/jpeg");
rl.ContentId = "uniqueId";
AlternateView htmlBody = AlternateView.CreateAlternateViewFromString(body.ToString(), new ContentType("text/html"));
htmlBody.LinkedResources.Add (rl);
mail.AlternateViews.Add(htmlBody);

Thanks to all of the followings:
http://dotnetperls.com/sqldataadapter-adonet
http://msdn.microsoft.com/en-us/library/det4aw50.aspx
http://www.dotnetspider.com/forum/ViewForum.aspx?ForumId=39
http://www.codeproject.com/KB/grid/Send_DataGridView_Mail.aspx
http://aspalliance.com/1354_Sending_HTML_Mail_with_Embedded_Image_in_NET.all
http://msdn.microsoft.com/en-us/library/system.data.datatable.select(VS.71).aspx
http://msdn.microsoft.com/en-us/library/system.net.mail.mailmessage.alternateviews.aspx
http://www.fuzzydev.com/blogs/dotnet/archive/2006/07/23/System_Net_Mail_AlternateView_LinkedResource.aspx

InfoPath 2007, saving forms without submitting

Well, I had that need! Where in my context submitting would trigger a workflow, a chain of emails saying what has been done and what needs to be done and so on. . .

Background: I have a forms library that house all the template forms and when any of the forms is filled and submitted, it will be submitted to its corresponding forms library. I thought it will keep the house clean. That was cool. . . but setting up that way was not very easy for me.

I had custom workflow for approval. Why? because approver was selected in the form. (See pervious posting)

After few test run, user wanted to save form without "submit"-ting it. I googled for "saving without submitting," thanks to all helper in infopathdev.com. The problem is, in SharePoint, container where the doc is opened is the default place for saving and my template form did not have "save" option.

I came with this solution.
Add a "Draft" checkbox, when checked, user meant, they are still playing with it and not yet ready for processing by workflow, and if not checked, otherwise. So, I created a Step 1 in my workflow to check the "Draft": if checked do not do anything stop the workflow else do what you are supposed to do.

and That worked!

Getting Announcements with attachments from sharepoint site

Situation:

I want a web page for the users who does not have network access to be able to look into current announcements published in our sharepoint.

My solution:

1. Use sharepoint web services to get list of current (unexpired/non-expiring) announcements

2. use a console application for 1. so that it can be scheduled and the sarepoint server is not hit everytime a user tryies to see the announcement

3. All announcements are saved in database with a link to attachments (if any)

4. Download all attachments to a location

5. When the schedule runs it clears the database and attachment folder so that we always have current data

This is how I did:

using System;
using System.Text;
using System.Xml;
using System.Configuration;
using System.IO;


namespace SharepointInterface
{
class Program
{
static void Main(string[] args)
{
string webPath = "http://MySharePointSite";
try
{
ClearAllFiles();//delete all previously downloaded attachment
XmlNode xmlData = GetDataFromWebService(webPath);
TraverseXmlData(xmlData);
}
catch (Exception ex)
{
//take necessary action I sent notification email
}
}

///

/// this function will delete all previously downloaded
/// files in 'DownloadLocation'
///

static void ClearAllFiles()
{
try
{
string path = ConfigurationSettings.AppSettings["DownloadLocation"];
string[] files = Directory.GetFiles(path);
foreach (string file in files)
{
FileInfo fi = new FileInfo(file);
fi.Delete();
}
string sql = "DELETE FROM Announcements; DELETE FROM Attachments";
//remove all records from database
DataAccessor da = new DataAccessor();
da.WriteData(sql);
}
catch (Exception ex)
{
//take necessary action
}
}

///

/// Function to communicate with sharepoint web service to get
/// required list data
///

/// Web path

//ref:
//http://www.sharepoint-tips.com/2007/02/how-to-use-getlistitems-web-service.html
private static XmlNode GetDataFromWebService(string webPath)
{
ListWebRef2.Lists sharepointLists = new SharepointInterface.ListWebRef2.Lists();
sharepointLists.Url = webPath + "/_vti_bin/Lists.asmx";
sharepointLists.UseDefaultCredentials = true;

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml("<Document> <Query /> <ViewFields /> <QueryOptions /> </Document>");
XmlNode query = xmlDoc.SelectSingleNode("//Query");
XmlNode viewFields = xmlDoc.SelectSingleNode("//ViewFields");
XmlNode queryOptions = xmlDoc.SelectSingleNode("//QueryOptions");

//get announcements that has no expiration date or the expiration date is higher than NOW
query.InnerXml = "<Where>" +
"<Or>" +
"<Geq>" +
"<FieldRef Name='Expires' />" +
"<Value Type='DateTime'>" + DateTime.Now.ToString("s").Replace('T', ' ') + "
"<OrderBy> <FieldRef Name='Modified' Ascending='False' />

///
///

///
///
private static void TraverseXmlData(XmlNode xnod)
{
XmlNode xnodWorking;
try
{
if (xnod.NodeType == XmlNodeType.Element)
{//its element
CrackAnnouncementXmlNode(xnod);
}

//if not element; check for child node
if (xnod.HasChildNodes)
{//the node has child node; traverse them as well
xnodWorking = xnod.FirstChild;
while (xnodWorking != null)
{//using recursive technique
TraverseXmlData(xnodWorking);
xnodWorking = xnodWorking.NextSibling;
}
}
}
catch (Exception ex)
{
//take necessary action
}
}

///

/// This function will break announcement node
///

/// Announcement
private static void CrackAnnouncementXmlNode(XmlNode xnode)

{
string sql = string.Empty;
string title, body, modified, editor, uniqueId, attachments;
title = body = modified = editor = uniqueId = attachments = string.Empty;

try
{
/* uncomment this section to see all returned attributes and comment all following if’s
for (int i = 0; i < title =" xnode.Attributes.GetNamedItem(" title =" title.Replace(" body =" xnode.Attributes.GetNamedItem(" body =" body.Replace(" modified =" xnode.Attributes.GetNamedItem(" editor =" xnode.Attributes.GetNamedItem("> 0)
editor = editor.Substring(editor.IndexOf(";#") + 2);
editor = editor.Replace("'", "''");
}

//this is prefixed with ;#
if (xnode.Attributes.GetNamedItem("ows_UniqueId") != null)
{
uniqueId = xnode.Attributes.GetNamedItem("ows_UniqueId").Value;
if (uniqueId.IndexOf(";#") > 0)
uniqueId = uniqueId.Substring(uniqueId.IndexOf(";#") + 2);
}

if (xnode.Attributes.GetNamedItem("ows_Attachments") != null)
{
attachments = xnode.Attributes.GetNamedItem("ows_Attachments").Value;
}

if (attachments != "0" && attachments != string.Empty)
{
//announcement has attachment so download it
DownloadFile df;
df = new DownloadFile(uniqueId, attachments);
df.ParseAndDownload(); //download file(s)
}

if (uniqueId != string.Empty)
{
sql = "INSERT INTO Announcements VALUES ('" + uniqueId + "', '" + title + "', '" + modified + "', '" + editor + "', '" + body + "')";
DataAccessor da = new DataAccessor();
da.WriteData(sql);
}
}
catch (Exception ex)
{
//take necessary action
}
}
}
}

Programming with SharePoint

I am writing a small webapp to download "Announcements" from a sharepoint site and had to work with GUIDs. The following link helped me to get guid i needed
http://blogs.msdn.com/ronalus/archive/2007/09/08/a-little-guid-picker.aspx

Hide "Cannot be blank" in InfoPath Services

In InfoPath form services, fields that have validation will display "Cannot be blank" message when hovered over the field. This can be annoying (very annoying:@). To disable this read this or change C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\INC\Core.js

change line from
objVisualizationDiv.style.display = "inline";
to
objVisualizationDiv.style.display = (strHelperName == "ErrorTip") ? "none" : "inline";


or comment out
border: 1px solid #B22828; background:. . .
with in .errorDiv section of ifsmain.css file in same foler

ref: read this

Thanks,