Tuesday, June 30, 2015

SharePoint List Template with Lookup Fields

If one creates a list template that has a lookup field to another list (the reference list), and then attempts to create a new list from that STP template in another site, the data in the lookup field of the new list will be empty. Of course the obvious reason is that the reference list does not exist in the new site.

The problem is that even if you create the reference list (or import it with another list template) in the new site, that won't simply work because the lookup field defined in the STP file refers to a list through a GUID that is valid only on the site from which the STP template was originally created.

Even though I created a list with the same name, it has a different GUID.

The solution to this is:

0-Import reference list to the new site, and note its new GUID. Open the STP file and replace the GUID of the original reference list with the GUID you just noted, this is how you can do that:

1-STP files are CAB files: so you can rename the template file's extension to be able to open it as a CAB file. You can verify this by openning the STP/CAB file and finding that it is a BINARY file that starts with the letters MSCF (Microsoft CAB Format).

2-Extract the Manifest.xml file from the CAB and open it in an XML editor. This is a big XML file, so I rather open it in an XML editor e.g. Visual Studio. Note that even you open it in an XML editor, it is not pretty formatted by default.

3-Find the definition of the lookup field. It will be something like the following. I have a Multi lookup field, so the lookup field type says LookupMulti. I found this field by searching for the GUID of the original reference list as you can find in the List attribute. I then replaced it with the correct GUID that applies to the site to which I'm importing my list with the lookup field to.

<Field Type="LookupMulti" DisplayName="RequiredTrainings" Description="The trainings required for the corresponding job title." Required="FALSE" EnforceUniqueValues="FALSE" List="{ddfd206e-206a-405b-8926-94447e2c3d09}" ShowField="Title" Mult="TRUE" Sortable="FALSE" UnlimitedLengthInDocumentLibrary="FALSE" RelationshipDeleteBehavior="None" ID="{165f9634-9a76-4c4f-b1f0-9b1e09e9f2ed}" SourceID="{56091bc8-c44c-438c-a114-18e283cd3ca4}" StaticName="RequiredTrainings" Name="RequiredTrainings" ColName="int1" RowOrdinal="0"/>

4-Recreate the CAB file. You can use makecab DOS command.

5-Rename the cab to STP and import it to the site, then create a new list from it!

Tuesday, June 9, 2015

ID Query String parameter in SharePoint Visual Web Part in a Wiki Page

What happens when you access a query string parameter in a visual web part?

A web part is just part of the ASP .NET SharePoint page, so one could safely assume that it is an ordinary and a correct thing to get the value passed to the page through a query string parameter. The visual web part control has access to the Request object which contains the data passed to the server as part of the request, including query string and form data. Of course the web part control and its child controls rely on the Web Forms View State, and that is also passed with the request.


It took me hours of experimentation and guesswork to realize that creating a visual web part that expects a parameter that is innocently called “ID” makes something under the hood, go haywire. I haven’t reversed engineered SharePoint to realize this, but it’s surely not just an ASP .NET issue. Because of course ASP .NET has no reason to restrict you from using any query string regardless of name.

SharePoint on the other hand, seems to treat a request with an ID in the query string, in a special way. ID is the same query string parameter used to show List Items, such as the pages to Edit and View list items. Request to these pages looks like: http://mysharepoint/sites/mysite/mylist/view.aspx?ID=1. And what I was trying to do was just let one web part page create links to another page with ID passed in the query string, and that page would have another web part that checks for the ID passed.

