How do I use an online survey with question dependency to create a backend that can generate documents?

I plan to create a survey that is taken at an on-line web page and that stores the results into an Access database. The answers are primarily drop-down, numeric or strings. The results are meant to populate a 30 page template document, thus so that I get a Office Word or Adobe PDF document back?

The survey --> backend --> document is basically a glorified mail merge. The limitations of a mail merge (being restricted to one data source, no objects) leave the project wanting in these two main areas:

The engine would need to deal with having a variable carrying a value of [x|y|z], and to have the engine render different, but pre-defined paragraphs.
In some cases there are branching survey variables where if A = 1 -> ask B,C,D(include more text for each in document); if A = 0 -> jump to E (omit text for B,C,D in document)
The engine would need to know to not render a paragraph in such a way that it is cut off between pages, render headers/footers, etc (less necessary, as can be added by hand)

The questions that I have are:

What is involved in creating some kind of engine that can do this?
How do I better explain what I am looking for, in terms of an engine and a process?
Is there a particular language or piece of software that excels at this type of purpose?

mfg

Posted 2011-04-12T20:44:55.740

Reputation: 579

Welcome to Super User! Your question is overly broad and could risk to be closed. We have the expectation from people to spend effort before asking a question because you would else require too much useless effort from us. The applications you mentioned are meant to view and edit documents and do some magic on it, but that's where it ends. While the magic is simple if you send a document to someone containing scripts and let him fill it in, the magic is hard do to this in a web & database centralized manner. It's near impossible...

– Tamara Wijsman – 2011-04-14T00:52:35.100

So, you are left with the two options provided to you. Option one: As mentioned by Joe Internet, you either develop a web-based engine in a web-supporting language that: Takes your Questions, Paragraphs and Logic in an Excel file as input; processes the User Input; and outputs it to the Database. Then you develop a desktop-based application/script that requests the data from the Database and spits it out into your Word Template Document. Option 2: Use an existing survey like Google Docs as mentioned by Journeyman Geek. – Tamara Wijsman – 2011-04-14T00:56:26.143

1@Tom Thanks for the edit. This is a broad question, but I don't think it's overly broad insofar as, as you said, it has a very limited set of possible solutions. Also, it's not exactly vague; the criteria seem specific to me, if I'm wrong I'd love to clear up any loose ends. – mfg – 2011-04-14T02:23:01.637

Answers

I'm not sure if there is any easy way (ie, an application already exists) to do what you want, but from your description, you'll have your work cut out for you.

Based on your description, I suggest splitting the project up into 2 parts. Part 1 would be the online survey, and part 2 would be the document generator.

For the online survey, you would build a web application that collects the answers to the survey questions. This could be done with a combo of, for example, Javascript/PHP/database or Asp.Net/database, etc.

For the document generator, you would create an application that would query the database holding the survey results, then take those results and create a formatted document or insert them into some form of document template.

I'm not sure of your planned deployment environment, but assuming this is a work-related project, I suggest the following tools in a Windows-based environment... SqlServer Express instead of Access for the database, Visual Web Builder Express for building the survey website. Both are free from Microsoft, and there is plenty of learning material online. For the document generator, you could use MS Word with VBA scripting to query the database, and place the query results into a Word template/document.

Joe Internet

Posted 2011-04-12T20:44:55.740

Reputation: 5 145

With Word and VBA scripting, would it be able to translate an answer into multiple entire paragraphs that need to be added? – mfg – 2011-04-12T21:27:57.570

There won't be any automatic translation. The computer will only do exactly what you program it to do. If the "answer" picked in the survey is "apple", you'll store the word "apple" in your database. When you query the database, you'll get the word apple. Word & VBA have no idea what this means. If you want to insert a recipe for each fruit, you'll need to 1) determine the fruit value returned from the database, 2) insert the correct paragraph text into the document. So... if answer="apple" then insert "apple pie recipe" ...can be done, but you need to supply the recipe. – Joe Internet – 2011-04-13T21:33:36.957

