The recent thread about online surveys/forms reminded me of something I wanted to do in the future, and the techs here probably have a better idea than me how to go about it.
For our summer school and saturday school we get about 500 applicants each year. At the moment a paper form is filled out and physically returned to the school, and the data is typed into an Access database manually. What I'd ideally like to do is have applicants fill out a website form to apply initially, and use some sort of software to manage the applications better.
The simplifed workflow would look something like this:
Online application form filled out=>
Emailed to office =>
data is transferred automatically or semi-automatically to some sort of searchable/editable storage (not necessarily a database).
Office reply by email or post stating whether the applicant has a place reserved or if they have been placed on the waiting list if all places are taken =>
Applicants can email office at any time for extra info or to cancel. If cancelling, they are taken of the main list and the next person on the waiting list is contacted =>
Course starts. Everyone finalises payment within first two days. If not, they're taken off the main list and the next person on the waiting list is contacted =>
Address and info kept for next year, because current participants are contacted earlier to give them preference, before the email form is made public.
I did some research into this already, looking mostly at 'event management' software and 'email form to database' software/servers, but didn't find anything particularly useful; no software provided a complete solution - basically the office staff would have to keep jumping between software, importing and exporting data as they went.
However you might know of other 'types' of software that could help with this task. ie. a free SIM/MIS program? A VLE like moodle?
The other thing I was thinking might be able to handle the job is Outlook 2002, but I've never been an Outlook user (ie. I've never even had it installed on any system I've owned). If Outlook recieved email forms, does it have enough features to allow it to manage the type of workflow outlined above?
Many years ago I was responsible for the very first rollout of Tesco home shopping.
This is in the days when the web was too slow to be totally online so you applied for a CD online which was then posted to your house for you to complete your orders and transfer them.
The bit I had to do was the recieving of emails (one for each application) and pulling them together into a single database to be printed onto the CD's and then posted (including walk sorting of post codes etc.)
The Tesco guys wrote the website so that the information was emailed as a CSV attatchment to me.
I then used Eudora Light as the email client which I set up to save the attachments to a set location on the network (which it did using sequential numbers). This generated a whole host of the CSV files in the one folder.
Then once a day I used a DOS batch file to collate them into one CSV file (copy command - 'copy *.csv collated.csv' or something like that) and import them into a database.
As you can probably tell this was about 12 years ago and is probably not totally relevant now, but thought that there might be something of use somewhere in this tale.
If it was me, I would have a database on the website where the information is put by the form so it can be recalled by the user and ammended much easier.
Then it is just a question of keeping that database and your local one synchronised.
If these emails are knocking about on a pop server, you could use fetchmail or some perl pop3 stuff (plenty on CPAN) to download mails off the server. Assuming these mails are in regular format, a couple of regex's later you have raw data. Then use DBI to poke this data into a db. Run this hourly with cron, or more frequently if you're a masochist, and bob's your mother's brother.
That sounds fantastic, exactly what i need, but unfortunately the budget I have (above webhosting costs) for the website and associated services is 0 euros.
Zoho Creator also looks good; with web applications like that, in twenty years no-one will be running software on their PC! However, exporting the remote database and incrementing the local one would have to be done manually everyday, and email management would need to be handled manually too.