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 template

On data level, each questionnaire template consists of the base Questionnaire row 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. The template contains the descriptive data, e.g., the definition of styles, business logic, and question setup.

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.

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": "f1.0",
  "@q": {
    "ID": "83a2a309-3af4-48f1-af5b-da6cd5801659",
    "defanswers": 1,
    "ds": "annotation"
  },
  "@root": {
    "name": {
      "v": "4180-AQ3"
    },
    "part-number": {
      "v": "PRODUCT ID: 4180AQ3"
    },
    "serial-number": {
      "v": "PI-74349"
    },
    "other-details": {
      "v": "TX FCC !D and IC Certification number for installed transmitter cards are located under the customer removable access panel, Keyboard, or Palmrest see user manual"
    },
    "device-photo": {
      "v": {
        "c": 1
      }
    }
  }
}

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 and compression reduces its size significantly.

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.

{{#CI form: title = Was this information helpful? How can we improve? | type = inputs | [textarea] }}