Call all Database Gurus!
March 15, 2010 5:16 PM Subscribe
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?)
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?)
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
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
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
Response by poster: 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
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
Response by poster: 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
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
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
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
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
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
posted by the biscuit man at 9:18 AM on March 16, 2010
Response by poster: 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
posted by edjusted at 12:42 PM on March 17, 2010
« Older How can I salvage my data from a Vista laptop that... | I need to write a macro to reformat daily reports... Newer »
This thread is closed to new comments.
posted by expialidocious at 5:37 PM on March 15, 2010