InspConverter

Warning

Work in progress! We are in the process of updating the information on this page. Subject to change.

InspConverter is a modified version of Resco Mobile CRM that can convert existing questionnaire answers to modern format:

• Change answer format from record-based to JSON.
• Convert template-independent questionnaires into template-dependent.

It is only available for Windows devices. InspConverter is available on demand: contact Resco support if you are interested.

Why convert questionnaires

Early questionnaire answer formats are prone to generate a lot of redundant data. Large volumes of data may cause various problems including slow synchronization times, timeouts, taking a considerable amount of server database, etc. These issues are discussed in the article Best practices for deploying Inspections.

Resco Inspections data model explains the basics of how data is handled in Resco Inspections. Read JSON storage for questionnaires for an in-depth discussion about the storage options.

Terminology

The term "template" refers to the questionnaire template as created in Questionnaire Designer. The terms "questionnaire" or "data questionnaire" or "questionnaire answer" refer to the filled (answered) questionnaire. Questionnaire answers are created in Resco Mobile CRM or in a different questionnaire player.

Both the templates and questionnaires are represented by the same CRM entity - resco_questionnaire. The difference between them is semantic: The template contains the definition of the UI and business logic, while the questionnaire represents data filled in by the user.

InspConverter workflow

InspConverter goal is:

• To convert server resco_questionnaire records to "template-dependent". (If not done already.)
• To convert server resco_questionnaire records to JSON. (If not done already.)

The user of InspConverter must select:

• Which questionnaires are converted (by specifying appropriate fetch).
• Which JSON format (FlexJson or MinJson) to use for the conversion.
  FlexJson format can be fine-tuned by selecting concrete questionnaire properties to be stored in JSON.

Here is the basic workflow of the conversion process, i.e., the steps carried out by InspConverter when a questionnaire is converted. The questionnaire is:

1. Downloaded from the server to the local computer,
2. Converted to template-dependent (if needed; a new template might need to be reconstructed),
3. Converted to the desired format (MinJson or FlexJson),
4. Uploaded back to the server.

If the questionnaire being converted is template-independent, InspConverter attempts to use the original template. (As referred by resco_templateid lookup.) If that template was modified or deleted, InspConverter re-creates the original template. Such templates are called "reconstructed" and are stored in a special folder called "Reconstructed Templates". These templates can be browsed in Questionnaire Designer.

Note

InspConverter refuses to start any conversion if there are any user edits of some questionnaire(s), which were not done by InspConverter itself. (Stating more precisely: If there are any modified non-serialized data questionnaires. InspConverter does not perform such modifications.) The reason is safety - these edits could be overwritten when downloading server records resulting thus in loss of user data.

Prerequisites

InspConverter prerequisites

In Woodford, prepare an app project that will be used by the InspConverter. It must meet these criteria in order to execute conversions successfully:

• Contain all inspection entities (I.e., resco_question, resco_questiongroup, and resco_questionnaire; resco_questionnairefolder does not play any role.)
• These entities are configured to use Sync Filter during incremental synchronization. (Woodford: Entity configuration: Set synchronization to "Incremental with Sync Filter".) Together with the recommended sync filter (see below) this choice assures fast sync and avoids conflicts between converter downloads and sync downloads.

Once the app project is ready, sync your InspConverter with this project. (In order to have a local copy of all templates.) The user running the InspConverter console must have complete CRUD permissions for all inspection entities.

Sync filter

We recommend that the Sync Filter is set to all templates, for example

<fetch>
	<entity name="resco_questionnaire">
		<filter type="and">
			<condition attribute="resco_istemplate" operator="eq" value="1"/>
		</filter>
	</entity>
</fetch>

(Note that the resco_istemplate column takes value "1" for templates and "0" for data questionnaires.)

Do not exclude any templates from the Sync Filter. (For performance reasons) InspConverter expects that all templates are stored in the local database.

Although the exclusion of data questionnaires isn't a requirement, following this advice improves your control over the conversion process. The basic advantage is that all data questionnaires you'll see on the local machine are converted questionnaires, which simplifies conversion testing.

Note	You do not need to specify Sync Filters for child entities (resco_question, resco_questiongroup). They are automatically derived from the parent filter.

Other conditions

To avoid unnecessary problems InspConverter refuses to start in these cases:

• In online mode
• When a sync is running
• When automatic sync is configured.

Requirements for other Resco Mobile CRM users

All clients working with questionnaires from the server where the conversion is executed must use Resco Mobil CRM v15.2 or later.

Older versions of Resco Mobile CRM may have serious problems when displaying converted questionnaires. (In the worst case, every question may be shown 2 times.)

InspConverter GUI

The InspConverter app includes an extra item, "InspConverter," on the home screen.

Click the item to display a list of questionnaire templates. Each row contains basic information about the template: name, id, and whether it is template-dependent (yes/no).

You now have two options:

• Execute the Run command in the top right corner to switch to the command-line interface, described in the next section.
• Click and hold on a template to start multi-selection; select at least one template you want to process, then execute the command in the top right corner to continue with the graphical user interface (described in this section).

The conversion window offers the following features:

• Selected Templates: read-only list of the templates selected in the previous step
• From, To: option to restrict the conversion to questionnaires answered in the specified period
• Do not upload converted questionnaires to server: enable if you want to check the questionnaires on the device before performing server changes. Highly recommended for the initial runs!
• Convert Command: displays the command that will be executed when you press the Convert button.
• Convert button: Perform the conversion.
• Conversion Setup: You can modify the conversion settings as described in Appendix 1.

If you are satisfied with the convert command and setup, click Convert to start the conversion process. You can view the logs of this process and monitor its progress.

Recommendations

Testing is a necessity; here are a few useful tips:

• Run InspConverter in the test environment first.
• For the initial runs, enable Do not upload converted questionnaires to server.
  • The noupload mode allows local testing of converted questionnaires without uploading them to the server. Try to open converted questionnaires in the mobile app.
  • If you are satisfied with the results, you can upload the questionnaires to the server. See Testing CONVERT for more information.
• The command line interface supports additional options SafeMode and DumpMode (also discussed in the link above).
  • The safe mode activates extra automatic checks.
  • Dump mode lets you preview the content of converted questionnaires. Results are stored in the column resco_questionnaire.resco_serializedanswers; its content can be previewed in any JSON viewer.
• Check the number of reconstructed templates. If there are too many, read special topics related to this subject.

Known issues

• After you convert a template using the GUI and return to the list of templates, the list is not updated with the latest conversion result. Leave the view and return to reload the list.

• If you have an older version of InspConverter installed on your computer, uninstall it before installing a new version.

InspConverter CLI

InspConverter is a command line window offering these commands (case-insensitive):

.HELP                    | Shows this text
.EXIT                    | Exit InspConverter
.LOG ...any text ...     | Adds message to the log file

.SETUP [-B[RIEF]]        | Lists settings
.SETUP -RELOAD           | Reload settings from InspConverter.xml

// Info about local data
.TEMPLATES               | List local templates by name
.LOCALSTATS              | Show details about local questionnaires

