Resco Inspections data model

From Resco's Wiki
Jump to navigation Jump to search
Resco Inspections
Backend-specific
Innovations
Miscellaneous

Resco Inspections utilizes the data-driven architecture rather than the metadata-driven approach. It means that the Resco Inspections data model (entities, fields, relationships) is already created, and when building a new custom questionnaire template you are not altering the data model – instead, you’re adding the 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.

All questionnaire data is saved as records on your backend server (Resco Cloud/Dynamics/Salesforce), in multiple custom entities:

  • 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
  • Question Group (resco_questiongroup) stores information about the question groups. Each question group can contain multiple questions.
  • 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 (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.

Inspections data model.png

Questionnaire template

Each questionnaire template consists of the base Questionnaire record with linked Questions and Question groups according to the design of the template as defined in the Questionnaire Designer.

These records together form the definition of the template. The template contains the descriptive data, e.g., the definition of styles, business logic, and question setup.

Standard record-based storage setup

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 records as the questionnaire template. This means that each answered questionnaire creates a Questionnaire record, Question group records, and Question records. These also include descriptive data. This way, the answered questionnaires are fully independent of the template, however, they occupy much more storage.

Template dependency

With template dependency, each answered questionnaire generates the same set of records 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.

Entity 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 records 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 record. 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.

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.