Russ' Do It Yourself Home Workshop

Finding Fixes to Just About Anything and Everything

AMOI F9 Bluetooth Earbuds Waterproof Instructions

Posted by Russell Wright on June 22, 2020

I’ve been purchasing several different waterproof earbuds to test them out in various cconditions.  Here are the latest instructions for the F9 earbuds made by AMOI Science and Technology. 2020-06-22







Posted in Uncategorized | Leave a Comment »

Mi Light User Instructions–Wiring and Remote Programming

Posted by Russell Wright on April 15, 2020

These are great lights that I have lighting up my landscape.  I’ve posted the instructions here so I don’t lose them, since I don’t program these lights very often.  They were purchased from Cabbi who works at  (













Posted in Instructions | Leave a Comment »

Honda Clarity Decklid Spoiler Installation 08F10-TRT-100

Posted by Russell Wright on March 22, 2020

So our task today is to install this decklid spoiler on a Honda Clarity PHEV.  The installation instructions were acquired from College Hills Honda.  I recommend you use the official instructions along with this pictorial guide, as a picture is worth a thousand words!


Remove the trim around the trunk lid hinges.


This is done on both sides.



There is a single fastener on the back of each hinge that needs to be removed.  They pull out and it’s possible you will break them, so you might need replacements.


The hinge covers snap together.  Use your fingers to pry them apart and they should pull apart. 


Remove the center trim cover by popping it off.  There are five fasteners and the upper corners holding it on.


Fastener location.


time to remove the pull handle.  Pry the cover.


Once the cover has been opened, you can see the latch holding the handle in place.



There is a single clip that can be released and then the handle tilts down to remove.


This shows the back end of the handle.


Now that the handle has been removed, you can begin removing the main trim piece.


More shots showing the handle detail.




Fastener locations on the main cover.  They are the “pop” variety.  Pull firmly with your hand as close to the fastener as possible.


In the middle of the trunk there is a clip that holds the two trunk springs.  You can release the clip and open it up so the right spring can be moved when you “unload” it so you can remove it.  This clip merely keeps the springs from flopping around in the middle of the trunk.



Due to the extra weight of the spoiler, a new right spring is installed.  There is a special tool for removing the spring, but I bet you don’t have one!  Neither do I.  I grabbed it with some channel lock pliers.  Twist downward away from the trunk hinge to pull it out of its clip and move it to the left.  The spring clip and the pad it rests on are held in place by the force of the spring.




The left side of the right trunk spring is hooked through some holes which hold it in place.  When you “unload” the spring you can shift it to the left to remove it.


Here’s another shot of the spring after I put it back.  You can see some of the marks from the pliers where I grabbed it.


This shows the removal of the deck trim.  This is what you’ll attach the new spoiler to.  There are four bolts that are removed from the inside.  I believe they are 8mm. 

Note that the fastener in the middle is released from the backside by pinching and, when you do, the trim will fall off (assuming you’ve popped off the other fasteners), so be careful! 

The electrical connector is more easily disconnected after the trim is loose and you remove it from its grommet retainer.  Trying to disconnect it from inside will only result in sheet metal cuts.


Here’s the connector you will disconnect.  It’s easy once you have access to it!


This shows the fasteners on the right side of the deck trim.


The whole point of getting the deck trim off is so you can fasten the new spoiler to it.  You’ll need to drill, cut and file, so be prepared!


The first step is to create the cutout that will allow access to the “tie-wrap” or “zip tie” style retainer system.  Not sure why they didn’t just use some self-locking nuts instead of this nylon “zip tie” retainer.

Use the drill template to mark the holes.  You flip it over to mark them on the other side.  It’s a very nice template!


Mark and use a sharp instrument to prepare for drilling.  Similar to how you would use a pin punch on a piece of metal for your pilot hole.


Start by drilling your pilot holes.


The instructions call for 3mm, 6mm, 8mm and then 10mm drill bits, but I found once it got big enough the larger drill bit was dangerously trying to grab the plastic and I felt that more filing would be a better solution.


The “hole” (whole) idea is to make an access port so you can get to the deck lid spoiler nylon fasteners that feed through the other holes you will be drilling.  It doesn’t have to be perfect, as no one will ever see it.


You do this on both sides (duh!).


There is another template you place on the deck lid trim piece you removed to mark the locations of the holes you will drill in the deck lid trim.


Then you drill the bigger hole.  You end up with a 10mm hole, which is roughly 25/64” or 13/32”.  A tiny bit bigger ain’t gonna’ make no difference.


Again, the idea is these nylon “zip tie” fasteners will feed through these holes.



And here’s where they end up.  I don’t have a picture of the fastener installed, but the instructions clearly show them. 


I also didn’t see any need to clip off the end since it never shows.


Note that the trim is fastened with lots of sticky, double-sided tape, as well as the two nylon fasteners on the ends.  Make sure if you have it in proper position before you remove the tape covers.  You’ll likely only get one chance!


Use the template to put masking tape in the correct locations to mark the edges of where the spoiler should be placed.  Perform some tests BEFORE removing any of the tape cover. 

Once you have the spoiler installed, you need to firmly press along its length to set the adhesive on the double-sided tape.  The instructions specify a roller, but I used a towel as a cover and lots of hand pressure along its length.


After you get the trim installed you will then place the plastic retainers on their “zip tie” ends.  Pull snugly, but don’t overdo it!


Now you can take your pile of trim pieces and put them back.  It’s easier putting them back, IMHO.


Posted in Auto Repair, Instructions | Leave a Comment »

MySite Profile and About Me missing from SharePoint User Menu

Posted by Russell Wright on January 15, 2020

I ran into this provisioning a Winshuttle SharePoint site after installation of SharePoint 2019.

When you click on the link looking for your MySite profile in SharePoint (on-premise 2019) you don’t see the About Me link.  You see this.


What you want to see is this.


This is because the Site Collection feature for SharePoint Server Publishing Infrastructure is not activated.



SharePoint 2019, SharePoint 2016, SharePoint 2013, SharePoint 2010, Site Collection Features, Activate SharePoint Publishing Feature, Team Site, About Me, Personalize this Page, Show Shared View, Reset Page Content, Sign Out

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

Setting the maxlength Attribute on a Multiple Lines of Text Field in SharePoint Online

Posted by Russell Wright on August 15, 2019

I had a situation where a person who created a SharePoint survey wanted to limit the amount of text that could be entered in a Multiple Lines of Text field.  There are many proposed solutions, but I was looking for the simplest.  Since the field is rendered as a “textarea” control you should be able to set the maxlength attribute to limit the number of characters that can be entered.  Here’s a bit of JavaScript that can do that. 

I first started with this post, but it was a little light on details, as there was no reference to JQuery.

Some prerequisites. 

  1. You must know how to add a script editor web part to a SharePoint page.  That’s where all the code goes. 
  2. The code must be placed on each page where you want to control the length.  That normally means NewForm.aspx and EditForm.aspx.
  3. You need to get the ID of each of the controls whose length you wish to limit.  These are pretty ugly in SharePoint.

You must first reference a JQuery library.  In this case, I’m pointing to one on the internet.  You can also download the library and place it in SharePoint and reference it from there.

If you notice we are selecting the control by its ID.  After the control is selected, its maxlength attribute is set to 15 (in this case).

< script type="text/javascript">
    function() {
  $("textarea[id*=’ctl00_ctl30_g_2e673377_8091_422f_a4a7_026fa9b214d4_ctl00_ctl02_ctl01_ctl00_ctl00_ctl04_ctl00_ctl00_TextField’]").attr(‘maxlength’,’15’); }
< /script>

In order to find the ID of the control, you can use the Edge or Chrome developer tools.  In either browser, you can press F12 to start the developer tools.  In the upper left corner you’ll see the element selector. 


Once you click the element selector you can switch to your browser window and select the control you wish to inspect.


You can see the ID of the control and copy/paste it for later use in your code.


Place your code in a script editor web part on the page (edit the page and add the web part to the page).


Here you can inspect the code again and see that the maxlength attribute has been added.


Posted in Office 365 | Leave a Comment »

BH-M32 “Trucker” Headset from Amazon

Posted by Russell Wright on July 19, 2019

Here are the instructions for the BH-M32 “Trucker” headset on Amazon.  Ordered one of these on Prime Day and, so far, the reviews from my coworkers have been positive on the sound.  I spend lots of time on web meetings:  Skype, Teams and Zoom.  Thought I would put the instruction manual online so I wouldn’t forget how to push the buttons.

I still need to test it out from a real noisy location with my cell phone to see how it functions in that type of environment.



Posted in Instructions | Leave a Comment »

Installing an Engine (ICE) Hour Meter on a 2018 Honda Clarity PHEV

Posted by Russell Wright on June 29, 2019

Love my Honda Clarity…and we also own a Prius Prime.  The Prius has lots more tech than the Honda, especially when it comes to monitoring the operation of the ICE (Internal Combustion Engine).  One of the simple upgrades is to install an engine hour meter so you can really keep track of how much the ICE is running.  This is most important for maintenance reasons.  And, if you’re concerned about the environment and don’t want to “over oil change” it is also a way to make sure you don’t throw out completely good oil.

Following the advice of KentuckyKen on the InsideEVSForum I purchased a low cost hour meter on eBay.  Shipped from the US, mine cost $6.98.  I purchased another from Hong Kong for $4.47.  I figured I’d do one for the Clarity and then try one for the Prime.

While there was a part of me who wanted to get a mechanical hour meter, I determined that not having to find a 12V wire that was energized when the ICE was on was a benefit of using one of these self-powered meters.  There is no power required as the meter has its own internal battery.  When it runs out, I’ll just buy another.


Here’s how it came.  Now to connect it to my snowmobile…oh, I live in Texas…I mean my motorcycle.


The package contents, unpacked.


The installation instructions.


For a mounting location, I like the plastic cover over the wires.  I think I can use the short screws through the meter to attach to the plastic cover without damaging anything.


The meter is just the right size to fit there.


See how it will look when installed?


Let’s remove the cover to expose the spark plug coils.  The cover just snaps in place with rubber grommets, so just pull upward to remove.  Here are a couple of pics to show you what the part looks like.



Now we have the coils exposed.


The easiest way to do this is to remove the coil.  Start by pressing the tab on the connector near my thumb end and gently pull on the sides to disconnect.


The coil is held in place with one 10mm bolt.  Simply remove it and pull up to remove the spark plug coil.  For all you old-timers, remember when there was one coil with spark plug wires running all over the place?  Now the low voltage wires are run to the individual coils and it makes for a much neater installation.  This EE loves me some electronics!


Here’s the fully removed coil.  So simple and elegant.


While you have the coil removed you can wrap the wire around it.  Try and keep it up off the seal.  What will happen is when you reinstall the coil you’ll find the wire is snugly compressed and, at least on my installation, it doesn’t appear to be going anywhere!  Be sure and leave enough to connect to the meter.


This is a little hard to see, but the wire just slips into the slot in the back of the meter and is held in place by compression.  Use a small screwdriver to shove it in place.


So here it is installed and I’m testing it.  Already have 0.1 hours (6 minutes) on the meter.  Had to run it around the neighborhood in Sport HV mode.


And here we are with the screws installed.  I’ll remove this in a bit and make sure the tips of the screws are not causing any issues.



Now let’s get out the label maker and put a label on it so those folks peeking under the hood will (hopefully) understand what it is.


After running around for a bit, punching it in Sport HV mode to get some hours on the ICE.


Posted in Auto Repair | Leave a Comment »

Installing a Rostra FrontZone 250-1920-FZ Ultrasonic Parking Assistance System on a 2018 Honda Clarity

Posted by Russell Wright on June 9, 2019

I love my Clarity.  But, I don’t feel comfortable knowing where the long front end is when I park the car.  So I embarked on adding some ultrasonic sensors to the front.  I suppose I could’ve added a camera, but I was looking for something a little less obvious in the cab.

This is not for the faint of heart.  It requires drilling holes in the bumper of your new car.  I was, however, able to do this without removing the bumper.

I ordered the Rostra FrontZone 250-1920-FZ from  $93 + 12.50 shipping:  $105.50.  I found the kit to be very complete and professional.

Something to note.  The Rostra system has a CAN interface that is supposed to detect when the vehicle is moving a certain speed (off at 10 mph and on at 6 mph) and disable/enable the system.  This only works on 11-bit CAN systems and my experience with attaching it to the OBDII port with the included connector has been disappointing.  I keep getting the E0 message.  At this point I’m not sure whether there is an incompatibility issue or not, as I understand most CAN-B (29-bit) systems can understand CAN-A (11-bit systems).  I have sent a question in to Rostra, but as of 6/9/2019, they have not responded.  I’m going to be rather torqued if they never respond!

“If the ECU is disconnected or getting incorrect information from the CAN-BUS interface, “E0” will be displayed and the system will sound 5 beeps consecutively.”

I first had to determine where to mount the sensors.  I decided I wanted two of them to be low (closer to the curb) and the others to be in the approximate locations recommended by the installation template.

For the sensors in the bumper, I measured from the car centerline…approximately 795mm.  After measuring, I pushed on the bumper and looked behind it with a light to make sure I could see where I was pressing and there was adequate clearance.  I marked the distance with a grease pencil and then measured to center the vertical distance.  I used a level to plumb the measuring lines.  I double and triple checked my clearance.



For the low sensors, I installed them in the black plastic trim area.  Perhaps I should’ve gone lower, but this seemed to be a good compromise of keeping them at the recommended 12” minimum height.  I drilled these first and actually used the drill for a metal bumper, so I had to install the rubber grommet retainers.  No big deal…they fit nice and snug.  And no painting required!



I painted the two mounted in the bumper with some white enamel paint.  I looked at trying to exactly match the pearl, but the would’ve required spending $75-100 on a multi-step paint.  Close enough.  Finish them off with some rubbing compound and a little wax.


The wiring part came next…and this was not easy.  I drove up on some ramps and removed one of the under covers.  This required removing 14 or 20 clips and a couple of bolts.  Not hard, just watch out for dirt dropping in your face!  I didn’t get any good pictures of this part of the process, but here are the clips and the cover, so you can get the idea.  This allows access for the wiring.

To get the wiring to my holes in the bumper, I used a piece of stiff wire to which I taped the ends of the wiring harness and fed them from below to the hole in the bumper.  I used a pickup tool or pliers to grab the end of the harness I was feeding from below.



Now for the part that took me hours to figure out.  I needed to get the harness through the firewall, but I really didn’t want to be drilling holes, mainly because there’s just not much room to work.  After messing around for hours, I finally determined I could shove the harness through the side of one of the big wiring harness grommets.  Here are some pictures of this from the firewall side.  Of course, you have to have access on the cabin side as well.

This first one shows how I was able to lift up the side of the grommet and, using a long screwdriver, pull it away from the edge enough to shove the harness through.  You can see the grommet that was on the wiring harness that would be intended to fit in a drilled hole.


Here’s another shot showing more of the large factory wiring harness grommet.  The red wire is my trailer hitch power wire I ran when installing my trailer hitch wiring.


And here’s another shot.  There is a factory plastic clamp (unused) that I commandeered as part of the wiring harness retention system.  I subsequently secured the harness with wire ties.


To access the cabin side of things, you can remove some trim pieces to gain access.  There are basically four trim pieces I removed.

The driver’s dashboard lower cover.  It just pulls away when you pull in the clip areas.  No screws to remove.


Here are some pics of the back side of the dashboard lower cover so you can see the white clips and the connector locations.



The driver’s kick panel.  Pull it toward the passenger side to remove the clips.


The door sill trim piece comes off by pulling straight up.  Not sure if you really need to remove this or not.


The side cover for running the wire for the display.


Location of the clips on this cover.


With the cover removed you can easily run the display wire to my location of choice.


This is my display location, before it was stuck down with the included double-sided tape.


Here’s the final location.  Very unobtrusive and out of the way.


To be able to turn the whole thing off, should it decide to beep randomly (and to account for taking it through car washes), I installed the included switch in the right hand blank.  The blank pulls out and I did some drilling and modification of it to allow the switch to snap in.  Again, you can’t even see this after installation.



After it was all hooked up I did some testing.  I used a small Pelican case as my target.  Here are the display readouts along with the associated tape measure “read out.”


The sensor is actually a 2-3 inches back.


This is the measurement to the bumper.  Anything less than 12 inches causes the display to beep constantly…as in “don’t go any further!”


Pretty accurate.  About 22” inches from the driver’s side.


At about 30 inches from the passenger side.


Right in front.


Measured from the license plate…that sticks out about 3-4” from the sensor location.


Overall, I’m very satisfied with the accuracy.

Posted in Auto Repair | Leave a Comment »

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.


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.


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.  🙂


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.”


For the Value I use this expression:


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:


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.


This is what the expression looks like:


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.


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.


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’].


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.


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:



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.


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


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


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


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.


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’.


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


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!


