How to mimic Pivot Table or Categories in Number 3+

Perhaps you are already aware that Apple has cut many features from latest versions of Numbers and their other MS Office like products. One of the most popular features that has been removed from Numbers is called Categories. By activating Categories on a table you could aggregate data and summarize values see an overview of what matters to you. Recently I was working on a list of values and since the list was not bigger than what Numbers can handle and I didn’t need the speed and power of Microsoft Excel. I though I will give Numbers a try. I still like the fact that you have a free canvas in Numbers that allows you to put put many tables independent from each other in one page. I think it is the only advantage of Numbers to its alternatives. Otherwise with just a few dollars per months you could have access to the latest version of Microsoft Office regardless of your OS or even directly in your browser plus Microsoft gives you a terabyte of online storage! OK, let’s get back to work before I change your mind 😉

Let’s start with an example like the following.

A B
1 Category Amount
2 Blue 11.20
3 Red 15.89
4 Red 10.30
5 Orange 32.12
6 Green 15.39
7 Blue 10.18
8 Green 24.76
9 Green 89.31
10 Orange 8.75
11 Blue 15.28

The first column is just containing the order of each row, the second indicates the category of each row. This could be anything like category of expenses in an expense report. It is actually the column that we are going to aggregate. The third column contains the values. It could be the amount of each expense for example.

Now, let’s say you want to aggregate it to the following table. But you want Numbers to do it for you so each time a series of row is added to the table you don’t need to calculate everything manually.

Category Sum
Blue 36.66
Red 26.19
Orange 40.87
Green 129.46

Since I want to do the aggregation in the same table, I need to find a way to detect distinct categories. Then per each category I need to calculate and display the sum of each category in front of it. If I display only the first occurrence of each category and hide other occurrences the problem would be solved. Let’s split the problem to smaller pieces as usual. Now the first question would be how do we find distinct categories?

Step 1 – Detecting distinct categories

Let’s add a new column called “Test” then put the following formula in the first cell of the column below the header: =COUNTIF(A$1:A1,A2) in other words you need to put it in C2 cell. Now click at the bottom of the cell’s rectangle and drag it to the bottom of the table. This will fill remaining cells of the column. The result will be the following table.

A B C
1 Category Amount Test
2 Blue 11.20 0
3 Red 15.89 0
4 Red 10.30 1
5 Orange 32.12 0
6 Green 15.39 0
7 Blue 10.18 1
8 Green 24.76 1
9 Green 89.31 2
10 Orange 8.75 1
11 Blue 15.28 2

As you can see, you only get zeros for the first occurrence of each category and that means we can detect them with an IF. Now, let’s put the following formula in cell C2 instead: =IF(COUNTIF(A$1:A1,A2)=0,"*","") and as before drag the corner of the cell until the bottom of the table which will give us the following result.

A B C
1 Category Amount Test
2 Blue 11.20 *
3 Red 15.89 *
4 Red 10.30
5 Orange 32.12 *
6 Green 15.39 *
7 Blue 10.18
8 Green 24.76
9 Green 89.31
10 Orange 8.75
11 Blue 15.28

Now for first occurrence of each category we are displaying “*” in column C.It means that we are able to detect the first occurrence of each category and display an arbitrary text in it. What about displaying SUM of each category there. This would be the final solution.

The final solution

In this step we are going to replace the “*” in the formula with SUMIF(Category,C2,Amount) this way the SUM of each category will be calculate in front of each occurrence of that category. All you have to do is to put the following formula in C2, then drag the bottom-right corner of the cell until the bottom of the column.
=IF(COUNTIF(C$1:C1,C2)=0,SUMIF(Category,A2,Amount),””)
The resulting table will be similar to the following. I just renamed the Test column to “Total per category”. You can use formatting to distinguish the aggregate row or even hide other rows if you like.

A B C
1 Category Amount Total per category
2 Blue 11.20 36.66
3 Red 15.89 26.19
4 Red 10.30
5 Orange 32.12 40.87
6 Green 15.39 129.46
7 Blue 10.18
8 Green 24.76
9 Green 89.31
10 Orange 8.75
11 Blue 15.28
How to mimic Pivot Table or Categories in Number 3+

How to warm-up SharePoint or other web applications and WCF (SOAP) services with PowerShell

There are many reasons you might want to warm-up a web application occasionally. It can be after a fresh deployment or on a regular basis after recycling application pools. Some times you might also need to warm-up SOAP services without going through front-end.

It might seems to be any easy task specially if you have PowerShell 3.0 or higher on your servers, but after Googling a while and reviewing some of the top hits I discovered that each solution is missing a part. Some only work in a single server scenario and some has forgotten that each HTTP response might contain links to scripts and images that we need to download and finally I could not find anything for SOAP services that just works. Long story short I decided to put together a simple script that just works and is easy to change to everyone’s needs.

