Russ' Do It Yourself Home Workshop

Finding Fixes to Just About Anything and Everything

Archive for the ‘Computer Software’ Category

Retrieving Single Item from SharePoint List in Microsoft Flow

Posted by Russell Wright on May 30, 2019

This seems like such a simple thing to do.  Select a single name/value pair from a SharePoint list used as an app configuration source.  I do this all the time with workflows so I can externalize email addresses, workflow participants, etc.  But in Microsoft Flow the rules have changed.  Here are a couple of design templates that can be used to get those name/value pairs storing your workflow configuration data.

In MS Flow, you’ll find two SharePoint actions:  Get Item and Get Items.  If you have an ID for a SharePoint list item your problem is solved.  Simply use Get Item and supply the list item ID.  This is handy when you’ve created a new item and need to reference it (update or retrieve it) during the flow execution.  It’s not quite so handy when you need to reference an item from another list and you don’t have the item ID.

Now on to Get Items.  Get Items returns an array of all the items selected from the SharePoint list, even if you narrow down your selection to a single item.  Once you get this “collection” you can iterate through it using an Apply to each loop to select the item you want.  I would argue, however, this is less efficient than selecting a single value and either referencing it with a first() function or by its index of [0].  You can actually see this difference in the execution of the workflow, as the Apply to each loop takes significantly more time to set up and execute.  Here are some examples.

There are four examples and I think I like example #4 the best.

Example #1

Initialize your variable.

image

Using the Get Items action, select your site and list and create a Filter Query to select the item from your list.  This assumes the Title (the name part of the name/value pair) is unique.  My filter query: Title eq ‘AdminFinalApprovalEmail’, will select a single item but, just for grins, I also add the Top Count of 1 just to make sure.

image

Here’s what my underlying SharePoint list AppConfig data looks like.  It contains name/value pairs.  The Title contains the name and a column called Value contains the value.  I guess I could’ve been more imaginative on the column names, like Fred and Ethel, but this works just fine.  🙂

image

Now I add a Set Variable action.  I’ve selected my variable I initialized earlier, AdminFinalApprovalEmail. Notice how I rename my actions to something long and descriptive.  That’s from years of programming and trying to make my code self-documenting.  My new name is “Set variable AdminFinalApprovalEmail.”

image

For the Value I use this expression:

body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’]?[0]?[‘Value’]

The body() function references my renamed SharePoint Get Items action.  Note that flow replaces spaces with underscores:  Get_items_Filter_by_AdminFinalApprovalEmail.  Now comes the somewhat confusing part.

The first ?[‘Value’]?[0] says to get the zeroth [0] indexed item from the Value portion of the body of the action Get_items_Filter_by_AdminFinalApprovalEmail.  There is only one item retrieved from the SharePoint list and this object contains all the fields retrieved for the item identified by “AdminFinalApprovalEmail.”

The next part ?[‘Value’] says to select the field named Value from the SharePoint results.  This is a bit confusing because I named the column Value which is the same name that Flow uses for its “Value.”  If the field name in SharePoint was “Ethel” it would look like this:

body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’]?[0]?[‘Ethel’]

Hopefully you get it.

Example #2

Now for the next way to do the same thing.  In this case we’ll adjust the expression to use the first() function.  I’m using a new variable called AdminFinalApprovalEmailFirst in this example.  I am still referencing the same SharePoint results retrieved using Get Items with the filter applied.

image

This is what the expression looks like:

first(body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’])?[‘Value’]

We reference the same body() data but apply the first() function to the first [‘Value’] on the SharePoint data.  This essentially is referencing the [0] index, since it’s the only one that was retrieved.  Note the position of the parenthesis for the first() function!  We then select the SharePoint Value field from the SharePoint results using the second ?[‘Value’] selector.

You’ll find these to be very fast, as a single item is retrieved from SharePoint and assigned without a loop.

Example #3

Now to show the for-each method.  Here’s an overview of the process.

image

In this example, I’m using Get Items without any renaming of the action.  Note there is no Filter Query applied, so all SharePoint items are retrieved from the list.  Since this list is pretty small (< 10 items) it’s no big deal retrieving all the items.

image

An “Apply to each” loop is used that iterates through the Value data.  The “value” object from Get_Items contains all the SharePoint list items; in this case there are eight items in the list.  It is essentially the body function:  body(‘Get_Items’)?[‘value’].

image

A condition action is used to filter the results.  In this case we are looking for items where the Title is equal to the string “AdminEmailCC.”  There is only one in the list.

image