We get the value for our admin email.


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.



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

Give Blood to Your Workflow

Posted by Russell Wright on February 13, 2019

Give the gift of life.

What does this have to do with SharePoint workflows?  Last week I was teaching our Mission: Automation class where we cover InfoPath and workflows created with SharePoint Designer.  One of the things we like to do in these classes is take some ideas from our students on business problems that they are trying to solve using SharePoint workflows and attempt to outline the solution and partially implement it during class (if it doesn’t become overly complicated).  This particular idea was generated by Janice and here was her problem.

Janice is the blood drive coordinator at her place of business.  She wanted to figure out a way to use SharePoint and InfoPath to allow people to register for times to give blood.  I thought this sounded like a great example of a scheduling application that many people could find other applications for, so I started down the road of creating the “Blood Drive Scheduling Application” during the class.  It uses an InfoPath form that an employee fills out to reserve a time slot for donating blood.  In this case, there are two donating stations and each one is scheduled at 15 minute intervals during the day.  In other words, for each 15 minute interval during the day, we can schedule a maximum of two people to donate at one time.

If someone changes their mind about the time slot they’ve reserved, we want to give them a way to make a change.  However, we don’t want to give them the ability to change the original form…they will simply request a cancellation and submit another form for a new time slot.  Some of this has to do with who has security to the different lists that we’ll be using in SharePoint to solve this problem.