// Info about server data
.SERVERSTATS -BULKTEST   | (*) Count nonserialized q'naires using bulk download. Often the fastest method.
.SERVERSTATS <templName> | (*) Analyze q'naires of given template (how many json, dependent...)
.SERVERSTATS <templId>   | (*) As above, just template ID is passed instead of name
.SERVERSTATS [-BYNAME | -BYID] [-B[RIEF]]    | (*) Questionnaire counts by template

// (*) Convert & upload (unless NOUPLOAD specified)
.CONVERT [-FROM:m-d-y] [-TO:m-d-y] <templName>
.CONVERT [-FROM:m-d-y] [-TO:m-d-y] <templId>
.CONVERT @fetch_file
.CONVERTMULTIPLE [-FROM:m-d-y] [-TO:m-d-y] <templId1>[;<templId2>...]

// Testing CONVERT
.CONVERT ... [-NOUPLOAD] | Convert without upload  (aborted after 1 batch)
      ... [-dump[:full]] | Dump conversion details (dump switched off after 1st batch)
.UPLOAD                  | (*) Upload eventual waiting converted questionnaires
.DROPUNSENT              | Drop local converted questionnaires waiting for upload
.CONVERTMULTIPLE ... [-NOUPLOAD] [-dump[:full]]
_______________________________________
(*) Marks commands that can be aborted
Log file = InspConverter.log
Dump file = InspConverter.dump

Notes:

• <templName> stands for template name (resco_questionnaire.resco_name). Optionally enclosed in apostrophes. Apostrophes are mandatory if the name contains white spaces. For internal apostrophes use .
• Date format: Use invariant (i.e., en-US) format. Time may be included, too. (For programmers: any format accepted by DateTime.TryParse() can be used.)
  Examples:
  2-1-22
  02-01-2022
  Feb-1-22
  February 1, 2022, 3:13:50 PM
• fetch_filepath: Either full path or relative path with respect to %APP_DATA% folder.
• APP_DATA folder refers to the Windows folder containing Resco Mobile CRM data files, typically c:\Users\user_name\AppData\Roaming\MobileCRM.
• ID format: Guid, for example 39a98b66-5b24-4213-a7d9-519edc51d6db.

Formal command parser syntax:

Command =: 
    [.]<cmd> [-<option>:<value>] ... <arg> ...
    or
    [.]LOG  ...any text interpreted as arg ...

cmd =: { help | exit | setup | templates | localStats | serverStats | convert | upload | dropUnsent }
option =: word
value =: word
arg =: word

word =:
	1. Sequence of non-whitespace characters
	2. Sequence of any characters enclosed in apostrophes: 'word1 word2 ...'. Internal apostrophe must be escaped as ''

Logger

Logger writes to the file InspConverter.log (located in the MCRM %APP_DATA% folder), where it collects:

• Error messages
• Conversion stats
• User messages (LOG command)

The log file size is unlimited. You may discard part of the log file content or even the whole file at any time.

If you set setup.LogLevel to "1", then some commands (CONVERT, UPLOAD) produce additional detail messages.

.TEMPLATES

The command lists template names as stored in the local database.

Sample output:

My test
InspConverter DefaultValues Template
GroupsAll Complex
TestCase11325
9714
TestCase11325_2
vobrazky 2
Test123
GroupsAll Complex Latest
IQM - Conversion To JSON
Maintenance Manual
!Vis question test
Vobrazky

.LOCALSTATS

The command shows details about the local questionnaires (templates, reconstructed templates, converted questionnaires waiting for upload)

Sample output:

65 (customer-defined) templates
0 reconstructed templates

Records waiting for upload:
	1 changed template(s)
	0 reconstructed template(s)
	233 converted questionnaire(s)

.SERVERSTATS -byName

The command executes this server fetch template by template:

<fetch no-lock='true' aggregate='true'>
	<entity name='resco_questionnaire'>
		<attribute name='resco_questionnaireid' alias='cnt' aggregate='count'/>
		<filter type='and'>
			<condition attribute='resco_name' operator='eq' value='...'/>
			<condition attribute='resco_istemplate' operator='eq' value='false'/>
		</filter>
	</entity>
</fetch>

If you don't have too many questionnaires, and your questionnaires use the default (i.e., template) name, then this might be the way to go. However, if you have large counts of questionnaires, you can experience troubles:

• Dynamics reports count only up to 50000 records. After that, you see just the statement >50K.
• Although the fetch is relatively simple it can cause a high server load. (Under occasions even timeouts may occur.)

More situations that may confuse you:

• Existence of different templates with the same name.
• A template was renamed
• Questionnaire name (by default template name is used) is overridden:
  You see this problem on "InspConverter DefaultValues Template" template, where only 10 questionnaires keep the template name, although "SERVERSTATS -byId" reports 20046 questionnaires using this template.

Sample output:

Templates sorted by questionnaire counts:
>50K x My test
10	 x InspConverter DefaultValues Template
7	 x GroupsAll Complex
6	 x TestCase11325
3	 x 9714
3	 x TestCase11325_2
2	 x vobrazky 2
2	 x Test123
1	 x GroupsAll Complex Latest
1	 x IQM - Conversion To JSON
1	 x Maintenance Manual
1	 x !Vis question test
1	 x Vobrazky
===> Totally 50067+ answered questionnaires, 48 unused templates (6734ms elapsed)

.SERVERSTATS -byId

The command executes this server fetch template by template:

<fetch no-lock='true' aggregate='true'>
	<entity name='resco_questionnaire'>
		<attribute name='resco_questionnaireid' alias='cnt' aggregate='count'/>
		<filter type='and'>
			<condition attribute='resco_templateid' operator='eq' value='...'/>
		</filter>
	</entity>
</fetch>

The output is less readable, but the fetch has fewer problems than "SERVERSTATS -byName" command:

• Template ID never changes (as opposed to the name).
• There is no problem if questionnaire names differ from the template name. (The number reported for the "InspConverter DefaultValues Template" template is correct.)
• Fetch condition is simpler, hence it causes a bit smaller server load.

Sample output:

Templates sorted by questionnaire counts:
>50K	 x a9d23048-6cd8-e911-a975-000d3a389769 (My test)
20046	 x d1c727d1-a6f0-ec11-bb3d-000d3a485c00 (InspConverter DefaultValues Template)
7	 x 07de36ad-367a-4267-9bea-9d68272b0c4f (GroupsAll Complex)
5	 x ba596943-5846-4cd1-a620-2bf896626880 (TestCase11325)
3	 x 8f49fa91-98ac-ea11-a812-000d3a2c5992 (9714)
3	 x c7448778-5df3-ea11-a815-000d3a2c53fa (TestCase11325_2)
2	 x 924940c2-bae8-ec11-bb3c-000d3a2fd7c2 (vobrazky 2)
2	 x 2dd6121e-262f-eb11-a813-000d3abf47bf (Test123)
1	 x a197fcc4-5c88-4535-99b8-1b12708a7740 (GroupsAll Complex Latest)
1	 x 8f271cc2-1849-ec11-8c62-6045bd8d285b (IQM - Conversion To JSON)
1	 x 0e8d28b9-a161-4298-a850-298017480e0f (Maintenance Manual)
1	 x 0517ca4e-537c-e911-a96c-000d3a389769 (!Vis question test)
1	 x 10e084ce-b9e8-ec11-bb3c-000d3a2fd7c2 (Vobrazky)

