It has been a while since I have worked on the APEX Survey Builder packaged application but I still get questions about it. One question that comes up repeatedly is how to associate the questionnaire responses with the person that responded. At first this question surprised me somewhat because survey research is about populations not individuals so surveys should be anonymous. To quote Designing an Effective Survey by Mark Kasunic:
“A survey, when conducted properly, allows you to generalize about the beliefs and opinions of many people by studying a subset of them”
Survey research lets you say with confidence “87% of our customers feel that the produce makes them more productive” not that John Snyders thinks he is more productive after using the product.
The confusion comes from equating filling out a questionnaire, which can be done for many different reasons, with survey research. Survey Builder was supposed to be about survey research but it is pretty good at creating and filling in questionnaires. So it is understandable that people want to use it in various ways including associating responses with the person that filled in the questionnaire. This means that the confusion was mine and that perhaps a future version of Survey Builder should include some mechanism to attach additional information including the respondents identity to a response. A complication in doing this is that the users of Survey Builder are the people creating surveys not the people filling out questionnaires; Survey Builder doesn’t intrinsically know who the people in the population are.
Here are some possibilities for tracking who filled in the questionnaire:
- Add a text question in the questionnaire that prompts for their name or other identifying information. Obviously this identity is not authenticated.
- If you distribute the survey by email the email address is already stored in the
EBA_SB_RESPONSEStable in columnemail_addr. It is just that we never report this information to keep the survey anonymous. You could create your own reports on this table from another application. - Unlock the app and change the code to make it do whatever you want. I gave some general information about the design of survey builder before that may be helpful if you go down this road. It may help you to realize why simply enabling single sign-on for Survey Builder or trying to pass information into the questionnaire via the URL will not work.
The best way to accomplish this today is related to how I envisioned distributing surveys through web sites. Survey Builder makes it easy to send email invitations to take a survey but another way is by providing a link to the survey in another web site, blog, or web application. This is easy if the survey is Self Selected (anyone can choose to fill in the questionnaire) because the URL is the same for everyone. The drawback is that there is no way to associate the responses with who filled them in. Random Sample surveys are uniquely associated with a person, its just that by design you don’t know who that person is. It is a small step to making the association between the person and the response as I’ll describe below. I always intended that random sample surveys could be distributed from other web sites/apps but I don’t think I ever explained how. I’ll cover that too.
Lets suppose that you have an APEX app and you want people to answer some questions about it and you want to know who provided the answers. This is just one scenario but should serve as a good example that can be adapted to many other situations. You can download a zip with the sample app and survey described here. You will need to have Survey Builder installed in your workspace as well.
I created an APEX app called Simple App (with survey). It looks like this.
There are three hidden fields on the page
- P1_SURVEY_ID with source:
select id from eba_sb_surveys where name = 'Simple App Survey' - P1_SURVEY_KEY with source:
select row_key from eba_sb_surveys where name = 'Simple App Survey' - P1_SURVEY_URL which is initially empty (source is null)
The button behavior is provided by a dynamic action that I will come back to. There is no real purpose to the Postal Code field. It is just an example of how you can include any amount of additional data about the respondent.
I used SQL Workshop to create a table to store the association between the survey response id and the username. The resulting SQL to create the table is:
CREATE table "SA_SURVEY" (
"ID" NUMBER,
"RESPONSE_ID" VARCHAR2(16),
"SURVEY_ID" NUMBER,
"USERNAME" VARCHAR2(500),
"POSTAL_CODE" VARCHAR2(10),
constraint "SA_SURVEY_PK" primary key ("ID")
)
/
CREATE sequence "SA_SURVEY_SEQ"
/
CREATE trigger "BI_SA_SURVEY"
before insert on "SA_SURVEY"
for each row
begin
if :NEW."ID" is null then
select "SA_SURVEY_SEQ".nextval into :NEW."ID" from sys.dual;
end if;
end;
/
Then I signed into Survey Builder and created a survey called Simple App Survey. It is important that I choose Random Sample for the survey type. For my example it really doesn’t matter what the questions are but here is what my questionnaire looks like.
From the Survey Details page make the survey active by clicking Begin Test or Begin Survey.
Now back to the Take Survey button in Simple App. The general idea is that when the button is clicked a new Survey Builder response record is created. This generates the unique random code that you see in questionnaire URLs. Then this same code is written to the SA_SURVEY table along with the current username and postal code. JavaScript is used to open the survey URL in a new window.
I created a dynamic action for the button. The first action is Execute PL/SQL Code.
Page Items to submit are:
P1_SURVEY_ID,P1_POSTAL_CODE,P1_SURVEY_KEY
Page Items to return are:
P1_SURVEY_URL
The PL/SQL code is:
declare
response_id sa_survey.response_id%type;
begin
response_id := eba_sb.add_response(:P1_SURVEY_ID);
insert into sa_survey (response_id, survey_id, username, postal_code)
values (response_id, :P1_SURVEY_ID, :APP_USER, :P1_POSTAL_CODE);
:P1_SURVEY_URL := 'https://apex.oracle.com/pls/apex/f?p=90305:Q::' ||
:P1_SURVEY_KEY || ':::code:' || response_id;
end;
This takes the survey id, and survey key as inputs and returns the unique survey URL. It uses the survey builder add_response function. This function generates the unique response code while inserting a new record into EBA_SB_RESPONSES. Make sure you also set Wait For Result to Yes so that the next action can access the URL returned. Set Fire On Page Load to No. Clearly you need to modify the URL for your environment.
The next action is Execute JavaScript Code. Set Fire On Page Load to No. The code is:
var url = $v("P1_SURVEY_URL");
if (url) {
apex.navigation.openInNewWindow(url);
}
That is all there is to it. When a user signs into Simple App they can fill in their postal code and click Take Survey. The survey looks like this:
When they submit their answers you can use the information in SA_SURVEY to associate the responses with the logged in user. You could enhance the logic in many ways. For example you could first check to see if the user has already filled in the survey by joining SA_SURVEY and EBA_SB_RESPONSES and checking if COMPLETED_DATE is not null. If they have taken the survey return null for the URL and the JavaScript code can display a message in that case explaining that they already took the survey.
Unfortunately this does nothing to enhance the reporting built in to Survey Builder. You will need to create your own custom reports in another application to include associated user data. The reporting in survey builder is not very advanced. But it does provide an XML export which you can use to export the data into other tools for more in-depth analysis (again you will need to merge in the user data). You can also write reports directly against the survey builder tables (EBA_SB_*).
This same technique of using eba_sb.add_response can be used to distribute a truly random sample survey from a web app. Using whatever means you have to track user identity in your APEX application randomly select a visitor (see chapter 3 of Designing an Effective Survey for selection considerations) to take the survey and use essentially the same code as above to generate a response and URL then redirect to that URL or open it in a new window. You don’t actually need to associate the user with the responses.
In any case the you should make the respondents aware of if the responses are anonymous or if they are personally identified and how the information will be used.