Now, before we get started let me say that there are a number of ways to solve this problem.  I was going for a solution that could be implemented during the class and illustrated some of the features of InfoPath and SPD workflows.  This is only one solution.  Much more elegant solutions could be implemented!

In this solution we’re going to use several lists.  Blood Drive Reservations will be a forms library where we’ll store the completed InfoPath forms.  Departments contains a list of the departments at the company.  Time Slots contains a list of all the available time slots during the day when a donation reservation can be made.  We’ll have a Cancellations list that we can use for someone to request a cancellation of their reservation.  Finally, we’ll use the built-in Tasks list to assign tasks when a cancellation is requested.

First we’ll start out by creating a custom list that has two columns.  One is the time for each of the 15 minute time slots during the day that we want to schedule and the other column is for recording how many reservations have been made for the time slot.  Here’s what the table looks like in datasheet view.


I created all the time slots for 8:00 AM thru 5:00 PM.

Next up is to create the InfoPath form and library in which the completed forms will be stored.  The form is pretty simple for our example.  Greg suggested we add the department field so there could be some competitive statistics compiled between the different departments.  Nothing like a little competition during blood letting!


The four fields defined in our form’s data source are DonorName, DonorEmail, DonorDept and TimeSlot.


The DonorName and DonorEmail are populated using default values obtained by calling the GetUserProfileByname method in the web service, UserProfileService, referenced at http://server/_vti_bin/userprofileservice.asmx.  This web service will return name/value pairs from the user profile store that is part of SharePoint and normally configured to synchronize with Active Directory.  The PreferredName and WorkEmail are used in this example.

