email form -> database

[Anti]

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?

Thanks for any advice.

[ajbritton]

I am also interested in automatic email processing.

I am receiving backup logs from lots of sites. The subject line is a "" delimited, comma separated list which signifies which site the log has come from and if it indicates a success or otherwise.

I want to import all these logs into a database automatically and produce a daily report which shows which known sites did not report a success.

I've fiddled with Access's ability to connect a table to a MAP folder, but have not gotten much further.

EDIT: Sorry! Did not mean to hijack the thread like that.

[limbo]

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.

[tom_newton]

[assumption=unix platform]
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.

[webman]

I'd recommend using something like Zoho Creator. No scripting required and it's free

[Geoff]

Also, if your doing this from scratch, I'd be really careful about handling the submitted data. The last thing you want is to be a spam relay, or suffer from XSS or SQL injection attacks.

[mrforgetful]

Microsoft Access 2007 has a lot of automatic email data processing, worth a look if you use it already.

Make your database, go through a data collection wizard choosing email as the collection method, choose the fields you want to include etc and that's it I think.

Outlook will autmatically open the received email replies and automatically import the data into your database.

[john]

Microsoft Access 2007 has a lot of automatic email data processing, worth a look if you use it already.

Make your database, go through a data collection wizard choosing email as the collection method, choose the fields you want to include etc and that's it I think.

Outlook will autmatically open the received email replies and automatically import the data into your database.

ooooh thanks thats worth looking into for some projects at work.

[Anti]

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.