===> Totally 70088+ answered questionnaires, 48 unused templates (5281ms elapsed)

.SERVERSTATS <templateId>

The command provides an analysis of questionnaires based on the given template ID. (How many JSON, dependent...)

It executes a sequence of fetches such as this one:

<fetch no-lock='true' aggregate='true'>
	<entity name='resco_questionnaire'>
		<attribute name='resco_questionnaireid' alias='cnt' aggregate='count'/>
		<filter type='and'>
			<condition attribute='resco_istemplate' operator='eq' value='false'/>
			<condition attribute='resco_templateid' operator='eq' value='...'/>
			<condition attribute='resco_templatedependent' operator='eq' value='true'/>
			<condition attribute='resco_serializedanswers' operator='null'/>
		</filter>
	</entity>
</fetch>

(The fetches are issued for all combinations of resco_serializedanswers {null | not-null} and resco_templatedependent {true | false}.)

The command has similar problems as mentioned above:

• Counts larger than 50K are not reported.
• Possible high server load.

Sample output:

> .SERVERSTATS d1c727d1-a6f0-ec11-bb3d-000d3a485c00

Template: InspConverter DefaultValues Template [d1c727d1-a6f0-ec11-bb3d-000d3a485c00]
Questionaires with record format: 20046 templateIndependent, 0 templateDependent
Questionaires with Json format: none
To be converted: 20046

Sample output for the case of "too many questionnaires":

Template: My test [a9d23048-6cd8-e911-a975-000d3a389769]
Questionaires with record format: 50K+ templateIndependent, 0 templateDependent
Questionaires with Json format: none
To be converted: 50K+

.SERVERSTATS <templateName>

The command provides the same analysis as above, just the template is given by name. The passed templateName is first converted to the templateID and then the command "SERVERSTATS <templateId>" is executed. If more templates share the same name, the analysis is carried out for all of them.

Sample output:

> .SERVERSTATS 'InspConverter DefaultValues Template'

Template: InspConverter DefaultValues Template [d1c727d1-a6f0-ec11-bb3d-000d3a485c00]
Questionaires with record format: 20046 templateIndependent, 0 templateDependent
Questionaires with Json format: none
To be converted: 20046

.SERVERSTATS -bulktest

The command estimates the count of questionnaires to be converted by executing this server fetch:

<fetch no-lock='true'>
	<entity name='resco_questionnaire'>
		<attribute name='resco_questionnaireid'/>
		<attribute name='resco_istemplate'/>
		<attribute name='resco_templateid'/>
		<attribute name='resco_templatedependent'/>
		<attribute name='resco_name'/>
		<filter type='and'>
			<condition attribute='resco_serializedanswers' operator='null'/>
		</filter>
	</entity>
</fetch>

The basic difference against other SERVERSTATS commands is that the fetch does not rely on the server to compute the counts (this is what aggregate fetches do), but downloads minimal questionnaire data to the client and does the counting there.

Unlike other SERVERSTATS commands, bulktest is able to report any record counts - at the cost of increased traffic, of course.

The idea behind this is that the fetch is extremely fast (5 small columns, server work is minimal). The tests showed a performance of ~0.2 ms/rec, which for example means that testing 1 million of resco_questionnaire records would take ~3.5 min using 200 API calls (page size = 5000). Concrete numbers in your case may differ, of course.

Sample output: (template name [ID], count of not serialized template-independent, count of not serialized template-dependent questionnaires):

Summary (Counts of non-serialized questionnaires by template):
   InspConverter DefaultValues Template [d1c727d1-a6f0-ec11-bb3d-000d3a485c00]:  Independent=20046
   My test [a9d23048-6cd8-e911-a975-000d3a389769]:  Independent=60198
   GroupsAll Complex [07de36ad-367a-4267-9bea-9d68272b0c4f]:  Independent=7
   9714 [8f49fa91-98ac-ea11-a812-000d3a2c5992]:  Independent=3
   TestCase11325 [ba596943-5846-4cd1-a620-2bf896626880]:  Dependent=5

.CONVERT [-FROM:m-d-y] [-TO:m-d-y] {<template> | <templateID>} [-noupload] [-dump:[full]]

The command converts and uploads questionnaires with the given template name or ID.

Conversion is a long-term process potentially taking hours or even days. It can be aborted by the user by pressing CTRL-C or by interrupting the internet connection. Note that abort does not cause any data integrity problems. Simply a part of resco_questionnaire records will be updated to use another storage format; the user won't notice anything.

The conversion process is very resource-demanding. It presents a constant flow of server requests, which may easily result in throttling on the server side. Conversion speed (i.e., number of web requests) can be regulated by setup parameter RequestsPer5Min. (See Appendix 1 where all setup parameters are explained.)

FROM/TO options are optional. If used, they are applied to either modifiedon or createdon fields. (Depending on setup parameter ConvertUsesModifiedOn.)

Based on the command options a resco_questionnaire fetch is constructed, such as for example:

<fetch>
	<entity name="resco_questionnaire">
		<filter type="and">
			<condition attribute="name" operator="eq" value="My inspection1" />
			<condition attribute="resco_istemplate" operator="eq" value="0" />
			<condition attribute='resco_serializedanswers' operator='null' />
		</filter>
	</entity>
</fetch>

Then the InspConverter starts downloading questionnaires matching this fetch.

Download is organized into batches. A batch is a set of resco_questionnaire records together with their child (resco_question and resco_questiongroup) records. Batch download is controlled by setup parameters BatchSize, DownloadPageSizeQQ, DownloadPageSizeQ, DownloadPageSizeG.

After a batch is ready (downloaded and saved to the local database), its resco_questionnaire records get converted to JSON. The used format is controlled by the setup parameter JsonSetupStr. Changed records are kept in the local database similarly to when the MCRM user modifies (say) a contact record. Notice that a part of the conversion is the deletion of local resco_question and resco_questiongroup records; they are not needed anymore.

What happens to templates during conversion?

• In the ideal case nothing - this is when the template exists and is set to produce template-dependent and serialized (JSON-formatted) data questionnaires.
• Non-existing templates are recreated. (This happens for template-independent questionnaires.)

Templates with inappropriate dependency or storage options are updated.
Template's storage options (field resco_questionnaire.resco_options) are changed as specified in InspConverter setup (see Appendix 1). Both template dependency and versioning are turned on if needed. All these changes are executed on the existing template, i.e. no new template is created.

The last conversion step is the upload of changed records. Upload is controlled by setup parameters MaxExecuteMultiple and MaxExecuteMultipleDataSize.

After the upload, the next batch is downloaded, converted, etc. The process continues until all questionnaire records are downloaded or the conversion process is aborted. For a sample CONVERT output, see Appendix 2.

.CONVERT @fetch_filepath [-noupload] [-dump:[full]]

This is a variant of the CONVERT command where the user supplies an explicit fetch. Everything else works as above.

