1. Blog
2. Web Programming

A custom administration panel to organize your business.

Posted May 08, 2009  |  By Robert Wallis, Senior Developer  |  Filed under: Web Programming

---

The majority of the work I do here at Jub Jub is creating custom web applications for our business clients. Every business does things their own way, so we often create a custom pages on our client's website. I'd like to show you the process we go through to create a custom panel to organize business information. Often these things are hidden in the admin panel behind a login and a password so we can't show it off.

We'll make an inventory tracking panel, but really any information that could be put in a spreadsheet, can be used. There are many reasons to not actually use a spreadsheet. The biggest, is that more than one person needs to use the spreadsheet. Then you have to keep passing the spreadsheet around via email, or be on the same network. Also different people enter data in different ways, forget to enter important details, or accidentally erase important records. I'm sure you can imagine the difficulty if two people edit two different copies of the same spreadsheet at the same time, and then try to combine the results. Using a custom web site, most if not all of these issues are solved easily.

Data Model

First, you tell us what information you store, what is important, and how you use it. Our example company makes jewelry. Not all their suppliers give them a custom bar code, or UPC number for the SKU. Their main problem is that some stones go missing, so they need to keep track of who changed the stock quantity, when they changed it, and how much they added or removed.

So we will create a table in the database that has the following "field set", or column names.

id - each item has a unique id upc - the manufacturer id name quantity

We also need a log table to keep track of the changes using the following field set.

id - every table should have a unique id inventory_id - the item that was changed in the inventory quantity - what the quantity was changed to email - the person that changed the item date - when did they do it

Now we will create custom admin panel. Often we create these as custom Expression Engine modules. Expression engine was written in PHP. We also write programs in ASP.NET C#, VB, and Python. The choice of language largely depends on your existing site, future maintenance of your site, and budget. I will use JS (JavaScript) so you can see the progress as we go, and it will not communicate with the server. Normally we wouldn't use just JS, but instead combine it with one of the server languages above.

I use a process I call "List, Edit, Add, Delete" because that's the order in which I will develop the site. Some developers call this "Create, Read, Update, Delete" (CRUD) but I like my acronym better.

The List

First I populate the database with test data, you don't have to worry about being able to read this. It's just FYI.

inventory = [ {'id':24, 'upc':'141422414', 'name':'0.5 karat diamond', 'quantity':322}, {'id':23, 'upc':'141422123', 'name':'1.0 karat diamond', 'quantity':80}, {'id':26, 'upc':'141422321', 'name':'2.0 karat diamond', 'quantity':43}, {'id':27, 'upc':'141422444', 'name':'2.0 karat ruby', 'quantity':57}, {'id':14, 'upc':'141422222', 'name':'4.0 karat ruby', 'quantity':51} ]; inventory_log = [ {'id':1, 'inventory_id':23, 'quantity':30, 'email':'[email protected]', 'date':'2009-05-01 08:31:12'}, {'id':2, 'inventory_id':23, 'quantity':27, 'email':'[email protected]', 'date':'2009-05-01 09:40:01'}, {'id':3, 'inventory_id':23, 'quantity':57, 'email':'[email protected]', 'date':'2009-05-02 08:37:44'}, {'id':4, 'inventory_id':23, 'quantity':50, 'email':'[email protected]', 'date':'2009-05-03 10:01:18'}, {'id':4, 'inventory_id':23, 'quantity':80, 'email':'[email protected]', 'date':'2009-05-05 08:20:02'}, ];

In the test data, Mary updates the inventory early in the morning. And usually gets a shipment of 30 1 karat diamonds. Jack just takes a few here and there during the mid morning while working.

Now I need to make this information readable. So I will create the list views. There should be two lists, the main list, and the list for the log. The log will be under the main list.

First I'll create the design for the main list:

id	sku	name	quantity
123	11111111	Item Name Test	121444
123	11111111	Item Name Test	121444
123	11111111	Item Name Test	121444
123	11111111	Item Name Test	121444
123	11111111	Item Name Test	121444

This will be fine, but I think it would be better if I could see who last updated it, a link to edit it and show the logs, and a delete button.

