No SQL For You! One Year!

Soup Nazi

The Need for Structure

In my years of working with M2M, one of my biggest frustrations is that it allows you to do almost whatever you want with it. There is a lack of structure, required workflows, etc. This makes data analysis difficult to impossible at times.

I’ll just name a few examples from the sales module.

  • M2M doesn’t really have a beginning and end to orders. You can open any order at any time, even after it’s been completely shipped and invoiced.
  • Sales order line items and releases have no real structure either. You can continue to add quantities or change prices on line items which have already been shipped or invoiced. M2M doesn’t track those changes either and the only “Order Date” you have is in the Sales Order Master table. This means that if you added items today to an order which was placed last year, there’s no way to determine when those items were actually added.
  • Sales, Quotes, Purchasing, Receiving modules are not transactional in nature so tracking history is nearly impossible.
  • You can change ship to addresses of sales order line items which have already shipped.
  • You can choose to invoice a shippable product from a shipper or directly from the sales order.
  • You cannot invoice a product from a shipper and a sales order on the same invoice. Therefore, if you have an order with both shippable and non-shippable items, an accounts receiving clerk will most likely invoice the shipped item from the shipper, and then add the sales order line item as a miscellaneous line item (in order to send the customer one invoice). This miscellaneous item doesn’t point back to the original sales order so it looks as if that line item was cancelled.

Suppose your company decided to study sales metrics and wanted to know how long it took to make and ship Widget A products? It’s nearly impossible to figure that out unless the users follow a standard procedure every single time. As a business intelligence specialist who works with M2M, I am forced to lecture users (like the Soup Nazi) to follow these procedures and spend a great deal of time with VBA customizations forcing my users to follow them so their data is as clean as possible.

I could elaborate further on nearly every module of M2M, but I’m just belaboring the point. The problem with M2M is actually that it is too permissive, not too rigid.

This has become particularly important to me as I work on that super secret project which I’m about to release on the world. Mwahahah!!!!

What do you think? Is M2M too rigid or too permissive?

8 comments to No SQL For You! One Year!

  • Hmm, very good points, and very insightful. I can remember being surprised at how easy it is to reopen an order.

    On a side note they need some enhanced recordkeeping. For instance, I have to keep track of costs every week because people are always asking what something cost last year. And we love the RPECAU audit report, but there are some holes in it. I wonder how hard it would be to keep track of every change everywhere.

  • Kim

    Ha, the soup Nazi. I do the same thing, but I try to be nicer about it to soften the blow.

  • Dennis, stay tuned. You’re going to love my super secret project.

  • Please tell me the ssp involves record auditing.
    That would be the best Jerry, the best!

  • lol, that’s great Mark. Unfortunately the super secret project will never be as good as Mendies. Mendies is Gold! Jerry Gold!

    However, I’m working on some form of auditing, but am hamstrung since M2M logs into SQL server with only a single login

  • roleki

    Funny this would be today’s rant; we just wound up with 37 of ‘Very Expensive Part X’ at our doorstep, when the pertinent JODBOMs clearly show we only need 19. I had engineers gathered around my cube with pitchforks and torches, shouting out crazy things like “M2M is doubling-up quantities on POs and costing us thousands of dollars!” and “My Excel spreadsheet is more accurate than M2M!”

    A quick little foray into DBRStage proved that somebody had reduced the JODBOM quantities after the PO was cut. Now, why isn’t there a trigger on THAT, or, more to the point, why doesn’t DBRStage go the extra mile and include user data?

  • Our procedures are very tight and consistent, if only because they have been doing it a certain way for so many years having installed M2M in the mid 90’s. However, one of the problems I encounter is unshipping a shipped shipper. Shipping tries to get ahead of the game and sometimes confirms a bad shipper. Things happen so fast it often gets invoiced before I am alerted. It was easier to fix before SQL because I could edit the SORELS and SOITEMS file pretty quickly. Now I have to script it. SQL has the “Edit top 100” lines thing – what about “edit a specific record”? I would like a “Reverse a bad shipper” option.

  • Rick, you can perform specific edits in any table with a simple update statement. However, just because you can, doesn’t mean you should.

    The M2M accepted method of fixing shippers is to zero out the quantities on the SHIP screen and then re-ship the item.

    I don’t edit the Sorels and Soitems in this situation, and certainly wouldn’t do so after an item was invoiced.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>