{"id":402,"date":"2015-12-07T22:03:16","date_gmt":"2015-12-08T03:03:16","guid":{"rendered":"http:\/\/hardlikesoftware.com\/weblog\/?p=402"},"modified":"2015-12-07T22:03:16","modified_gmt":"2015-12-08T03:03:16","slug":"how-to-identify-survey-builder-respondent","status":"publish","type":"post","link":"https:\/\/hardlikesoftware.com\/weblog\/2015\/12\/07\/how-to-identify-survey-builder-respondent\/","title":{"rendered":"How to identify Survey Builder respondent"},"content":{"rendered":"<p>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 <a href=\"http:\/\/www.sei.cmu.edu\/reports\/05hb004.pdf\">Designing an Effective Survey<\/a> by Mark Kasunic:<\/p>\n<blockquote><p>\n&#8220;A survey, when conducted properly, allows you to generalize about the beliefs and opinions of many people by studying a subset of them&#8221;\n<\/p><\/blockquote>\n<p>Survey research lets you say with confidence &#8220;87% of our customers feel that the produce makes them more productive&#8221; not that John Snyders thinks he is more productive after using the product.<\/p>\n<p><!--more--><\/p>\n<p>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&#8217;t intrinsically know who the people in the population are.<\/p>\n<p>Here are some possibilities for tracking who filled in the questionnaire:<\/p>\n<ul>\n<li>Add a text question in the questionnaire that prompts for their name or other identifying information. Obviously this identity is not authenticated.<\/li>\n<li>If you distribute the survey by email the email address is already stored in the <code>EBA_SB_RESPONSES<\/code> table in column <code>email_addr<\/code>. 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.<\/li>\n<li>Unlock the app and change the code to make it do whatever you want. I gave some general information about the <a href=\"http:\/\/hardlikesoftware.com\/weblog\/2013\/07\/22\/survey-builder-design\/\">design of survey builder<\/a> 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.<\/li>\n<\/ul>\n<p>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 <b>a<\/b> person, its just that by design you don&#8217;t know who that person is. It is a small step to making the association between the person and the response as I&#8217;ll describe below. I always intended that random sample surveys could be distributed from other web sites\/apps but I don&#8217;t think I ever explained how. I&#8217;ll cover that too.<\/p>\n<p>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 <a href=\"http:\/\/hardlikesoftware.com\/weblog\/download\/apex\/SampleAppWithSurvey.zip\">download<\/a> a zip with the sample app and survey described here. You will need to have Survey Builder installed in your workspace as well.<\/p>\n<p>I created an APEX app called Simple App (with survey). It looks like this.<\/p>\n<p><img loading=\"lazy\" src=\"http:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_2.png\" alt=\"Simple App (with survey)\" width=\"673\" height=\"233\" class=\"aligncenter size-full wp-image-418\" srcset=\"https:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_2.png 673w, https:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_2-300x104.png 300w, https:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_2-672x233.png 672w\" sizes=\"(max-width: 673px) 100vw, 673px\" \/><\/p>\n<p>There are three hidden fields on the page<\/p>\n<ul>\n<li>P1_SURVEY_ID with source:<br \/> <code>select id from eba_sb_surveys where name = 'Simple App Survey'<\/code><\/li>\n<li>P1_SURVEY_KEY with source:<br \/> <code>select row_key from eba_sb_surveys where name = 'Simple App Survey'<\/code><\/li>\n<li>P1_SURVEY_URL which is initially empty (source is null)<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>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:<\/p>\n<pre style=\"white-space:pre;word-wrap:normal\"><code>CREATE table \"SA_SURVEY\" (\r\n  \"ID\" NUMBER,\r\n  \"RESPONSE_ID\" VARCHAR2(16),\r\n  \"SURVEY_ID\" NUMBER,\r\n  \"USERNAME\" VARCHAR2(500),\r\n  \"POSTAL_CODE\" VARCHAR2(10),\r\n  constraint \"SA_SURVEY_PK\" primary key (\"ID\")\r\n)\r\n\/\r\nCREATE sequence \"SA_SURVEY_SEQ\"\r\n\/\r\nCREATE trigger \"BI_SA_SURVEY\"\r\n  before insert on \"SA_SURVEY\"\r\n  for each row\r\nbegin\r\n  if :NEW.\"ID\" is null then\r\n    select \"SA_SURVEY_SEQ\".nextval into :NEW.\"ID\" from sys.dual;\r\n  end if;\r\nend;\r\n\/\r\n<\/code><\/pre>\n<p>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&#8217;t matter what the questions are but here is what my questionnaire looks like.<\/p>\n<p><img loading=\"lazy\" src=\"http:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_3.png\" alt=\"Survey\" width=\"673\" height=\"233\" class=\"aligncenter size-full wp-image-418\" \/><\/p>\n<p>From the Survey Details page make the survey active by clicking Begin Test or Begin Survey.<\/p>\n<p>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 <code>SA_SURVEY<\/code> table along with the current username and postal code. JavaScript is used to open the survey URL in a new window.<\/p>\n<p>I created a dynamic action for the button. The first action is Execute PL\/SQL Code.<\/p>\n<p>Page Items to submit are:<br \/> <code>P1_SURVEY_ID,P1_POSTAL_CODE,P1_SURVEY_KEY<\/code><br \/>\nPage Items to return are:<br \/> <code>P1_SURVEY_URL<\/code><\/p>\n<p>The PL\/SQL code is:<\/p>\n<pre style=\"white-space:pre;word-wrap:normal\"><code>declare\r\n    response_id sa_survey.response_id%type;\r\nbegin\r\n    response_id := eba_sb.add_response(:P1_SURVEY_ID);\r\n    insert into sa_survey (response_id, survey_id, username, postal_code)\r\n        values (response_id, :P1_SURVEY_ID, :APP_USER, :P1_POSTAL_CODE);\r\n    :P1_SURVEY_URL := 'https:\/\/apex.oracle.com\/pls\/apex\/f?p=90305:Q::' ||\r\n        :P1_SURVEY_KEY || ':::code:' || response_id;\r\nend;\r\n<\/code><\/pre>\n<p>This takes the survey id, and survey key as inputs and returns the unique survey URL. It uses the survey builder <code>add_response<\/code> function. This function generates the unique response code while inserting a new record into <code>EBA_SB_RESPONSES<\/code>. 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.<\/p>\n<p>The next action is Execute JavaScript Code. Set Fire On Page Load to No. The code is:<\/p>\n<pre style=\"white-space:pre;word-wrap:normal\"><code>var url = $v(\"P1_SURVEY_URL\");\r\nif (url) {\r\n    apex.navigation.openInNewWindow(url);\r\n}\r\n<\/code><\/pre>\n<p>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:<\/p>\n<p><img loading=\"lazy\" src=\"http:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_4.png\" alt=\"Questionnaire\" width=\"459\" height=\"617\" class=\"aligncenter size-full wp-image-420\" srcset=\"https:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_4.png 459w, https:\/\/hardlikesoftware.com\/weblog\/wp-content\/uploads\/2015\/12\/sb_identity_4-223x300.png 223w\" sizes=\"(max-width: 459px) 100vw, 459px\" \/><\/p>\n<p>When they submit their answers you can use the information in <code>SA_SURVEY<\/code> 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 <code>SA_SURVEY<\/code> and <code>EBA_SB_RESPONSES<\/code> and checking if <code>COMPLETED_DATE<\/code> 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.<\/p>\n<p>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_*).<\/p>\n<p>This same technique of using <code>eba_sb.add_response<\/code> 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&#8217;t actually need to associate the user with the responses.<\/p>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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<\/p>\n<div class=\"more-link-wrapper\"><a class=\"more-link\" href=\"https:\/\/hardlikesoftware.com\/weblog\/2015\/12\/07\/how-to-identify-survey-builder-respondent\/\">Continue reading<span class=\"screen-reader-text\">How to identify Survey Builder respondent<\/span><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18,3],"tags":[41,42],"_links":{"self":[{"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/posts\/402"}],"collection":[{"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/comments?post=402"}],"version-history":[{"count":27,"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/posts\/402\/revisions"}],"predecessor-version":[{"id":433,"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/posts\/402\/revisions\/433"}],"wp:attachment":[{"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/media?parent=402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/categories?post=402"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hardlikesoftware.com\/weblog\/wp-json\/wp\/v2\/tags?post=402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}