Find an item in SharePoint

I got a request from user “Can you help me find number series owned by ‘AFS’, it seems I cannot search myself”. Sure this easy - SharePoint to the rescue:

Easy peasy I thought, but Oh no the dreaded 5000 limit again:

I tried the search window, but no SharePoint is relentless:

No! Search cannot be done in SharePoint. Why am I not surprised?
Ok, Excel to the rescue:
Export the SharePoint list to Excel. First I get a friendly reminder:
Yes MS Excel is compatible.
Then I get another friendly security notification:
This I’m not sure what it is about but let’s go crazy, Rock’n’Roll I press ‘Enable’.

And voila here is Excel with its working search function, working in no time on super large datasets (5000 items and even larger than that):

And the result:  

Can it be simpler? Thank you SharePoint for superior search functionality😀


Expiration date

This night my telephone rang the alarm bell to tell me my mail password had expired. To my surprise it just did it once, I expected it to wake me up every hour or so.
This morning when I switched on my computer I was greeted by ‘your outlook password has expired, consider changing…’.
‘Great’ I though, Sunday morning at home changing Windows/Outlook/AD password have never worked for me before. I always end up calling the support desk and hear them say ‘this look odd’ and then they reset all that can be reset for my account and then it works for another password period. That procedure however requires I’m at the office work hours.  
‘Great’ I though, ‘Sunday morning and I cannot log in to the company network’, I had planned to work the whole day. Anyway I had to try the password change, lo and behold, I just ctrl+alt+del ‘change password’ and it worked like a charm, the same thing with my mobile, just changed the password and the mail started to replicate. I was stunned! First time ever the password procedure worked for me. Now I only have to sit back and wait for a number of ‘job failed’ mails. Many backend jobs need access to network resources like email, databases etc. In theory you should create special userids+fixed passwords or ‘bypass procedures’ for such jobs, but this often takes effort and time you do not have and you definitely do not want to wait for this while you are developing a new backend process. I use my own userid. I’m sure many of you do the same. After a while you forget all about it, which means ‘failed job’ mails turns up somewhere and you say ‘f-k I forgot about this, I need to fix this properly’, then you update the password and forget about it until next time you password expires.

Software expiration timing.

Many years from now, 04:00 in an Italian hotel on our first honeymoon night, the night porter was banging on the door, telling me he had an important message for me. It was from the company, the company computer had stopped, it refused to do anything from midnight. After an hour discussing with the operator back home I realized the RACF compatible security system ‘Top Secret’ had silently expired three months in advance. We had to wait until the ‘Top Secret’ guys woke up, they promptly sent a patch and promised to remove all hard stops out of the software, later I was told all ‘Top Secret’ installations had stopped the same night.
2009-01-01 05:00 coming home after a serious new year's party, almost still with the funny hat on I turned on the computer just to check everything was OK. Of course it wasn’t, (the VPN software had expired 2009-01-01 00:00). I ordered a more nearby colleague to take a taxi to the office to check what the problem was, he reported all was OK, it took me a while to find out it was ‘only’ the VPN that had expired. Together with IT-support I had it all fixed by the afternoon and could go to sleep.

Software should ideally only expire the second or third  ‘work’ Wednesday in the month at 11:00 nonsensitive months, at the company that is February, April, May, October, November. There is as far as I know no software that have an expiration mechanism that can follow my recommendation. I try to go for the 15th in nonsensitive months, December and January is a nono! Interestingly the old Romans did not have month names for this period of the year, they only had ten months, I bet they foresaw the evil of having software expire that time of the year.


The online administration

The other day I posted a comment on a proposed tax in Sweden to increase the defense budget with some six billion €. The proposal made by a professor of economics Lars Calmfors was sound and backed up with valid economic arguments.
This made me thinking even though political economy is an empirical science, it has come a far way from witchcraft. Why not hand over the country's economics to scholars?  But then again there is a political in “political economics”, maybe it’s best to let the politicians have a say. This again made me thinking.
The national budget in Sweden as in most countries have been managed the same way for ages, every year there is a new annual budget with a few additions.But we should be able to better than that. Today we should be able to gather and analyse data of importance in real time, with the right BI tools for data intensive applications we should be able to give the politicians (and scholars) better decision support. Real time monitoring of the country economic key value figures, automatic alarms etc. The online administration of a country, that is able to act immediately when things change,  how cool wouldn’t that be? The smart country; Country vs. 2.  Will the world be a better place? Yes a small tiny little bit better and that is huge.
A final thought, does this already exists? Are country administrations already online?


Interesting quote

I found this quote on Andy Burns' Blog:

“On two occasions I have been asked, ‘Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?’ I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.”

– Charles Babbage

I when given more time will read more of Andy Burns posts.


CSOM PowerShell update SharePoint list from Excel

