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.

Reports, lots of them, at the same time?

So my attempts to get my sales representative reports to export to different directories based on the sales rep number hit a snag.  Mainly that Vineyardsoft KnowledgeSync doesn't want to let me dynamically select a report export location.

There is a way around this though!  It just takes some set up work!  I went and made an event for each rep instead of one event that spits out all the reports.  Now I currently have 48 sales reps, so the first time set up was a bit painful, but upkeep shouldn't be too bad when I add the occasional new rep.

There was a slight additional snag though.  When exporting the records to the directories they were getting mixed up.  For competitive sales reps, this could be a big problem.  I found if they were run quickly one after another is when they were getting this mix up.  So it was time to make a convention.  I opted to set a schedule for each one that uses their sales representative number as the minute for when the report runs.  Now my sales reps all have low numbers, so chose a convention that is right for you.  It could be one every minute, or the last two digits of their number, etc.  Now I have them all spaced out and no more report export mix ups.

Remember to make a new schedule, don't edit the current one.  Otherwise all your reports will still be on the same schedule.

These monthly reports are run on the 4th of each month to account for weekends and weekends with holidays attached.  I opted for after 10 am because we run overnight functions in Macola ES (updating costed bill of materials, if you are interested) so we needed to make sure the reports don't run until we come into the office and post the invoices from the previous workday.