The DonorDept field is populated by creating a data connection to a SharePoint list containing departments.  Nothing really complicated here.

Now, the time slot field is populated in a rather unique way.  The idea here is to filter the available time slots based on the value in Counter field in the Time Slot list.  We’re going to be updating the Counter field with a workflow whenever a new reservation is submitted.  We need to create a view on the field that is filtered to only show time slots when the value of the counter is less than 2.  I created a view called AvailableTimeSlots and added the simple filter as shown.

Now we’re going to use a trick to populate the DonorTimeSlot field.  We’re going to create a data source to an XML file and reference the SharePoint URL protocol supported by OWSSVR.DLL.  This is documented in the WSS SDK and is referenced as follows.  http://server/path/sitename/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List={ListGUID}&View={ViewGUID}.  The exact URL for our example is http://portal.awbikes.local/sitedirectory/bd/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List={BAA912CA-D4D4-4786-B2B8-B33DA8691CA4}&View={35F16EC8-64E9-4BCE-8C8A-CD3D142AD894}.

The GUIDs for the List and View can be found by selecting the Modify View link in the view selector and decoding the GUIDs in the URL that is displayed.


The List GUID and View GUID are URL encoded, and you can “un-encode” them if you want to (but you don’t have to) by replacing %7B with a left curly brace { and %7D with a right curly brace }.  %2D can be replaced with hyphens.  In the example above, %7BBAA912CA%2DD4D4%2D4786%2DB2B8%2DB33DA8691CA4%7D becomes {BAA912CA-D4D4-4786-B2B8-B33DA8691CA4}.

