Sharepoint 2010 Two Level Cascading Dropdowns

It has been quite awhile since my last blog post so please excuse my absence.

I wanted to come back strong with a tutorial on how I implemented cascading dropdowns in a Sharepoint 2010 site.  Please enjoy.

If you are not familiar with Sharepoint, that is quite alright.  Perhaps this tutorial will inspire you to explore.  For those of you that are familiar, hopefully you find this tutorial helpful.  I found some great blogs that were very well written and taught me a lot so I thought I would share what I learned.  It may not be the best and only way to implement the solution but it worked for me so here we go.

Sharepoint 2010 unfortunately does not provide cascading dropdowns as an Out-of-the-box feature for their lists and document libraries.  But like any business analyst would agree, when it comes to data management and user input, there is an undeniably high demand for this functionality.  Filtering corresponding selections based on a previously defined value not only helps ensure better data but also allows the user to use the tool more intuitively cutting down on turn around time.

Step 1: Manage reference files

  • At the top level site of your desired site for the dropdowns
    • Upload the files to the Site Assets list. (I chose this directory for flexibility and organization purposes, you can chose to upload these files anywhere your desired site would have access to).

Step 2: Prepare the lists for the dropdowns

  • Create a list for the top level dropdown values (i.e., Category)
  • Create a list for the second level dropdown values (i.e., Subcategory)
    • Ensure that this second level list includes a lookup column to the top level dropdown to establish a relationship
  • Add a lookup column to each list in your desired list or document library for the dropdowns

Step 3: Create custom New and Edit forms using Sharepoint Designer

  • Navigate to the desired site
  • Open the site in Sharepoint Designer (Site Actions –> Edit in Sharepoint Designer)
  • Under the desired list or document library
    • Copy, paste, and rename the NewForm.aspx page to create a custom New input form (It is not suggested to edit the OOB NewForm.aspx to prevent any unknown dependencies to fail)
    • Copy, paste, and rename the EditForm.aspx page to create a custom Edit form
  • In the list or document library properties, remember to assign the newly created custom forms as default

Step 4: Add javascript code

  • Back at the site UI, navigate to the list or document library settings
  • Under Advanced settings, select “No” for the “Launch forms in a dialog?” option (This step is so that the new and edit forms will not display in a dialog window so the page can be edited)
  • Create a new item (this should now be using the newly created new form, double check the page name in the url)
  • Edit the new item page (Site Actions –> Edit Page)
    • Add a Content Editor Webpart
    • Edit the webpart and select “Hidden” under the Layout section
  • Click on the content editor text area and open the HTML Source using the Ribbon
    • Paste the following javascript code (Perform the same for edit form):

<script language=”javascript” src=”/office/development/SiteAssets/jquery-1.3.2.min.js” type=”text/javascript”></script> //modify src URL as necessary

<script language=”javascript” src=”/office/development/SiteAssets/jquery.SPServices-2013.01.min.js” type=”text/javascript”></script> //modify src URL as necessary

<script language=”javascript” type=”text/javascript”>
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “Subcategory”,                          //name of list defining parent child relationship
relationshipListParentColumn: “Category”,      //name of parent column in relationship list
relationshipListChildColumn: “Title”,                 //name of child column in relationship list
parentColumn: “Category”,                                  //name of parent column in current list
childColumn: “Subcategory”,                               //name of child column in current list
debug: true                                //Mark as false if don’t want debug messages displayed
});
});
</script> ***Make sure column names are valid site columns***

 

  • Edit the edit form in the same manner as the new form
  • Navigate back to the list or document library settings and reset the “Launch forms in a dialog?” option back to “Yes”

Step 5: Perform ample testing and enjoy the newly implemented cascading dropdowns

What would be amazing is if this functionality could work for multi-select list boxes as well.  Maybe in Sharepoint 2013?  A girl can hope.

As always, thank you very much for reading.  Please be sure to comment with questions or suggestions!

References:
http://www.uccorner.com/54/sharepoint/cascading-drop-down/comment-page-1/#comment-2348