fetch_filepath = Either full path or relative file path with respect to %APP_DATA% folder.

The file must contain a valid resco_questionnaire fetch, which

• Does not contain any links. (For the sake of performance.)
• Excludes templates.

Tips:

• Fetch condition "resco_serializedanswers == null" is recommended, but (so far) not required.
• Another good idea is to consider the completion status of the questionnaires. (Provided you use this field, of course.) For example, adding fetch condition "resco_completionstatus == 1" restricts the conversion to completed questionnaires only. As a bonus, this limitation avoids sync conflicts for other MCRM clients. (I.e., questionnaires being updated and converted by different clients at the same time.)

For sample CONVERT fetches, see Appendix 4.

Testing CONVERT (Safemode, noupload, dump)

If you switch on SafeMode (setup parameter) then every converted questionnaire will be subject to additional validation. (Extra code that loads converted questionnaire and compares its content against the original questionnaire instance downloaded from the server.) This adds an extra layer of safety at the cost of losing some performance.

Other possibilities for testing require deeper technical knowledge:

Upload step is skipped if "-noupload" option is used. In this case, CONVERT leaves converted records in the local database, where they wait for a future decision:

• The user may test converted questionnaires. (Provided current Resco Mobile CRM customization gives such a possibility.)
• The user may issue .UPLOAD command to upload all waiting records.
• Alternatively, the next .CONVERT will upload all waiting records, too. (Unless "-noupload" is used.)
• The next MCRM sync will upload all local changes, including the waiting converted questionnaires.
• Finally, the user may delete converted questionnaires by using the DROPUNSENT command.

If you want to check the conversion in depth, you can use "-dump" option. (See Appendix 3, where the conversion process is explained in greater depth.)

Because of performance reasons:

• In NOUPLOAD mode only 1 batch is processed.
• The same limitation is enforced also in DUMP mode.

Apart from the DumpMode, Resco Mobile CRM offers another possibility to check the changes made during conversion - Change List form, especially its ViewEdits command.

.CONVERTMULTIPLE [-FROM:m-d-y] [-TO:m-d-y] <tempalteId1>[;<templateId2>...] [-NOUPLOAD] [-dump[:full]]

This command converts and uploads questionnaires for the given templates. The list of templates for conversion is defined by their IDs, separated by ';'.

Essentially, a foreach cycle is performed for the passed templates, and a simple convert command with defined parameters is called within this cycle. For more information, read the .CONVERT section.

Passed parameters are applied to every template. If you want to apply different parameters for a specific template, please use the .CONVERT command instead.

.UPLOAD

Command uploads eventual waiting (unsent) converted questionnaires and created or modified templates, if any. Use LOCALSTATS to test if there are any waiting questionnaires.

A modified questionnaire happens to be unsent when:

• .CONVERT used NOUPLOAD option, or
• The conversion was interrupted before the changes were uploaded, or
• The mobile user edits a questionnaire by standard means in the mobile app. (This is not recommended, of course.)

Note that in principle you could use standard MCRM sync instead of .UPLOAD. Don't do so unless you understand all implications.

Sample output:

Summary: Templates: 2 uploaded, 0 failed, 4 ApiCalls | Questionnaires: 1022 uploaded, 2 failed, 1968 ApiCalls
Elapsed 81 sec

For detailed progress messages use LogLevel 1. For further information about the handling of question records see the chapter "Cleanup of orphaned question records" under Special Topics.

.DROPUNSENT

Command drops eventual waiting (unsent) converted questionnaires. Notice that DROPUNSENT command deletes all modified questionnaires even if the edit action was done outside of InspConverter.

Note that such questionnaires are typically a consequence of either NOUPLOAD option or interrupted conversion. Use LOCALSTATS to test if there are any waiting questionnaires.

Sample output:

Collecting information...

1 reconstructed template(s) deleted in 1437ms
22 converted questionnaire(s) deleted in 735ms

WARNING:
1 modified template(s) remained, which appear to have changed the storage format specs. (Such templates will produce converted questionnaires in the future.)
Use FullSync if you wish to undo these edits.

Note about the warning:

• The referred template is an old template whose storage options were set to JSON in process of conversion. While we could undo these edits, this would negatively impact eventual new questionnaires based on a modified template. (Although a rather theoretical possibility, we prefer to avoid that risk, especially if the template is prepared for conversion.) If you want to restore the original template, you need to use FullSync by going to Setup and using the Delete Data option.

Special topics

Questionnaire conversion, Reconstructed Templates

Let's start with the explanation of the concept of 'template equality'. We'll need it in process of questionnaire conversion.

A questionnaire template defines questions, UI, formats, logic etc. Comparing all these components is a tedious process, that's way InspConverter uses hash-based comparison. Template hash is a string computed (by SHA256 crypto algorithm) out of template components. Two templates are considered equal if they have the same hash.

Self-contained (template-independent) questionnaires contain not only data (responses), but also (original) template reference and the definition of the template really used (actual template). Actual template represents original template in the state when the questionnaire was created. Original template might have been edited since, or even deleted.

At the start InspConverter loads all existing templates and computes their hashes. All of this is stored in the TemplateCache.

Let's now describe the algorithm how a template independent questionnaire is converted.

At first, we reconstruct actual template stored in the questionnaire. (After all, the template is contained in the questionnaire itself.)

Then we compute reconstructed template's hash and check if the TemplateCache contains a template with that hash.

Converted questionnaire will use:

• That template, if found.
• Reconstructed template otherwise. (Will be stored permanently and added to the cache, of course.)

The problem of Too Many Reconstructed Templates

Above algorithm is straightforward, but there is a catch - we might get too many templates.

Let's illustrate it by this sequence of steps (T is template, Qx are questionnaires based on T): T created -> Q1 created -> T edited -> Q2 created -> T edited -> Q3 created -> InspConverter conversion

How will InspConverter handle this situation?

1. At the beginning TemplateCache contains (current) template T.
2. Reconstructed template RT1 of the questionnaire Q1 has different hash from the template T. Consequently, RT1 will be added to the cache and converted questionnaire Q1 will use RT1.
3. Same for Q2: Converted questionnaire Q2 will use its reconstructed template RT2.
4. Converted questionnaire Q3 will use template T.

At the end we'll have converted questionnaires Q1-Q3, original template T and reconstructed templates RT1 and RT2. All templates are set to use JSON storage format and are grouped together as versions of the original template.

You see that every template modification (which was used to create at least one answered questionnaire) results in a newly created reconstructed template. This is ok, template versioning works the same way.

But imagine customization which (mis)uses question label column to store some dynamic information. Examples:

• Question label reflects the answer to the previous question.
• Rule engine modifies question label to contain time when question was answered.

You may find these examples strange, but some designers with great invention really use to do such things. Now they face a risk, that the conversion will generate too many reconstructed templates.

Here is the point: If the label is included in the hash computation (it is by default), we get too many reconstructed templates. (Possibly 1 template for each questionnaire.)

Alternative solution would be to exclude label from hash (see Appendix1, ExcludedHashFieldsMask setting) and store it in JSON. (You need to select FlexJson format and set it up appropriately.)

Conversion details