In the next dialog on the Data Connection Wizard, choose the option to Access the data from the specified location.

Give the data connection a name and make sure you automatically retrieve data when the form is opened and click Finish.

You can see the completed URL n the Summary section.  I like the way the URL looks when the GUIDs are un-encoded…just easier on the eyes.

On the list box entries for the DonorTimeSlot field you need to reference this data source, OWSSVR, and select the entries as shown.

This will populate the DonorTimeSlot list with the filtered list from the AvailableTimeSlots view.

When we publish our form we will promote all of our fields so they appear as metadata columns in SharePoint.

Now, on to our first workflow.

The first workflow is fairly simple.  When a new form is added we want to increment the value of the counter in the Time Slots list for the time slot selected.  If two people select the same time slot, the value will be incremented to 2 and the time slot will no longer show up as an available time slot on the drop-down list in our form (because of the filter we created for our AvailableTimeSlots view).  Within this workflow, we can also send a confirmation email to the donor.  If we really want to get fancy, we can send a reminder email 24 hours before the scheduled appointment.  Here’s the workflow in SPD.

First, let calculate the new counter value by retrieving the value currently in the time slot and adding 1.  We’ll save this calculated value in a variable called IncrementedTimeSlotCounter.

To retrieve the current value of the counter we perform a lookup which reads as

