Generate Survey123 Report

[ChelseaRozek]

Really excited about the latest Python API update. Would like to be able to export survey responses as reports with Python. I'm currently stuck at this part. I can see that the reports get generated and I can download them (image below from Survey123 website), but I receive an error in the code: KeyError: 'results' (see attachment). I'm also curious if you can access the generated reports directly with Python after making them.

[JohnStowell]

Hi Michelle, if you are getting syntax errors with print() that would indicate you are using Python v2.

My code is written for Python v3. Can you check which version you are using? 

[MichelleWilliams1]

Thank you for your quick response. I was using Python v2.

v3 - The code seems to run, but I'm getting zero reports, and I submitted a new survey today, any ideas why?

--- STARTING REPORT GENERATION PROCESS --- 2020-02-24 13:59:55.291881
Initialising session in AGOL
Reading Survey123 information for ID: 43335467cc64408d8928c890124a9ee6 
Selected template: <Item title:"BSI Infraction Letter2020B.docx" type:Microsoft Word owner:WilZZZ >
Generating report(s) for submissions from last 24 hours
Downloading relevant report(s) to: C:\Users\WilZZZ\Documents\BSIFeatureReportsPY
REPORTS GENERATED: 0
--- REPORT GENERATION PROCESS - FINISHED ---

--- STARTING EMAIL PROCESS ---
Getting list of Word docx files in: C:\Users\William5\Documents\BSIFeatureReportsPY
Files:
Reading raw table data from Word document(s)
DOCUMENTS SENT TO RECIPIENTS: 0
--- EMAIL PROCESS - FINISHED ---

[JohnStowell]

I've seen that issue with another user of the script, not sure if they were able to get it working or not?

cc Simon Allard

I think it is related to the where clause of the script. Try changing:

where_filter = '{"where":"CreationDate >= CURRENT_TIMESTAMP - INTERVAL \'1\' DAY"}'

to:
where_filter = '1=1'
And see if you have any luck. Also check the utc_offset variable is correct for your timezone.

If that works, you might need to play around with the where query at your feature server query page to see what works for your specific dataset (eg. https://....arcgis.com/.../ArcGIS/rest/services/.../FeatureServer/0/query )

This page helps to explain what the date query parameters should look like:

Querying Feature Services: Date-Time Queries 

[SimonAllard2]

Heya, I never did get it to work. I used the same where clause that worked when tested on the rest end service.

I removed the where clause and used the default 1:1 and got all the reports to export out!

I need to spend some more time on it, maybe if it becomes of buisness critical importance...

Cheers,

Simon

Get Outlook for Android<https://aka.ms/ghei36>

[MichelleWilliams1]

Thank you, 

I've updated my utc_offset and made the where_filter = {"where":"LetterPrinted='no', AND Submit='yes'"} let me know if this is correct #Newbie.

I'm getting this error:

  File C:\... line 145, in main
     if surveyID in x.description:
TypeError: argument of type 'NoneType' is not iterable

I'm not sure if my where_filter or something else. Thank you for your time.

[ElizabethBurniston]

I have the script working fine through Task Scheduler. It creates and drops the word document in AGOL, and has successfully accessed the feature report template. However, the report does not output any of the Survey123 results. It looks to be just a copy of the template. Can you tip me off as to where I have gone wrong? Thank you!

[Anonymous User]

Thank you for this script. It helped me fight through the process of automating download of feature reports from Survey123. However, I have a question. I have an Excel list of about 500 feature reports that need to be downloaded. The list includes a lot of identifying information from the records in the survey's feature service. For example, the list includes a field called "1. WMIS Number:", which is a unique identifier. I need to query this field for the where_filter, and after reading your script, I tried the following as well as some other queries:

where_clause = '{"where":"1. WMIS Number: = \'100615\'"}'
where_clause = '{"where":"1. WMIS Number:=\'100615\'"}'

Neither of these worked. I attached a screenshot of the field in Survey123. Do you have any suggestions?

[BHeist]

Drew,

I was able to get the 'Where' clause to work using this syntax:

"last_edited_date" was the field in my layer that I wanted to query by. This generated a report from my survey for the last 7 days successfully 

where_filter = "last_edited_date >= CURRENT_TIMESTAMP - INTERVAL '7' DAY"

survey.generate_report(template, where_filter, "+0400", "NCLF Daily Flare Inspection")

[ChrisBerryman]

I have been trying to get this to work for awhile, and sometimes I notice that the reports get generated and are in the temp folder on my C drive within a zip when I use "1=1", however when I try to use a where statement "created_date >= CURRENT_TIMESTAMP - INTERVAL '100' DAY", it completes but does not create any reports.  any ideas?

from arcgis.gis import GIS
import os
from zipfile import ZipFile
from arcgis.apps.survey123._survey import SurveyManager, Survey

gis = GIS("portal url","un","pw") #fill in 3 strings here
survey_mgr = SurveyManager(gis)
survey = survey_mgr.get("32a7416f7af14d8db2b20f19ca95be5b") #fill in 1 string here
print(survey.report_templates) #see all available print templates for this survey
surveytemplate = survey.report_templates[0] 
print(surveytemplate)

reportTitle = "str=${lineid}_TEFIS_${tefis}_StrNo_${strno}"
whereQuery = "created_date >= CURRENT_TIMESTAMP - INTERVAL '100' DAY"
output_format = '{"output_format":"str=pdf"}'
print(whereQuery)
try:
      print('Trying to generate...')
      survey.generate_report(surveytemplate, "created_date >= CURRENT_TIMESTAMP - INTERVAL '100'       DAY")
      print(survey.reports)
      print('Finished')
except Exception as e:
      print(e)

[BHeist]

Chris,

Not sure if this would make a difference or not but have you tried passing your where_filter variable into the survey.generate_report function as a parameter? Instead of using the SQL statement as the parameter.

I would also double check your use of the "created_date" field and make sure it's the correct field both in it being a date/time object and that the name is correct. 

like so:

where_filter = "created_date >= CURRENT_TIMESTAMP - INTERVAL '100' DAY"

survey.generate_report(surveytemplate, where_filter)

Also, maybe try a different day interval and see if it's the use of 100 days that's throwing it off? Maybe try 7 and see if that works?

Hope this helps!