Assuming it’s found in the list, the “yes” branch sets the variable value.  The SharePoint item field named “Value” is selected (it has the email address).  Note the code that goes along with this:

items(‘Apply_to_each’)?[‘Value’]

image

Now let’s look at the execution of all these options in a Flow test.  Note that the “Apply to each” loop is the time hog.

image

The “Apply to each” has to cycle through eight items in the list.

image

Seven of the eight results do not satisfy the condition.  Notice the Expression result=false.

image

The seventh item is the one we’re looking for, so Expression result=true.  At this point we can set the AdminEmailCC variable.

image

Example #4

Now here’s another method that may be the best one of all.  This method uses a single Get Items action and subsequently filters the SharePoint results with an Array Filter action.

image

Next is the action to filter the array. Value is the list of items from Get items.  It should be pretty obvious we are filtering by the Title column to get the row where Title = ‘AdminEmailCC’.

image

Now the magic occurs.  We will initialize a new variable using the expression:

json(string(first(body(‘Filter_AdminEmailCC’))))?[‘Value’]

Like the other solutions, we get the first (and only) item from the array.  Then we convert it to a string so the json() function will work.  Using the json() function we can select the Value field.  And that’s it!

image

We get the value for our admin email.

image

The nice thing about this solution is it hits the SharePoint list only once.  After all the results are retrieved from SharePoint, it’s simply a task of filtering the array to get the specific row and then initialize a variable with the specific field from the single valued array.

And to wrap it up, I send myself an email with the results.

image

image

Advertisements

Posted in Computer Software, Microsoft Flow | Leave a Comment »

Importing an ICS Attachment to the non-Default Outlook Calendar

Posted by Russell Wright on February 13, 2019

Here’s a quick post on how to handle an ICS attachment (iCalendar or vCalendar file) when you have multiple O365 (or Exchange) accounts in the Outlook desktop application.

I have two Outlook accounts:  Business and Personal.  I am using Outlook (2016 I think, version 1808) to manage both calendars.  When an ICS file comes in on my personal (non-default) account as an attachment I’d like to add the contents to my personal calendar.  If I double-click and open it and then save, it will save to the default (business) calendar.  There’s a simple way to handle this problem.

Make sure you have your non-default calendar displayed in the calendar view.  Simply click and drag and drop the attachment to the non-default calendar and BOOM!  You’re all done.  Simple drag-and-drop works fine.

 

Posted in Computer Software, Office 365 | Leave a Comment »

Solved! – Outlook 2016 will not Display Images in Emails: Red X

Posted by Russell Wright on September 10, 2018

If you’re looking at this post you’ve probably been trying to solve the problem of Outlook 2016 suddenly not displaying images in email.  This problem was on my daily driver Windows 7 Dell E6530 (I have others).  Others have had it on every other Windows OS.  You’ve probably tried all kinds of things and are mad at Microsoft.  Same here.

1. Internet explorer advanced options:  Do not save encrypted files to disk.

image

2. Outlook options in the Trust Center:  Don’t download pictures automatically in standard HTML email messages or RSS items.

image

3. Send Pictures with Document in HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\Options\Mail missing from the registry.

image

4. Removing some Windows updates (should never have to do this, but we all know we’ve done it).

…and probably some others.

However, I found that resetting IE fixed the issue for me.

In IE Advanced Settings, click the reset button and reboot.  Yeah, sucky solution, but it’s the only one that worked for me.

image

References:

[SOLVED] Outlook 2010 not displaying images

Inline images may display as a Red X in Outlook – Microsoft Support

Pictures cannot be displayed and are shown as red X in Outlook

Fixing Outlook’s pictures show as red X problem – Spiceworks

Red X’s in Email Messages – Slipstick Systems

Microsoft Outlook 2016 Red X instead of Pictures – Windows 10 Forums

Posted in Computer Software | Leave a Comment »

pfSense SMTP Office 365 Email Settings on Netgate SG-3100

Posted by Russell Wright on August 17, 2018

It’s always a challenge to get the SMTP settings correct for sending through Office 365 SMTP email.  Here’s what I did for my new Netgate SG-3100.

You can start at System | Advanced | Notifications.

image

The important thing is to point to the email server than ends in mail.protection.outlook.com which is actually in your DNS MX record.

Port:  25

DO NOT CHECK:  Enable SMTP over SSL/TLS

Notification E-mail Auth Mechanism:  LOGIN

What I normally do in Office 365 is create a single “service account” email and assign multiple aliases to it.  Then I use the alias in the particular application, e.g. netgatedevice@mydomain.com.  This alias also serves as the Notification E-mail Auth Username, which you can leave blank since you have it in the FROM address.