Standard CRM attributes in converted records are set to these values:

Reconstructed template (+ its questions and groups):
	createdon, modifiedon – set to time of the conversion (Warning: Reconstructed template is created later than their data questionnaires.)
	createdby, modifiedby, ownerid – user realizing the conversion

Converted questionnaire:
	createdon, createdby, ownerid – preserved from original record
	modifiedby – user realizing the conversion
	modifiedon – set to time of the conversion

Custom questionnaire attributes:

Reconstructed template: These attributes remain unset. (Default values apply, if any.)
Converted questionnaire: These attributes remain unchanged.

Errors and data integrity

InspConverter gets a record set (questionnaire + its child questions/groups) and outputs a modified questionnaire, i.e., 1 record only. (Child questions/groups become orphaned and should be deleted from the server at some later time.)

On top of that existing templates are modified (by setting the correct storage format), eventually, new templates might be created (reconstructed).

From the data integrity point of view, all the above operations are safe.

The upload process is safe, too. It starts uploading templates and any error during this operation breaks the conversion process altogether. When the process aborts during the upload of data questionnaires, there is no danger either: Upload can be resumed later, but even if not - the mobile app is prepared to work with both converted and non-converted questionnaires.

What can happen, however, is a conflict with other users who may happen to modify the questionnaires being converted. A conflict example:

• A questionnaire is being converted.
• At the same time, the same questionnaire is updated on another device by modifying one or more questions. Even if the next sync succeeds, these modifications will be (silently!) lost - they will update meanwhile orphaned resco_question records.

Recommendation: Perform conversions for completed questionnaires only. (See the sample fetches in Appendix 4.)

API calls

The word throttling refers to the server policy to prevent an excessive number of web requests sent by a single client.

For example, March 2019 limits for Dynamics 365 state these limits:

• Max 4000 requests per sliding 300-sec window.
• Combined execution time of incoming requests cannot exceed 1200 secs over a time window of 300 seconds.

InspConverter is a tool that needs to issue a large number of server requests. Setup parameter RequestsPer5Min gives you the possibility to keep the number of issued requests at a reasonable level. It defines the maximum number of web requests per 5-min sliding window. If this limit is about to be crossed, InspConverter starts pausing and the user sees this message:

Pausing for 123 secs due to requests limit (800 requests/5min) exceeded.

If this happens, the command's final summary will contain the waiting total, such as for example:

Pause time due to RequestsPer5Min limit since app start = 2460 secs.

Note that only CONVERT/UPLOAD commands are supervised; BulkTest (which is also able to produce a considerable number of web requests) is not.

Client vs server-based conversion

The adopted solution is client-based. This means the client (MCRM app) downloads questionnaires, converts them, and uploads the results back to the server.

The alternative would be a server-based conversion tool, which could be realized for example as a Dynamics app. While this concept provides the distinct advantage of bypassing web transfers, it requires more programming effort and more difficult maintenance. Here are some of the drawbacks:

• Mastering the programming in several external environments (Dynamics, Salesforce, Resco Cloud)
• Transfer of questionnaire's logic from Resco Mobile CRM
• Difficult maintenance

Expected performance

This algorithm was selected as the best one:

• Download questionnaires and its child question/group records
• Convert questionnaires
• Upload converted questionnaires
• Cleanup of orphaned resco_question + resco_questiongroup records:
  Server: Admin action required
  Client: Done immediately

Any performance estimates face uncertainties such as server and network performance or questionnaire complexity, especially count of questions.

Here is a real life example (conversion output for questionnaires containing 240 questions on average):

BATCH#13> 100 questionnaires (+24200 questions) downloaded in 66265ms, downloadSize=81M, speed=1284507By/sec
BATCH#13> Converted in 14296ms (Dropped 24200 questions + 0 groups)
BATCH#13> Uploaded in 4157ms

In this case (i.e. 240 questions/questionnaire) we have this performance:

• Download 662 ms/questionnaire
• Conversion 143 ms/questionnaire
• Upload 42 ms/questionnaire

This gives the total ~850 ms/questionnaire.

How would you estimate questionnaires with different size, say with 100 questions? In fact the amount of work done is approximately linearly dependent on the number of questions. (Especially for the download phase.) So for questionnaires with 100 questions you could expect performance ~400 ms/questionnaire.

Note that the performance is far from being optimal. We could certainly introduce parallelism (multi-thread downloader working simultaneously with the conversion) or speed up the conversion. However, this is a single-purpose tool and we were rather afraid that higher performance would lead to more server throttling. So, instead of maximizing the throughput we invested effort into the regulation of API calls, which has exactly opposite effect.

Cleanup of orphaned question records

The basic idea of the conversion process is to move the information stored in standalone resco_question/resco_questiongroup records to the parent questionnaire. Question and group records become thus orphaned and need to be discarded.

Consider first what would happen if InspConverter used the standard way, i.e., if it would explicitly issue DeleteRequest for orphaned questions.

• First, instead of 1 upload request per questionnaire, we would have potentially 100s of requests (1 extra request per questionnaire question).
  Consequently, the upload process would be too slow, the server load would increase dramatically, and we could expect server throttling errors.
  For large installations, the conversion process would turn into a never-ending story.

• Let's continue with the delete processing. Every deleted resco_question(group) record would be logged as 1 resco_mobiletracking record on the server. (Recycle bin record for Salesforce)
  This table collects information about deleted records. Every Resco Mobile CRM client would download this information during sync and delete appropriate records.

• If there are many deletes, resco_mobiletracking server table will grow extremely (maintenance might be required) and mobile clients will experience long processing of the delete information.
  (Salesforce: recycle bin would overflow, and the deleted information would be lost to a large extent.)

Impact of huge server table resco_mobiletracking is platform-dependent:

• Dynamics:

MCRM code is optimized: Large delete counts will be handled by forced full sync of relevant tables.

This situation would last for the whole conversion period.

• Resco Cloud:

Problem: Resco Mobile CRM will download all new resco_mobiletracking records leading to low performance (possibly dozens of minutes spent in delete processing) and frequent sync aborts.

Mobile users might not be able to use IncSync during the whole conversion period.

• Salesforce:

The recycle bin is limited to some 25K records. SyncDeletes will be slow (typically ~30 secs), but not extremely slow.

Part of the delete information would be lost, as mentioned above.

Because of the above reasons, InspConvert does not perform a cleanup of orphaned question/group records. Instead, server admin should take care of the cleanup themselves.

Admin recommendations for Dynamics/Resco Cloud:

• Server admin: Delete orphaned questions after the conversion process is done.
• Woodford: Temporarily exclude resco_question/resco_questiongroup entities from DeletePlugin.
  Warning: They might be re-enabled automatically. (Happens when Inspections are enabled in some project.)

Admin recommendations for Salesforce:

• Server admin: Delete orphaned questions after the conversion process is done.
• No remedy for slow delete processing during synchronization is available.

Salesforce note (old installations):

• If Resco Mobile CRM connects to a Salesforce organization that does not have an installed Resco managed package, questionnaires are stored on Resco Cloud. In this case, Resco Cloud recommendations apply.