Select Counter from Time Slots where Time Slots:Title equals Blood Drive Reservations:Donor Time Slot.

We are matching up the Title in the Time Slot list (e.g. 8:15 AM) with the time slot selected by the donor on the form from the drop-down list.  Then we simply add 1 to it and store it in a variable.

Next we update the value of the counter in the Time Slots list.  In this example, we’ve created another step in the workflow to do this.

Here’s the screen shot of selecting the Counter field to update in the Time Slots list.

In the “Find the List Item” section, here’s the lookup.  Again, we’re matching up the Time Slots:Title field with the Current Item:Donor Time Slot field (Current Item being our item we’re working on in the Blood Drive Reservations list).

Now let’s create a workflow that sends a confirmation email and a reminder email.  We’ll send the confirmation email right away and pause until early in the morning on February 3rd and then send a reminder email.  We can do this with a new workflow that also starts when the item is created.  It is okay to have multiple workflows running.  Here’s what it looks like (nothing fancy).  We can reference the Donor Time Slot in the email by clicking the Add Lookup to Body button.

The second step we’ll use the Pause Until Date action and put in a pause with a hard date of February 3rd and a time of 4 a.m.  Just a quick and dirty reminder email.

We’re all set on the reservation side.  Now, on to the cancellation process.  Here’s the way the cancellation process will work.

We really don’t want end users to be able to delete reservation in the list and we can control that by creating a permission level that is similar to Contribute but without the permissions to edit and delete.  We can alter the security settings on the reservation list so that our Visitors have this permission level for the reservation list.