http://jassimkhan.blogspot.com/2010/10/implement-cascading-dropdown-in.html

Posted in Technical Tutorials, Work Experience | Tagged , , , , , | Leave a comment

Cursing at SQL Cursors

Most every engineer who’ve had database query experience responds to the use of “cursors” in a negative way.  But why?!  What is it about these cursors that bring out the worst in people?  I had to get to the bottom of it.

I first discovered these reactions when I wrote an SQL stored procedure using cursors during a data warehouse project I was on.  Being inexperienced with the optimization of SQL, I thought it made perfect sense at the time to use a cursor.  I knew I needed to create a dynamic SQL string that appended the value of each row from a result set.  Naturally, I broke it down into steps.  First, get the result set so I have values to work with.  Then, go through row by row to create my string.  So logically, I thought, loop in SQL? Cursor!  I had used cursors before in my previous project where I had to write code to print each row of a result set to an excel file.  So I knew using a cursor would do exactly what I needed but I never fully understood its impact or explored alternatives.  It wasn’t the best way but it was A way.

When I had a code review with my dev team, the DBA and a fellow coworker definitely did not fail to express their feelings about cursors.  The DBA ended up spending close to an entire day figuring out how to re-write my code in some other way.  Our manager had already approved the stored procedure I wrote because it works but the DBA’s passion towards eliminating the use of cursors was so overpowering that he couldn’t give it up.  It wasn’t until I did more research on SQL cursors that I found the community of cursor haters.  There were an abundance of questions and elaborate solutions on how to code around using cursors (when using one probably would’ve performed the same functionality with less of a headache).  What I disliked was finding people who disliked cursors simply because everyone else hated it without fully understanding why.  I didn’t want to become that person so the following are some things I found out about cursors.  You can decide for yourself whether or not cursors deserve your spite.

  • “It isn’t the cursor, it’s the misuse of the cursor that’s the problem.” – This is a quote from S. Lott who answered a similar question on the StackOverflow forum.  And he makes a good point.  Turns out a lot of people (like me) uses cursors in place of JOINs because it’s easier to think through the process logically.  It is not always obvious what the result set of a JOIN will return you but if you go row by row, you’re pretty much doing the work manually what a database engine is designed to do.  This manual work is of course a slow process compared to an application system that is optimized to do the same work.  SQL is meant to be set-based so using cursors and having a procedural mind-set isn’t advised.

http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much

Writing SQL queries is like solving math problems.  It makes more logical sense to look at it step by step when you don’t fully understand the process but it’s slow.  So people tend to skip steps because it’s faster.

  • More operations = more sql statements = more maintenance – So a cursor requires 2 operations: a placement operation to move the cursor to a row in the result set, and a fetch operation to return the row’s data.  Performing these operations requires the following steps:
    • Creating the cursor using a declare statement
    • Setting cursor options
    • Specifying results set
    • Grabbing the data using fetch statements
    • Cleaning up the cursor using a close statement
    • Deallocating memory used by the cursor

 

  • Complicated set-based operations for row-by-row work – From my project scenario I described above, this is an example of the set-based code we ended up with:

SELECT @SQL =
CASE WHEN a.DATA_TYPE <> ‘decimal’
THEN ISNULL(@SQL + ‘,’,) + ‘ [‘ + b.DisplayName + ‘] ‘ + a.DATA_TYPE
+ ISNULL(‘ (‘ + CONVERT(VARCHAR(20), a.CHARACTER_MAXIMUM_LENGTH) + ‘) ‘, ) +
ELSE ISNULL(@SQL + ‘,’,) + ‘ [‘ + b.DisplayName + ‘] ‘ + a.DATA_TYPE
+ ISNULL(‘ (‘ + CONVERT(VARCHAR(20), a.NUMERIC_PRECISION) + ‘,’
+ CONVERT(VARCHAR(20), a.NUMERIC_SCALE) + ‘) ‘, ) +
END
FROM INFORMATION_SCHEMA.COLUMNS a
INNER JOIN [MDM].[mdm].[tblAttribute] b ON b.TableColumn = a.COLUMN_NAME
WHERE a.TABLE_NAME = @TableName AND a.TABLE_SCHEMA = @Schema AND a.COLUMN_NAME LIKE ‘uda%’
ORDER BY a.ORDINAL_POSITION