Handling of orphaned questions on other Resco Mobile CRM clients:

• Resco Mobile CRM v15.2 will remove these records during sync cleanup.
• Older Resco Mobile CRM versions will have a huge problem: Not only will orphaned questions occupy extra storage, but they will also be shown in the mobile app along with serialized questions. (Every question will be displayed twice.)

As an inspiration for admins, below are SQL commands added to Resco Mobile CRM sync cleanup to delete orphaned questions/groups:

DELETE FROM resco_question WHERE resco_questionid IN (
	SELECT resco_questionid FROM resco_question AS Q
		INNER JOIN resco_questionnaire AS QQ ON Q.resco_questionnaireid = QQ.resco_questionnaireid
		WHERE 
			QQ.resco_istemplate = 0 AND
			QQ.resco_serializedanswers IS NOT NULL AND
			Q.resco_answerstorage != 0
)
DELETE FROM resco_questiongroup WHERE resco_questiongroupid IN (
	SELECT resco_questiongroupid FROM resco_questiongroup AS G
		INNER JOIN resco_questionnaire AS QQ ON G.resco_questionnaireid = QQ.resco_questionnaireid
		WHERE 
			QQ.resco_istemplate = 0 AND
			QQ.resco_serializedanswers IS NOT NULL
)

Impact on incremental sync

Converted questionnaires are physically represented as modified resco_questionnaire records on the server. Standard incremental sync downloads these records before deciding whether they are needed on the client. This means, that unless we prevent that, all converted resco_questionnaire records will be sooner or later downloaded to every client device. Depending on the size of the server questionnaire data set this can be a very costly process, which can manifest as long download times, but also as insufficient client storage.

An especially risky situation arises when the typical client uses a highly selective sync filter. (I.e., receives just a small part of questionnaire records.) The only way to prevent this problem is to set synchronization to Incremental with Sync Filter (Woodford).

One specific problem is that if the sync filter is based on modifiedon, IncSyncFilter won't help. There is no way out - if the questionnaire data set is large such sync filter must be changed. IncSyncFilter has one drawback - it ignores records that stopped matching the sync filter. For example, if we have the sync filter:

(data_questionnaires OR Active templates)

then the client won't notice that some templates were deactivated on the server.

If you fear this problem, you should force FullSync after the conversion process is over.

Appendix 1: Setup file InspConverter.xml

You can finetune some InspConverter settings by editing the file InspConverter.xml.

1. Start the InspConverter app, then select InspConverter from the menu.
2. Multi-select at least one of the templates, then click Run.
3. Expand the Conversion Setup section.
4. Click Edit Setup to open the file in a built-in text editor.
5. Modify as needed and save all changes

Alternatively, you can find the file in the %APP_DATA% folder of Resco Mobile CRM. The file is created when you run InspConverter for the first time. You may use any text editor to modify the file. Verify that the file has the correct XML format. (Formal check: open the file in any XML viewer, such as Chrome.) To make the changes effective in the app, execute the .SETUP -RELOAD command. To review the current settings via the command-line interface, use .SETUP command.

Here is a sample InspConverter.xml file:

<?xml version="1.0" encoding="utf-16"?>
<JsonConverterSetup
	xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Version>3</Version>
	<DownloadPageSizeQQ>100</DownloadPageSizeQQ>
	<DownloadPageSizeQ>5000</DownloadPageSizeQ>
	<DownloadPageSizeG>2000</DownloadPageSizeG>
	<BatchSize>100</BatchSize>
	<MaxExecuteMultiple>100</MaxExecuteMultiple>
	<MaxExecuteMultipleDataSize>1048576</MaxExecuteMultipleDataSize>
	<LogLevel>0</LogLevel>
	<ConvertUsesModifiedOn>false</ConvertUsesModifiedOn>
	<SafeMode>true</SafeMode>
	<RequestsPer5Min>0</RequestsPer5Min>
	<ExcludedHashFieldsMask>QuestionVisible QuestionEnabled QuestionRequired QuestionStyle GroupVisible GroupExpanded QuestionnaireDescription</ExcludedHashFieldsMask>
	<JsonSetupStr>-minJson -includeUnanswered:n -qqMask:All</JsonSetupStr>
	<ConversionFormat>Json</ConversionFormat>
</JsonConverterSetup>

Available settings

Download

BatchSize	(int) Count of questionnaires converted at once. Default: 100.
DownloadPageSizeQQ	(int) Page size when downloading questionnaires. Default: 100.
DownloadPageSizeQ	(int) Page size when downloading questions. Default: 5000.
DownloadPageSizeG	(int) Page size when downloading groups. Default: 2000.

Upload

MaxExecuteMultiple	(int) Max number of records to upload in one request; Default: 100
MaxExecuteMultipleDataSize	(int) Max size (in bytes) of records to upload in one request; Default: 1048576 (1MB).

Other

LogLevel	1) Whether file log (InspConverter.log) has to show detail information. Default:0. Note: Console will always display log level 0
ConvertUsesModifiedOn	false) CONVERT command interpretation: Whether FROM/TO refers to createdon (false) or modifiedon (true); Default: false
SafeMode	false) Whether CONVERT command performs independent validation of the conversion result; Default: true
RequestsPer5Min	(n) Max count of API calls per 5 min window. Concerns CONVERT/UPLOAD commands only. Default: 0 (unlimited)

Conversion format

JsonSetupStr	(string) Described below. Default is shown in above InspConverter.xml sample.
ExcludedHashFieldsMask	(string) Described below. Default is shown in above InspConverter.xml sample.
ConversionFormat	Use Json (default) to convert questionnaires to JSON and make them template-dependent. Use TemplateDependent to make questionnaires template-dependent.

Conversion format setup (JsonSetupStr)

Note: The following explanation is very technical. You will understand it better if you keep in mind its purpose—to specify columns of different inspection entities that need to be stored into a JSON string. Finally, the main JSON purpose is to save storage space, and this can be achieved mainly by stripping off unneeded information.

Formal definition:

JsonSetupStr =:
-{ minJson | flexJson | version:{m1.0 | f1.0} }	m1.0 stands for MinJson, f1.0 for FlexJson format. minJson/flexJson implicitly refers to the most recent version supported.
-includeUnanswered:y/n	Whether unanswered questions (or questions answered with default value) should be stored in Json format; valid for both MinJson/FlexJson.
-qqMask:flags	Questionnaire fields to be stored in Json format; valid for both MinJson/FlexJson.
-gMask:flags	Group fields to be stored. (FlexJson format only)
-qMask:flags	Question fields to be stored. (FlexJson format only)

Flags syntax:

- Syntax1: Comma-separated list of flags. Alternative separator is "|".
- Syntax2: Single integer value = sum of flag values. Either hexa or decadic integer format can be used.
- Possible flags are listed below, for example TemplateId for qqMask.
- Numbers represent a set of flags lumped together, for example 
	-- "qqMask:0x1A" means "qqMask:CreatedBy,TemplateId,ScoreLabel"
	-- "gMask:3" means "gMask:Label|Description"

Flags dictionary