The problem happens specifically when you request a Wiki page with the ID parameter. In my scenario, I created a couple of web parts and added them to different Wiki Pages. Then they link to each other using the ID parameter. The issue happens even if you don’t use web parts at all. Just create a Wiki page, and add ID=1 to its query string (e.g. http://mysharepoint/SitePages/TestWikiPage.aspx?ID=1).

It simply doesn’t work and one must choose another query string parameter name. Choose for example: MyID or CustomerID instead of ID. Read about another SharePoint mystery here: SharePoint application page 404 error!


Try it! Just create request a SharePoint Wiki page with the ID parameter passed as a query string parameter in the request URL. Please let me know if it magically works for you, because for me, it doesn’t work even if I don’t use web parts or any kind of customization. This is one more example of how the various SharePoint components interact in complex subtle ways creating such unexpected behavior. Things should be simpler and I talk about that here: Diagnosing SharePoint Errors.

Tuesday, June 2, 2015

SharePoint Application Page results in HTTP 404 Error!

This is an example of how perplexing the interaction of the different SharePoint components creates confusing debugging scenarios for developers. I describe the process I went through to diagnose an elusive HTTP 404 error. If you just want the solution, find the section titled “The Solution” near the end.

I had a working SharePoint farm solution with two application pages. I developed the relatively simple solution mostly using Entity Framework. I understand that touching the SharePoint database is an anti-pattern, anything that goes around the official reliable API is a bad anti-pattern. I wasn’t touching the database, just looking J. It was an experimental project anyways. But which database I was accessing, is not related to the issue I was facing:

I deployed the application successfully to a different server. Everything is working fine.

The environment of the test server changes much dynamically than a relatively static very stable production server. For some reason, going back to this project after a while, I run the solution and navigate to one of the SharePoint application pages. To my unpleasant surprise, I get an HTTP 404. Everything is working except my two pages.

What could be going wrong? The solution is deployed successfully and I can find my application pages in the SharePoint layouts folder (defaults to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS\). I traverse the possible-solution-tree or graph, no, it’s actually a maze (at least to someone like me who is not a Microsoft MVP SharePoint consultant with 15 years of experience).

I added try-catches everywhere, even though I had a big try-catch wrapping the main part of my code. No exception was caught, so I thought there is some exception handled by an internal component of SharePoint, and that component is totally invisible to me. Speaking of S = A + B in one of my previous posts, we have S = A + B + C where C is a component that is just invisible to you!

I also tried to recreate the application pages and re-adding them as maybe some SharePoint project build configuration got messed up somehow, then I tried to recreate the whole project to the same result: HTTP 404. I was stunned because there is no configuration or deployment issues and no apparent problems with my code, which was working before!

Suspecting again the problem is with the code, I created an empty SharePoint application page to my project, and to my surprise it worked. This points that I am indeed doing something wrong with my application pages, yet no exception is thrown that I can catch! And what is this HTTP 404 for an application page that is actually there?! Chrome’s debugging console simply just shows that the application page is being requested but not found.

I remember how sometimes Visual Studio debugger fails to catch some kind of errors, either because it is configured not to do so, or it just fails: COM Exception and Visual Studio crashing. But this doesn’t seem to be a Visual Studio problem.

Then I checked the SharePoint logs… What a nightmare trying to find your way there.

What I’m trying to illustrate here is that checking the various components in a system in an attempt to diagnose errrrrors involves considering different scenarios and options. I tried many options until…

The Solution

I discovered that the primary difference between my application pages that caused the server to return a 404, and the other empty application page is the reference to EntityFramework. When the runtime tries to load EntityFramework.dll, a System.IO.FileNotFoundException is thrown because the DLL is not deployed in the right way with the solution.

A SharePoint solution is dissimilar to a regular Web Application project in the way it is deployed. When a web application is deployed, the application can find the libraries that it references in the same location of the application’s DLL or in the machine’s GAC.

In my case, and for some reason, the SharePoint solution was not able to find EntityFramework.dll. It’s a file, and because of the System.IO.FileNotFoundException, either ASP.NET or SharePoint environment found it appropriate to just return a 404. This is simply does not follow the HTTP protocol where a 404 means that the client requested the wrong location. The server not being able to find files required for the page to operate correctly is in fact a 500 server side error!


Deploying EntityFramework.dll in the GAC solved the issue. I must remark that part of the possible-solution-tree has something to do with running Visual Studio with Administrator privileges.

Monday, June 1, 2015

SharePoint: Thoughts on Diagnosing Issues in a Complicated ASP .NET Web Forms Application

SharePoint is an ASP .NET application, built with many out of the box enterprise collaboration features and a sophisticated customizability model (SharePoint solutions). Unfortunately, it’s an ASP .NET Web Forms application.

Microsoft’s ASP .NET team realized the importance of a clearer and cleaner web development model wherein control is given back to the web developer with ASP .NET MVC. It is a great alternative to ASP .NET Web Forms. Many ASP .NET developers struggled with Web Forms to create web solutions that required more granular control over the outputted HTML and JavaScript (of course there is the advantage of black-boxed web controls and third party products that make a lot of things easy in Web Forms: that’s another thing to consider). One can find lots of thoughts comparing the two on the web.

Although many find ASP .NET Web Forms limiting in many ways, it’s the technology used to build SharePoint as SharePoint predates ASP .NET MVC. Building SharePoint applications using ASP .NET MVC is a different story; you can customize SharePoint by building SharePoint Solutions that use ASP .NET MVC. What I’m talking about here is the system itself: the built-in features and tools. For example, the display, edit, and new forms for lists and items and the master pages in SharePoint are ASPX pages, Web Form pages. In addition, Visual Studio Tools for Office, which includes SDK libraries for SharePoint and project templates for SharePoint solutions, has Web Forms ASPX pages with code behind, and a Visual Web Part project references System.Web by default (System.Web.dll contains the System.Web.Forms namespace).

I think that using Web Forms and the accompanying limitation in a clean architecture is one important factor that makes SharePoint such a hard thing to master. I imagine a much better SharePoint had it been developed using ASP .NET MVC. Technology choices affect architecture which surely affect developer experience in turn. Componentization is part of the process of creating a good architecture, and SharePoint is full of components running here and there (including Windows services), and componentization is only one part of a good architecture and it does not necessarily mean clarity. It also certainly does not mean clarity for the developers who are implementing and building on top of SharePoint.

Diagnosing failure is one primary task of IT specialists and developers. Figuring out why something occurred or behaved the way it did is one vital part of such diagnosis, and wandering a complicated world of componentization and the resulting exponentially complicated possible-solution tree is not a trivial task. SharePoint is not a trivial system and so is diagnosing its issues. I believe I’m not the only one who struggles all the time trying to find out the root cause of a problem.

Components interact as they function. When something fails, the obvious is that the problem could be in one or more of these components. But beyond figuring out whether each of the components is working or not, the interaction modes of those components within contexts (static or dynamic) is also something to consider. For example: suppose a system S composed of components A and B fails. A is working fine, and so might B. But beyond consider that S = A + B, there is an environment E, the host of relevant configuration and other elements, within which A and B might not be able to operate together correctly. This means that the components are fine, but the configuration of the environment causes only their interaction with each other to be faulty. We might also need to check whether the environment has hidden interfering elements, X, Y, and Z that has components in related environments.

As I said before, the search space is huge with SharePoint.  I went into the abstract here as this applies to any system, but I’m trying to illustrate the point of why SharePoint is tricky to master, and why is it hard to diagnose it and see through it with clarity and confidence, at least for the non-masters who have been SharePoint-ing since more than a decade.

The thoughts here are provoked because of what happened to me a few days ago while debugging a SharePoint application page.


I will blog more about that in another post, but what I faced was an interesting issue with one of my custom SharePoint application pages, wherein I was getting a Not Found (HTTP 404) error in the browser when requesting an application page that surely existed. At first I thought it was some deployment issue as perhaps one of the test deployments remained in an inconsistent state for some failure yet to be diagnosed. Or maybe my code was causing some underlying API to throw an exception not accounted for which was not catchable for some reason by a try-catch. I went on searching for a solution, traversing SharePoint’s complicated possible-solution-tree. The reason that I found surprised me. I will write more about that in a subsequent post.

Thursday, April 9, 2015

Automating Application Backup Task without an API using Win APIs (User32.DLL)

How can a programmer be happy working with manual tasks? The obvious answer is 'by automating them.' I always like to remind myself that I should delegate to the machine that which it is supposed to do: automatable, computable, or mechanical tasks.

One difficulty we face with iScala is the lack of programmatic APIs to deal with things from Administration such as User Permissions and menu items, to Functional such as receiving a purchase order (GRN). We get around these by accessing the database directly, circumventing the application altogether as we have no choice when it comes to our need to integrate with other applications and processes (think of a new modern application that needs to communicate/affect/manipulate/get a respond from a legacy application for example).

For some situations, we are able to use the database because we have access to it, we understand it at least to some extent, we have documents describing it, and we have a test environment on which we can change things in the UI and see how they reflect on the database. We have to do this, despite it being a malpractice, because we don't have proper APIs. This topic reminds me of an architectural principle called Service Oriented Architecture (SOA), which is almost always on my mind when thinking of systems.

I admit I was not very excited with my latest task of backing up iScala modules through the application. There is nothing in the SQL Server database that I can invoke to have that done (iScala application backup is dissimilar from SQL Server backup which can be automated using SQL Server Management Objects (SSMO)). At first, I felt that there is no hope to automate this task, despite my strong feelings that the task should be automated as it is clear, repetitive, and surely specifiable algorithmically for a computer to be able to do it. I was upset because automation of this kind of tasks is an integral part of my professional philosophy and perspectives, and I felt I will only be able to do this task by mundanely clicking my way through.

That's when my semi-random experiments during college came into play: I recalled that one can programmatically move the mouse cursor, simulate clicks and keyboard presses using the native Windows APIs. So I wrote an application that was able to find the iScala application window and the buttons inside of it, move the mouse to them and click them in the sequence required to create backups. Once the backup is complete, the application would log-off iScala and change the company and logon again to repeat the process of backup. All I had to do last night was to check whether it was running as expected every once in a while.

It wasn't easy and required a lot of experimentation but it was fun and well worth it because this unpleasant task will always be a constant reminder that I can make a choice: approach something negatively and do it the "normal" way, or take risk, invest time and effort, and try to do it in an innovative time saving way.

This philosophy reminds one that software is not merely a job, but a beautiful tool that empowers the person wielding it.


Although nowadays having a programmatic interface to the software is as important as having a user interface to it. Imagine a program with no user interface that allows users to perform the functions of the program? Now, the same rhetorical question applies to application programming interfaces: imagine a program with no API that allows programmers to access the functions of the programs, programmatically.

Sunday, April 5, 2015

SharePoint list fields: getting the internal name and setting its value

Just a quick interesting remark on SharePoint CSOM.

I was trying to batch upload a bunch of records in a list that contains an "Email" field. My code consisted of a loop and the creation of items using ListItemCreationInformation and setting the fields on the added item like this:

item.ParseAndSetFieldValue("Email", email);

//other fields omitted.

The operation fails with the following exception message

Column 'Email' does not exist. It may have been deleted by another user.  /sites/MySite123/Lists/MyList123

So I used CSOM itself to explore the issue further by getting some information on the field. Then checking the internal name using 

var internalName = list.Fields.GetByTitle("Email").LoadExec().InternalName;

I got the unexpected value of "_x0065_zz0" but I was not surprised because there is a lot of "anomalies" to deal with when using SharePoint. I am still not sure why the internal name is like this. I have a feeling that the user who created the columns created strange names for them, and then renamed the fields. Renaming a SharePoint field does not change its Internal Name that you use with ParseAndSetFieldValue and other CSOM interfaces.

To find the internal name, I use CSOM to get a field from a field collection by using its title as follows:

list.Fields.GetByTitle("Mobile No.").LoadExec().InternalName

My field name is "Mobile No." so this returns "Mobile_x0020_No_x002e_". The internal names do not contain special characters such as the space, the comma, or the period so they are hex encoded.

Thursday, January 15, 2015

iScala ScaSystemDB

This article discusses iScala ERP 2.3. I am not an iScala specialist but worked on some internals for a while.

There are a lot of things to talk about in iScala; it is an ERP and thus naturally sophisticated. From the business perspective the specialized “functional” modules (e.g. accounting, logistics and others) are what are important but as a software developer I will focus in my blog on technical topics. This post will be an overview and serve as an introduction to other future posts.

I explain some general background information about the nature of the tables and databases representing iScala companies and will also maintain a list of other posts that I write about the subject here.

One can easily explore iScala’s technical internals by using SQL Server Management Studio (SSMS). My assumption here is that the iScala implementation is using SQL Server and not some other DBMS. SQL Server Profiler tracing feature can also help greatly if you want to see how the various iScala screens work and which tables they affect.

There are two kinds of databases within iScala ERP:
  1. Administration database: ScaSystemDB
    • There is only one ScaSystemDB per iScala server.
    • Information about iScala companies and settings, such as company database and server and authentication information, are also stored in ScaSystemDB.
    • This is also where user information and their configuration, such as permissions, are stored.
    • The iScala Administration Console works with ScaSystemDB and the configuration accessed there is stored in ScaSystemDB.
  2. Business databases
    • From the iScala Administration Console, right clicking on the “Companies” node and choosing “New Company…” opens the “Add Company” dialog. There you choose the database name and server and the unique 2-character Company Code. The Company Code is used in table names as I explain below.
    • There is a database for each iScala company and these databases contain the various tables for business information such as finance and logistics. Some of these are year dependent: they change every year and most commonly a new fresh table is created when a new year is set up in iScala. I discuss these in the next section “Business Tables”.
    • Each company database also contains user defined tables that could be made company and year specific.

Business Tables

Business tables are those that store business information such as stock info, logistics, accounting, and other functional information. To a newcomer, table names might be a little confusing as they are not descriptive (for example the purchase orders table is not called PurchaseOrders) but rather a combination of codes. Once you have the naming convention in mind, understanding what a table is for will be much easier.

A table name is 8 characters long and consists of the following:

MODULE + SERIAL + COMPANY + YEAR

To illustrate, “PC” is used to refer to the purchase order module. Let’s say my ‘Flying Rabbits’ company was assigned code FR and the year is 2015. The serial number is just an incrementing number from 01 to 99. The module code and serial number identify the purpose of the table and I call them the “table identifier” but it does not even indicate company or year. The Purchase Order Head table will have the name PC01FR00. The year is 00 because PC is not year dependent. Knowing that this table is not year dependent is important especially if the year in question is 2000 or 2100. 00 here does not indicate those years! An example of a year dependent table would be the General Ledger Transactions table GL06.

Each of these elements is two digits and so most table names are 8 characters. You can use a dictionary that describes the various module tables, their codes and even description of the columns. You can find such a dictionary here.

Table column names are named in a serial nonsensical manner such as PC01001, PC01002, PC01003, etc. The column name is prefixed with the table identifier (the first 4 characters, i.e. Module + Serial). Many tables have their primary key or primary identifying piece of information as the first column. For example a purchase order number is stored in the first column in purchase order related tables (i.e., PC01001 . Column PC01001, PC03001 and PC04001 in all of the tables PC01, PC03 and PC04 represent the order number and the primary key respectively.

Table Identifier = Module + Serial
Column Name = Table Identifier + Column Serial

As for understanding the columns, there is no practical way to know the purpose of the PC0023 column without a dictionary such as the one mentioned above.

Understanding all this helps a lot of you are looking into the database directly. Understanding this is vital for the effectiveness of writing procedures that read or write data from the database. For example it helps one realize the suitability of using dynamic SQL queries when interacting with company tables because you can make the company name and year a parameter and then construct the desired table name dynamically.

In this post I talked about iScala from a partial SQL Server perspective. I just talked about the databases, tables, and columns and only mentioned stored procedures as a possibility. In future posts I’ll elaborate more about how you can write stored procedures that interact with the iScala database both from an administrative and functional points of view.