Russ' Do It Yourself Home Workshop

Finding Fixes to Just About Anything and Everything

Archive for the ‘Uncategorized’ 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, Uncategorized | Leave a Comment »

Turn off Clutter in OWA Office 365

Posted by Russell Wright on June 26, 2017

Turn off clutter in Outlook Web Access in Office 365.  Thanks to Micael AC for the real solution!

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_outlook-mso_win10/clutter-options-missing/46298cc6-b21f-45ac-9462-ffb20ae5bf05?rtAction=1498485082753

Your clutter settings are probably missing, so you must first change the Display settings.

image

image

image

Posted in Uncategorized | Leave a Comment »

Repairing the Eddy Current Brake on a LifeFitness x5i Elliptical

Posted by Russell Wright on April 19, 2016

Well, I’ve now done this twice and I never published the pictures I took the first time, so here goes. 

What you may notice is the resistance is always higher than what you want…or what you remember the least resistance to be.  In fact, at one point, you might have heard the "pop" or "snap" while using your elliptical and notice the resistance no longer adjusts as low as it should.  You’ve come to the right place, and I’ll show you how to repair this for $10 or so.

So what is an "Eddy Current Brake?"  Well, it’s the magnetic part that creates the adjustable resistance on your machine.  In particular, we’re talking about the FB1 manufactured by Chi Hua in Taiwan.  To break (brake…LOL) it down in simple terms, eddy currents are created from the magnetic flux (of magnets) passing through a coil of wire (conductor).  Basically, you have a generator that creates small currents in a conductor that are essentially short circuited and turned into heat.  You can read about it at the source of all knowledge…Wikipedia.  This is what makes the drag force on your elliptical…you know, the thing that makes your legs hurt and sweat pour from your body.  Check it out on their web site and use your web browser’s translate feature.

image

Well, here’s what happens.  The blue actuator pulls and releases the cable based on the level setting.  This makes the white part in the green ECB (Eddy Current Brake) travel back and forth, which moves the magnets away from and closer to the flywheel, generating your resistance.

image

Unfortunately, after some period of time, the cable wears and snaps in two.  This is shown after removing the green part (essentially the stator). 

image

Getting the green part (stator) out (assuming yours is green, too) requires some mechanical work, but it’s not too hard…just takes some time.  You have to remove the flywheel.  Release the tension on the belt by loosening the nut on the tensioner.  Then you have to remove the flywheel nuts and the tensioner bracket.

image

image

You also have to remove the stator locking bracket to get it out of the way.  The actuator cable is easier to remove from the blue actuator side first by pulling and unwrapping it from the nylon spool.

image

What you are going to have to do is fashion a new cable to replace at least the one that broke.  Maybe two, if the other is worn and fraying.  I did this by purchasing a bicycle shifter cable ($5) and grinding down the barrel end with my Dremel tool to make it the correct diameter and length.  The ball end of the cable is not very critical.  It simply needs to NOT pass through the hole in the magnet.  You can pass the barrel end through the hole when threading it in place.  The replacement for the ball is a cable clamp that has been "swaged" in place using a crimping tool.  I used my ratcheting electrical crimpers, but you may want to opt for something more professional, like these hydraulic crimpers.

image

Here are my notes I took with my calipers.  Notice the leftover Christmas notepad paper. 

image

I didn’t get any pics of the stator taken apart, but it’s pretty easy to remove.  Two allen set screws hold it to the shaft.  Loosen these and pull it out…you’ll feel the magnets wanting to keep it in the flywheel.  Then there are some black phillip screws holding it together and the four silver screws that hold the thingamajiggy with the allen set screws.  Inside you’ll see the white plastic actuator that connects all the cables.

When you are done you’ll have something that looks like this.  Not really pretty, but very functional.

image

Posted in Uncategorized | 5 Comments »

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 Uncategorized | 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 Uncategorized | 1 Comment »

Upgrading the Aerator on a Septic Systems

Posted by Russell Wright on August 8, 2015

I know!  Let’s design a septic aerator with an electric motor that sits down in a very caustic environment where the motor and bearings will only last 2-3 years so the homeowner has to constantly replace it.  And when it floods, it’s always good to have electrical power sitting under water!  NOT!

WP_20140610_08_10_02_Pro

This is the stupidest design in the world.  Poor idiots at Norweco.  They haven’t figured out that electricity and water don’t mix.

WP_20140610_08_09_53_Pro

No problem…let’s upgrade!  First you need to get an aeration stone like is used on a pond.  I found one on eBay.  It’s a 7" dome diffuser airstone.  It was about $50 or so.

WP_20140614_08_39_57_Pro

Next, put it on a long length of (properly measured) PVC.  I chose to use electrical conduit.

WP_20140614_08_39_47_Pro

Next, attach a PVC union for easy installation and removal.

WP_20140614_08_40_05_Pro

Now you’re getting the idea.  The assembly will be lowered into the chamber where aeration occurs.  Notice the notch taken out of the side of the concrete collar for the air supply line.

WP_20140614_08_40_50_Pro

You can lower it all the way.

WP_20140614_08_40_57_Pro

And make the final attachment with the PVC union.  This is where prior measuring and cutting is important!

WP_20140614_08_41_08_Pro

Now it’s all attached.  See the old electrical outlet?  It’s dead now.

WP_20140614_08_41_30_Pro

How did the air line get there?  A little digging!