@mfg: So, in short: Yes, with conditionals you can turn an answer into multiple paragraphs. In your online survey (which would be most likely written in PHP or ASP.NET) you can do this to show more questions based on an answer. In your export to a document you can do this to show a paragraph of text if the user has selected an answer. For example, if a user selected that he has a headache, you could list medicines...

– Tamara Wijsman – 2011-04-14T00:36:44.140

that (VBA) is good to know. At base, this question is about me bridging a certain programming literacy. I would possibly be sitting down to pitch this and I don't want to be mumbling half-understood technologies. Thanks for trying to explain some possible routes to get done what I'm looking for. – mfg – 2011-04-14T02:50:42.487

stop right there. Just stop. Don't pass go. Do not collect 200 dollars.

There's no clear idea of the technologies to be used, and 'How can I make this happen within Microsoft Office (plus Acrobat)? ' just dosen't quite cut it as far as planning.

Fact is, from the details in the questions, the best way to do it is the survey's option from google docs - it handles creation of the survey, you can mail it out to whoever you need to, and download the results as a nice little excel. You can probably either do some scripting kungfu on that spreadsheet then simply embed it in word

Journeyman Geek

Posted 2011-04-12T20:44:55.740

Reputation: 119 122

You're absolutely right there's no clear idea. That is what the question is asking for. I am an Office Assistant and what to thread my way through the toolkit available in a corporate office setting. I want to have a clearer idea than "I know a web survey can render a complex document that is more than a mail merge." Office plus Acrobat is a limitation but it's what I have to work with. I'm interested in Google more but know it would be locked down. As for scripting, could it handle adding different paragraphs based on survey variables? – mfg – 2011-04-13T00:59:41.220

I don't think it can fork options - but at this point, you're talking a full fledged project which probably goes beyond the basic scripting abilities that MS word has. google docs surveys does allow you to send a repondant to specic pages based on what was chosen http://docs.google.com/support/bin/answer.py?answer=141062&hl=en . I've never seen anyone do a complex survey based off mail merge and office.

– Journeyman Geek – 2011-04-13T05:05:45.060

1MS Office has more than basic scripting abilities. You can build customized applications with VBA. The real issue here is that what OP wants to do requires understanding computer programming, which is not the case for OP. OP can certainly learn this, which is the first step of this project, IMO. – Joe Internet – 2011-04-13T21:39:36.577

the OP would also likely be better off using a web specific programming language. Doing it with office (and... acrobat? WHY?) seems unnecessarily painful. – Journeyman Geek – 2011-04-13T23:10:13.177

@mfg: You should consider putting more effort towards reaching your goal if you want such a survey system. You aren't going to get any web behavior with solely Office or Acrobat or VBA; as Journeyman Geek mentioned you will need to learn a web specific programming language if you would want to write this yourself. But as you barely have an idea what you talking about and probably don't want to spend a lot of time on learning, you should check your interest and spend some time in looking if Google is too locked down for you. If it is, there are other survey systems out there, Google them... – Tamara Wijsman – 2011-04-14T00:44:54.213

@Journey Office (+Acrobat, though that was mentioned only for the sake of document rendering) is what is immediately at my disposal in terms of base tools on my (the Office Assistant's) desktop. I am sure the IT department has significantly more resources; but I wanted to set a baseline to ask what technologies could be used to bridge the gaps in my process. Once I have an idea of which ones would be used, I can try to develop more focus on what the engine project would entail. – mfg – 2011-04-14T02:49:56.403

If all else fails...

Looking into the first suggested duplicate for your question, I found a paid solution called Formstack.

This could be a really good solution if all else fails, it contains the following features for surveys:

Conditional Logic: What you explained in your question.
Smart Routing: Conditional logic, but for who to send intermediate responses to.
Data Sharing: Share intermediate responses.
Data Exports: Export to Excel/Word, or see it online.
Website Integration: Integrate in your company website, restyle the surveys.

They have starter and professional plans which might be interesting to check out...

Tamara Wijsman

Posted 2011-04-12T20:44:55.740

Reputation: 54 163