Please note that my only assumption is you have PowerShell 3.0+ in your servers.

Currently the script takes care of the following tasks, but I will most likely improve it to cover other useful scenarios.

  • Calling SOAP operations and sending parameters and custom headers
  • Calling front-end URIs and downloading scripts and images that are local to the front-end
  • Logging to a configurable folder and file name
  • Cleaning up old log files

Currently, I have the following points in mind to improve the script.

  • Put configuration in a different file.
  • Improve function definitions (named and typed parameters and description).
  • Default values for parameters when it makes sense (e.g. log folder can be the current folder).
  • Support REST services.

I’m open to any suggestion and feature request. Please let me know if you found it useful or if have got something wrong.

How to warm-up SharePoint or other web applications and WCF (SOAP) services with PowerShell

How to create search query content source in SharePoint using PowerShell

They are called Content Sources in the graphical user interface of SharePoint and Search Query Content Source when using PowerShell. A content source is basically pointing to a source of data that can be indexed in the search database. Because the data is indexed, when later users run queries the result is returned almost instantaneous. It is very easy to add a content source by using SharePoint Central Administration, but you might need to create a long list of content sources in a batch for example as part of a disaster recovery process or many other reasons. Here is one way I do it in a PowerShell script.

$contentSourceConfig = @(
    @{
        Name = "Articles"
        StartAddresses = "http://mydomain1/site1/fr,http://mydomain1/site1/nl,http://mydomain1/site1/en"
    },
    @{
        Name = "Technical"
        StartAddresses = "http://mydomain1/site2/fr,http://mydomain1/site2/nl,http://mydomain1/site2/en"
    }
)

foreach($config in $contentSourceConfig) {
    $contentSourceName = $config.Name
    $startAddresses = $config.StartAddresses
    $contentSource = Get-SPEnterpriseSearchCrawlContentSource $contentSourceName -SearchApplication $ssa -ErrorAction SilentlyContinue
    if ($contentSource) { $contentSource.Delete() }
    $contentSource = New-SPEnterpriseSearchCrawlContentSource $contentSourceName -Type "Web" -StartAddresses $startAddresses -SearchApplication $ssa
    Write-Host "Search crawl content source: $contentSourceName created successfully."
}

As you see in the above example I have put all the required configuration in one array of custom objects. If you need to add more content sources you may simply copy-past one and change the properties. This way the configuration is separated and easier to read or change while still it is in the same file with the script so you won’t have to rely on extra XML file(s) to put beside the script.

If you are more comfortable by separating the configuration from the script. You can cut the first part and past it to another script file or use the XML approach.

For more details about the cmdlets I’ve used I suggest you have a look on the following MSDN articles:

How to create search query content source in SharePoint using PowerShell

How to read optional attributes in XML with default values

When we are reading data from XML data sources, it is very common that when a value is not given for an attribute or an element, we have to use a default value instead. Having to check for the existence of the node each and every time we are reading a value can be annoying and clutter the code. To address this issue I have made a few useful extension method that you can use copy and use in your code. These are written on top of XAttribute class, but you can write similar methods for other types and APIs pretty much the same way.

public static class XmlExtentions
{
    public static string ValueOrDefault(this XAttribute attr)
    {
        return ValueOrDefault(attr, string.Empty);
    }
    public static string ValueOrDefault(this XAttribute attr, string defaultValue)
    {
        return attr == null ? defaultValue : attr.Value;
    }

    public static T ValueOrDefault<T>(this XAttribute attr, T defaultValue) where T : struct
    {
        if (attr == null) { return defaultValue; }
        var value = attr.Value;
        var convertedValue = typeof(T).IsEnum ? Enum.Parse(typeof(T), value) : Convert.ChangeType(value, typeof(T));
        if (convertedValue == null) { return defaultValue; }
        return (T) convertedValue;
    }

    public static T ValueOrDefault<T>(this XAttribute attr, Func<T> defaultDelegate) where T : struct
    {
        if (attr == null) { return defaultDelegate(); }
        var convertedValue = Convert.ChangeType(attr.Value, typeof(T));
        if (convertedValue == null) { return defaultDelegate(); }
        return (T)convertedValue;
    }
}

Once you get it in place. It is pretty easy to use. Assume that we have the following XML document:

<Configuration>
    <Search Enabled="True" RankingAlghorithm="BS77" LowMark="1000" HighMark="2000">
        <Source Name="Orders" Type="WebService" />
        <Source Name="Products" Type="REST" />
    </Search>
<Configuration>

As is shown below, you can read any type of values exactly the same way.

