Going bonkers with SharePoint ODATA

This is the SharePoint Odata interface I created to extract the currency rates of the month, a join of two SharePoint lists:

https://company.com/metadata/currcodes/_api/Web/Lists/GetByTitle('MonthlyCurrencyRates')/Items/?$select=Currency_x0020_Code/currencyName,Currency_x0020_Code/Title,Currency_x0020_Code/currencyNumCode,Currency_x0020_Code/currencyAC_Reporting,Currency_x0020_CodeId,Year,Month,Value%5Fx0020%5FEnd%5Fx0020%5Fof%5Fx0020%5FP,Average%5Fx0020%5FValue%5Fx0020%5FEnd%5Fx0,Value,Average%5Fx0020%5FValue&$expand=Currency_x0020_Code&$filter=Year eq '2016' and Month eq 7

Almost self explanatory, don’t you agree? It took me a weekend to pull this together, but maybe I’m a bit daft. Anyway I started selecting the entire list:

https://company.com/metadata/currcodes/_api/Web/Lists/GetByTitle('MonthlyCurrencyRates')/Items

 

I got a shitload of XML in response, but it was a good start, simple request, complex response. No worries we just limit the response with odata=nometadata in the accept header later on. I added a $filter on year and month column, (for reason I do not know this was not defined as a datetime value), anyway the filter was easy to create:

$filter=Year eq '2016' and Month eq 7

It worked after just a few tries. Wow SharePoint ODATA is easy! Let’s limit the columns, I started by:

$select=Year,Month

Yike! It worked too, happy smile on my face. Now I wanted to join my Currency rates list with the Currency List, and all of a sudden hell broke loose. After a lot of Googling around I realized joining lists in SharePoint is both limited and complex. I understood you need to specify join as $expand using a lookup field in the list. After searching for a while I found the field ‘Currency Code’ linking to the Currency list. As $expand seemed a bit complex I tried to display the ‘Currency Code’, and nothing fucking worked, whatever I tried I just got error in return. I assumed it was the ‘blank’ in ‘Currency Code’ that was the root to my problems. After hours of testing and googling I found there is an internal name that you can find by looking at the last part at the URI at the SharePoint change column transaction:

The picture is a bit small, but the important item here is the Internal name at the upper right

For ‘Currency Code’ it was ‘Currency%5Fx0020%5FCode’, silly me who couldn’t figure out blanks are replaced by  ‘_x0020_’, that is the most natural thing to do. Anyway my query didn’t improve a bit by this hard earned knowledge. Whatever I tried no darned ‘Currency%5Fx0020%5FCode’ showed up in the response.

I decided to sleep on this. Next morning after a healthy breakfast I pondered maybe you cannot display the lookup field, you have to do this $expand thing. After a lot of googling and some testing I got the currency name from the currency table by :

$select=Currency_x0020_Code/currencyName,Year,Month&$expand=Currency_x0020_Code

Finally I could create the SharePoint ODATA interface. This is a Powershell script snippet I use to define the interface:

$usel = '$select=Currency_x0020_Code/currencyName,Currency_x0020_Code/Title,Currency_x0020_Code/currencyNumCode,Currency_x0020_Code/currencyAC_Reporting,Currency_x0020_CodeId,Currency_x0020_Code/Title,Currency_x0020_Code/currencyNumCode,Currency_x0020_Code/currencyAC_Reporting,Currency_x0020_CodeId,Year,Month,Value%5Fx0020%5FEnd%5Fx0020%5Fof%5Fx0020%5FP,Average%5Fx0020%5FValue%5Fx0020%5FEnd%5Fx0,Value,Average%5Fx0020%5FValue'; $uexp = '$expand=Currency_x0020_Code'; $uri = "https://thecompany.com/metadata/currcodes/_api/Web/Lists/GetByTitle('MonthlyCurrencyRates')/Items?" + "$usel&$uexp" + '&$filter=' + "Year eq '" + $yr + "' and Month eq " + $mo; $res = $wc.downloadString("$uri");

I still do not know how to continue a PS script statement on the next line so  you have to live with the horrible formatting. If you study the ODATA interface at the top of the post carefully, you’ll see the column Currency_x0020_CodeId. After I created the interface I stumbled upon a post that explained if you want to display a lookup column you must add ‘id’ as suffix, silly me again such a lack of imagination not to figure that out, this small problem caused me to do hundreds futile tests.

To be a bit serious, if you are still reading, take another look at the interface at the top. You can argue the column names in our lists were not wisely chosen, but any good software should prevent or warn users from choosing names that will lead to confusion. This is a classic a design miss, hiding complexities to achieve a user friendly system, this almost always fails. In this case the internal column name is to important to hide away.

Now I only have to take care of the output. Remember I wrote I would limit the output by specifying odata=nometadata in the accept header. It turn out this does not work for XML, and I cannot make it work for JSON either. But that is for another day and another post. I need some mental recreation after this interface writing.