The Attivo Blog

We all know that Event Manager is a terrific data-entry slave for businesses. Need Table 1: column A totaled and copied into Table 2:column B?  No problem. Need Table 3: column B tallied and copied into Table 3:column C? Cake. Need Table 1: column A to somehow related all the way across to Table 3: column C? Might be a little messy in SQL, but Event Manager can handle it with relative ease. But sometimes our linking between tables forces Event Manager into the position of needing to separate rows that later we need to be totaled, or vice-versa.  The quick solution to this would be to populate a free-field on each individual row with that total amount; but it's easy to see that so many "writes" are unnecessary and can cause confusion later on.  Instead, it is much more beneficial to split up the single event into 2 or more separate queries. One of The Attivo Group's customers recently needed to implement an Order Acknowledgement that would be automatically distributed when certain criteria were met.  Event Manager satisfied that initial requirement easily with a simple Order Acknowledgement Event provided with the software.  Then the customer recognized that the canned event contained Total Amounts on the order that deducted back-ordered items. While this logic makes sense to many businesses; this customer preferred to include the total amount of all order lines on the acknowledgement.  When initially contacted about this task, I explored the idea of combining the total process into the existing query, but found that I was always forced to convert linked tables from outer joins to inner joins, thereby removing my ability to display multiple rows on the acknowledgement itself.  With that idea shelved, I split my task in two. First I wrote a query to total all the order lines at the line level, and store that total on the order header.

SELECT sum(line.qty_ordered*line.unit_price) AS "total", header.ord_no, header.ID FROM (line LEFT OUTER JOIN header ON line.ord_no = header.ord_no)   GROUP BY header.ord_no, header.ID

Then the triggered event stored the "total" above in a free field at the header level, virtually next to the "incorrect" amount. 

update header set freefieldX={total} where ID={ID}

Finally, I then added an additional filter to my separate Order Acknowledgement query/event that made it ONLY query orders that had a filled in freefieldX.

SELECT line.id, line.ord_no, line.(...),header.freefieldX, header.ord_no, header.(...), etc... FROM (line LEFT OUTER JOIN header ON line.ord_no = header.ord_no)  WHERE header.freefieldX <> 0 AND header.freefieldX is not null, etc...

Notice I did not group the resulting rows together this time, as I no longer needed them to be totaled; this was already done in the previous event!  I then let the second Order Acknowledgement run as designed, with the single exception of replacing the default TOTAL AMOUNT with the amount stored in freefieldX. While this nearly completes the event's multi-step procedure, there's two small steps needed to be absolutely sure that query/event #1 always runs before query/event #2.  First, I make sure the queries/events are on the same schedule in the system.   Then I set the priority of the first event to be higher than the priority of the second event. These steps prevent event #2 from waiting until the next scheduled time before processing - thus allowing event #2 to process immediately after event #1 completes.

I love my GMC Yukon XL. Easy to drive, roomy, the dog loves it, and pound for pound, not bad on fuel mileage either. I also love my '57 Chevrolet Bel Air - and I hope I can get it put back together some day. Check it out... I'd love to own one of those new 2010 Chevrolet Camaros...might help extend my midlife crisis. And the Cadillac CTS - what a great car! But don't ask me to buy a Chevrolet Cobalt. Not going to happen. If I need a small car (and I do), I will probably buy a Honda Accord or Civic.  Honda really knows how to build a great small car.  They last forever, and rarely break.  Nothing against people who have purchased a Chevrolet Cobalt - but it just isn't the first small car that I would consider buying.  I just know that Honda builds a high quality, fuel efficient small car. What does this have to do with Productivity?  A lot.  I think it is very important to do what you are very good at, and keep getting better at it.  Forget the rest.  Why are we forcing GM to build small, fuel efficient cars?  Can't we let them build the big cars and trucks, and leave the smaller cars to the experienced like Honda, or the new upcomers like Tata Motors in China?  GM actually makes money selling those big cars and trucks, and loses money on the small ones. Consider that we are a global market, and that so much of the components and technology is made right here in the USA anyway.  What I'm suggesting does not mean our auto industry will shrink any.  It just means that Toyota or Tata or Honda might sell more units than GM, since they are making cars for the masses.  But GM can own a very big share of the truck and luxury vehicles market - where they actually know how to make money. So, what is it that your company is really good at?  Got any losers you need to unload?  This is a great time to do just that - a little spring cleaning will improve your bottom line.

