Russ' Do It Yourself Home Workshop

Finding Fixes to Just About Anything and Everything

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.

http://sharepoint.sureshc.com/2016/04/how-to-set-character-limit-for-multiple-lines-of-text.html

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

//code.jquery.com/jquery-3.2.1.min.js
< script type="text/javascript">
$(document).ready(
    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. 

image

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

image

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

image

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

image

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

image

Posted in Uncategorized | 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.

BH-M32-1

BH-M32-2

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.

image

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

IMG_20190629_065104

The package contents, unpacked.

IMG_20190629_065423

The installation instructions.

IMG_20190629_065307

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.

IMG_20190629_065650

The meter is just the right size to fit there.

IMG_20190629_065715

See how it will look when installed?

IMG_20190629_065704

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.

IMG_20190629_065216

IMG_20190629_065223

Now we have the coils exposed.

IMG_20190629_065204

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.

IMG_20190629_065957

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!

IMG_20190629_070308

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

IMG_20190629_070323

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.

IMG_20190629_070856

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.

IMG_20190629_070921

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.

IMG_20190629_075436

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.

IMG_20190629_075411

Video:

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.

IMG_20190629_080149

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

IMG_20190629_090100

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 SavingLots.com.  $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.

IMG_20190527_162615

IMG_20190527_162630

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!

IMG_20190527_162816

IMG_20190527_162826

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.

IMG_20190527_163020

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.

image

image

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.

IMG_20190527_194340

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.

IMG_20190527_194315

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.

image

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.

image

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

IMG_20190527_185553

IMG_20190527_185559

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

image

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

image

The side cover for running the wire for the display.

IMG_20190527_193915

Location of the clips on this cover.

IMG_20190527_193904

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

IMG_20190527_193854

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

IMG_20190604_171954

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

IMG_20190609_103732

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.

 

image

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

IMG_20190604_171901IMG_20190604_171921

The sensor is actually a 2-3 inches back.

IMG_20190604_171928

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

IMG_20190604_171954IMG_20190604_172017

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

IMG_20190604_172052IMG_20190604_172108

At about 30 inches from the passenger side.

IMG_20190604_172202IMG_20190604_172224IMG_20190604_172232

Right in front.

IMG_20190604_172306IMG_20190604_172323

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

IMG_20190604_172341IMG_20190604_172358

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.

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

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.

1-TimeSlots

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!

2-SignUp

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

3-DataSource

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.

http://portal.awbikes.local/SiteDirectory/bd/_layouts/ViewEdit.aspx?List=%7BBAA912CA%2DD4D4%2D4786%2DB2B8%2DB33DA8691CA4%7D&View=%7B35F16EC8%2D64E9%2D4BCE%2D8C8A%2DCD3D142AD894%7D&Source=http%253A%252F%252Fportal%252Eawbikes%252Elocal%252FSiteDirectory%252Fbd%252FLists%252FTime%2520Slots%252FAvailableTimeSlots%252Easpx

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 »

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 »

Installing a Trailer Hitch on a 2018 Honda Clarity

Posted by Russell Wright on December 25, 2018

I’ve owned a 2002 Prius, a 2005 Prius, a 2010 Prius and my wife now has a 2017 Prius Prime.  They have all had trailer hitches installed for carrying bikes and towing my little 5’ x 8’ trailer for those Home Depot runs.  But now since we’ve joined the PHEV ranks, I recently expanded our PHEV fleet with the addition of a Honda Clarity.  Unlike the Prius Prime, there is only one manufacturer of a trailer hitch for this car (at least at this point in time).  Torklift has an Ecohitch (Part numbers: x7377 (2”), x7378 (1¼”)) that is on the pricey end of what I would normally pay for a hitch, but it seems to be well made and doesn’t stick out like a sore thumb, so I bought one.  It took a couple of weeks to get it since it was around the Christmas holidays and their site said they were experiencing a “high order volume.”  Good for them!

The hitch comes with a set of printed color instructions that are pretty good, but no one ever seems to get enough pictures for me.  I like pictures, so here’s my contribution to this project, should you choose to accept it.

As an overview, you need to remove the two tailights so you can remove the bumper facsia (bumper cover) to gain access to the bumper crash bar.  The hitch mounts underneath the bumper crash bar with new longer bolts that are supplied.

From tool standpoint, you’ll need:

  • Phillips screwdriver (misc screws)
  • Torx screwdriver (bumper cover screws)
  • 8mm socket (for taillights)
  • Flat screwdriver (for loosening pop rivets and trim panel fasteners)
  • Needlenosed pliers (for squeezing wire stay to remove it)

Begin by opening the trunk and removing six fasteners (three on each side) that hold the fabric trunk panels.  This should take about five minutes.

image

image

After these fasteners are removed you need to remove the wear molding that extends across the trunk.  Begin by removing the trunk latch cover.  It unsnaps pretty easily, so don’t overdo it.

image

Here’s a blurry closeup of the trunk latch cover.

image

Here’s a view of the fasteners that hold the wear molding in place so you can get an idea of where to pull up.  If you aren’t familiar with these, sometimes it can feel like you’re going to break something when you pull up.  That shouldn’t happen on new plastic parts.  When you reinstall, you just rap them with your hand to pop them back in place.

image

Just another pic of the wear molding.

image

After peeling back the fabric panels, you should be able to get to the back of the taillights.  Here you will need to remove four 8mm nuts, disconnect the connector and then simultaneously press both clips to remove the taillight.  The taillight should slide out directly towards the back.  You’ll notice in the pictures there is a fastener on the forward part of the taillight that fits over a clip.  It should slide off from this connector.

image

Here’s a back view of the forward part of the taillight showing the sliding connector.  I actually popped the connector out because I didn’t know it should slide on and off.

image

Here’s a removed taillight.

image

Here’s a pic of the empty taillight socket.

image

Here are a couple of pics of the back of the taillight locations.  You can see the connector and the holes where the taillight studs and clips go.  This is on the driver’s side.

image

This is on the passenger’s side.

image

The next couple of steps are pretty straightforward.  There are two big black torx screws that need to be removed from bumper fascia.  So, that means you’ll need a torx screwdriver to remove them!

image

Now its time to remove the phillips screws from each wheel well.  If you don’t have splash guards (mud flaps) you’ll probably have two on each side.  If you have splash guards there will be an extra three screws holding the splash guards in place and a panel fastener on the bottom of the splash guard.  Remove all these screws.  I don’t have a picture of this.

Next, under the bottom of the bumper cover are six (or eight) panel fasteners.  If you removed spash guards you already removed two.  You’ll have to lay down on your back and use your screwdriver to pry the heads up so the “pop rivet” panel fasteners will come out.  This picture shows them after they’ve been removed (along with the crash bar).

image

Now comes the scary part.  From the wheel well area grasp the bumper fascia at one corner and pull firmly up and away from the vehicle.  The fasteners should pop loose as shown in the following pictures.

image

You have to put your hand in the crevise and pull!  Pop, pop, pop it goes!

image

Keep going…you’ll get to the fasteners you can see that were beneath the taillights.

image

Once you get all this loose, the bumper cover should come off and reveal the crash bar, which is held in place with six bolts.

image

Disconnect the wire stay from its mount point by squeezing the backside of it with a pair of needlenose pliers.  This has to be moved aside as there is not enough clearance behind the trailer hitch for it to remain in its original location.  I wrapped a little extra tape around it just to prevent any future chafing.

image

Here’s another pic of the wire stay removed from its original mount point.

image

Now you can mount the hitch behind the crash bar and install the six bolts.  Torque to 85 ft-lbs.

image

To allow room for the bumper cover to be reinstalled, you need to trim a section out of it that is located around the receiver.  The section to be removed is approximately 4-1/2” wide and 4” deep.

image

Here are the recommended dimensions for material removal.

image

Another view of the bumper cover after the material has been removed.

image

Some pictures showing the fit after the trim job.

image

image

I also pulled a power wire through the grommet and tied it off under the car for any future power requirements for the trunk area (e.g. for a trailer wiring harness).

image

Now with the assistance of your helper, you can place the bumper cover back over everything a button it up.  As they say, put everything back in the reverse order of how you took it apart!

image

It should look something like this when you are done.

image

Posted in Auto Repair | 10 Comments »

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 »