Table with many fields or table with generic field?

[drose0321]

I'm designing an application in which the user has to answer lots of questions (about 180).  I want to show the user just one question at a time and control the flow based on prior answers.  My current solution is to use a table with generic fields (e.g. Question, Answer, Number) and then use a related command table to move from question to question by changing the number of the question.  This works great to present the questions to the user and collect their answers, the problem is that since the fields are generic when I later want to use the data I have to parse it out by, for example, going to the questions table, look for the question number that corresponds to the user's first name and then add that to a variable and etc.  I wonder if there is a better way to handle this.  Am I missing something obvious?   Thanks!

 

 

[comment]

 I don't really understand your description - esp. the part about "controlling the flow based on prior answers".  In general, a solution that collects answers to questions (i.e. a survey) will have a table of Questions (where each question is a separate record), and a table of Responses (where each response is a record linked to the corresponding question). If you have multiple respondents, then you would have a table for them too - and each response would be linked to both a question and a respondent. 

There are variations on the theme, but this is the basic structure.

 

[drose0321]

The flow is controlled means, for example, if question 1 is "Have you ever been to Paris?"  And the answer is "no", rather than show question 2 (which is "Did you go to the Eiffel Tower?") the next question the user sees will be question 6 (which has nothing to do with Paris).  This is a single user application so the questions and answers are in a single table (rather than a one-to-one related table).  It just seems cumbersome doing it like this, collecting all the questions in generic fields and then, later, parsing them out into fields or variables to be able to use the data in forms or reports and I wonder if I could do something similar using a table with lots of fields (e.g. a field for "Been to Paris" and a field for "Eiffel Tower - Y/N and basically 180 fields).  The question is, is there a reasonable way, with that setup to control the way the user sees the questions?  

Edited June 24, 2017 by drose0321

[comment]

No, it is not reasonable to have a field for each question. Especially if you want to present one question at a time and control the order in which they are presented.

 

9 minutes ago, drose0321 said:

for example, if question 1 is "Have you ever been to Paris?"  And the answer is "no", rather than show question 2 (which is "Did you go to the Eiffel Tower?") the next question the user sees will be question 6

If that is the pattern for all questions (Yes/No) , you could have two fields in the Questions table to store the IDs of the next question to go to in each case. 

 

[drose0321]

Thanks.  I didn't think there was a better way to do it, but I wanted to see what other people thought.  I think I'll add a section to the script to go to the next question that captures the answer in a variable and enters it into a table with all the fields as the user goes along.  I do want to end up with all the answers in a table for use in reporting and forms generation. 

[comment]

10 minutes ago, drose0321 said:

I think I'll add a section to the script to go to the next question that captures the answer in a variable and enters it into a table with all the fields as the user goes along. 

I am not sure what you mean by that. The answer should be entered by the user directly into a record. This could be the same record as the question (in case you have only one respondent) or a related record. There should be no need to capture it in a variable and enter it elsewhere.

And if by "table with all the fields" you mean a table where each field is a question and/or an answer, I advise you - again - to abandon the idea.

 

[LaRetta]

22 minutes ago, drose0321 said:

Thanks.  I didn't think there was a better way to do it, but I wanted to see what other people thought. 

You have come here for opinions of experts who use this program every single day to service thousands of businesses.  You have been lucky enough to enlist the assistance of one of the top developers but yet you will not listen?  That is like going to a doctor who specializes in brain tumors and saying, "Nah, I think we should cut here instead but thanks for the suggestion."  It makes more sense to follow his advice specifically along the way.  If you do not (and as you have already shown), you will end up with a mess which will take far longer to correct later.

Don't build your house on a lousy foundation (structure) ... get the base right to begin with.  If you do, the rest of your work on your solution will be far simpler instead of a nightmare.  We see many who do not listen who return time and again where they've dug themselves in deeper and deeper with no hope of changing the structure later without a HUGE problem.  Believe me, I know what migrations of data from improper structures entail and it requires an expert and a lot of time/money get THEN get the structure rewritten as it should be, and your good data moved to it.

Create the foundation properly the first time.  Life will be so much easier. I suggest that you take a new file and build what you think Comment suggests and present it - let him direct you if need be on it - and keep building.  As is, you are not listening.  Every time you have a question about it, post your sample file with your questions.  If the questions aren't on same subject, post a new question again attaching the file.  In this way, Comment or other advanced Developers can help you.  :-)

[drose0321]

I can't think of any other way to do it.  The user answers question # 1 "Yes, I went to Paris" and 170 other questions each in it's generic question/Answer/number table.  Then later I want to print a form (Name - from question # 8) and Paris (from question # 1) and wedding date (from question number 73) and all the other questions.  So how do I get them into my form?  I'm going to the questions table and searching for question # 8 and filling the $Name variable (so I can put it where it belongs) and etc.   I can't think of any other way to fill a complex form (with many logical decision points (e.g. we don't print the "France" part at all if they don't go to France). 

[comment]

13 minutes ago, drose0321 said:

Then later I want to print a form (Name - from question # 8) and Paris (from question # 1) and wedding date (from question number 73) and all the other questions.  So how do I get them into my form? 

You don't print a form. You print a list. If you want to print only some of the answers, then perform a find for them. There is no other way to do it. At least not a reasonable one. You would have to design a separate layout for each possible combination of answers you'd ever want to print. Not to mention that summarizing the answers to produce any meaningful statistics out of them would be just as impossible.

 

[bruceR]

"I can't think of any other way to do it."

Wow. Well; we can; and you been repeatedly given correct instructions. Listen to what Laretta and Comment have told you.

[drose0321]

You people are surprisingly unfriendly.  I'm trying to ask reasonable questions.  Unfortunately, I can't upload a sample file because the intellectual property belongs to my boss.  I can't use a report, I need a precisely formatted form as the end result.  I have a solution that works, but is cumbersome and I just wondered if anybody had any suggestions for a better way to do it, so far I haven't seen anything.  Really, only Comment has even tried to be helpful, the rest of you just seem to be killing time making unnecessary comments. 

[LaRetta]

Wow, sorry you feel that way.  I was truly trying to help.  I wasn't asking that you upload your file but rather start with a fresh one.  We've been in this business professionally for a combined total of easily 60 years, full time, so we know what needs to happen to assist you.  As Comment suggests as well, I encouraged you to abandon your idea.  You can shoot the messenger if you wish but it doesn't change reality.  If someone is walking towards a cliff, should we remain silent?

[drose0321]

Ok  Let's try this again.  Here is a very basic sample file - it has a questions/answers/number table and a control table and a bit of the question flow logic and an idea of the kind of output that is needed.  I guess I wasn't explaining things properly.  You can see that I'm using variables to parse out the individual questions so that I can set them into the kind of output format I need.  If anyone has a better idea for how to do this I'd love to hear it.  As you can imagine this is pretty cumbersome with many questions. 

example.fmp12

[comment]

3 hours ago, drose0321 said:

I guess I wasn't explaining things properly. 

I guess not, because everyone here thought you had a field for every question. Your file shows every question as a separate record, so that's a good start. Again, assuming there is only a single respondent - otherwise you need to separate the questions from the answers.

IMHO, you don't need the Control table. You only need to navigate from record to record in the Questions table itself. But that's a minor point: using a relationship to show the current question can work just as well. Except that the Number field in the Control table should be a global field (and it could be in any table).

As for the navigation, instead of writing a giant script covering 180 possible scenarios, you should make your script generic and use the Question table to store the specific instructions (I have already said this in my second post here). The default would be Go to Record [ Next ] (or, if you are using a relationship to show the questions, you would set the global field to itself + 1). 

 

4 hours ago, drose0321 said:

I can't use a report, I need a precisely formatted form as the end result. 

I don't see "a precisely formatted form" in your file. I see text comprised of different answers. The formulation of each answer should be done as a calculation field in the Questions table, and you could use a summary field to put them together. 

However, this will not work if the formulation of one answer depends on answers given to other questions. I am not sure how representative your example is in this regard but if this cannot be avoided, you are looking at an awful lot of work the result of which won't be pretty.

 

Edited June 25, 2017 by comment

[drose0321]

Thanks.  I like the idea of using a navigation field in the questions table, it keeps the logic closer to the questions and should make it easier to change.  

I didn't build a precisely formatted form for the sample, the  idea I was trying to get across is that specific information needs to be extracted from specific questions and displayed in a specific context. 

Yes, the formulation of one answer does depend on the answers given to other questions, thus my current solution (and the one I was looking for an alternative to, although I was unclear about it), which is to build a table which does, in fact, have one field for each question, which I populate with a script that looks at each record in the questions table and, based on it's number, sets a variable and then the proper field in the table with one field for each question.  It's cumbersome, but it let's me write the final output with considerations like:  If number of children is greater than 3 and your share of expected health care costs is greater than 40%, then an amount no greater than 25% of Adjusted Gross Income (where the bold represents fields in the table with 180 fields).

 

[Wim Decorte]

1 hour ago, drose0321 said:

It's cumbersome, but it let's me write the final output with considerations like:  If number of children is greater than 3 and your share of expected health care costs is greater than 40%, then an amount no greater than 25% of Adjusted Gross Income (where the bold represents fields in the table with 180 fields).

 

 

Not only is it cumbersome, it's almost certainly entirely unnecessary.  It would help to understand your thinking into why you feel you can't achieve the final output with the proper data structure that does not require one field per question.

(If it wasn't clear from the other posts in the thread: having one field per question is a really bad idea).

[drose0321]

Nobody has yet to propose a different solution.  As you'll see from my post above, I need to extract information from the generic answer fields and use it to determine the output based on various answers to specific questions, I can do it with variables, but that's just one step less than sticking the values into a table and I have to re-run the script to acquire the variables every time the application is reopened,  so a table seems like a better choice. 

[Wim Decorte]

42 minutes ago, drose0321 said:

Nobody has yet to propose a different solution.  As you'll see from my post above, I need to extract information from the generic answer fields and use it to determine the output based on various answers to specific questions, I can do it with variables, but that's just one step less than sticking the values into a table and I have to re-run the script to acquire the variables every time the application is reopened,  so a table seems like a better choice. 

Perhaps you should show the script that you use to do the thing that you describe as "I can do it with variables,..."  I don't follow that part.  Rereading the whole thread: it is not entirely clear to me what your desired outcome is.  I'm guessing a printout of the survey showing the relevant questions and answers?

That should be completely doable without collecting anything in variables or dumping data from variables into a separate table with one field per question.  Most of the answers provided so far point in that direction but I think we're lacking a lot of specifics as to what you are currently doing or what you want to end up with.

If what I wrote in my first paragraph is what you are after, then the suggestions provided so far are on the right track.

For tables/entities you have:

1- Questions (all possible questions)

2- Survey types (vacation, wedding, whatever,...)

3- Survey template (all possible questions for a given survey type - a join table between 1 and 2)

4- Question rules (this is the most complex one since it describes the path through the survey, the conditional aspect of what question from the survey template is served up next, is optional, is skipped,...)

5- Surveys (the parent entity for someone starting to answer questions)

6- Questions on the Survey (questions taken from the survey template)

7- Answers to the Questions

When a survey is completed you basically have nothing but a found set of Q&A records for the survey and outputting / printing that is dead-simple and does not require collating that data in yet another table.

If you don't agree with these entities, let us know which ones you don't see and why?

[drose0321]

I'm sorry. I'm doing a terrible job explaining this project.  It's not a generic questionnaire , the end product is a medical/legal document with boilerplate and specific answers in specific places and calculated results (based on other specific answers) in other places.  So, it's like a questionnaire in that the user answers lots of questions, but that's just to gather the specific information needed to generate the documents.  Then we need to parse the answers to extract the specific information we need.  Does that make it clearer? 

[Ocean West]

This was a solution I worked on years ago in FM 11 I have not upgraded yet for 16, there are a lot of better ways to make it more efficient. 

setting up a survey you setup all your questions and answers and you can decide the type of information you require as an answer

• text
• number
• currency
• date
• duration
• single choice
• multiple choice
• likert scale

branching logic can be made so that if you choose a particular question you could go down a chain of new questions.

The end result is a table of records for each participant that will have a record for each question on the survey. Various means of formatting this for a printed report obtainable. however because data is not normalized out additional logic and thought would have to be considered if you are wanting all respondents answers be aggregated. 

https://cl.ly/0p310i1K3S1P

[drose0321]

Thanks. I'd like to see how you handle the data type control and branching.  If I understood the video,it looks like you end up with all the answers in a table with generic fields, Question/Answer/Number - So I still need to parse the data by question number to extract the answers to fill in the forms (and make the calculations) that I need as my output.  Again, to me it seems that the only way to do this is is to look at each record in the questions table, set a variable based on it's record number and then, optionally (although I opt to do it) use the variables to set fields (one filed for every question) that I can then use to create the legal/medical document (with specific answers to specific questions in specific places and various calculations based on specific answers)  that I need in the end. 

[comment]

1 hour ago, drose0321 said:

 It's not a generic questionnaire , the end product is a medical/legal document with boilerplate and specific answers in specific places and calculated results (based on other specific answers) in other places.

I think by now two things have become clear:

1. Your application is not a survey and most, if not all, advice given to you under this wrong impression is irrelevant to your real situation;

2. Filemaker is not a particularly well-suited tool for creating this type of application. I am not saying it's impossible to create it in Filemaker, but since the intended product is not a database, it will be less than ideal. 

You have two conflicting requirements here: (a) show each question separately; (b) define calculations taking several answers to specific questions as the input. I see several possible solutions to this conflict, none of them very attractive:

• You could have a field for each answer and use a very long script to collect the answers, one by one, using a global field to capture the user input and distribute it to the appropriate field;
• You could collect the answers into individual records and use a table of Calculations to produce the required results. The relationship would make all Questions related, and the calculation fields would use the GetNthRecord() function to pick among them.
• Combine the two by collecting the answers using individual records, then running a script to transfer them into individual fields in another table (where the calculation fields would be also living).

 

[drose0321]

Ok.  Thanks.  I basically am doing your first option.  Filemaker is a requirement for this project and when the only tool you have is a hammer...

[Wim Decorte]

If the end result is a legal document then creating the output is a matter of merging data with the template document.  The actual document template should be in Word or a similar tool that is good at that. Word and its counterparts are very good at allowing data to be merged into templates.

So you have the two challenges:

1- guide the user through the right questions, in FM.

2- merge the answers into a document (FM data --> the document template)

I'll disagree on the 'only tool' bit, use a document creation tool for the template; don't do that part in FM.  Let FM be the data and set up the merge.

 

[drose0321]

I'd love to do it as a merge with Word, but unfortunately, that is not an option here.  Thanks!

[Wim Decorte]

Why not?

[drose0321]

Because this will ultimately be a runtime app distributed to our clients (we are a public interest law firm) and we can't count on even a significant portion having word.  We need a self-contained solution...

[Wim Decorte]

Hmmm... I would seriously reconsider making an app that produces a (binding?) legal document and that people will have physically on their machines.  But that's just me and maybe I'm making the legal aspect too big.

Might a web app work for your scenario?

Without integration with a proper document generation tool, you still don't need to take data from the answers and put them in a separate table.  Can you give us some examples of boilerplate text where answer data would need to be inserted?

[drose0321]

It's complicated.  Because we are a public interest law firm, many of our clients don't pay any fee.  The point of the software is to let clients determine if they can file themselves (and then produce the necessary forms) or if they require assistance from us.  The lawyers here are uncomfortable (from a liability and legal ethics standpoint) with storing "non-client" data on a server that we own or control.  So, this is the compromise solution.