If someone wants to cancel, they can insert an item in the Cancellations list.  They can select themselves from a drop-down list of people who have reservations and click OK to create the request.  I chose to do it this way so we can get a good match on the Donor Name.  There are other ways to handle this, but this is quick and dirty.

Once they create a cancellation request, a task is assigned to Connie via a workflow.  In this workflow I Iogged some variables to the workflow history list for debugging purposes and then assigned a task to Janice, er, I mean, Connie using the Collect Data from a User action.  Logging information to the history list is a very valuable technique for troubleshooting and validating information in the workflow.  I normally use three actions in sequence to do this:  Build Dynamic String, Set Workflow Variable and Log to History List.

Tasks are very important to understand in workflows.  They are so important they get their own categorization in the Actions selection list.

Collect Data from a User allows you to assign a task and, when that task is edited, a custom form is displayed with fields on it that you define in the workflow.  In this case we are going to ask Connie if it is okay to delete the reservation with a Yes/No drop-down.  Another workflow, Delete Reservation, was created to start on a change to the Tasks list.  It’s job is to determine what the response was (Yes or No) and lookup and delete the reservation.  One of the reasons this method was chosen was because the workflow runs with the credentials of the person who started it.  Connie will have the permissions required to delete the item in the reservations list, while the donor does not.  It also allows her to verify that the person has requested to delete their reservation and not somebody else’s.  Here’s what the Collect Data from a User action looks like.

Next we’ll take a look at the Delete Reservation workflow.  This workflow is created on the Tasks list.  It has three steps.  In the first step we set some variables that we’ll use in the workflow.  These are very important because we are looking up the IDs of the Blood Drive Reservation we need to delete as well as the Time Slot we need to decrement so it is again available.  The key to these lookups begins with the fact that workflow tasks hold some very important information.  The contain a “foreign key” back to the ID of the item in the original list.

The first thing we need is the ID of the reservation we might be deleting.  To get this we choose to lookup the ID field from the source Blood Drive Reservations.  We are going to find this by first matching the name selected in the Cancellations list with the Donor Name in the Blood Drive Reservations list.  To find the correct item in the Cancellations list, we need to reference the Workflow Item ID in the Tasks list (our current list) and match that to the ID of the item in the Cancellations list.  It is very important to understand that you have access to the Workflow Item ID in the Tasks list.  This can be very confusing if you are new to SharePoint Designer workflows, but with some patience it can be understood without having to be a programmer.

Once we have the ID of the reservation, we can find the ID of the time slot that is referenced on the reservation.  We’ll use this to update the counter on the time slot.  We want to find the ID of the time slot so we match the Donor Time Slot on the Blood Drive Reservations list (e.g. 8:30 AM) with the Title on the Time Slots list.  We can find the Donor Time Slot by matching the ID of the Blood Drive Reservation with the ID that we looked up in the previous action and stored in the variable ReservationID.

The next step is just some more logging of messages and variables to the history list for debugging purposes.  In this example I’m logging the IDs I looked up to insure that I have the right ones.

The final step is to actually delete the reservation.  First we look up the current value of the time slot of the reservation to be deleted and subtract 1 and store the result in a variable called DecrementedTimeSlotCounter.  This is easy to do since we have already looked up the value of the ID of the time slot that we need to update in the Set variables step.

We can then update the counter value, again using the ID of the time slot that we previously set in the variable TimeSlotID.

Finally we can delete the reservation by using the ID of the reservation that we previously stored in the variable ReservationID.

I know this has been a long post, but hopefully it helps some folks out there become more familiar with SPD workflows.  This solution is far from perfect and there are several other ideas I would probably incorporate, but it provides a good demonstration of creating a scheduling application with workflows.  I hope you’ve enjoyed it and learned at least one thing (always my goal).

I’m on my way to bed so I can get on a plane tomorrow to do some training in San Antonio, Texas…a short flight from Dallas.  Y’all take care!

Give the gift of life…donate often!

Posted in SharePoint, Workflow | Leave a Comment »