Looking for workflow system with email reminders

Most of the techs in our IT team do desktop support. We support several hundred Windows and Mac users and most of them have computers purchased by funds provided by our organization. We have a fair amount of turnover of employees and computers. Sometimes we replace old computers with new ones. Sometimes we reimage computers because the image is old or the computer gets infected etc.

We have been using a spreadsheet in Google Drive to track this process. We have one sheet for incoming computers and one for outgoing (going into service or back into service). These sheets have columns for the various tasks we do to configure the computers and for the user's name, tag number description, the tech's name etc.

Often the outgoing sheet doesn't get all the data entered because when we start imaging (or reimaging) the box from SCCM, since that can take up to several hours we go do other tasks and sometimes forget to complete all the fields in the sheet. This is a source of frustration for our supervisor as he wants us to fill in all these fields.

I thought that if we had a system where any row in the sheet (equivalent to a record in a database) was started but not completed after a certain amount of time or let's say each day at 6:00 am that system would send an email reminder to the tech whose name is on that record as a reminder to finish filling in all the fields in the record. We typically enter our user name as the tech's name and this is the part of our email addresses before the @ sign so it seems simple for the system to take the tech name and build the email address to notify the tech of the incomplete record.

I realize this is a pretty ambitious request but does anyone know if MS Access or SQL can do this? If not then is there a commercial application that can do this task?

Thanks,
Don
donanderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access can easily handle this. SQL Server is a database engine, and you could use it to store the data, but you couldn't create an interface with it. You'd have to use something like Access or .NET for that.

I don't know of any ready-made Access systems that would do this. There are several sites that list Access Templates, like http://access-templates.com/. You might be able to find something that would start you off there.

Depending on the amount of data you want to record, it shouldn't be too difficult to create if you have any Access skills. You'd need a table to track the Project details, something like:

tProject
-------------------
Project_ID
Project_Name
Project_Date
Employee
Status
etc etc

You could then create a Form, and users could use this form to fill in data for their project, and set the Status. To send alerts, you'd use the Form Timer, which can fire off code at a specific interval. To send email, you can use DoCmd.SendObject.

If you need more than this, please write back with specific questions. In many cases, with "project" type questions you're better off asking specific, focused questions. For example, asking 'How do I create a form' will get you a lot of flippant RTFM sort of comments (since you really should already know how to create a form), but asking how to make your form do "xyz" will get you all the help you need.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:
You can take a look at www.medforcetech.com for their workflow solutions. It will pretty much handle what you need

If you want to be hands on, SQL server can also be a solution. Create your table with the appropriate fields. You can then create a stored procedure and schedule it. The SP should basically traverse the table and find the incomplete records and using dbmail, send out the alerts.
donanderAuthor Commented:
It looks like the Medforcetech Workflow solution would be what we would need but this looks very expensive. We are a nonprofit cancer research institute so there is no way I could get the funds for that. Since we have database programming expertise in-house I think we could come up with something.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you have in-house programming experience, then the first thing to consider is the skills and strengths of those in-house people. If they are very skilled in ASP.NET, but no so much in Access, then the obvious choice for an interface language would be ASP.NET.

Of course, if their background is in Access/VBA/SQL, then Access is the obvious choice. It can do pretty much anything, and is widely available (and the Runtime is free).

As I mentioned, try to firm up the specifics of your needs and post back here. We'll help you get started, then you can ask more focused questions as issues pop up.
donanderAuthor Commented:
Hi Scott,
I imported our spreadsheet into Access as a table and made a rudimentary form to enter data and sent that to one of our users who is a DBA. Normally we provide IT support to him but I turned the tables and asked him to help me get this going and he is going to work on making the first iteration of the workflow database.

My question now, and hopefully it is focused enough to avoid RTFM is, if we use the Form Timer, can it only use a duration of time passed since the last change to a record to decide when to send an email reminder or can we set it up to send an email reminder say at 6:00 am every weekday for records that have not been completely filled in?

Thanks,
Don
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Form Timer's simply execute for a specified interval, and then run code - so no, you really cannot set that up to run every day at 6:00 AM. If you use the Form timer, and set the Interval to 5000 (5 seconds), then whatever code you have in the Form_Timer event will fire every 5 seconds.

The best way to do something like this is to create an Access database that ONLY sends the email reminders - nothing else (no data entry, etc). You can "link" the tables in that db to your other db (the one where the users will enter data), and add your VBA routines to determine which records need reminders, etc. Make sure you use an AutoExec macro (which fires each time the database is opened), or add code to the Load or Open event of your Startup form. Then, use the Windows Task Scheduler to fire that database each weekday at 6:00. When the db is fired from Task Scheduler, the startup code (in the Form, or in the AutoExec macro) will fire, and your reminders will be sent.

Also - here on EE you really should ask separate questions as new questions. While this one does have  some relevance to your original question, it's one that should really be asked separately, as a new question. You should close this one out, and ask future questions as a new question. This will expose your question to ALL members of the EE community, which always tends to get better/faster answers.
donanderAuthor Commented:
Scott's answers put together got us going on this. The DBA who is writing the code put something together that has the skeletal functions on which we can build a working system.

Thanks!
Don
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.