Timing is everything

Holiday weekends are wonderful things.  I especially love them because I almost never take vacations.  (Except to visit my in-laws, and I wouldn't really call that a vacation.)  We just had Memorial Day and I loved the extra time with my family.

They do have to be taken into account though when deciding on when to schedule your reports.  The audience and competency of your audience can play a role in this too.  In my organization we have two major groupings for our Event Manager reports.

1. Staff that works at our office directly for the company.
2. Sales representatives that work outside of our office that we have contracts with

Lets look at these two groups separately.

The internal staff receive reports that state what orders are past due.  It looks at the expected ship dates on all of the line items of all of the open orders and compares them to the current date.  The report runs Monday through Friday at 10 am.  This allows for the previous day's invoices to be posted and those items to be moved to the history files and thus, not be included.  However, because of a holiday, those invoices don't get posted until a day later and the report runs while everyone is out.  Therefore the recipients had a report waiting for them when they got in that made it look like everything was running late!

This type of problem is solved easily enough with a "Please remember to ignore the reports that are generated on the holiday".  The other option is to use the "run on holiday" flag in the schedule tab.  So far I have just opted to tell the folks.  It has also worked as a nice test.  If they ask too many times about the data in the report, after I have told them to ignore it, I know not to trust them with sensitive information and sharp objects.

The second group, the sales representatives, get weekly and monthly sales reports.  I have discussed these in previous posts.  These we wanted to be more careful with since they go outside of our company.  When I first chose the date and time for these, I planned very carefully.  We wanted to make sure there were no sales that were missed.  So, all sales related reports always run after 10 am.  That ensures that we have had time to get into the office (even if there is snow) and post the invoices.  (If the lady that posts invoices normally is out of the office, I even cooked up an event to remind the rest of accounting to do so.  It runs at 9, giving them an hour.)

If you are wondering why we don't post right after the invoices are run, it is because there are processes overnight that run.  We recalculate the cost of the bill of materials to account for any components that might have been received in and shipped out the same day.

To use the holidays feature of Event Manager, first make sure your holidays are set properly.  Go to the Administrator application and check the holiday schedule in the software setup section.

Holiday schedule management

 You can also add more holidays that your company may observe.  Make sure all holidays that you have off have the "Active" button checked.

Active holiday checkbox

Now, make sure all of your events are set properly.  Daily things are generally the ones that you don't want to run on holidays.  Just check the box if it should run on a holiday, and uncheck if you don't want it to run.

Check box to run on holidays, uncheck to not run on holidays.

One nice feature is that you do not have to actually edit the schedule.  So you can have reports using the same schedule that do and do not run on holidays.


So that is all we need to do to account for holidays, right?  We can just let Event Manager work while we relax?  Yes, but we still have to chose our run times carefully....

On the monthly reports, any holiday that occurs on the first of the month (New Years), would cause those reports to never include the sales from the last day of the previous month.  So we can't run them on the 1st.  If the 1st falls on a Saturday, we have to wait until the weekend is over, so we can't use the 2nd or 3rd either.  If the 1st falls on Friday, we will be out of the office for the holiday and the weekend, and so we have to wait until the 4th to be in the office again to post those invoices from the end of the month.  Assuming you want a "set it and forget it" report, the 4th is the day to use as a convention for monthly reports.  Even if the 4th is a Saturday or Sunday, you will have been in on the 2nd to post those invoices.

On the weekly reports we have to take into account that many holidays are observed on Monday.  So, we can't run the reports on Monday or the sales from Friday would not get included in those reports.  So for us the day we run weekly reports is on Tuesday.  This allows for a Monday holiday.  If there were the possibility of two consecutive holidays then we would have to move it out to Wednesday.  There are only two sets of holidays (in the United States, at least) where there are two holidays in a row that we could possibly be closed.  The first is Thanksgiving and the day after Thanksgiving.  However since these are always at the end of the week, they don't pose a problem.  The invoices from Wednesday sit until Monday and get posted right at the start of the week before anything runs.  The other holiday set is Christmas Eve  & Christmas.  In our office, we have to work Christmas Eve.  Therefore, we don't have to worry about the consecutive holiday scenario.  If we luck out and start getting Christmas Eve off, I'll have switch the day to Wednesday.

So, I don't mind letting Event Manager run on the weekend and holidays while I relax with my family, but we do have to make sure that we plan for them carefully.

The next holiday will be July 4th.  So, my monthly reports will be running while I enjoy the time off.  Everything will be posted on the 2nd, and all I have to remember is my sunscreen.

Countdown

One of the notifications I have in Vineyardsoft Knowledgesync event manager is one to let us know how many of our products don't have a box size and weight on file.  We have a guy in the warehouse who is supposed to let us know when he ships something with no box and weight size filled in, what the correct one is.  Unfortunately, that hasn't happened much, and so we have quite a few that need box and weights filled in.

We started with over 5000 sku's that need this information.  It has been a slow effort to get it to trickle in.  So to better track the info, I was asked to include a count of how many items are on the report.  Previously we were copying it to Excel and seeing how many rows there were.

So the first idea is to add count(dbo.imitmidx_sql.item_no) to the query...

First thought

However, I had a bad feeling that wasn't going to work out too well.... I was right.

Error message

So, in order to bypass this lack of "group by" clause, I opted to just do a second query definition.

New query definition

I copied my original one, and added a "part 2" to the name and description to keep them straight.  Then I removed all of the columns and put in my counter.

New additional query

Presto!  We have a counter now!  3546 more items to go!

Reply to

Silly as it may sound, our event manager received an email response from an angry sales representative.  I forwarded it on to the person he was actually mad at.  I guess the lesson to be learned from that is to make sure that someone is getting emails that are being sent in response to the event manager alerts and reports.

Dropbox!

Our CEO has been pushing to have more of our files available to our sales representatives via Dropbox.  He is hoping that in doing so, the sales representatives will be out in the field with their tablet and able to just pull up a copy of the catalog or price list while meeting with customers.  He also asked if it was possible to deliver the sales reports to them in this manner.

So, I remembered that when we were installing Vineyardsoft KnowledgeSync that we were told it could save files to a location for us as a result of an event.  So, I decided to test out whether that location could be a Dropbox folder.  Turns out it can!

You may remember that for my sales representatives reports I have all of the sales reps set up as separate subscribers with their reports staggered slightly to avoid mix ups.  So, in each of the subscribers, under their "copy" tab is the location of a Dropbox folder for their reports to go to. 

Put the location of the folder in these fields

These are confidential with each sales representative agency, so each agency has their own folder for these.  We have a different folder shared with them for marketing documents that are not confidential.

If you want to try this at your company, get a commercial account on Dropbox, and set up your folders how it makes sense for your application.  Make sure to do your testing before you start sharing the folders, because after they have been shared, those other people can start seeing the files.

On your event, in the "copy" tab make sure that you have the boxes checked for including files and reports on copy operations.

Make sure these two boxes are checked

Then in the "subscribers" tab, click on the check-box for "copy" for those people whose folder should get a copy of the file, or report being generated.

In this example, rep 7 is getting an email and a copy in their Dropbox folder, but I am just getting an email.

Saving files to directories like this is an excellent time to add dates to the file names.  If you have not seen my post on how to do so, check it out here!

So get out of the rut of just sending everyone emails as a result of your events and try to some of the other delivery options available via Vineyardsoft Knowledgesync!

Where Did It Go?

So today was the first day my 48 sales representative reports went out all by themselves.  I was even in a meeting for half of the time and just heard my phone chirp once a minute as I received a copy of each.  I was very pleased that later in the afternoon one of my sales representatives happened to stop into the office.  I asked him if he got his report today, bit was surprised that he had not.  I even checked his phone myself.

So I went to our firewall, Untangle and checked the email traffic and saw that about a dozen were quarantined by it.  So, remember that sending out lots of reports in a row that all have the same text in them looks an awful lot like spam to the untrained, computerized algorithmic eye.  When you set up your email address that your event manager software is going to use, make sure it is set as a safe sender in your firewall!

Who is getting this?

You might have noticed that if you add lots of subscribers to an alert, none of them know about any of the others.  It can be a good thing if you are quietly keeping an eye on someone making sure they act on their report, but sometimes you know other folks are getting it, but knowing who all might also be acting on it can be tricky.

So, a good practice for any multi-recipient emails is to just add a list to the bottom of the email.
It is super easy to do with just a pinch of old time HTML.  You may remember your unordered list tags <UL></UL> and list items <LI></LI>and your nice small text tag <H6></H6>  Just combine them as below:

<H6><UL>
<LI>Recipient 1</LI>
<LI>Recipient 2</LI>
etc.
</UL></H6>

Now when you do it, take out all of the extra line returns before saving it, otherwise it makes the list longer than it needs to be, but for legibility here I used them.  If your staff has trouble with small font, bump them up to an H5 instead.

Conditional coded HTML

Lets face it, if a lazy person can avoid clicking a button to find out how much work they have to do, they will.  This is especially important when deciding how Vineyardsoft KnowledgeSync should send data.

One of our reports tells the folks in the warehouse when we show a negative inventory on hand.  Most likely it is because someone did an inventory adjustment while not realizing that the product was just misplaced, or at someone's desk for demonstrative purposes.  It happens, deal with it, move on.  That said, no one wants to have to go looking to see what happened, and do another inventory adjustment.  It's much easier to pass the buck or pretend you didn't see the email.

Which brings us to our trusty event manager.  So, I could do an absolutely beautiful report in Crystal with lots of color and information.  But then it would get sent as an attachment.  Does anyone really think that the warehouse crew will happily open that attachment every day?  Most likely not.  So, I have the information in the body of the email instead, so that it pops right up for them.  Some of them even have their work email sent to their phone, so I know they can see it without having to download a PDF viewer.

Did you forget all of your high school / early college HTML classes?  I hope not, because that is what Event Manager uses to pretty up your email notifications.

Now my boss doesn't readily understand the differences between HTML and Crystal, so something that is easy to do in Crystal, isn't necessarily so easy to do in HTML.  One of which is changing the formatting based on the data.

In order to do this, I created a calculated field in my Query Definition.  I then wrote it do return a <B> tag in the desired condition.

In the body of the email, I put the tag in the place where a <B> may or may not go and write in a </B> tag to close it regardless of whether it is there or not.  Voila, conditional formatting!

This example was something easy, just making something bold.  But the same principle could be used to other things also.   Just remember you might need to do the computation twice to make an open tag and a closed tag, depending on what type of formatting you are trying to do.