Sue Hernandez's Blog

January 11, 2010

Workflow Webinar Announced – Presented by … ME!

Filed under: MOSS, SharePoint Designer, Training, Workflow — Tags: , — Susan Hernandez @ 5:11 pm

Please visit my company’s site for information on the Webinar http://bit.ly/5OJwf5.

Only $99!!!

Our Agenda:

  • Out-of-the-box Workflows:  Descriptions and settings
    • Reviewing OOB Workflows
    • Creating a Workflow based on an OOB template
    • Running the new Workflow
  • SharePoint Designer Workflows: Basic
    • Creating a new Workflow against a specific Doc Library
    • Steps, Conditions, and Actions – an Overview
    • Setting the First Step
      • Basic If/Else statement using Conditions
      • Assigning a Task (“To-Do Item”)
      • Using a Workflow Lookup to another list
    • Setting the Second Step
      • Sending an Email using the Workflow Lookup
    • Running the Workflow
  • SharePoint Designer Workflows: Advanced
    • Creating a new workflow against a specific Doc Library
    • Modifying the Initiation Form (Using Initiation Parameters)
    • “Log to History List” Action – Best Practice
    • Creating and Setting Variables
    • Collect Data From User action (Instead of To-Do Item)
    • Finishing the Workflow
    • Running the Workflow
  • Shortfalls of Designer Workflows
  • (Time Permitting) Sneak Peek at a Visual Studio Workflow
  • Q&A

You will receive a full Transcript of the event, as well as Homework exercises and a Quick Reference Guide.

Register Now!  http://bit.ly/5OJwf5.

January 7, 2010

InfoPath: Importing a Spreadsheet

Filed under: InfoPath, MOSS — Susan Hernandez @ 11:29 pm

Have you ever wondered if you could import a spreadsheet into InfoPath?   

Well I have a scenario where we have users that are requesting for a purchase to happen – it’s not a purchase order yet.  They need to put in several line items into a recurring table by importing a spreadsheet / CSV file.

What I did, was I put an attachment on the form called ImportSpreadsheet.  During my custom Visual Studio Workflow, IF the file exists, I process the spreadsheet.  I haven’t completed the code yet to process the spreadsheet itself(SEE UPDATE), but I did figure out how to add items to a recurring section.  Below, in my code sample, _purchaseRequest is the serialized InfoPath Document.

Let me know if you have any questions or need more information. 

UPDATE:  I have updated the code with all the refs I used, as well as the updated code for parsing the CSV file.

