Resco Inspections data model

Resco Inspections utilizes the data-driven architecture rather than the metadata-driven approach. It means that the Resco Inspections data model (tables, columns, relationships) is already created, and when building a new custom questionnaire template you are not altering the data model – instead, you’re adding the table rows (entity records). As a consequence, the user who manages the questionnaire templates does not have to be a backend administrator since this user is not changing the data model.

Tables

All questionnaire data is saved as rows on your backend server (Dataverse/Resco Cloud/Salesforce), in multiple custom tables.

Questionnaire table

"Questionnaire" (resco_questionnaire) stores information about the questionnaire templates, snippets, and answered questionnaires. Each questionnaire can contain any number of question groups and questions.

How is a particular questionnaire record used depends on the following properties:

• As a questionnaire template (if Is_Template = Yes)
• As a snippet (if Is_Snippet = Yes)
• As a filled-in questionnaire

Answered questionnaires have a completion status field (resco_completionstatus) that can take three values (and null):

• Null: Same as [0]
• [0] Active (incomplete, in progress)
• [1] Completed
• [2] Canceled

Answered questionnaires with completion status "1" or "2" can no longer be modified.

In the lifecycle of questionnaire templates, they can take one of the following statuses (statuscode):

• [1] Active
• [473220000] Draft
• [473220001] Inactive
• [473220002] Published
• [473220003] Archived
• [473220004] Staged
• [2] Completed

Questionnaire Answer table

This table was introduced in release 17.1 as an alternate location for storing questionnaire answers. It supports only the most modern way of storing answers, i.e., in JSON format, therefore, it has much simpler structure compared to the questionnaire table.

Question Group table

"Question Group" (resco_questiongroup) stores information about the question groups. Each question group can contain multiple questions.

Question table

"Question" (resco_question) stores all the questions created for the questionnaire, as well as answers. The unformatted answer value can be found in the Value (resco_value) field; formatted value in the Value Label (resco_valuelabel) field.

Questionnaire Folder table

"Questionnaire Folder" (resco_questionnairefolder) stores information about the folder structure that organizes questionnaire templates in the designer. Each folder can contain multiple questionnaire templates. Usually, this entity does not need to be enabled in the app project.

See also: Dynamics 365 – how is the data stored? Blog

Questionnaire images

Answers to image/media-type questions are saved in the Note/Annotation entity.

• annotation.objectid points to resco_questionnaire
• annotation.subject equals to resco_question.resco_name

Additional conventions apply to images in repeatable groups.

Questionnaire templates

On data level, each questionnaire template consists of the base Questionnaire with linked Questions and Question Groups according to the design of the template as defined in the Questionnaire Designer.

Together, these table rows form the definition of the template. In addition to questions, the template includes descriptive data, e.g., the definition of styles, business logic, static images, report definition.

Questionnaire answers

Legacy record-based storage setup for answered questionnaires

When an app user starts answering a questionnaire in the app, a new copy of the template is created. With this setup, each answered questionnaire is saved as the same set of table rows (entity records) as the questionnaire template. This means that each answered questionnaire creates one Questionnaire row, Question group rows, and Question rows. These also include descriptive data. This way, the answered questionnaires are fully independent of the template. However, they occupy much more storage.

Note	Record-based storage is being deprecated in favor of JSON storage. See Active deprecations for more information.

Template dependency

With template dependency, each answered questionnaire generates the same set of rows as the standard storage setup. However, the descriptive data is removed. This data is always read from the linked template. This way, a lot of storage space can be saved. Using template dependency reduces the database footprint and increases sync performance.

Note	Template-independent templates are being deprecated. See Active deprecations for more information.

Table of template-dependent and template-independent fields

Table	resco_questionnaire	resco_question	resco_questiongroup
Core fields	createdby, createdon, modifiedon, overriddencreatedon, ownerid, resco_completionstatus, resco_duration, resco_folder, resco_issnippet, resco_istemplate, resco_languagecode, resco_name, resco_questionnaireid, resco_regardingid, resco_regardingidlabel, resco_regardingidname, resco_result, resco_reusefromprevious, resco_score, resco_serializedanswers, resco_templatedependent, resco_templateid, resco_version, resco_versionname, statuscode	createdby, createdon, modifiedon, overriddencreatedon, ownerid, resco_answeredon, resco_displayformat, resco_enabled, resco_icon, resco_index, resco_isseparator, resco_kind, resco_name, resco_questiongroupid, resco_questionid, resco_questionnaireid, resco_rawdatevalue, resco_rawidvalue, resco_rawnumericvalue, resco_required, resco_reusefromprevious, resco_score, resco_showonreport, resco_value, resco_valuelabel, resco_visible, statuscode	createdby, createdon, modifiedon, ownerid, resco_expanded, resco_name, resco_questiongroupid, resco_questionnaireid, resco_repeatindex, resco_templategroupid, resco_visible, statuscode
Template-dependent fields	resco_archivedon, resco_autoreport, resco_description, resco_folderid, resco_layout, resco_localization, resco_options, resco_publishedon, resco_publishnotes, resco_reportingproperties, resco_reusefetch, resco_rules, resco_script, resco_styles	resco_answerstorage, resco_defaultvalue, resco_description, resco_label, resco_localization, resco_max, resco_min, resco_options, resco_precision, resco_reporting_properties, resco_rules, resco_style	resco_description, resco_index, resco_label, resco_localization, resco_repeatconfig, resco_reportingproperties, resco_rules

JSON storage

With JSON storage, the answered questionnaires do not produce any Question or Question group table rows at all. Instead, all the data is saved directly in the Questionnaire record. When JSON storage is enabled for the questionnaire template, Questions and Questions groups are packed into a JSON string and stored in the "resco_serializedanswers" field in the Questionnaire table row. For further reporting, this JSON needs to be parsed based on the names and values of the Questions and Question groups.

Here is a general example of the JSON that is stored in the "resco_serializedanswers" field.

{
  "@ver": "m1.0",
  "@q": {
    "ID": "417f5c8a-826d-4eb5-ace4-ee807c393933",
    "defanswers": 0
  },
  "@root": {
    "device-photo": {
      "c": 1,
      "s": "annotation"
    },
    "other-details": "Service Tag: 6VJR9D2",
    "serial-number": "S/N: CN-0292K4-74261-688-4U8L-A03",
    "name": "MADE IN CHINA",
    "part-number": "14968330022"
  }
}

The primary advantage of the JSON format is better sync performance:

• Depending on the customization, the sync upload time is reduced to 10-15%.
• Sync download times will be reduced as well.

Additionally, answered questionnaires require significantly reduced database space.

For more information about this format, see JSON storage for questionnaires.

Backend server limitations

For very large questionnaires (much larger than the recommended maximum of 200 questions), using JSON storage means that you can hit the maximum field size limit:

• On Salesforce, the limit is 128 kB
• On Dynamics, it's 1MB

In these scenarios, consider compressed JSON. JSON format aims to be readable for humans. Compression goes against this principle, but data size is reduced significantly.

More information

Read Best practices for deploying Inspections for advice on how to store questionnaire data most efficiently.

Was this information helpful? How can we improve?