Visual Studio debugging and the Avast pop-up


Whenever I do programming on my own laptop, because I have Avast installed, every time I try to debug a program I get the above pop-up appear in the bottom right of my screen. The debugger doesn’t attach, and the program attempts to run for up to 15 seconds, before Avast decides it’s actually OK to run, cancels the program, and then restarts it attaching the debugger. This is very annoying.

How to fix

Open Avast and click on Settings found at the bottom of the left menu.

In the settings menu, under General, there is an Exclusions section.

Click browse and navigate to the folder where you store your Visual Studio Projects and select the tiny tick box next to the folder. Then click OK.


Now click OK on the Avast Settings.

When you debug your Visual Studio projects now, you will not get that pop-up that is scanning the .exe file, and the debugger will attach without any issues.

Power BI using Parameters to use the same PBIX file for different tenants


In the past I, have created a Power BI file connecting it to the developer tenant data, design the reports based on the dummy data in my development tenant, before publishing it to the production tenant using the production data. For those that have done this before, you will know it’s not easy because all your data queries are pointing to the URL you first used, every query you have created will have this URL in, also GUIDs for lists filters etc could all be different in another tenant.

I will be continuing from my last example of a movie database from my previous post. I will be updating the queries so that in future, there is one location to change my parameters. My previous post has 4 queries which was my main Movies list from SharePoint, the TaxonomyHiddenList from SharePoint, and two references of the TaxonomyHiddenList renamed and filtered for Genre and Country.

First I need to think what might be different in the other tenant compared to my original one.

  • Tenant Name
  • Site/Web URL
  • Genre TermSet Id
  • Country TermSet Id

I need to create 4 paramters and name them to correspond with possible changing values. To do this, I need to ensure I’m in the Edit Queries. From your report, on the ribbon click Edit Queries. This will bring up the Edit Queries editor.

Underneath your current queries, right click and from the context menu, select New Group. Name the group Parameters. Your original queries will be places in a group folder called Other Queries.

Right click the Parameters folder, and from the context menu, select New Parameter…

Add 4 new parameters:

  • Tenant:
    • Name: Tenant
    • Required: True
    • Type: Text
    • Suggested Values: Any
    • Current Value: mytenant.sharepoint.com
  • WebSiteUrl:
    • Name: WebSiteUrl
    • Required: True
    • Type: Text
    • Suggested Values: Any
    • Current Value: /sites/taxonomy
  • GenreTermSetId:
    • Name: GenreTermSetId
    • Required:True
    • Type: Text
    • Suggested Values: Any
    • Current Value: f46ebfeb-cc18-479f-bac8-48fdca36dd6c
  • CountryTermSetId:
    • Name: CountryTermSetId
    • Required: True
    • Type: Text
    • Suggested Values: Any
    • Current Value: 6e0c3d2e-5514-4744-a08d-7318fd437a45

Click OK.

You should now have 4 parameters showing in your Query window. Now we need to use these.

Starting with the CountryTermSetId, previously my Country query was filtered on a text value that was the GUID of the TermSetId. Now I have a parameter I can use instead. I will want to update my query to use a parameter instead of free text. In the Applied Steps for Country, I can click the cog next to Filtered Rows which is step 2.

This brings up a Filter Rows dialog, where you can see it current is using a Text value to query against.

By selecting the dropdown where it currently shows ABC, you can select parameter instead. Then in the last dropdown box you are given all your parameters, here I would select CountryTermSetId.

I then repeat those steps for Genre query, using the GenreTermSetId instead.

For the movies query, I need to click the Advanced editor from the ribbon bar to amend the query to use parameters.

The text in this window is the query that has been currently set.

let
    Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/taxonomy", [ApiVersion = 15]),
    #"acd616ae-ee59-4597-86f3-3d5b65e64547" = Source{[Id="acd616ae-ee59-4597-86f3-3d5b65e64547"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"acd616ae-ee59-4597-86f3-3d5b65e64547",{{"ID", "ID.1"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "Total Worldwide Box_", "Genre", "Country"}),
    #"Expanded Genre" = Table.ExpandRecordColumn(#"Removed Other Columns", "Genre", {"TermGuid"}, {"Genre.TermGuid"}),
    #"Expanded Country" = Table.ExpandRecordColumn(#"Expanded Genre", "Country", {"TermGuid"}, {"Country.TermGuid"})
in
    #"Expanded Country"

We need to change the Source row (Line 2), and the #”acd616ae-ee59-4597-86f3-3d5b65e64547″ (Line3).

The Source row (Line 2), would need the URL part changed from

Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/taxonomy", [ApiVersion = 15]),

to

Source = SharePoint.Tables("https://"&tenant&WebSiteUrl, [ApiVersion = 15]),