// reading a bool
var isEnabled = sourceNode.Attribute("Enabled").ValueOrDefault(false);

// reading a string
var scopeName = sourceNode.Attribute("Name").ValueOrDefault("Default");

// reading a string and using empty string (string.Empty) as default value
var scopeName = sourceNode.Attribute("Name").ValueOrDefault();

// reading an enum
var sourceType = sourceNode.Attribute("Type").ValueOrDefault(SearchSources.Html);

// reading an integer
var lowMark = sourceNode.Attribute("LowMark").ValueOrDefault(100);

// Using a delegate to provide the default value
var highMark = sourceNode.Attribute("HighMark").ValueOrDefault(()=> App.GetUserSetting("Search.HighMark"));
How to read optional attributes in XML with default values

Disabling Security warning for Attach to process in Visual Studio 2010, 2013 and 2015

You probably have already suffered from the pain of having to click one more time when attaching visual studio’s debugger to a process. As developers we all have the obsession to be more productive and everything that comes in the way is a bummer. I should have shared this little secret before, but … I forgot.

You know this message box, right?

attach-security-warning1

Now when a colleague sent me a link to a blog post that explains how to change a registry key to disable the security warning in Visual Studio when attaching to processes, I decided to write a little “.reg” file to make it even easier for you. This warning is there to warn you when you are attaching the debugger to a process and that process is (that are running with different accounts than the one running Visual Studio)  The reg file works for Visual Studio 2010, 2013 and 2015 no matter which edition. You can view / download it from here:

Disabling Security warning for Attach to process in Visual Studio 2010, 2013 and 2015

Connecting WebParts programmatically in SharePoint

Let’s start by an example.

Example

In this blog post, I’m going to assume we have a webpart that displays a contact list and another one that displays a map with the possibility to pinpoint an address. To keep things simple I assume the address is merely a string.

public class Map : WebPart
{

}

public ContactList : WebPart
{
    [Personalizable]
    string SelectedAddress { get; set; }
}

To connect these webparts, we typically do the following four steps:

  • Write an interface to contain the information we need to transfer (i.e. Address)
  • Implement the interface in the provider webpart (i.e Map)
  • Write a callback method in the provider webpart to return the address.
  • Write another callback method in the consumer webpart to receive the address.
public interface IAddressConnection {
    string SelectedAddress { get; set; }
}

public class Map : WebPart
{
    [ConnectionConsumer(&quot;Pinpoint address&quot;, &quot;Address&quot;)]
    public void ConsumeAddress(ISelectedAddress address)
    {
        // Set the map extent and display a pinpoint.
    }
}

public ContactList : WebPart, IAddressConnection
{
    [Personalizable]
    public string SelectedAddress { get; set; }

    [ConnectionProvider(&quot;Selected address&quot;, &quot;Address&quot;)]
    public ISelectedAddress ProviderAddress()
    {
        return this;
    }
}

ConnectWebParts()

In order to connect two webparts, you need an instance of each webpart plus the WebPartManager (to be precise the SPLimitedWebPartManager) for the page they are hosted in and of course you need to know which of their connection points you are going to connect.

Let’s say you already have the WebPartManager and the two webparts at hand. In that case you can use the following method. Note that T is the interface these two webparts share.

public void ConnectWebParts(WebPartManager webPartManager, WebPart consumer, WebPart provider)
{
    var consumerConnection =
        webPartManager.GetConsumerConnectionPoints(consumer)
            .Cast&lt;ConsumerConnectionPoint&gt;()
            .FirstOrDefault(c =&gt; c.InterfaceType == typeof(T));
    var providerConnection =
        webPartManager.GetProviderConnectionPoints(provider)
            .Cast&lt;ProviderConnectionPoint&gt;()
            .FirstOrDefault(c =&gt; c.InterfaceType == typeof(T));
    webPartManager.ConnectWebParts(provider, providerConnection, consumer, consumerConnection);
}

The above method assumes that these webpart only have one connection point of type T, which makes sense most of the time, but in case your webpart have multiple connection points of the same interface.

By using ConnectWebParts method, we can easily connect the Map to the Contacts webpart.

ConnectWebParts&lt;IAddressConnection&gt;(webPartManager, map, contacts)

But, how to get the WebPartManager and instances of the webparts? you might ask. These webparts might be used in any page, but you definitely know on which page you are going to connect them. By having a reference to the page you can instantiate a WebPartManager and with that you can find you webparts too. If you are doing this in the code-behind of the page or a control (or webpart) in the page, you can do as following:

var webPartManager = WebPartManager.GetCurrentWebPartManager(this) // or this.Page if we are in a control.
var consumer =
    webPartManager.WebParts.Cast&lt;WebPart&gt;()
        .FirstOrDefault(w =&gt; w.GetType() == typeof(Map));