SG-3100 pfSense SMTP Email Configuration

Posted in Computer Software, Networking | Leave a Comment »

OneNote needs a password to sync this notebook. Click here to enter your password. Get Rid of KB3055034 and KB3054886!

Posted by Russell Wright on November 5, 2015

This has been driving me nuts.  I’ve searched for hours.  For me, I’ve finally tracked it down to two updates in October.  KB3055034 and KB3054886.  After removing both my OneNote 2010 seems to be syncing again.  I first wrote about this for KB3055034 in this post.  But I was still having problems.  Then I found this post.  I’m not using SmartVault Drive, but since they appear to be related I removed them both.  Success!  This pic is showing the uninstallation of KB3054886.  I uninstalled it from everything.  Same for KB3055034…you’ll see it installed multiple times.  After the uninstallation, reboot.

KB3054886 Uninstall

Now I can sync from OneDrive again!

Some other background.  I have a personal OneDrive (Live) account.  I also have two OneDrive for business accounts.  I expect them all to work, but I’m finding OneDrive for business has lots of sync issues.

Some other posts I’ve looked at.  There were others.

http://meyermed.com/2014/02/fixing-the-error-onenote-needs-a-password-to-sync-this-notebook-click-here-to-enter-your-password/

https://community.spiceworks.com/topic/1236093-kb3055034-breaks-opening-office-files-directly-from-sharepoint

https://support.smartvault.com/04Support/01Knowledge_Base/KB3055034_%2F%2F_KB3054886_-_Uninstall_update_for_Office_2010_causing_Word_or_Excel_to_crash_when_opening_Word_or_Excel_files_from_the_SmartVault_Drive

http://faq.mydocsonline.com/1009/ms-office-2010-update-kb3055034-causes-authentication-error-when-opening-office-files-from-web-folders/

https://support.microsoft.com/en-us/kb/3099951

http://superuser.com/questions/986693/excel-2010-crashes-when-opening-files-from-sharepoint-office-365

Posted in Computer Software, Office 365 | Leave a Comment »

October 2015 Office Updates: KB3055034 Causes OneNote 2010 to Crash on Sync

Posted by Russell Wright on October 27, 2015

I can fully attest to this being a problem.  I noticed it when on site with a client and attempting to connect to our shared OneNote notebook on SharePoint 2010.  I was "catching up" with stuff that occurred over the last couple of weeks and OneNote would crash each time I tried to sync.  I even tried re-connecting to the OneNote notebook, but that also failed.

I opened Programs and Features and selected the View Installed Updates on the left and waited for Windows 7 to chunk through the multitude of updates.  When it finally settles down you can enter KB3055034 in the search box and, if you’re lucky, it will show you the multiple times it’s installed.  In my case it was four times:  Visio, Project, Office and something else I don’t’ remember.  I removed it four times and after it is gone syncing works again.

image

See how syncing works again?

image

Posted in Computer Software | 1 Comment »

Outlook 2010 Stops Receiving Exchange Email

Posted by Russell Wright on April 15, 2015

If you have Outlook hooked up to an Exchange mail box (Office 365 in this case) and mail stops dropping into your inbox, try this.

Right-click on the offending Inbox and click Properties…

image

Under the General tab, click Clear Offline Items.  This will empty the cache.  In a scary fashion, all your inbox mail will disappear and (should) will re-sync with Exchange and re-populate.

image[7]

Hopefully this will clear up your issue.  It did mine.

Posted in Computer Software | Leave a Comment »

Where do I get GACUTIL.EXE?

Posted by Russell Wright on February 10, 2015

If you find yourself needing to use the GACUTIL program to install some "assemblies" (DLLs) in the global assembly cache, you can get it from the Windows SDK.  Now, when you get ready to install, if you don’t need all the other stuff, select the following options.

image

By selecting only the .NET Framework 4.5 Software Development Kit you will get GACUTIL.EXE installed in the C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools folder, at a disk cost of < 75 MB.  Much better and much quicker than over 500 MB!

image

Posted in Computer Software | Leave a Comment »

Cisco VPN Client Encrypted and Decrypted Packets are Zero–No DNS Resolution on Windows 7

Posted by Russell Wright on September 26, 2014

This problem has been killing me!  I’ve searched and searched and finally came across this article (and a fix that actually works!):

http://hydrous.net/weblog/2009/10/28/force-windows-to-use-a-vpns-dns-server

Here’s the background.