This wasn’t a particularly difficult query but someone with little to no SQL experience would have a hard time debugging something like this rather than something that visually is easier to understand like a cursor.

  • Requires less mental effort – Set-based queries are not an easy task.  Take the query I mentioned above for example.  It took me less than an hour to come up with the code that does the same task with a cursor but it took our DBA close to an 8 hour day to come up with the set-based query above.  It just depends on priorities of the project or situation.

 

  • Out performs While loops – When it comes to large amounts of data, every second it takes to perform queries count.  For row-by-row processing, cursors are better than while loops.

 

  • Longer process time due to memory consumption – “A cursor is a memory resident set of pointers – meaning it occupies memory from your system that may be available for other processes”.  The time it takes a cursor to open and fetch data is not optimal.

This experience has helped me learn more about cursors and what is actually happening behind the scenes.  It’s not that often that us engineers dive deeper into why a certain practice is frowned upon when we’re trained to do things quick and easy.

Below are some of the references and blogs I found that were very helpful:

  • Hillary Cotter did a great job writing her series of articles on SQL cursors.  Check it out here.
  • Jeff Smith has a cool explanation on how to work around cursors. So visit him here.
  • Bill Graziano from SQLTeam.com provides some statistics to cursor performance here.

I definitely plan on adding to this post as I learn more and more.  If you have experience in this field, please feel free to provide your input.  As always, thanks for reading!

Posted in Work Experience | Leave a comment

Get Inspired

How can anyone not be inspired by this woman and this speech?  Kudos to all the powerful women out there.  Thank you for helping me get out of bed in the morning and thrive.❤

Posted in Influential Women | Tagged , | Leave a comment

Hey you’re quiet today

After working at my company for a year and 2 months now, I have come to realize a very important misconception:  Engineers are soooo not “quiet and anti-social”.  My company does tend to hire engineers that are more outgoing since we are also consultants… but still, they say women talk a lot…please!  I am quiet today because you did enough talking for the both of us. O snap!…Jk.

I love the fact that all these engineers that I’ve met are very friendly as well as smart but if you’ve ever been the only girl in a group of 3 or more guys, it’s almost impossible to get a word in or participate/be interested in the topic.  Here are a few reasons why:

  1. Fact spewing contest
    • Who can tell the most facts about a topic.  Most of their sentences begin with “Did you know…?” or “Actually, that is incorrect…”  What are you trying to prove?  Are you trying to impress a girl?…I’m not impressed.  Are you trying to impress the others?…that’s gay.
  2. What’s your favorite episode of Star Trek?  Remember that line from <insert nerdy movie title here>.
    • The stereotypes are true about this one…sadly.  Some sci-fi/science related tv series are very entertaining i.e. The Big Bang Theory but my little brain cannot possibly remember every line that was ever said (maybe my brain was not cut out for engineering…)
  3. How dumb non-engineers are
    • This is why we have no friends…
  4. How dumb other engineers are
    • This is why we have enemies…
  5. Sports
    • Yes, sports.  Some engineers are athletic as well.  Even if they’re not, guys just like following sports.  If only guys think Dance is a sport…
  6. And of course the bashing of the girlfriend or wife
    • I understand most of the time it is meant to be a joke but women NEVER find it funny, that’s why you don’t say it in front of them.  Soooo…hello?  Am I not a woman?

Anyways, please don’t get offended.  The reasons I’ve listed are probably somewhat exaggerated and I don’t mean to pose all engineering guys out to be condescending jerks.  I just wanted to express that, being a girl in engineering, it’s definitely hard to relate to the things guys tend to talk about.  A lot of times, not being able to participate in these conversations makes me feel uneducated,exhausted, anti-social, and outcasted.  My engineering skills are probably already subpar and now I feel the same about my social skills.  I just need to work twice as hard to keep up that’s all (note: may not only be gender related.)

