Stock Taking, jQuery, Reporting

By | March 21, 2011

Last week a new client approached me to help out with an admin function within her site. Here’s the basic idea;

Each Order received has a tickbox by the product:

The tickbox would update an entry in the database to state whether the product is “in stock” (1) or “out of stock” (0). The system worked well, but was coded as part of the form so needed the [update] button to be pushed. That’s not a massive big deal, but it’s an extra step that might get forgotten by the data entry people and so on.

Once the days orders of products had been “ticked”, a report is run to show the shopwoner what needs to be ordered from her suppliers and what she has already on hand. This was already coded.

The client had attempted to make the checkbox fire automatically using javascript, but had come to a dead end – which is when I was contacted.

The brief (in brief!!)

Make the checkbox fire automatically, to update the database entry on a per product, per order basis. Make the table row highlight if the checkbox is ticked.

The solution

Firstly, I tried to use the customers existing javascript code, but after a couple of hours was just going around in circles trying to make sense of what was happening. I gave up on that, and rolled my own.

The first step was to get it all working on a new RC2 (note that the client does not yet run 2.3.1, though 2.3.1 and 2.2 are very similar in most resepects especially in the admin area). I installed rc2 and amended the orders_products DB table to hold a tinyint flag saying 0 or 1 for stock purposes.

I then added a checkbox for each product ordered, the result looked exactly the same;

Next up, I coded up a .js file which (on checkbox tick) reads the value of the checkbox (1 or 0) and the value of the product ID and the Order ID. It then uses the ajax function of jquery to open a standalone php file which takes those inputs and updates the database accordingly. The .js file also fires off a css update using addClass to highight (or un-highlight) to row affected (which means anyone doing data work, can straightaway see what has been changed);

And no need to press the update button any more.

4 thoughts on “Stock Taking, jQuery, Reporting

  1. Anthony

    Pretty cool. We’ve been thinking about a marking system like this for internal use as we have several employees who might be involved on a single order if there are multiple items that are on back order. Rather than a tick box we are considering a text field so we can add notes for each. Same idea though. Good stuff!

  2. Gary Post author

    Anthony – a textbox would be slightly more problematic – as a textbox does not have anything that says “I’m done, update me” – so you would need something that updates the database… Either a button. Or maybe when the user “un focus” from the textbox, or it could be updated based on a delay of a few seconds typing. It’s more difficult – let me know how you solve it when you come to code it.

  3. Anthony

    I haven’t looked at it yet, but the order editing contribution updates automatically when you tab off a field. I’d think I could just use the existing code and add it to the new fields. Haven’t checked it out yet though!

  4. Dawn

    I’m trying to reduce the amount of paperwork our business generates, I was hoping there would be a contrib to add a tick box next to the product list on the invoice, so that when an item is picked it can be checked off. It doesn’t need to have a function on the website, I just need something on the invoice for manual picking/packing checks. Please can you get in touch for a quote. Many Thanks

Leave a Reply

Your email address will not be published.