QuickBooks doesn't do manufacturing, does it? Well, kind of. It does have a basic bill of materials, which will consume components and create a parent item. But that's where it stops. Intuit QuickBooks Pro, QuickBooks Premier or QuickBooks Enterprise Suite have never been known as robust manufacturing solutions. Manufacturing Systems Designed to Work with QuickBooks First, I should tell you that our company, The Attivo Group, is an Intuit Solution Provider, and that we sell and support QuickBooks Enterprise Suite.  We also sell and support several tier 2 ERP (Enterprise Resource Planning) systems such as Microsoft Dynamics GP and Exact Macola.  These are wonderfully capable systems, but may be more expensive or overly complex for the smaller, $1-10 million manufacturer. The reason I wanted to point this out is to help small manufacturers understand that they should implement good planning and control systems, but they shouldn't overspend to achieve the goal. Fortunately, there are several great software applications that are designed to work with QuickBooks to support manufacturing needs.   Here's the great part - you don't have to disturb your accounting department to implement them!  There might be some small process changes to deal with, but no major overhaul of your entire business.  This is like remodeling the house without ripping out the kitchen or either of the bathrooms. The key is understanding those requirements, and choosing the right solution.  Although it might seem like there are a lot of software choices, the choices are narrowed when specific requirements are considered.  First, narrow the field with basic choices, by identifying the type of manufacturing support that you need, such as:                     -  Make to order                     -  Configure to order                     -  Make to stock                     -  Process Manufacturing, etc. Then, consider unique requirements such as compliance needs, serial or lot traceability, project management needs, labor tracking, outside processing or other needs.  Each application provides a different set of functions, and in some cases are designed to support particular types of industries.  So get some help on determining your needs before you start looking - or you will be looking for a while, since there are over 100 options out there.  Some of the more widely used applications are (click on them for more information):

• Fishbowl Inventory
• MISys Manufacturing
• Exact JobBOSS
• Exact MAX
• AdvancePro

Check out the Intuit Marketplace, and see for yourself.  There is a lot of good software out there to manage your operations.  Our experience is that most of the $1-10 million manufacturers are using QuickBooks for accounting, and Excel for everything else, as tedious as that is.  Even though Microsoft Excel is a great application, it's time to put in an easy to use system that can really save your company time and money.  And you don't have to throw QuickBooks out to do it!

What the economic melt down has meant to business consulting and software sales

The majority of our clients have been predominantly smaller mid-market sized companies, typically privately owned, with revenues in the $5-100 million range.  They have been absolutely frozen in place since about October of 2008.  Unable to make any decision whatsoever, they have stopped all spending on marketing, certifications, automation or any kind of improvement initiatives of any kind for their business.  Six months have now passed.  Still no signs of life.  You can probably guess what happened to the business software projects that we were supposed to work on with them.  Forget new system implementations.  The few projects on the street have had so many vultures flying around them that they are hardly worth chasing.  Not a very healthy situation at all. We've made a few very important shifts in our business model that have really helped a lot.  Here's a few: But here's the key - smaller, bite-sized projects.  Instead of $50-150,000 projects, we're working on $5-25,000 projects.  And they are coming from the $1-10 million revenue sized companies.  Not the mid market.  (Still no signs of life there - they still have their hands in their pockets holding onto their wallets). Service industries have less complex system requirements than manufacturers, but they have needs nonetheless.  We are working on some very interesting projects with service companies, and have gained instant entry into the market with the addition of Will Breiholz as our new Vice President of Business Development.   Will is a 12 year veteran of the Intuit Marketplace, and brought along plenty of knowledge and contacts with him. Will most recently served as General Manager for BQE Software, the developer of the popular BillQuick Software.  BillQuick is a professional services time and billing system, that is used by architects, engineers, attorneys and others that need to track their time and bill customers against projects or contracts.  We're implementing tier-two sized ERP offerings such as Exact MAX, Exact JobBOSS, MYSis and Fishbowl that are designed to integrate with QuickBooks for the accounting needs.  This saves clients a significant amount of money, since most can get away with basic accounting, but still need the functionality of bigger systems for their operations management.  This makes a lot of business sense to me.   Not only that, Eunice, the 40-year veteran bookkeeper, does not have to give up her QuickBooks system and learn a new system (which would have killed the deal, by the way).   I don't want to go too far out on a limb with this, but I see this as a real growth market for us. I welcome any and all comments on how you have changed your business in these tumultuous times - we could all use some good hints on how to stay alive!