Having experienced so many of these “lunches”, though, I would actually have to say that it motivates me even more to go educate myself, and gain new interests.  Even though I can’t participate much, I personally do love absorbing the information.  If everybody is showing-off, somebody’s gotta be listening right?  Bottom line is, I can’t change who I am but I will strive to be better.  I’ve already started making it a habit to browse engadget.com (shout out to Lewej) every day and have learned so much about today’s technology.  =]  Let me know if there’s other interesting sites to check out!

As always, thanks for reading!  Share your thoughts!…I would love to hear from someone who’s been the only male in a female conversation.  We have intelligent talks about life too! like how to walk in high heels!

Posted in Work Experience | Tagged , , , , , | 3 Comments

What’s your major?!

I’d like to start off my blog going back to where it all started.  College.

Everyone knows the one question most asked was “So what’s your major?”  Typical question.  No big deal.  It was how people started off the conversation to get to know you.  Fine.

But for me, this question usually came with a follow up question, or rather a follow up reaction, of “What?! WHY?!” as if my answer just changed the world as they knew it.

All these questions started to pop into my head.  Do I not look smart?  Is it because I’m a girl?  Why do people have assumptions about my major?  And where did these assumptions come from?  And, Really? Did you have to react that dramatically?

When I asked them back about their reactions, usually the reply was “You just don’t look like a computer science major.  I would have never guessed.” or “Are there even girls in your computer classes?”

Hearing the different responses made me realize how ignorant people were to how influential women were and are in the technology world.  The compiler (the program that translates programming languages into machine language that computers can understand; pretty much the foundation of all things software) was first developed by a woman.

The more surprising discovery was how ignorant people were to the different kinds of people in this industry.  What in the world are computer science majors supposed to look like?  I’m the one taking the classes and I don’t recall the existence of a computer science fashion bible.  Is having brains AND beauty really that rare?  I wouldn’t say I’m the prettiest girl around but I sure as hell don’t prance around with untamed hair and 2 inch thick glasses wearing baggy men’s clothes and sweatpants all day(Ok, maybe on my lazy days…but that’s not the point!)  I’ve met plenty of computer science friends who are girls.  I’m proud to say they beat out most guys in getting internships and job offers and they looked good doing it.  I just want someone to show me where they’re getting these impressions from.  Come on!  We’re in the 21st century where gay marriage is legal!

In the end, though, I have to thank those who presented their misconceptions to me.  Because it gave me more of a fire, more of a motivation to continue down this path I’ve chosen for myself.  For me, it wasn’t  really a matter of proving gender equality to society but more a matter of proving to myself that doing what I love far outweighed fitting in with society.  Feeling confident in an unusual environment is not a small feat and I encourage everyone to take on the challenge.  I’m still working at it today.

So now I want to know if there are others(guy or girl) out there who’ve experienced the same?  How did people react to your “unusual” major in college?  How did you coup with the reactions?  Maybe being “unusual” was an advantage?  And what about those of you who majored in exactly what people thought you’d study?

I’m just sharing my experience so feel free to prove me wrong. =]

-Bonnie

Posted in Introduction, Work Experience | 6 Comments

system.out.println(“Hello world!”);

Hello everyone!  My motivation for starting this blog is to share my experiences as a female in a male dominated IT world.  Many may not see this as a major issue but there are struggles that women go through when working in a male-dominated field.  So I just want to share some of my thoughts and see if others feel the same way I do.  I also want to use this blog to document certain technical discoveries about software that I’ve learned while working.

Currently, I am a software consultant for an IT Consulting firm.  Hopefully you won’t find my blog boring and I look forward to sharing my experiences from this not-so-popularly-talked-about world of IT.

Posted in Introduction | Tagged , , | 1 Comment