Slow Summaries

[aubaine]

I have just started working with FM10, so please excuse my newbness. I have imported several tables from CSV files downloaded from Survey Monkey. The goal of this database is to easily import CSV files downloaded from Survey Monkey that will automatically calculate the statistical data.

The CSV files that Survey Monkey gives you are:

Collectors (gives the survey title and a unique CollectorID for each survey)

Respondents (gives a unique RespondentID for each person completing the survey)

Questions (gives each question a unique QuestionID)

Question Options (Gives each option for each question a unique OptionID as well as the text for each question option, and its related QuestionID)

Responses (gives each response a unique Key1, Key2, and/or Key3 for each RespondentID's responses to the associated QuestionID)

The various Keys are related to the OptionIDs depending on the type of question.

I have set up all of the relationships so that I can pull up all of the relavant data, and I have started to create Count ([if (QuestionOptions 2::OptionText = "2007";1;0)] for example to get all answers whose text match "2007") and Summary fields (summarizing each Count field) to count the number of instances of defined OptionIDs (responses) for each QuestionID in the survey.

The problem is that with 4,500 respondents to the survey, and with each response to each question from each respondent being a new record, there are about 550,000 records in the "Responses" table. With each possible answer to each question requiring two fields (one for the Count function, and another Summary field to summarize the Count field to get a total) there are a lot of summaries going through a lot of records, making loading extremely slow. I want a layout that will show all of the question options to get a picture of how many respondent chose each option, but with each one taking a minute or two to load, the layout will take forever to load completely.

Is there any way to make this more efficient? Creating 2 fields for every possible answer in the survey is cumbersome enough, but the loading times are unbearable on top of that! I thought that I could just store the fields, but because they all reference a separate table I can't.

Any help would be appreciated. IF I failed to provide any important information, please let me know.

Edited December 9, 2009 by Guest

[comment]

I want a layout that will show all of the question options to get a picture of how many respondent chose each option

This is best done by producing a report from the Responses table, sub-summarized by question and by response. The only field required for this (other than what your survey service supplies) is a summary field defined as Count of ResponseID.

[aubaine]

The problem is that what you suggest (a count of the ResponseIDs) will only give me the total number of responses. What I need is the total for each unique entry (total number for each response selected).

[comment]

No, the same summary field will show sub-summary values when placed in a sub-summary part.

[aubaine]

Wow - that is awesome! Thanks so much for your help!

[aubaine]

Ok, the problem I'm having now is that I can't properly format the report to give me the information that I want.

I have two subsummaries: one by the question title, and the second by the optiontext. Then the count of response summary is in the second subsummary. I have no body because it only lists the instances of each response, and i only need the count, which i have.

The problem is that I can't seem to figure out how to limit the returns properly. For example, I want a report that will give me the total of each response to each question for all respondants that chose a specific answer to a question. However, when I perform a find for that responsetext, the report only shows the one response, whereas I want to see what those respondants chose for other questions as well.

Any ideas?

[comment]

First, I'd suggest you base your sub-summaries (and the sort order) on the "local" QuestionID and QptionID fields in the Responses table (you can still display the related text fields on the layout).

Now, to find all responses by respondents that chose a specific answer to a specific question, you could:

1. Go to a layout of Respondents and search the related fields Responses::QuestionID and Responses::OptionID for the required question and answer. This will you give you a found set of the respondents you are interested in;

2. Go to Related Record [from Responses; Match found set]. This will give you all the responses by the respondents selected earlier, and only by them - so you only need to sort them to run your report. Note that this method might be slow - see:

http://fmforums.com/forum/showtopic.php?tid/202496/post/321783/#321783

[aubaine]

Thanks again for your help on this. I think I'm starting to get there.

I'm not sure I understand part 2. Can you explain this in more detail? Do you mean to go to the Responses layout (from the Respondant layout) and then repeat the previous search? I think I'm missing a step here.

Edit: Nevermind, I think I understand what you meant now. I wasn't familiar with the Go to Related Record button script. I'll play around with that and see if I can do what you suggest.

Edit2: I got it! And it work perfectly!!! Thank you so much - you are awesome!

Edited December 11, 2009 by Guest