REFS:

        private void ImportSpreadsheet()
        {
            if (_purchaseRequest.ImportSpreadsheet != null && _purchaseRequest.ImportSpreadsheet.Length > 0)
            {
                // Add items to the Items List
                try
                {
                    SPEventManagerWrapper.DisableEventFiring();

                    SPFile docFile = workflowProperties.Item.File;

                    // http://weblogs.asp.net/jan/archive/2004/05.aspx?PageIndex=2
                    while (docFile.CheckOutStatus != SPFile.SPCheckOutStatus.None)
                    {
                        System.Threading.Thread.Sleep(1000);
                        docFile = workflowProperties.Web.GetFile(docFile.UniqueId);
                    }

                    using (Stream formStream = docFile.OpenBinaryStream())
                    {
                        // Load the Existing Document
                        XmlDocument xmlDoc = new XmlDocument();
                        xmlDoc.PreserveWhitespace = true;
                        xmlDoc.Load(formStream);

                        XmlNamespaceManager xmlns = new XmlNamespaceManager(xmlDoc.NameTable);
                        xmlns.AddNamespace("my", "http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-12-22T15:57:36");

                        XmlNode itemizedListNode = xmlDoc.SelectSingleNode("/my:PurchaseRequest/my:ItemizedList", xmlns);
                        XmlNode subTotalNode = xmlDoc.SelectSingleNode("/my:PurchaseRequest/my:ItemizedList/my:Subtotal", xmlns);

                        double subTotal = 0;
                        double taxTotal = 0;
                        double totalWithTaxTotal = 0;

                        int lineItemNumCounter = 1;

                        string csv = System.Text.ASCIIEncoding.ASCII.GetString(_purchaseRequest.ImportSpreadsheet);
                        DataTable dt = CsvParser.Parse(csv, true);
                        int rowCounter = 0;
                        foreach (DataRow r in dt.Rows)
                        {
                            // Account for Header Row
                            if (rowCounter == 0)
                            {
                                rowCounter++;
                                continue;
                            }

                            string lineItemNum = lineItemNumCounter.ToString();
                            string lineItemDesc = r["Item Description"].ToString();
                            string partNumber = r["Vendor Part #"].ToString();
                            int quantity = 1;
                            if (!r.IsNull("Quantity"))
                            {
                                string qtyString = r["Quantity"].ToString();
                                qtyString = qtyString.Replace(" ", "");
                                if (!string.IsNullOrEmpty(qtyString))
                                {
                                    quantity = Convert.ToInt32(qtyString);
                                }
                            }
                            double unitPrice = Convert.ToDouble(r["Unit Price"]);
                            double taxPercent = 0;
                            double preTaxPrice = unitPrice * quantity;

                            double hiddenTax = 0;  // Convert.ToDouble(string.Format("{0:0.00}", (preTaxPrice * .01 * taxPercent)));
                            double totalWithTax = preTaxPrice + hiddenTax;

                            XmlNode itemNode = xmlDoc.CreateElement("Item", xmlns.LookupNamespace("my"));
                            itemNode.AppendChild(xmlDoc.CreateElement("LineItemNumber", xmlns.LookupNamespace("my"))).InnerText = lineItemNum;
                            itemNode.AppendChild(xmlDoc.CreateElement("ItemDescription", xmlns.LookupNamespace("my"))).InnerText = lineItemDesc;
                            itemNode.AppendChild(xmlDoc.CreateElement("PartNumber", xmlns.LookupNamespace("my"))).InnerText = partNumber;
                            itemNode.AppendChild(xmlDoc.CreateElement("Quantity", xmlns.LookupNamespace("my"))).InnerText = quantity.ToString();
                            itemNode.AppendChild(xmlDoc.CreateElement("UnitPrice", xmlns.LookupNamespace("my"))).InnerText = unitPrice.ToString();
                            itemNode.AppendChild(xmlDoc.CreateElement("TaxPercent", xmlns.LookupNamespace("my"))).InnerText = taxPercent.ToString();
                            itemNode.AppendChild(xmlDoc.CreateElement("PreTaxPrice", xmlns.LookupNamespace("my"))).InnerText = preTaxPrice.ToString();
                            itemNode.AppendChild(xmlDoc.CreateElement("TotalWithTax", xmlns.LookupNamespace("my"))).InnerText = totalWithTax.ToString();
                            itemNode.AppendChild(xmlDoc.CreateElement("HiddenTax", xmlns.LookupNamespace("my"))).InnerText = hiddenTax.ToString();

                            itemizedListNode.InsertBefore(itemNode, subTotalNode);

                            subTotal += preTaxPrice;
                            taxTotal += hiddenTax;

                            lineItemNumCounter++;
                        }

                        totalWithTaxTotal = subTotal + taxTotal;

                        xmlDoc.GetElementsByTagName("my:Subtotal")[0].InnerText = subTotal.ToString();
                        xmlDoc.GetElementsByTagName("my:TaxTotal")[0].InnerText = taxTotal.ToString();
                        xmlDoc.GetElementsByTagName("my:OrderTotal")[0].InnerText = totalWithTaxTotal.ToString();

                        formStream.SetLength(0);
                        formStream.Seek(0, SeekOrigin.Begin);
                        xmlDoc.Save(formStream);

                        docFile.SaveBinary(formStream);
                    }
                }
                catch (Exception ex)
                {
                    continueWorkflow = false;
                    ErrorMessages = ex.ToString();
                    WorkflowOutcome = "Error";
                }
                finally
                {
                    SPEventManagerWrapper.EnableEventFiring();
                }

                resetInfoPathForm();
            }
        }
    //http://knab.ws/blog/index.php?/archives/10-CSV-file-parser-and-writer-in-C-Part-2.html
    public class CsvParser
    {
        public static DataTable Parse(string data, bool headers)
        {
            return Parse(new StringReader(data), headers);
        }

        public static DataTable Parse(string data)
        {
            return Parse(new StringReader(data));
        }

        public static DataTable Parse(TextReader stream)
        {
            return Parse(stream, false);
        }

        public static DataTable Parse(TextReader stream, bool headers)
        {
            DataTable table = new DataTable();
            CsvStream csv = new CsvStream(stream);
            string[] row = csv.GetNextRow();
            if (row == null)
                return null;
            if (headers)
            {
                foreach (string header in row)
                {
                    if (header != null && header.Length > 0 && !table.Columns.Contains(header))
                        table.Columns.Add(header, typeof(string));
                    else
                        table.Columns.Add(GetNextColumnHeader(table), typeof(string));
                }
                row = csv.GetNextRow();
            }
            while (row != null)
            {
                while (row.Length > table.Columns.Count)
                    table.Columns.Add(GetNextColumnHeader(table), typeof(string));
                table.Rows.Add(row);
                row = csv.GetNextRow();
            }
            return table;
        }

        private static string GetNextColumnHeader(DataTable table)
        {
            int c = 1;
            while (true)
            {
                string h = "Column" + c++;
                if (!table.Columns.Contains(h))
                    return h;
            }
        }
        private class CsvStream
        {
            private TextReader stream;

            public CsvStream(TextReader s)
            {
                stream = s;
            }

            public string[] GetNextRow()
            {
                ArrayList row = new ArrayList();
                while (true)
                {
                    string item = GetNextItem();
                    if (item == null)
                        return row.Count == 0 ? null : (string[])row.ToArray(typeof(string));
                    row.Add(item);
                }
            }

            private bool EOS = false;
            private bool EOL = false;

            private string GetNextItem()
            {
                if (EOL)
                {
                    // previous item was last in line, start new line
                    EOL = false;
                    return null;
                }

                bool quoted = false;
                bool predata = true;
                bool postdata = false;
                StringBuilder item = new StringBuilder();

                while (true)
                {
                    char c = GetNextChar(true);
                    if (EOS)
                        return item.Length > 0 ? item.ToString() : null;

                    if ((postdata || !quoted) && c == ',')
                        // end of item, return
                        return item.ToString();

                    if ((predata || postdata || !quoted) && (c == '\x0A' || c == '\x0D'))
                    {
                        // we are at the end of the line, eat newline characters and exit
                        EOL = true;
                        if (c == '\x0D' && GetNextChar(false) == '\x0A')
                            // new line sequence is 0D0A
                            GetNextChar(true);
                        return item.ToString();
                    }

                    if (predata && c == ' ')
                        // whitespace preceeding data, discard
                        continue;

                    if (predata && c == '"')
                    {
                        // quoted data is starting
                        quoted = true;
                        predata = false;
                        continue;
                    }

                    if (predata)
                    {
                        // data is starting without quotes
                        predata = false;
                        item.Append(c);
                        continue;
                    }

                    if (c == '"' && quoted)
                    {
                        if (GetNextChar(false) == '"')
                            // double quotes within quoted string means add a quote
                            item.Append(GetNextChar(true));
                        else
                            // end-quote reached
                            postdata = true;
                        continue;
                    }

                    // all cases covered, character must be data
                    item.Append(c);
                }
            }

            private char[] buffer = new char[4096];
            private int pos = 0;
            private int length = 0;

            private char GetNextChar(bool eat)
            {
                if (pos >= length)
                {
                    length = stream.ReadBlock(buffer, 0, buffer.Length);
                    if (length == 0)
                    {
                        EOS = true;
                        return '';
                    }
                    pos = 0;
                }
                if (eat)
                    return buffer[pos++];
                else
                    return buffer[pos];
            }
        }
    }
 

January 5, 2010

Why won’t my emails send when I’m using a Workflow Lookup

Filed under: MOSS, SharePoint Designer, Workflow — Susan Hernandez @ 1:33 am

I have had to fiddle around with SharePoint Designer Workflows.  What I have done is set an Initiation Parameter to the email address of a Person.  Then, in the Send Email action, I specify the To field, select Workflow Lookup… and select Workflow Data and then my Initiation Parameter.

However, the email never gets sent.  I have also tried DOMAIN\username in the Initiation Parameter, but that didn’t work either.

Thanks for any help.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.