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 Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.