You can see we are inserting the parameters names and concatenating them using the ampersand.

The Line 3 row we are using the Title of the list instead of the ID as this would be different in another tenant.

#"acd616ae-ee59-4597-86f3-3d5b65e64547" = Source{[Id="acd616ae-ee59-4597-86f3-3d5b65e64547"]}[Items],

to

#"acd616ae-ee59-4597-86f3-3d5b65e64547" = Source{[Title="Movies"]}[Items],

We would also do something similar to the TaxnomyHiddenList query too. Changing the first 3 lines from:

let
    Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/taxonomy", [ApiVersion = 15]),
    #"63f7f485-a5ca-4be5-815f-d0e3235e96d1" = Source{[Id="63f7f485-a5ca-4be5-815f-d0e3235e96d1"]}[Items],

to

let
    Source = SharePoint.Tables("https://"&Tenant&WebSiteUrl", [ApiVersion = 15]),
    #"63f7f485-a5ca-4be5-815f-d0e3235e96d1" = Source{[Title="TaxonomyHiddenList"]}[Items],
 

If you have followed this correctly, you would find that your data still works, and there are no issues. Click Close & Apply from the ribbon. The apply query changes will happen but your data will be the same. In the future when you want to publish to a different tenant you just need to change your parameters, instead of going to each query and updating the URL ID’s/GUIDs.

SharePoint Designer 2013 Workflows and POST REST API


Following on from my last post of using GET REST API in workflows, this post will show how you can create an item in a list using the POST REST API.

Creating a list Item using REST API.

Using the same list as before, on my SharePoint site, I have a list. The list is nothing special, it’s just an example of some data.

Title: String
Person: User field
Colour: Choice field

You wouldn’t do this in real world scenario, but my demo will demonstrate when you run the workflow on any item, it will just create a new item in the list.

A better real world scenario might be when an item is added to this list, some of the information needs to be copied and created in another list, or when an item is changed a workflow fires to copy the information to another list similar to a history list.

Creating the workflow

In SharePoint Designer, I have gone to my list, and created a new workflow.

  • First, we need to add a Dictionary Item. This is so we can create a header for our HTTP Call. Type in “Dictionary
    and then in the options to choose from select Build Dictionary.

    Click on “this”.

    A dialog will appear, and it is here we need to add two headers.
Name Type Value
accept String application/json;odata=verbose
content-type String application/json;odata=verbose

Click on Variable:dictionary, and create a new variable called Headers.

  • We need to create another Dictionary now, this is to store the “type”. When you create an item with REST API, you need to provide the __metadata with the ListItemEntityType. Typically this item is SP.Data.[ListName]Item. However, to be absolutely sure, you can use this REST API call in the browser to find out.

     

    https://<tenant>/sites/<Site>/_api/web/lists/getbytitle('	<listTitle>')/ListItemEntityTypeFullName
    
Name Type Value
type String SP.Data.DemoListItem

  • One more dictionary needs to be created, this is the RequestContent for the POST Call.
Name Type Value
__metadata Dictionary Variable:EntityType
Title String New Item
Colour String Blue
PersonId String Current Item:Created By (return field as: User Id Number)

When you create this, ensure that __metadata has 2 underscores in front of it.
All fields are the internal names.

User fields all have an Id field with it, in my case the Person field has a PersonId field, it is this that I set with another User Id.

Choice fields just accept a valid string.

Taxonomy fields are a little different, see section below.

  • Now we can call a HTTP web service. Underneath the dictionary, start typing “http” and press enter to insert Call HTTP Web Service.
    Click on “this” to open up the dialog box, and then click on the … for the string builder dialog box.

    I like to use as much dynamic values as possible so my URL in here looks like the following:

    [%Workflow Context:Current Site URL%]/_api/web/lists/getbytitle('[%Workflow Context:List Name%]')/items
    

    Set the HTTP method to HTTP POST.

    After entering the URL and setting the Method, click the dropdown at the far right on the line in SPD and select properties. Set the RequestHeaders to the Dictionary Variable:Headers you made in the first step. Set the RequestContent to the dictionary Variable:NewItemMetadata. Set the ResponseContent to a new variable, and the ResponseStatusCode to a new variable. Click OK.

    The ResponseContent will be populated with the results, and the ResponseStatusCode will be populated with Created if successful, or a different value if something went wrong.

  • After the Web service call, I add an If Statement, to test that the variable responseCode equals to Created.
  • I log success to the history list.
  • In the else statement of if “responseCode equals Created” I grab the error message from the ResponseContent dictionary and output the value to an error message. Then I display the error message in the history list. To grab the error the dictionary path is error/message/value

  • Don’t forget to Transition to Stage “End of Workflow” at the end.

Publish your workflow.

When I run the workflow against any list item, after a moment a new entry is added to the list.

Updating Taxonomy/managed metadata column in POST REST API calls.

To be able to update a Taxonomy column in a list you would need to create the following 2 dictionary’s first, then assign 2nd dictionary to the column.

TaxonomyMetadata Dictionary

Name Type Value
type String SP.Taxonomy.TaxonomyFieldValue

TaxonomyValue Dictionary

Name Type Value
__metadata Dictionary TaxonomyMetadata Dictionary
Label String <Label value>
TermGuid String <TermGuid>
WssId String -1

<Label Value> – the actual value of the taxonomy term
<TermGuid> – The guid of the taxonomy term found in the termstore.
The WssId can always be -1 as SharePoint can work that out itself, but it requires a value.

PostRequestContent

Name Type Value
__metadata Dictionary Variable:EntityType
Title String New Item
<TaxonomyColumnInternalName> Dictionary Variable:TaxonomyValue Dictionary

It is the final dictionary above that you would set in the web service call as the RequestContent.

SharePoint Designer 2013 Workflows and GET REST API


SharePoint Workflows have been around for a long time, and with Microsoft flow now firmly taking hold the need for SharePoint workflows might be less. However, I recently had to work with SharePoint Workflows and learnt a few things around using REST API and I wanted to share my knowledge.

Reading a list/Item using REST API.

On my SharePoint site, I have a list. The list is nothing special, it’s just an example of some data.

Title: String
Person: User field
Colour: Choice field

All I’m going to do in the workflow, is when it is run, it will grab the items in the list, and then display the results in the history list. The point of this demo is to show how to make REST API calls and grab/loop through data.

Creating the workflow

In SharePoint Designer, I have gone to my list, and created a new workflow.

  • First, we need to add a Dictionary Item. This is so we can create a header for our HTTP Call. Type in “Dictionary
    and then in the options to choose from select Build Dictionary.

    Click on “this”.

    A dialog will appear, and it is here we need to add two headers.
Name Type Value
accept String application/json;odata=verbose
content-type String application/json;odata=verbose

Click on Variable:dictionary, and create a new variable called Headers.

Because SPD is a pain, it has already created a variable called dictionary, if you click on Local Variables from the ribbon, you can delete this variable. This will ensure you keep only the variables you are using within your workflow and save confusion later. Always give your variables sensible names, just as if you were writing code.

  • Now we can call a HTTP web service. Underneath the dictionary, start typing “http” and press enter to insert Call HTTP Web Service.
    Click on “this” to open up the dialog box, and then click on the … for the string builder dialog box.

    I like to use as much dynamic values as possible so my URL in here looks like the following:

    [%Workflow Context:Current Site URL%]/_api/web/lists/getbytitle('[%Workflow Context:List Name%]')/items?$select=Title,Colour,Person/Title,Person/Name,Person/EMail&$expand=Person
    

    In the above API call I’m bringing back additional information about the Person from the person column. See at the end of this post about bringing back other information on a person column.

    After entering the URL, click the dropdown at the far right on the line in SPD and select properties. Set the RequestHeaders to the Dictionary you made in the first step. Set the ResponseContent to a new variable, and the ResponseStatusCode to a new variable. Click OK.

    The ResponseContent will be populated with the results, and the ResponseStatusCode will be populated with OK if successful, or a different value if something went wrong.

  • After the Web service call, I add an If Statement, to test that the variable responseCode equals to OK.
  • Now we want to grab the results. So now we will grab a dictionary item. Type “Get” and press enter to insert Get an Item from a Dictionary.
    Click “item by name or path” and type “d/results”
    Click “dictionary” and select “ResponseContent”
    Click “item” and create a new variable. This variable is a dictionary too that will contain the individual items. I’ve called mine DemoList.
  • We need to count the number of items in the new dictionary variable. I do this to ensure that we have returned items.
    Type “Count” and press enter to insert count items in a dictionary.

    Click “dictionary” and select variable “DemoList”.
    Can leave output Variable to count.

  • Put another If statement, and check that Count is greater than 0.
  • We now need to create a variable that will be our indexloop.
  • Add a loop with a condition. (Alternatively, we could have skipped the last step and added a “loop n times” instead).
    Set the condition to read. “The contents of this loop will run repeatedly while: Variable: indexloop is less than Variable: count

  • Now I’m going to get the values of Title, Colour, Person/Title and Person/Name from the DemoList dictionary.
    Type “Get” and press enter to insert Get an Item from a Dictionary
    Click “item by name or path” and type “([%Variable: indexloop%)/Title”

    Click “dictionary” and select “DemoList”
    Click “item” and create a new variable.

    Repeat this for all the parameters.

  • Still inside the loop underneath gathering the parameters we need to raise the index variable by one.
    Type “Calc” then press enter to insert Do Calculations
    Click first “value” and select the Variable: “indexloop”

    Click second “value” and set to 1.

    Set the output to a number variable. I called mine NewIndex

  • Now you need to set the variable NewIndex to indexloop.
  • Lastly, for demo purpose, I’m logging the results I’ve got to the history list. This logging to history lists is still within the loop.
    Type “Log” then press Enter to insert log to History List.
    Then I’m just logging out the variables I’ve captured.
  • Don’t forget to Transition to Stage “End of Workflow” at the end.

Publish your workflow.

When I run the workflow against any list item I see the following results in my History List.

Bringing back additional Person Data in REST API calls.

To bring back a person column data (or a lookup column), you need to expand it in a REST API call, you also need to include the values you want to bring back from the expanded column. A list of all possible values you can bring back for a user column can be found if you type the following in a browser (after already signing into SharePoint that is).

https://<tenant>/sites/<site>/_api/web/lists/getbytitle('<ListTitle>')/items(1)/<UserFieldInternalNameColumn>

*I’m using XV – XML Viewer chrome extension for the layout.

Hide your documents from Delve


Delve is a powerful tool to see and discover information that is likely to be interesting to you. You only see content that you have access to. The powerful search behind Delve uses the Microsoft Graph.

You can access Delve from the Waffle inside your Office 365, or by going directly to the URL https://delve.office.com and signing in with your 365 account.

Now you or someone else will only see a document if they have access to it. However, there might be a reason that you don’t want it to show up in Delve at all. To prevent a document showing up in delve you can create a HideFromDelve column of the type Yes/No in the library the document is in. The column creates a new crawled property within search schema called ows_HideFromDelve. This is automatically mapped to the HideFromDelve managed property. Set the column value to Yes for the document you do not wish to see in Delve.

Set the default value to No otherwise every document you create within the library will not show up in Delve (Unless this is the desire outcome).

SharePoint Online site keeps refreshing on Microsoft Edge


A weird thing started happening on my Microsoft SharePoint site yesterday. Every time I hit a page/site the browser continuously refreshed over and over. I had no problems if I did this in Chrome, or even Internet Explorer, only in Microsoft Edge. Originally I thought it was a problem with the site, because if I went to a different tenant I didn’t encounter the same issue.

So, I then booted up another pc that had Microsoft Edge on it, and viewed my site expecting it to refresh over and over, but it didn’t. Therefore, it seemed to be a problem with my Edge explorer.

I first tried to clear all settings. Clicking on the 3 ellipses and then select Settings > Clear browsing data. I ticked everything to be cleared, and then clicked Clear. Unfortunately, this didn’t work for me, and made my Edge explorer crash on opening.

Extreme measures were required. I needed to complete reset Microsoft Edge. Now if you are following these steps, I must warn you, you might lose your favourites, history and settings. I recommend that you create a full backup or create a system restore point. Although I didn’t bother with a system restore or backup. My favourites were still there afterwards, I’m not sure if this is a syncing thing between my devices though, as I’ve just noticed that my favourites are the same on two machines.

Fixing Microsoft Edge

  • Close all open Microsoft Edge browsers.
  • Open file explorer and navigate to the following location (You will need to show hidden files):
    • C:\users\<YourUserName>\AppData\Local\Packages
  • The folder called Microsoft.MicrosoftEdge_8wekyb3d8bbwe first right click it and select Properties, and remove the check from the Read-Only option, click Apply and then OK
  • Now try and delete the folder. If you get Access Denied prompts, just select Continue. Any files you cannot delete just skip for now.
  • Go inside the Microsoft.MicrosoftEdge_8wekyb3d8bbwe folder and try deleting any remaining folders, you might be not able to delete the AC folder.
  • Restart your computer.
  • Now we are going to obtain the Microsoft Edge package and reregister it. Open Windows PowerShell by right clicking it
    and Run as administrator
  • Type the following to get to your user cd c:\Users\<YourUserName> press enter.
  • Now type
Get-AppXPackage -AllUsers -Name Microsoft.MicrosoftEdge | Foreach {Add-AppxPackage -DisableDevelopmentMode -Register "$($_.InstallLocation)\AppXManifest.xml" -Verbose}
  • If successfully you will not see any red message in the powershell window, just a yellow one saying operation complete for C:\Windows\SystemApps\Micorosft.MirosoftEdge.8wekyb3d8bbwe\AppXManifest.xml”
  • Reboot your machine on more time. Afterwards Microsoft Edge will be restored and I found that I didn’t have a refreshing issue with my SharePoint online site anymore.