Call all Database Gurus!

March 15, 2010 5:16 PM

We have a process at work that requires multiple approval steps that we're trying to track.

There are a potential 8000+ SKUs to track and each SKU needs to have each step accounted for (e.g. concept approval, art approval, pre-production sample approval, production sample approval). We have been tracking this with a spreadsheet but it's very cumbersome, and we've moved it to a MS Access db. The biggest problem with this is that it's not easily shareable everyone who needs a status check has to ask the person in charge of the DB instead of being able to look it up themselves. FWIW we're not a big company and probably no more than about 10 people ever need to look up this kind of info)

Can anyone give us suggestions on a better way to do this? Our criteria: 1. able to generate lists of items based on criteria (e.g. everything with item # prefix "X" that's not approved, anything that's been submitted for concept approval over 2 weeks ago, etc)

2. have some way for simple lookups/searches that can be performed by non-techie people (e.g. when was XYZ submitted for artwork approval? has the pre-production sample for ABC been approved, and if so, when?)

3. (this is important) the db should be relatively simple to create and maintain. We've looked at and think Filemaker might be a good solution.

4. here's a bonus: it would be ideal if an email was generated to certain people whenever that record was updated. e.g. if Joe was responsible for item XYZ, have an email sent to him any time XYZ's record is updated...likewise for Sally and item ABC)

We've considered Filemaker, CentralDesktop.com, and have even looked at doing some sort of Google spreadsheet. PHP/mysql is not out of the question if there was an easy way to create layouts and search result pages. As you can probably guess, we're not real strong with administrating DB's. Are we on the right track or is there something else we should be looking at?

(btw, why can't I ever get line feeds to work properly in Ask Mefi? is it just me?)

posted by edjusted to computers & internet (11 answers total)

What you need is a workflow management tool, with perhaps a dash of project management. You don't necessarily need to build it yourself. Don't focus on implementation details (PHP/MySQL vs. Access vs. Filemaker; layouts and search results pages; web-based or desktop), focus on business needs and go from there. You might find an existing product that works for you with no extra development needed. There are open-source and commercial tools available - why not start by evaluating one? The only ones I've ever used were heavy-duty enterprise applications, so I have no specific recommendations for you.
posted by expialidocious at 5:37 PM on March 15, 2010

do you have any developer/coder types in your office? if not you might be best off finding someone to help develop this for you. FileMaker wouldn't be a bad choice if you want to roll your own yourself, or you could hire a consultant to develop a Web-based solution for you. (maybe put a post in Jobs here? I do some of this kinda stuff and there's a lot of other people here who do it too.)

if nothing else, you can load your existing Access DB into Microsoft SQL Server 2008 Express, which would give you the ability to have the data visible by multiple people (Access can be used to query the SQL Server database). this may not be the easiest way to do it, but it'd be pretty quick to get going.
posted by mrg at 5:42 PM on March 15, 2010

You are describing Product Lifecycle Management software.

This type of software lets you set up a lifecycle for any task - you can fully customise the name of each step, the number of steps, who can do each step, who gets an email when a step is completed, so on and so on.

We use Enovia's Matrix. I have no idea what your budget is, but an enterprise solution like this is expensive.

Their may be open source / freeware examples out there, I'm not sure. Would be great if there was.
posted by trialex at 5:48 PM on March 15, 2010

Thanks for the answers so far. PLM software seems really overkill for us at this point.

We currently have one person who acts as the human funnel for everything, and that's *ok*. All we're trying to do is to help that person out. I used to be somewhat familiar with Filemaker years ago, so I know it can do the job, but it's criteria #4 that's stopping me. I know you can send email from within Filemaker, but not sure if it can be programmed to automatically trigger emails upon changes or something like that. I wanted to get some opinions before going through the process of creating/implementing either Filemaker, some open source solution, web apps, what have you, migrating all the existing Access data over, training everyone, etc.
posted by edjusted at 6:22 PM on March 15, 2010

Oh yeah, if it makes any difference...sometimes we *skip* approval steps due to time constraints, but we always want to "go back" and get those items approved after the fact and make sure our paperwork is straight. So we don't necessarily need anything to "lock" any steps (e.g. the program doesn't need to "stop" anyone from moving to step 3 if they haven't done step 2).
posted by edjusted at 6:26 PM on March 15, 2010

I did something very similar in Sharepoint. If you use outlook, you might consider this route. I've seen hosted sharepoint solutions also, if you don't want to run one yourself. Feel free to memail me if you want more info.
posted by nightwood at 6:50 PM on March 15, 2010

This seems like a perfect job for a web based database. There are a bunch of them out there.

I work on the Intuit QuickBase team, and it sounds like it does exactly what you need. If you want more info, you can memail me.
posted by Philbo at 8:12 PM on March 15, 2010

Access can handle a workgroup of that size without issues, especially if you split the DB into a front end (i.e. queries, forms, reports), installed locally on each user's workstation, and a back-end (i.e. tables/data only), installed on a file server. Splitting is quite easy and you can find guides online about how to do it.

Doing automated emails with Access is a pain, though. It can be done via VBA, but it isn't easy.

You can develop a custom solution in PHP/MySQL, of course. You can even build one that hits the Access DB (or a regularly cloned copy of it) via ODBC. Though, if you're going for a web-based solution, you might as well go the whole hog and import the data into MySQL or SQLite. Any web-based solution would make the automated email part easy.
posted by wheat at 11:54 PM on March 15, 2010

I am with Wheat. Access should be able to achieve what you need. It can easily handle 10 people hitting it as long as you aren't simultaneously trying to hit and update the same record.

As far as the workflow is concerned, keep it simple. Use a drop-down field which contains all of the steps in the workflow. Select the step in the workflow, update additional information in the record, save, done. Need to skip a step? Use the drop-down field and select the next step. Sometimes typical workflow systems are more of a pain than they are worth. You end up creating all these strict rules and it just doesn't work in the real world. Keep it simple. As said above, multiple people can hit an Access database and perform lookups.

You should be able to hack / google a VB script that will email someone when the record is updated.

If you don't want to go down the Access route, I would check out Zoho Creator. It is straightforward and quite powerful.
posted by jasondigitized at 5:24 AM on March 16, 2010

FileMaker Pro can send emails pretty easily. Not sure how it works on Windows, but it will send them through your existing email client. A new version just came out and it introduced some new script triggers based on editing a field or layout. Here is a tutorial on them.
posted by the biscuit man at 9:18 AM on March 16, 2010

Thanks all! I'm leaning towards some sort of web-based solution to try to keep development and access simple, and I'm going to check out Zoho Creator and Quickbase. If those aren't suitable, I'll probably check out the new version of FileMaker Pro and Mysql/PHP next.
posted by edjusted at 12:42 PM on March 17, 2010