WP_20140607_11_02_04_Pro

Just a shallow trench.  I think it took me about 45 minutes.  I’m pretty good with a spade.

WP_20140607_11_07_51_Pro

Covering it back up.

WP_20140607_12_05_17_Pro

Watering the sod back down.

WP_20140607_13_19_05_Pro

The final attachment to an aeration pump.  This is a very standard pump I got off eBay.  It’s a Hiblow HP-80 and was $248.00.  A little tubing and fittings and a bit of wiring (it runs all the time) and you’re done.  At this point in time it’s been running for over a year with no issues.  There’s a filter you have to clean and/or replace once or twice a year, but no big deal.  My effluent has never been cleaner!  That’s a nice way of saying, "My @#$% don’t stink!"

WP_20140607_16_11_16_Pro

Posted in Uncategorized | Leave a Comment »

Upgrading the Plumbing and Hardware on an SR Smith Slide

Posted by Russell Wright on August 8, 2015

Yes, we have a slide…and a diving board…on our pool.  OMG!  Now that we’ve established that and the fact that we are not going to remove them, here’s my latest upgrade.

The slide is about 15 years old and empties into our salt water pool.  The original plumbing on it is in the form of 1/4" tubing attached to recirculating water coming from the pool pump.  However, it’s never worked very well because of the pressure drop from the 3/4" PVC to the 1/4" flexible tubing with which the slide is plumbed.  In fact, at one time, I added a spigot from our irrigation system which worked pretty well, but the primary problem remained.

WP_20150808_08_55_29_Pro[3]

The primary problem is the 1/4" tubing never seems to last more than one season.  We’re in Texas and it’s hot!  Between the heat and the (somewhat) cold (yes, it does freeze at times), the tubing becomes fragile and breaks, necessitating its replacement every season.  So, when the kids are over and it’s broken, a hose gets dragged to the slide and tied to and draped over a handle.  Not cool.  Plus, whether it’s the hose or the spigot, it’s up to me to turn off the fresh water supply, since no one ever seems to be able to do that when they are done.

WP_20150808_08_55_41_Pro

My goal was to reduce the pressure loss by using a larger main line up and down most of the length of the slide.  You can see my attachment of 1/2" PVC to the 3/4" PVC main line.  After the reduction fitting, I used a removable PVC union fitting so the entire extension can be easily detached and removed, if necessary.

WP_20150808_08_56_08_Pro

Under the slide I provided a cutoff valve so it can be easily turned on and off at the slide.  There is also a master Jandy valve located at the pool equipment.

WP_20150808_08_56_00_Pro

I was originally going to plumb it with 1/2" flexible PVC, but it seemed to be a bit difficult to get 11 feet of it without purchasing a 50 foot role.  Home Depot and Lowes don’t stock the small stuff.  So I finally decided to use rigid 1/2" PVC.  In order to make it contour to the slide, I used my heat gun and patiently heated it up until it was flexible enough to form into the correct arcs.

WP_20150808_08_56_57_Pro

Probably the neatest thing I did was take a PVC cap and drill and tap a hole that would accept one of those quick connect 1/4" tubing bulkhead connectors.  I used plenty of Teflon tape on the threads and screwed it into the end of the modified PVC cap.  This provides me with a quick way to make a short 1/4" tubing connection from the 1/2" PVC to the 1/4" slide fitting.

WP_20150808_08_56_36_Pro

I did this at both the bottom and top, leaving enough room so the 1/4" tubing has room to flex and bend.  On the bottom, I replaced the bulkhead connection on the slide (since it broke) with another 1/4" tubing quick connect, so replacing this short piece of tubing literally takes about 30 seconds.  The lower slide nozzle correctly screws on the end of this quick connect bulkhead fitting.

WP_20150808_08_56_18_Pro

Here’s the top.  The tubing is a bit longer, but still quick and easy to replace.

WP_20150808_08_57_06_Pro

An underside look at the top.

WP_20150808_08_57_17_Pro

Next, I replaced all the hardware with stainless steel hardware.  I ended up having to cut some of the bolts because the salt had corroded them so badly.  My hardware was ordered from Albany County Fasteners on the web.  Love these guys and their stainless hardware.  Shipping is free with a $25 order!  Or you can purchase stuff from them on eBay.  Here you can see the carriage bolts replaced with stainless.

WP_20150808_08_58_19_Pro

This is essentially a stainless lag screw replacement.  I had to go a bit bigger because the aluminum rail was stripped, probably during the initial installation.

WP_20150808_08_59_01_Pro

I had an issue with the aluminum handles being loose.  Problem was the aluminum attachment block in the handle appears to be held in place with epoxy of some kind that had lost its grip.  I re-engineered it with a through hole and stainless hardware. 

WP_20150808_08_58_04_Pro

I also had to drill and tap out the broken screw that comes up from the bottom of the slide.  When I attempted to remove these screws that hold the handles in place, they promptly broke.  In fact one was broken already which was the cause of one of the handles being loose (very unsafe).  Not surprising, they were completely rusted.  These screws fit into the bottom of the aluminum block that extends from the handles.  They are not going anywhere now!

WP_20150808_08_58_39_Pro

The complete project was probably around $50 in hardware and supplies and the results were great.  There is now plenty of water that recirculates through the pool and on to the slide with significantly less pressure drop.  It used to dribble…now it sprays!

Posted in Uncategorized | 2 Comments »