qMask flag	Flag value
None	0x0 or 0
Label	0x1 or 1
Description	0x2 or 2
Visible	0x4 or 4
Required	0x8 or 8
ValueLabel	0x10 or 16
ShowOnReport	0x20 or 32
AnsweredOn	0x40 or 64
Index	0x80 or 128
Style	0x100 or 256
All	0xFFFF or 65535

gMask flag	Flag value
None	0x0 or 0
Label	0x1 or 1
Description	0x2 or 2
Visible	0x4 or 4
Expanded	0x8 or 8
All	0xFFFF or 65535

qqMask flag	Flag value
None	0x0 or 0
AnsweredOn	0x1 or 1
CreatedBy	0x2 or 2
CreatedById	0x4 or 4
TemplateId	0x8 or 8
ScoreLabel	0x10 or 16
All	0xFFFF or 65535

Sample setup strings

These setup strings have the same effect:
	-version:m1.0
	-minJson

These setup strings have the same effect:
	-minJson -qqMask:AnsweredOn,CreatedBy,CreatedById,TemplateId,ScoreLabel
	-version:m1.0 -qqMask:31

These setup strings have the same effect:
	-minJson -qqMask:All
	-version:m1.0 -qqMask:0xFFFF

These setup strings have the same effect:
	-version:f1.0 -includeUnanswered:n -qqMask:AnsweredOn|CreatedBy|CreatedById|TemplateId|ScoreLabel -gMask:Label|Description|Visible|Expanded -qMask:Label|Description|Visible|Required
	-flexJson -includeUnanswered:n -qqMask:0x1F -gMask:0xF -qMask:0xF

ExcludedHashFieldsMask

List of flags representing fields that are ignored in template hash computation. For more information, see respective chapters in Special Topics. The following table lists available flags and respective template fields.

(D) marks flags switched on by default.

QuestionVisible	(D) resco_question.resco_isvisible
QuestionEnabled	(D) resco_question.resco_enabled
QuestionRequired	(D) resco_question.resco_required
QuestionLabel	resco_question.resco_label
QuestionDescription	resco_question.resco_description
QuestionStyle	(D) resco_question.resco_style
GroupVisible	(D) resco_questiongroup.resco_isvisible
GroupExpanded	(D) resco_questiongroup.resco_expanded
GroupLabel	resco_questiongroup.resco_label
QuestionnaireDescription	(D) resco_questionnaire.resco_description
None	None of the above flags
All	All above flags

Appendix 2: Sample CONVERT output (* marks detailed logs)

Converting

<fetch>
<entity name="resco_questionnaire">
	<filter type="and">
		<condition attribute="statuscode" operator="ne" value="2" />
		<condition attribute="name" operator="eq" value="My inspection1" />
	</filter>
</entity>
</fetch>

BATCH #1> 100 questionnaires (+12300 questions) downloaded in 12345ms, downloadSize=123M, speed=10012By/sec
*BATCH #1 saved in 123ms
*Restored template: ...name...
BATCH #1> Converted in 12ms (Dropped 1234 questions + 12 groups)
*Uploading 3 templates

Starting upload
3 recs uploaded, 0 failed
*Uploading 48 data questionnaires
Starting upload
10 recs uploaded, 0 failed
11 recs uploaded, 0 failed
...
BATCH #1> Uploaded in 12334ms
BATCH #2> 100 questionnaires (+12300 questions) downloaded in 12345ms, downloadSize=123M, speed=10012By/sec
BATCH #2> Converted in 12ms (Dropped 1234 questions + 12 groups)
...
BATCH #2> Uploaded in 1234ms
So far 200 questionnaires converted, 0 failed

...

BATCH #112> 100 questionnaires (+12300 questions) downloaded in 12345ms, downloadSize=123M, speed=10012By/sec
BATCH #112> Converted in 12ms (Dropped 1234 questions + 12 groups)
...
BATCH #112> Uploaded in 1234ms
So far 22400 questionnaires converted, 2 failed

Summary:
UploadStats: Templates: 2 uploaded, 0 failed, 4 ApiCalls | Questionnaires: 1022 uploaded, 2 failed, 1968 ApiCalls
*** WARNING ***
Run MCRM and check Sync Errors: There are 5 questionnaires with upload errors.

Stats: 1 restored templates, 1234 serialized questionnaires, 1200 questionnaires made template dependent (132000 question(group) recs deleted) in 2450 sec

Web calls stats:
- 5563 web calls within last 182 min
- Peak usage over 5 min sliding window: 201 calls.
- Pause time due to RequestsPer5Min limit since app start = 2460 secs.

Appendix 3: Dump mode

Below, you will find a simple dump example:

• A batch with one questionnaire only
• That questionnaire has one question only.

Dump content is written (actually appended) to the file InspConverter.dump in the %APP_DATA% folder. Similarly to the log file, this file is not maintained; it is your responsibility to control its size.

The questionnaire is template-independent and uses a record-based format. Additionally, it contains a reference to the original template, resco_templateid.

During the conversion, a new template is reconstructed from the data stored in the resco_questionnaire record, i.e., from the fields resco_options, resco_rules, resco_script, resco_styles...

Subsequently, an attempt is made to find an existing template with the same content. If such template is found (typically the template referenced by resco_templateid), that template is assigned to resco_templateid field; the new template is discarded. Otherwise, the new template gets the name "Reconstructed..." and is stored permanently.

What happens to the data questionnaire?

• The fields resco_options, resco_rules, resco_script, resco_styles... are cleared.
• The field resco_templateid is set to point to the reconstructed template.

The dump log has three parts:

• Data as arrived from the server (i.e., fetch results)
• Converted questionnaire as stored in the database (marked with "DumpDb>" label). This will be uploaded to the server to overwrite the old questionnaire content.
• Reconstructed template as stored in the database (again marked with "DumpDb>" label). This will be uploaded to the server as a newly created template.

Note that to limit the size of logged data, potentially large columns (styles, rules, autoreport, reportingproperties) are trimmed to 50 characters. If you want to see the complete contents, use the "-DUMP:FULL" option. Beware that the log size will grow substantially in that case.

//==================================================================
// Dumping BATCH #1 (Data read from Server)
//==================================================================