As you probably know I’m not a great fan of MS SharePoint, I consider it to be a design disaster, where most sound design principles have been sacrificed on the altar of stupid simplification. The dreaded 5000 limit, the silent 2000 limit and the bizarre field naming conventions just to name a few. SP is good for (temporary) simple web sites with small amounts of data, integrating with MS Office suit FULL STOP, PERIOD, END.
For some weeks (actually a bit more) I have had this +1000 line Excel sheet on my desk of necessary changes in a +5000 SP list. The excel sheet did not contain row ID. No one at hand could help me how to apply these changes with standard SP scripting or Nintex workflows. And I sure do not know how to do this with SP or Nintex scripting. My choices were C# or Powershell script using the CSOM library, our SP service provider do not allow me to run SOM scripts on their infrastructure plus the very idea of running transactions on the SP server is daft if you ask me. I decided to go for Powershell scripting just for the hell of it. I do not know Powershell .NET or SP well and I have no documentation of it so I have to rely on Google normally my favourite source of developer visdom. Unfortunately Google is a bit complicated with SP, I connect to SP via CSOM and I do not find that many working CSOM examples on Google, moreover many of the CSOM examples are in fact SOM, it seems many do not recognize there is a difference, I can sympathize with that, it should not be different libraries for connecting SP depending on from where you are calling SP. Microsoft have a great pedagogical task to do, promote CSOM and teach the SP community to use CSOM and depricate SOM a.s.a.p. Two SP libraries are one to many. Anyway after almost a day of Googling, picking bits and pieces from posts, extrapolating and trial and error I had a working script, I had to save my Excel sheet as a CSV file. It took some 40 minutes to run which is ridiculous, but it’s SharePoint so you have to live with it I suppose. Anyway the PowerShell script is quite elegant and it did the job. I like PowerShell scripting, if I only can get my head around the .NET object model I can probably do many things fast and efficient in the Windows environment.

$x = @"
This script updates items in a SharePoint list from a csv file
The csv file does not contain the ID of list items, matching is done on 'key' fields.
1. read the csv file into an iterator
2. iterate through the SharePoint list
3. for each list item, iterate through the csv file
4. if cvs row match update the ShartePoint list with values from the csv row

Note! This type of matching is inefficient, but quite ok for SharePoint lists as there few items in lists
and SharePoint per se is easily 'saturated', this matching gives SharePoint pauses to recover.   

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  

$UserId = "USERID"
$Password    = ConvertTo-SecureString 'PASSWORD' -AsPlainText -Force
$siteURL = 'https://thehub.group.atlascopco.com/metadata/group_codes'
$listItemstname = "RG_PGC_Codes" # SP list
$readItems = 5000                # Items to read from SharePoint list

# csv column names
$csvRG = "Rate Group Code"       #key
$csvPGC = "Product Group Code"   #key
$csvGAC = "GAC"                  #Key
$csvUNIT = "Unit"                #key
$csvVALIDTO = "ValidTo"          #value
# csv file
$delimeter = ";"
$filePath = "C:\rgupdate.csv"
$csvData = Import-Csv $filePath -Delimiter $delimeter # Load the CSV file containing the updates
$credentials = New-Object System.Management.Automation.PSCredential $Username, $Password
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
$ctx.credentials = $credentials  
try {  
   $list = $ctx.web.Lists.GetByTitle($listItemstname)  
   $listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery($readItems))  
       foreach($li in $listItems) {
           if ($li["ValidTo"] -eq $null){    # Filter; only null 'ValidTo' are interesting
               foreach ($line in $csvData){
                   if (($li["Rate_x0020_Group_x0020_Code"].LookupValue -eq $line.$csvRG) -and ($li["Product_x0020_Group_x0020_Code0"] -eq $line.$csvPGC) ){
                        Write-Host "Match"
                        $ID = $li["ID"]
                        write-host $line.$csvUNIT,$line.$csvGAC,$line.$csvPGC,$line.$csvRG,$line.$csvVALIDTO, $ID
Write-Host "ID -" $li["ID"] ", RG -" $li["Rate_x0020_Group_x0020_Code"].LookupValue ", PGC -" $li["Product_x0020_Group_x0020_Code0"] ", GAC -" $li["GAC0"] "Unit -" $li["Unit"].LookupValue " ,validTo -" $li["ValidTo"]
                        $li["ValidTo"] = $line.$csvVALIDTO
# 'Break' will stop update an item after first match, 'Return' stop processing after first match (for testing purposes)
                       # Return
                   }                        # endif Match
               }                            # end foreach csv row
   }                                        # end foreach SP item
} catch {  
   write-host "$($_.Exception.Message)" -foregroundcolor red  


In memory of Hans Rosling

Bill Gates writes it much better than I can do https://www.gatesnotes.com/About-Bill-Gates/Remembering-Hans-Rosling

"Hans Rosling turned global health and poverty data into graphically engaging, focused, insightful, compelling, even exciting stories" (Mike Urbonas)
Hans took Business Intelligence to a global scale. He invented a new type of graphics I had not seen before the GapMinder graphics, with moving bubbles he showed us the world as we didn't know it. Hans was not old only 68, but as as Keith Richards once said "The good guys go first".


Auf Wiedersehen

2012-03-24 I wrote my first post in this blog. I started to write due to inspiration of my colleague Petr Hutar, in one of the first meetings with him he said, “I have lot’s of other interests beside computers, I just forgot what they are”. At that time Petr put words on who I felt I was, so I stole with pride and made the quote mine.
Now Petr is leaving the company to pursue a somewhat different IT career going from administrative IT systems and IT architecture to robotics and factory automation. As Petr is an extremely creative person with a technical degree in robotics, I’m sure we will see great things from Petr in future.
You may call Petr an IT-nerd, but more than that he is a well respected eloquent gentleman that can see and present the big picture, not only the miniscule details of program algorithms. My best wishes Petr, I hope we have a beer together in the not too distant future.