var provider =
    webPartManager.WebParts.Cast&lt;WebPart&gt;()
        .FirstOrDefault(w =&gt; w.GetType() == typeof(Contacts));
ConnectWebParts&lt;IAddressConnection&gt;(webPartManager, consumer, provider)

What if the code is not running in the page? Then you need to get an instance of the page. Let’s use SPLimitedWebPartManager this time.

using (var site = new SPSite(&quot;http://mydomain/mysite&quot;))
{
    var file = site.RootWeb.GetFile(&quot;myfolder/mypage.aspx&quot;);
    var webPartManager = file.GetLimitedWebPartManager(PersonalizationScope.Shared);
    var consumer =
        webPartManager.WebParts.Cast&lt;WebPart&gt;()
            .FirstOrDefault(w =&gt; w.GetType() == typeof(Map));
    var provider =
        webPartManager.WebParts.Cast&lt;WebPart&gt;()
            .FirstOrDefault(w =&gt; w.GetType() == typeof(Contacts));
    ConnectWebParts&lt;IAddressConnection&gt;(webPartManager, consumer, provider)
}

Please note that you might need to checkout the page, publish and approve it if versioning or approval workflow has been enabled on the library that contains the page.

I hope you find it useful. By the way, don’t forget the null checks!

Connecting WebParts programmatically in SharePoint

How to install/update Atom, Sublime, VSCode and Brackets packages behind corporate proxy (NTLM)

Many developers these days are using these fancy new cross-platform text editors that are lightweight and fast to reduce the workflow of writing and testing code. I’m mostly talking about web developers here. If you are new in this world I suggest you give the followings a shot as after days of researching and trying I think currently they are the bests:

Now back to the subject. Many of these text editors support proxy servers, but none of them that I have tried support NTLM for authentication and if your corporate proxy is using NTLM they simply cannot connect to it. Or lets say not directly! Actually the only solution I have found is to use a software called CNTLM that can act as a proxy server itself and behind the scene it can connect to another proxy server that is using NTLM. The good news is this software is open-source, very lightweight and independent of any external library (written in C) and can take care of all the complexity of NTLM. In fact You can easily setup CNTLM to allow any application that does not support NTLM authentication to connect to the internet. Atom is based on NodeJS (most of them are) so you’ll be able to apply what you will learn hear to other similar text editors as well.

Configure CNTLM to use your corporate proxy

  1. CNTLM is an opensource software and you need to download its latest version from SourceForge website here.
  2. Now you need to install it by running the installation package. There is not much to worry about. it just adds a few shortcuts to your start menu and copies the file under “Program Files (x86)“.
  3. All the settings of CNTLM are stored in Program Files (x86)\Cntlm\cntlm.ini, and you can open this file from Start Menu > Cntlm > cntlm.ini
  4. The cntlm.ini file is quite self descriptive. You just need to add the following lines in this file:
Username    your-username
Domain      your-domain-name
Password    your-password-or-leave-empty
Proxy       proxy-ip-or-hostname:port
Proxy       another-proxy-ip-or-hostname:port
Listen      3128

you can add as many Proxy lines as you want, Cntlm will try them one by one until it can find one that works.

Please note that the address of your proxy will be "localhost:3128" by default but you can change it to any desired port that is not currently being used by another program. You just need to change the value in front of Listen setting. I have highlighted the line so you never miss it.

  1. Star the proxy server by  heading to Start Menu > Cntlm > “Start Cntlm Authentication Proxy”
  2. Test your shiny new proxy by using your favorite browser.

Configure Atom to use Cntlm as proxy server

  1. Start Atom by heading to Start Menu > All Programs > GitHub, Inc > Atom
  2. Go to File > Settings or press Ctrl+Comma. Click on “Open Config Folder” button in the left pane of the Settings tab. It will open and show all the content of the setting folder in Atom.
  3. Click and open .apmrc file located under the root setting folder (.atom folder) and add the following lines:
http-proxy=http://localhost:port-number-to-be-used-by-Cntlm
https-proxy=http://localhost:port-number-to-be-used-by-Cntlm
strict-ssl=false
registry=http://registry.npmjs.org/

The last line (that I have highlighted) tells npm to use the http address instead of the default https address. This is because I noticed that Cntl might have some problems with https addresses. This issue might be solved later and you might not need it in the future.

  1. Restart Atom and enjoy using your favorite packages.

A little bonus

By running the following command you can tell many other programs to use your new little proxy server by default. Unfortunately not every program supports it.

netsh winhttp set proxy localhost:3128 "localhost,127.0.0.*"

How to install/update Atom, Sublime, VSCode and Brackets packages behind corporate proxy (NTLM)