resco_questionnaire[ae14bcbe-51bb-4b3d-b456-c707acf627cd]
statecode = 0
resco_featureversion = 1
resco_versionname = vobrazky-2
resco_templateid = 924940c2-bae8-ec11-bb3c-000d3a2fd7c2
resco_templatedependent = False
resco_converttojson = False
resco_folderidLabel = Timotež
resco_completionstatus = 1
resco_name = vobrazky 2
resco_languagecode = 1033
resco_folderid = 29ee0bfc-b8e8-ec11-bb3c-000d3a2fd7c2
resco_styles = [{"Name":"Button","Height":0,"LabelPosition":4,"La...
resco_layout = {"layoutType":0}
resco_issnippet = False
resco_questionnaireid = ae14bcbe-51bb-4b3d-b456-c707acf627cd
resco_templateidLabel = vobrazky 2
resco_options = {"commands":"","validateOn":0,"enableScoring":false,"copyStaticImages":true,"rulesVersion":-1,"versioningEnabled":false,"groupExpandMode":0,"enableAutoreport":true,"compressProperties":false,"storageConfig":{"ver":"0"}}
resco_reusefromprevious = 473220000
resco_duration = 20
resco_autoreport = <Report> <Styles> < Style Name="Normal"><Padding>4</Padding>...
statuscode = 1
resco_templateidTarget = resco_questionnaire
resco_folderidTarget = resco_questionnairefolder
resco_reportingproperties = {"styles":"[{"Name":"GroupHeader","Height":0...
resco_istemplate = False

resco_question[d2ff126a-95f1-4f83-9070-16e0950871bd]
resco_layout = {}
resco_displayformat = 473220000
resco_questionid = d2ff126a-95f1-4f83-9070-16e0950871bd
resco_required = 473220000
resco_answeredon = 6/10/2022 12:44:28 PM
resco_style = Normal
resco_enabled = True
resco_showonreport = True
resco_answerstorage = 0
resco_questionnaireidTarget = resco_questionnaire
resco_questionnaireid = ae14bcbe-51bb-4b3d-b456-c707acf627cd
resco_visible = True
resco_index = 1
statuscode = 1
resco_questionnaireidLabel = vobrazky 2
resco_kind = 473744289
resco_rawidvalue = bd5070ce-f184-4f41-9b8c-f97e285a462a
resco_reusefromprevious = False
statecode = 0
resco_name = imagemedia
resco_options = 2147483646;;annotation;;;;{"maxCount":10,"maxImageWidth":null,"maxImageHeight":null,"maxFileSize":null,"thumbnailFormBreakAfter":4,"thumbnailFormMaxCount":null,"thumbnailReportBreakAfter":4,"thumbnailReportMaxCount":10}
resco_label = Image/Media
resco_isseparator = False

//==================================================================
// End-of-dump
//==================================================================

DumpDb> resco_questionnaire[ae14bcbe-51bb-4b3d-b456-c707acf627cd]
resco_completionstatus= 1
resco_converttojson= False
resco_duration= 20
resco_featureversion= 1
resco_issnippet= False
resco_istemplate= False
resco_languagecode= 1033
resco_name= vobrazky 2
resco_questionnaireid= ae14bcbe-51bb-4b3d-b456-c707acf627cd
resco_reusefromprevious= 473220000
resco_serializedanswers= {"@q": {"ID": "ae14bcbe-51bb-4b3d-b456-c707acf627cd", "by": "Timotej Lesko", "byID": "59916d71-044e-ec11-8c62-0022487edd22", "defanswers": 0, "ds": "annotation", "on": "2022-06-10T12:44:28", "tID": "924940c2-bae8-ec11-bb3c-000d3a2fd7c2"}, "@ver": "m1.0"}
resco_templatedependent= True
resco_templateid= 924940c2-bae8-ec11-bb3c-000d3a2fd7c2
resco_versionname= vobrazky-2
statuscode= 1
resco_templateidTarget= resco_questionnaire

// Reconstructed template

DumpDb> resco_questionnaire[3f50a7dd-eff9-468f-b984-e53661714480]
resco_autoreport= <Report><Styles>< Style Name="Normal"><Padding>4</P...
resco_converttojson= False
resco_folderid= 2159636a-6d53-4386-953b-1f000aaeee5f
resco_issnippet= False
resco_istemplate= True
resco_layout= {"layoutType":0}
resco_name= Reconstructed.vobrazky 2~1
resco_options= {"commands": "", "compressProperties": false, "copyStaticImages": false, "enableAutoreport": true, "enableScoring": false, "groupExpandMode": 0, "rulesVersion": -1, "storageConfig": {"jsonConfig": {"groupProps": 0, "includeUnanswered": false, "questionProps": 0, "questionnaireProps": 65535}, "ver": "m1.0"}, "validateOn": 0, "versioningEnabled": true}
resco_publishedon= 9/16/2022 12:33:58 PM
resco_publishnotes= Template created and published by automatic conversion tool.
resco_questionnaireid= 3f50a7dd-eff9-468f-b984-e53661714480
resco_reportingproperties= {"styles":"[{"Name":"GroupHeader","Height":0...
resco_reusefromprevious= -1
resco_styles= [{"Name":"Button","Height":0,"LabelPosition":4,"La...
resco_templatedependent= False
statuscode= 473220002
resco_folderidTarget= resco_questionnairefolder

DumpDb> resco_question[b931dd34-08c2-4344-a45f-45a9c20a207e]
resco_answerstorage= 0
resco_displayformat= 473220000
resco_enabled= True
resco_isseparator= False
resco_kind= 473220016
resco_label= Image/Media
resco_layout= {}
resco_name= imagemedia
resco_options= 2147483646;;annotation;;;;{"maxCount":10,"maxImageWidth":null,"maxImageHeight":null,"maxFileSize":null,"thumbnailFormBreakAfter":4,"thumbnailFormMaxCount":null,"thumbnailReportBreakAfter":10,"thumbnailReportMaxCount":null}
resco_questionid= b931dd34-08c2-4344-a45f-45a9c20a207e
resco_questionnaireid= 3f50a7dd-eff9-468f-b984-e53661714480
resco_required= 473220000
resco_reusefromprevious= False
resco_showonreport= True
resco_style= Normal
resco_visible= True
statuscode= 1
resco_questionnaireidTarget= resco_questionnaire

Appendix 4: Sample CONVERT fetches

Non-serialized questionnaires belonging to the template MyTemplate

<fetch version="1.0" aggregate="false">
  <entity name="resco_questionnaire">
    <filter>
      <condition attribute="resco_istemplate" operator="eq" value="0" />
      <condition attribute="resco_name" operator="eq" value="MyTemplate" />
      <condition attribute="resco_serializedanswers" operator="null" />
    </filter>
  </entity>
</fetch>

All non-serialized questionnaires

<fetch version="1.0" aggregate="false">
  <entity name="resco_questionnaire">
	<filter>
	  <condition attribute="resco_istemplate" operator="eq" value="0" />
	  <condition attribute="resco_serializedanswers" operator="null" />
	</filter>
  </entity>
</fetch>

Completed non-serialized questionnaires

<fetch version="1.0" aggregate="false">
	<entity name="resco_questionnaire">
		<filter>
			<condition attribute="resco_istemplate" operator="eq" value="0" />
			<condition attribute="resco_serializedanswers" operator="null" />
			<condition attribute="resco_completionstatus" operator="eq" value="1" />
		</filter>
	</entity>
</fetch>

Non-serialized questionnaires with archived template
(Such templates are outdated, hence their data questionnaires are probably "old".)

<fetch version="1.0" aggregate="false">
	<entity name="resco_questionnaire">
		<filter>
			<condition attribute="resco_istemplate" operator="eq" value="0" />
			<condition attribute="resco_serializedanswers" operator="null" />
		</filter>
		<link-entity name='resco_questionnaire' alias='L0' from='resco_questionnaireid' to='resco_templateid' link-type='inner'>
			<filter type='and'>
				<condition attribute='statuscode' operator='eq' value='473220003'/> <!-- 473220003 = archived -->
			</filter>
		</link-entity>	
	</entity>
</fetch>