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

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.

{
  "formatversion": 1,
  "answers": [
    {
      "questionid": "a4027e94-d125-48f8-a41b-08095d9b42a3",
      "name": "resco_regardingid",
      "answeredon": "2021-02-15T13:01:58.0864501Z",
      "value": "account, f44f57be -1afb-4020-8ad1-113a2ce83520, testing55",
      "index": 7,
      "kind": 473220007,
      "label": "Related To",
      "style": "Normal",
      "valuelabel": "testing55",
      "showonreport": true
    },
    {
      "questionid": "7133d3 ee-5a99-45dc-81b8-91ec9f305abc",
      "name": "lookup#001",
      "answeredon": "2021-02-15T13:02:04.1715884Z",
      "value": "account, 377aaabc -ef82-4elc -9bc8-524961fa5374, testin44",
      "questiongroupid": "resco_questiongroup, ee7d6923-515e-45ff-a010-8elea14 bef8b, group#001",
      "index": 3,
      "kind": 473220007,
      "label": "Lookup",
      "options": "<lookup><extra><filter entity=\"account\"></filter></extra></lookup>",
      "style": "Normal",
      "valuelabel": "testin44",
      "showonreport": true
    }
  ]
}

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

• Depending on the customization, the sync upload time is reduced to 10-15%.
• We expect a decrease in the sync download times, although the effect will be smaller.

The storage impact of JSON format depends on whether we talk about the server or the client:

• For a client with efficient customization (i.e., not using fields such as ownerid, statuscode, createdby…) JSON might even slightly increase consumed storage.
• On the server side, JSON will bring some savings. This is because JSON skips standard entity fields (ownerid, createdby…), and some OOB indexes (5+) are removed for resco_question and resco_questiongroup entities.

Note	Newer, customizable JSON format further reduces the database footprint. 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.

Conclusions

As the questionnaire templates vary in terms of the number of questions, question groups, and complexity, it is not possible to measure the exact storage impact of the JSON format. As a rough statement, it is possible to say that JSON won’t have a big impact on the client storage but will bring some storage savings on the server side.

On the other hand, JSON impact on the sync performance will be enormous, especially for the upload phase.

Compressed JSON can yield reduced storage needs, but its impact on sync is marginal (HTTP data is compressed by default).

Template dependency can save 60-80% of the storage in a typical scenario. By reducing the number of transferred fields, it will also significantly improve the sync performance.

Was this information helpful? How can we improve?