As a consultant, I have multiple VPN clients at any given time loaded on multiple machines.  In this case it was my old trusty Dell D830 (upgraded with an SSD for like-new performance) that was giving me fits.  At some point in time the Cisco VPN client got to the point it was able to connect, but I could not access any resources on the client’s network.  Basically, there is no DNS resolution and nothing would ping or connect.  When you start looking around at the VPN Client Statistics, you notice the Packets Encrypted and Decrypted values are 0…they never change. 

image

In this screen shot, you’ll see they are NOT zero, which means things are working again!

image

Well, how do you fix this?  It appears it has to do with the binding order of the Cisco VPN adapter you see in your Network Connections. 

image

Checking out the key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage and finding the Bind property, you can open it up and see a bunch of devices and their GUIDs.

image

Now the trick is to be able to look at this list and determine which one(s) belong(s) to the Cisco VPN adapter and move it/them to the top of the list.

image

One of the ways to do this is to navigate to the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters\Interfaces key and start clicking through the short list, while paying attention to potentially identifiable information in the right pane.  Usually this is in the form of a NameServer, which you can generally find in the properties of the network adapter after you’ve made a connection to the VPN server and the VPN network adapter has been enabled.

Now, it appears, at least in my case, that there are a couple of entries that look suspicious.  They were suspicious because they were both subnets that are used within the VPN network adapter configuration for this client, i.e. IP address beginning with 172.x.x.x and name servers in the 10.10.x.x range.  What I found was a 172.26.x.x NameServer and a 10.10.x.x NameServer and I adjusted them so they were at the top of the list, with the 172.26.x.x entry at the top and the 10.10.x.x entry just below it.  I’m not sure if one of these is just a bad entry that could be deleted, but for the time being I’m leaving them both in, until such time I can have a better determination.

image

image

image

I fired up the VPN, it connected and, low and behold, the packets were encrypting and decrypting again!  DNS name resolution was working!  All’s well in Cisco VPN land, once again.

 

Search terms:

Cisco VPN connects but doesn’t work
No DNS resolution on Cisco VPN
Encrypt and Decrypt not working Cisco VPN client
Connect to VPN but can’t access any network resources

Posted in Computer Software, Networking | 1 Comment »

Fix OneDrive/SkyDrive on Windows 8.1 Using syncDriver

Posted by Russell Wright on June 24, 2014

Oh man!  Are you tired of this giant screw-up with OneDrive (formerly known as SkyDrive)?  I’m tired of wasting my time with MS and reading their non-solutions.  When I found syncDriver referenced in the link above, I decided this might be the fix I’m looking for.  It’s small, it’s simple…and it’s an application…not a part of the operating system!

I installed it, fired it up, told it where to sync the files (c:\users\rwright\onedrive) in a folder I provisioned myself, and it was off to the races!

Look!  It has a user interface!  It actually tells you what’s going on!

image

Cool…options!  Exactly what you would expect.

image

And…oh boy…wait for this!  You can choose the folders you want to sync!

image

Need some proxy-ing to get in/out of your environment?  Thar it is!

image

Look, it’s in your tray…because it is…wait for it…an application!

image

And look-ee here.  It’s got a right-click menu…just like a reel application!

image

So far, so good.  More to come.

2014-06-25 Update:

syncDriver is operating well and I have moved all my documents from my “broken” SkyDrive folder in my old user profile to my new OneDrive, powered by syncDriver, folder in my new user profile.  I used WinDiff to compare the two directories to validate I wasn’t missing anything…except for those in the old SkyDrive folder that hadn’t been kept up-to-date.

2015-07-17 Update:

Okay, now to turn off the original OneDrive sync completely refer to this article.  Basically you are editing this registry setting.

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Skydrive]
"DisableFileSync"=dword:00000001

2016-05-12 Update:

Well, my OneDrive has been broken on my Yoga for several months and I just got to looking at it.  syncDriver could not log into my OneDrive account.  However, my OneDrive account and my credentials were just fine.  I was getting an error, “Your IT department made a change that prevents you form syncing your personal OneDrive on this computer” when I viewed the OneDrive settings.  I uninstalled syncDriver, thinking I was going to get rid of it and try to go back to the MS OneDrive sync, but then found there’s a group policy that might be causing the problem.

In gpedit.msc (Group Policy editor) find this:  Local Computer Policy | Computer Configuration | Administrative Templates | Windows Components | OneDrive | Prevent the usage of OneDrive for file storage.  The default for me was Not Configured.  I changed it to Disabled and that appears to have made OneDrive available again. 

image

I re-installed syncDriver and it immediately started syncing once again.  Yay!

Posted in Computer Software | Leave a Comment »