id	sku	name	quantity	updated
{id}	{sku}	{item}	{quantity}	{time} {name}
{id}	{sku}	{item}	{quantity}	{time} {name}
{id}	{sku}	{item}	{quantity}	{time} {name}
{id}	{sku}	{item}	{quantity}	{time} {name}
{id}	{sku}	{item}	{quantity}	{time} {name}

The above table is much better. The link to edit is on the left, and the link to delete is on the far opposite. This will prevent accidental deletes when you really just want to edit. (a common user interaction in web design mistake) Imagine if you needed to check the table from your touch screen mobile phone, if the buttons are too close, it's very difficult to click the correct item. The padding around each cell has also been increased for this same reason.

Also the most common, short information is shown. We expect people to want to see the sku, quantity, and last checkout without having to "drill down" to the next page. But there isn't too much information, it fits on one screen horizontally. Many people have a mouse wheel, so it's easy to scroll vertically, but few people have a mouse trackball on the top of the mouse that will allow scrolling left and right, as well as up and down. So we always want to keep the amount of information within the horizontal boundaries, but vertical boundaries can be extended easily. People are used to scrolling up and down.

Hooking up the Database

Now, I'm going to change all those {curly brace} placeholders into real data from the database we created above. Normally this would come from a professional database like MySQL, SQL Server, Oracle, or an AS400. But I'm going to use JavaScript so that everyone reading this article has their own copy of the database, and won't mess up the experience of others.

Sometimes this is called "data binding" because the database is "bound" to the design. There have been many many programming languages, SDKs, APIs, and software packages invented to do automatic data binding. However in my 14 years experience, they are always just as difficult to use as the non-automatic way. However data binding has it's place when creating software to allow a non-programmer to create tables. And we have used it very successfully in that area to allow our clients to change tables.

This is the place where having custom programming work really differs from buying a software package to design the site. This example has a secondary log table to track the changes made to the main table. Although a log table is a simple concept, most software packages can not handle this. And the few that do handle it, are very difficult to configure to make it work correctly.

It only has to be slightly more complicated than the most simple table or spreadsheet to make custom programming more a more easy and flexible solution.

id	sku	name	quantity	updated

Edit and Add

We can list the data in a table from a database. But when you click the edit link, or the delete button, nothing happens. Now we will add the ability to create and remove records from the database.

The delete doesn't need a new page, because we can easily create a confirmation dialog like . Another benefit of the confirmation dialog is that is relieves stress from the server by having completely "client side" code. And it relieves stress from the user by being quicker than a request to the server and back just to ask if you are sure you wanted to delete.

Adding and editing items can use the same design, but different code. We could create a separate form for editing the quantity and logs, but I think it would be easier and more simple to the user to just keep it in the same form. So we only need to design one form, even though we are using two database tables. One table is the items, and the other table is the log.

This form will show when you click on the sku, let's design it:

This design has many thought out features. It's not just the first thing that came to our head.

First, the name of the item and it's ID are in a header at the top. This is good because when they want to change the name, and start typing the name it's easy to forget exactly which item they were changing in the first place.

Next, the font has been increased for the all the inputs to 14px, and there's a 4px padding inside the inputs. This not only makes it easier to read, but also helps prevent mistakes.

Also the log is on the same screen, so you don't have to go to a separate place to see who edited the item.

Now it's time to hook up the form we designed with a working table. And here are the results.

id	sku	name	quantity	updated

The item quantity is forced to be a number. When you type a number and then letters, then click another field, it will change to the number. Also the form shows in the middle of your screen, unless you use Internet Explorer version 6 where it appears at the top of the screen because IE6 is the worst browser ever.

When you enter a new item or save it. A new entry in the log is created by you ([email protected]) at the current time.

Conclusion

There are many steps involved in creating a useful tool to organize your business. Nearly anything is possible with custom development work. Of course, I'm plugging my own job. But, if something like this would help your business, let us give you a quote. Everyone's business is different. We want to make a website software solution that will fit your business, giving you an edge. Pre-built sites will push your business into a generic mold. Even sites designed for your particular industry will just make you like your competition, and don't allow you to innovate. A custom solution can quickly be modified at any time as your business changes.

Also, we give more than just the black and white requirements of the job. We add extra little features that make the site better. Like we say around here "The details are in the implementation." Meaning you can have a site, that technically works but is implemented bad. A great site works, is easy to use, and helps your business. At Jub Jub, we make great sites.