JSON storage for questionnaires

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

This document formally describes new formats used for storing questionnaire data. They were introduced in Release 15.1 with the goal to minimize storage footprint.

Questionnaire templates (or simply templates) are created in the Questionnaire Designer tool. They represent suitably arranged questions, but – what is important – no answers.

After the user fills in the questions, we have an answered questionnaire. In the end, there is the original template plus a set of answers. All this data must be stored somehow. This can be done in many ways, each with its own pros and cons.

All new formats are JSON-based. This means they serialize questionnaire content into a JSON string. After reading this document, you should be able to read (decode) this string.

The questionnaire data model originally worked with 3 entities:

  • resco_questionnaire entity representing either the template or answered questionnaire.
  • resco_questiongroup entity representing question groups.
  • resco_question entity representing individual questions.

The above entities are documented here. A questionnaire consists of questions that can be optionally grouped into groups.

In the original storage format, questions and groups were stored as separate records, and the filled questionnaire was fully self-describing, i.e., independent from its template. (For example, the questionnaire could be used even after its template was modified or deleted.) While this is a very flexible concept, drawbacks of this design appeared soon:

  • Too many records and consequently long sync times.
  • Integrity problems. (Because a questionnaire is a distributed set of records.)
  • Large storage consumption.

To address the above problems, Resco did several upgrades to the format. Among them, two concepts are especially important:

  • Template-dependent questionnaires: Design-related data is not copied into answered questionnaires saving thus a considerable portion of storage. Template changes are addressed by template versioning. (Drawback is that the template is needed for the processing of answered questionnaires.)
  • JSON storage format addresses the problem of integrity and of “too many records”: When the questionnaire is saved, its child records (group/question) are serialized into one JSON string, stored as a specific questionnaire field (resco_serializedanswers) and the child records are subsequently discarded. When the answered questionnaire is opened again, the original group and question records are reconstructed from the JSON storage.

However, the JSON format did not solve all the storage problems. The reason was that it stored too many details and even some redundant information.

Another problem was the limits imposed by the backend servers, which limit field size (JSON string was originally stored in a single field) to 1M characters (Dynamics) or 128K characters (Salesforce).

Resco offers also optional JSON compression, which squeezes the storage requirements much further. However, this format has also drawbacks, especially if the customers want to process the results themselves.

To finalize the format evolution, with release 15.1, Resco introduced the next-generation storage format. And to fit all possible (often contradictory) requirements, there are two formats, both JSON-based:

Flexible JSON
This format cures the basic problems of the original JSON format, i.e., it leaves out nearly all information that can be reconstructed from the template. Moreover, customers can customize the format by deciding which data items are stored.
Minimal JSON
Goes even further as it stores the bare minimum – key/value pairs, where the key is the question name and the value represents the user answer. This is the most storage-efficient JSON format possible. Those who don’t need additional data (such as question scores) can enjoy minimal storage footprint and easy result processing.

Apart from the question data, both formats also contain the questionnaire header, containing the most important items such as IDs of both the answered questionnaire and its template. The weight of these items is small, especially in the case of large questionnaires. They were added to make the JSON information as complete as possible, so that the customers may eventually export JSON strings “as-is” into data warehouses for long-term storage.

Note To save storage space, default values and default answers are not serialized.

Of course, both formats can be optionally compressed in the same way as the original JSON format.

Configuration in the designer

You can enable new JSON storage in the Questionnaire Designer.

  • For new questionnaires, in the new questionnaire wizard, switch to the recommended settings.
  • For existing questionnaires:
  1. On the list of questionnaires, select the questionnaire and enable versioning. New JSON formats are only available for versioned questionnaires.
  2. In the questionnaire properties, set Dependency Level to "Full" and Storage to "JSON (New)". Optionally, enable Compression.
  3. Optionally, click Configure JSON > Open editor and customize what and how to save.
    Json configurator in Questionnaire Designer
    • Select Include unanswered questions in the Flexible JSON file to include unanswered questions in the JSON string. Unanswered questions take up extra space in the JSON file, however, storing them can be useful for third-party reporting tools.
    • Select Minimize data saved in the JSON file if you want to select the minimal variant of JSON.
    • Select Serialize fields even if their value equals default value to include more detail in the JSON string. The JSON will include even fields (such as question labels) that have not changed compared to the default value defined in the template. This resulting JSON string can potentially take up much more space.
    • Alternatively, set up what should be included in the flexible JSON: Unanswered questions? Which question fields? Which question group fields? Which questionnaire fields?
  4. Save all changes.

If you have previously collected significant data using inspections and you would like to convert previous answers to the new format, contact Resco support.

Legacy JSON

This chapter has informational value only as we are deprecating this format.

The JSON string is constructed as follows:

{ 
"formatversion": 1, 
"answers": [AnswerItem1..AnswerItemN], 
"groups": [GroupItem1..GroupItemM] 
}

AnswerItems is a dictionary that can have the following keys:

Field name
without the "resco_" prefix
Default Type Notes
questionid GUID
name string (unique in context of questionnaire)
answeredon null Date (‘2011-10-05T14:48:00.000Z’)
value null string question response
defaultvalue “” string
description “” string
questiongroupid null string (“resco_questiongroup,guid,groupname”)
icon “” string
index 0 int order of the question in questionnaire
kind 0 int type of the question, datetime, lookup, text…
label “” string display name of the question
min “” string minimum number
max “” string maximum number
options “” string configuration for various question types
precision 0 int precision of decimal numbers
style “” string style name for the question
valuelabel “” string human readable (formatted) value, used in reports
visible true Boolean initial visibility of the question
required 473220000 int None (473220000), SystemRequired (473220001)
isseparator false Boolean if true this is static question (description, image…)
displayformat 473220000 int question format (text, URL, email, duration…)
answerstorage 1 int where the answer is stored: 0-record, 1-Json.
showonreport 1 int whether this question should be displayed on the report
rules “” string question specific rules
score 0 int question score
localization “” string
layout “” string position of the question on the grid

GroupItems is a dictionary that can have the following keys:

Field name
without the "resco_" prefix
Default Type Notes
questiongroupid GUID referred by question.questiongroupid
description “” string
expanded true Boolean
index 0 int order of the group in questionnaire
label “” string display name of the group
repeatconfig “” string config for repeating groups
repeatindex 0 int
rules “” string group specific rules
templategroupid GUID reference to a group from the template
visible true Boolean initial visibility of the question
localization “” string
layout “” string layout definition (list/grid…)

What is serialized: If the value of the question field is different from null and from the default, the field is serialized (added to the AnswerItems, resp. GroupItems dictionary).

A serialization sample can be found here.

How to improve this format?

The biggest problem with this format is that it stores too many fields. For example, IDs are not needed as the question/group records are re-created on an as-needed basis; rules, question labels or descriptions can be read from the template, etc.

It sounds tempting to “improve” storage format by simply leaving out all the above fields. However, questionnaire business logic allows changing even seemingly immutable fields. For example, some customers want to dynamically change question labels depending on previous answers, and they want to keep this info for reporting.

Example of minimal JSON

Let’s have the questionnaire form filled as follows:

Age = 12
City = Senec
Sex = Male
[Computer]
    Price = 427.12
    Comments = Not working at all
[Repair]
    Technician = contact;<id>
    Repairedon = 2022-02-16 11:18:34
    No of issues = 2
[Topic]
    Mark = 4.5    Passed = 0    Signature [empty]
[Topic]
    Mark = 2.1    Passed = 1    Signature [image1] [image2]

The form contains 3 simple questions, 2 groups (Computer, Repair), and one repeatable group Topic. The serialized JSON in this minimal format might look like this:

{
	"@ver": "m1.0",
	"@q": {
		"id": "eb614e48-4c4f-e911-a9a5-000d3a37da27"
	},
	"@root": {
		"age": 12, 
		"city": "Senec",
		"sex": "Male"
	},
	"computer": {
		"price": 427.12,
		"comments": "Not working at all"
	},
	"repair": {
		"technician": "contact;f868ef1e-cff8-e811-a980-000d3a37d634",
		"repairedon": "2022-02-16T11:18:34.000Z",
		"no_of_issues": 2
	},
	"topic": [ 
		{ "mark": 4.5, "passed": 0 },
		{ "mark": 2.1, "passed": 1, "sig": {"c": 2, "s": "annotation"} }
	]
}

The second topic has two images stored in annotation records with subjects sig#002$1 and sig#002$2. Both annotations are attached to the resco_questionnaire record with the ID specified in @q.id. (See the Images chapter for details.)

Example of flexible JSON

The same data stored in the flexible JSON format might look like this (note the different specification of image storage):

{
	"@ver": "f1.0",
	"@q": {
		"id": "eb614e48-4c4f-e911-a9a5-000d3a37da27",
		"on": "2022-03-16T11:18:34.000Z",
		"s": 2427
	},
	"@root": {
		"age": { "v": 12, "s": 2, "on": 3544, "vl": "12" },
		"city": { "v": "Senec", "s": 0, "on": 3734 },
		"sex": { "v": "Male", "s": 5, "on": 3900 }
	},
	"computer": {
		"@props" : { "e": 0},
		"price": { "v": 427.12, "s": 427, "on": 4000, "vl": "$427.12" 	},
		"comments": {
			"v": "The device is not working at all",
			"s": 0,
			"on": 4120
		}
	},
	"repair": {
		"technician": { "v": "contact;f868ef1e-cff8-e811-a980-000d3a37d634", "s": 5, "on": 4200, "vl": "WaBez" },
		"repairedon": { "v": "2022-02-16T11:18:34.000Z", "s": 0, "on": 4370 }
	},
	"topic": [ {
			"mark": { "v": 4.5, "s": 5, "on": 4600, "vl": "4.5" },
			"passed": { "v": 0, "s": 0, "on": 4690, "vl": "Did not pass" }
		},{
			"mark": { "v": 2.1, "s": 2, "on": 4900, "vl": "2.1" },
			"passed": { "v": 1, "s": 10, "on": 4950, "vl": "Passed" },
			"sig": { "v": {"c": 2, "s": "annotation"}, "on": 5200 }
		}
	]
}

Storage requirements

If we omit constant headers (@ver and @ nodes) and strip white spaces, the above samples will show that the flexible variant takes ~2x more storage than the minimal variant. This result may change depending on the setup of the flexible JSON format (which question attributes are stored), but also depending on the size of the data stored.

The legacy JSON format typically takes more than 10 times the storage of the minimal variant. (Or 5x as much as the flexible variant.) 

Serialization of answers (question value objects)

A questionnaire consists of questions. From the UI point of view, questions are components. Each component produces a value. Values are serialized as described in the following table.

Component Serialization Example
Text string “Martin”
Multiple Lines of Text string “Imagine a much longer text here...”
Single Choice String; option is chosen by the user “cat”
Multiple Choices Semicolon-delimited list of options chosen by the user “cat;dog;bird”
Whole Number Number 128
Decimal Number Number 3.15
Yes/No 1 or 0 (1 – true, 0 – false) 1
Option Set Whole number representing chosen option 2041
Date String: UTC date in ISO 8601 “2011-10-10"
Date & Time String: UTC datetime in ISO 8601 “2011-10-10T12:12:00”
Lookup String: <entityname>;<id> “account;ea3dadd6-3002-4191-b379-d6edf6ea67c8"
Regarding lookup Same as Lookup
Image / Media Allows 1+ images. Stored as json object with these fields: *)
“c” - number of images
“s” – storage; omitted if equal to the default storage as stored in @q.ds.
{
“c”: 2,
“s”: “annotation
}
Tagged Image Same as Image/Media. The JSON object has additional tags array, i.e., JSON objects {"gid": string, "x": int, "y": int}.
x/y = pixel coordinates, origin TL corner
{ 
“c”: 1, tags”: [ 
  { “gid”:”groupA”, ”x”:12, ”y”:13}, 
  { “gid”:”groupN”,”x”:2,”y”:3 } 
] }
Image Recognition Same as Image/Media As Image/Media
Signature Same as Image/Media As Image/Media
Email string “sample@resco.net”
URL string “https://docs.resco.net”
Barcode string “88233-123"
HTML string “<h1>Hello world</h1>”

*) Images are stored outside of JSON as questionnaire attachments - CRM annotations, OneDrive files... Image query to be used is described here; the details:

  • Image query will use questionnaire id (@q.id) and question name (JSON keys).
  • In the case of repeatable groups, the question name includes also a 1-based index of the group, for example, mark#001 and passed#001 for the first “topic” group in our examples.

The optional image description (only allowed for single image questions and annotation storage) is stored in the annotation.notetext field.

Description of minimal JSON

The design goal for this format was to minimize the storage footprint. Therefore, question details are omitted. In fact, questions are represented just by the name and the answer entered by the user (key/value pairs, where the key is the question name and the value is the answer). If you want to store additional data (such as score), you need to use the flexible JSON format.

The serialized string is a JSON object containing these root level keys (fields, properties…):

  • “@ver” stores format version (“m1.0”: "m" for minimal JSON, major version = 1, minor version = 0)
  • “@q” stores global questionnaire data. It is a JSON object with these keys:
a) “id” = questionnaire record ID. You may need it in image queries or when accessing custom questionnaire columns, for example.
b) “ds” = “<storage>”.1) This is an optional field, auto-generated by the questionnaire serializer if there are multiple image fields sharing the same storage. In this case, the @q.ds field is added and respective image values do not have the “s” field. (Storage optimization.)
  • “@root” is a container for questions not belonging to any group. Omitted if there are no such questions.
  • The remaining keys represent groups.

Treatment of groups:

  • Groups, which were defined as repeating in the template, consist of an array of repeated groups.
  • Both repeated groups and “@root” as well as root-level (ordinary) groups consist of questions.

Format of questions:

  • Each question is represented as key/value pair, question_name = serialized_answer. For answer serialization, see the previous chapter.

Question names:

  • For ordinary groups, question_name represents the name of the question.
  • For repeated groups, question name as defined in the original template group is used. (Note that, for example, image queries use the name question_name#nnn, where nnn is group index.)

Minimal JSON format skips unanswered questions or questions with default answers.2)

The formal schema of the minimal JSON format can be found in Appendix 1.

Notes:

1) Storage is either name of the entity used to store the binary (typically “annotation” in Dynamics/Resco Cloud, “contentversion” on Salesforce), or one of {SharePoint | DropBox | GoogleDrive | OneDrive | Box }
2) Note that it is not possible to differentiate between the situation when a question was not touched at all and a situation when the user explicitly selected the default answer.

Description of flexible JSON

This format is a superset of the minimal JSON format. It stores all the minimal data plus optional details. Selection of details is made at design time and is described in Questionnaire Designer documentation (TBD). For the format schema, see Appendix 2.

Even if you can make the flexible JSON store the same data as the minimal variant (by skipping optional details), it will still produce a bit larger output. This is due to overhead in serializing of questions.

Root level

The output is a JSON object containing these root level keys:

  • “@ver” stores format version (“f1.0”: f for flexible, major version = 1, minor version = 0)
  • “@q” stores global questionnaire data. It is a JSON object with these keys:
Key Serialize if Type
ID Always String: Id of the questionnaire
s If scoring is enabled Number: Questionnaire score
ds On demand String: Name of the default storage of binary questions (e.g. images)
on On demand String: When the questionnaire was answered. ISO 8601 date format.
by On demand String: Name of the user who filled the questionnaire
byID On demand String: ID of the user who filled the questionnaire
tID On demand String: Id of the template
defanswers On demand Int: Whether default answers are included: 0 or 1
  • “@root” is a container for questions not belonging to any group. Skipped if no such questions exist.
  • The remaining keys represent groups. Repeated groups are treated in the same way as for the minimal JSON format.

Groups

Groups (incl. “@root”) consist of questions and an optional “@props” container with these optional group properties (serialized only if different from the template):

resco_group field Abbreviation Serialize if Type
resco_label l On demand String
resco_description d On demand String
resco_visible vis On demand Number: 0 or 1
resco_expanded e On demand Number: 0 or 1

Questions

Questions are serialized as key/value pairs, where the key is the question name (as described in minimal JSON format), and the value is a JSON object with these attributes:

resco_question field Key Serialize if Type
resco_value v Always String/number/object: Serialized answer.
resco_score s If scoring is enabled Number: Question score
resco_label l On demand string
resco_description d On demand string
resco_visible vis On demand 1 if true, 0 otherwise
resco_required r On demand 1 if true, 0 otherwise
resco_valuelabel vl On demand string
resco_showonreport sr On demand 1 if true, 0 otherwise
resco_answeredon on On demand Number; number of milliseconds between question answering and questionnaire creation
resco_index i On demand Number (int)
resco_style st On demand string

Image/Media questions

For Image/Media questions, additional key-value pairs exist:

resco_question field Key Serialize if Type
none c always Number; max count of images allowed
resco_options s if image storage of the question differs from the default image storage stored in "ds" field in @q object String; table/entity where the image is saved

Appendix I – JSON schema for the minimal format

{
	"$schema": "https://json-schema.org/draft/2020-12/schema",
	"$id": "questionnaireanswers-min.schema.json",
	"title": "Schema for minJson Answer Storage",
	"description": "Holds the answers and other properties of the questionnaire",
	"type": "object",
	"properties": {
		"@ver": {
			"description": "Version number of the schema - m1.0, m2.1...",
			"type": "string",
			"pattern": "^m[1-9][0-9]*\\.[0-9]+$",
			"minimum": 1
		},
		"@q": {
			"description": "Holds information about the questionnaire itself.",
			"type": "object",
			"properties": {
				"id": {
					"description": "The id of the questionnaire.",
					"type": "string"
				},
				"ds": {
				"description": "Storage used for binary questions: entity name or cloud service name.",
					"type": "string"
				}
			},
			"required": [
				"id"
			]
		},
		"@root": {
			"type": "object",
			"description": "Stores answered questions, that do not belong to any group.",
			"patternProperties": { "^[-_a-zA-Z0-9]+$": { "$ref": "#/$defs/question" } },
			"additionalProperties": false
		}
	},
	"patternProperties": {
		"^[-_a-zA-Z0-9]+$": {
			"anyOf": [
				{ "$ref": "#/$defs/group" },
				{ "$ref": "#/$defs/repeatablegroup" }
			]
		}
	},
	"additionalProperties": false,
	"required": [ "@ver", "@q" ],
	"$defs": {
		  "question": {
			"description": "Stores question answer. The type depends on the question being serialized.",
			"type": [ "number", "string", "object" ]
		  },
		  "repeatablegroup": {
			"type": "array",
			"description": "List of repeated groups of the same type",
			"items": { "$ref": "#/$defs/group" }
		  },
		  "group": {
			"type": "object",
			"description": "Group containing questions along with their answers.",
			"patternProperties": {
				"^[-_a-zA-Z0-9]+$": { "$ref": "#/$defs/question" }
			},
			"additionalProperties": false
	   }
	}
}

Appendix II – JSON schema for the flexible format

{
	"$schema": "https://json-schema.org/draft/2020-12/schema",
	"$id": "questionnaireanswers-flexi.schema.json",
	"title": "Schema for flexJson Answer Storage",
	"description": "Holds the answers and other properties of the questionnaire",
	"type": "object",
	"properties": {
		"@ver": {
			"description": "Version number of the schema - f1.0, f2.1...",
			"type": "string",
			"pattern": "^f[1-9][0-9]*\\.[0-9]+$",
			"minimum": 1
		},
		"@q": {
			"description": "Holds information about the questionnaire itself.",
			"type": "object",
			"properties": {
				"id": {
					"description": "The id of the questionnaire.",
					"type": "string"
				},
				"ds": {
					"description": "Storage used for binary questions: entity name or cloud service name.",
					"type": "string"
				},
				"defanswers": {
					"type": "integer",
					"minimum": 0,
					"maximum": 1,
					"default": 0
				},
				"s  ": {
					"description": "Overall score of the questionnaire.",
					"type": "number"
				},
				"on": {
					"description": "Time when the questionnaire was answered.",
					"type": "string"
				},
				"by": {
					"description": "Name of the user that filled in the questionnaire.",
					"type": "string"
				},
				"byID": {
					"description": "Id of the user that filled in the questionnaire.",
					"type": "string"
				},
				"tID": {
					"description": "The id of the questionnaire template.",
					"type": "string"
				}
			},
			"required": [ "id" ]
		},
		"@root": {
			"type": "object",
			"description": "Stores questions, that do not belong to any group.",
			"patternProperties": { "^[-_a-zA-Z0-9]+$": { "$ref": "#/$defs/question" } },
			"additionalProperties": false
		}
	},
	"patternProperties": {
		"^[-_a-zA-Z0-9]+$": {
			"anyOf": [
				{ "$ref": "#/$defs/group" },
				{ "$ref": "#/$defs/repeatablegroup" }
			]
		}
	},
	"additionalProperties": false,
	"required": [ "@ver", "@q" ],
	"$defs": {
		"question": {
			"description": "Stores question answer and details.",
			"type": "object",
			"properties": {
				"v": {
					"$ref": "#/$defs/questionvalue"
				},
				"s": {
					"description": "Question score",
					"type": "number"
				},
				"l": {
					"description": "Question label",
					"type": "string"
				},
				"d": {
					"description": "Question description",
					"type": "string"
				},
				"vis": {
					"description": "1 if question is visible, 0 otherwise",
					"type": "integer",
					"minimum": 0,
					"maximum": 1,
					"default": 1
				},
				"r": {
					"description": "1 if question is required, 0 otherwise",
					"type": "integer",
					"minimum": 0,
					"maximum": 1,
					"default": 0
				},
				"sr": {
					"description": "1 if question should be shown on the report, 0 otherwise",
					"type": "integer",
					"minimum": 0,
					"maximum": 1,
					"default": 1
				},
				"vl": {
					"description": "Human readable answer on the question",
					"type": "string"
				},
				"on": {
					"description": "When the question was answered: milliseconds since questionnaire creation.",
					"type": "integer"
				},
				"i": {
					"description": "Index (order) of the question",
					"type": "integer",
					"minimum": 0
				},
				"st": {
					"description": "Style name of the question",
					"type": "string"
				}
			},
			"additionalProperties": false
		},
		"questionvalue": {
			"description": "Stores question answer. The type depends on the question being serialized.",
			"type": [ "number", "string", "object" ]
		},
		"repeatablegroup": {
			"type": "array",
			"description": "List of repeated groups of the same type",
			"items": { "$ref": "#/$defs/group" }
		},
		"group": {
			"type": "object",
			"description": "Group containing questions and optional changed group fields.",
			"patternProperties": {
				"^[-_a-zA-Z0-9]+$": { "$ref": "#/$defs/question" }
			},
			"properties": {
				"@props": {
					"type": "object",
					"description": "Properties that differ from the template group",
					"properties": {
						"l": {
							"description": "Group label",
							"type": "string"
						},
						"d": {
							"description": "Group description",
							"type": "string"
						},
						"e": {
							"description": "1 if the group is expanded, 0 otherwise",
							"type": "integer",
							"minimum": 0,
							"maximum": 1,
							"default": 1
						},
						"vis": {
							"description": "1 if the group is visible, 0 otherwise",
							"type": "integer",
							"minimum": 0,
							"maximum": 1,
							"default": 1
						}
					},
					"additionalProperties": false
				}
			},
			"additionalProperties": false
		}
	}
}

Appendix III – Images

Images are not serialized in JSON. (Why they should?) They might not be even stored in the same database as the answered questionnaire.

The basic idea is that images are "attached" to the questionnaire they belong to.

On top of that, we need another criterion to find the actual question where the image is attached. To achieve that, image attachments bear the name of the question.

However, this is still not enough as the questions may be repeated (if they belong to repeatable groups) and even some questions allow multiple images.

That's way this rather complex syntax for the image name: <question_name>[#ggg][$i]

  • #ggg = 1-based group index formatted as #001, #002...; omitted if the question does not belong to a repeatable group.
  • $i = 1-based image index formatted as $1, $2...; omitted for single image questions.

We already saw image names such as sig#002$1 in the preceding examples.

Example: How to load images stored in annotations

If the question does not belong to a repeatable group, we can use this fetch (the ID "eb614e48…” refers to the questionnaire):

<fetch>
	<entity name="annotation">
		<attribute name="documentbody"/>
		<attribute name="filename"/>
		<filter type="and">
			<condition attribute="objectid" operator="eq" value="eb614e48-4c4f-e911-a9a5-000d3a37da27" />
			<condition attribute="subject" operator="like" value="question_name%  " />
		</filter>
	</entity>
</fetch>

The fetch above hides a potential risk: If there is another question whose name extends “question_name” (such as “question_name1”), we get also images belonging to that question. If this can happen, use this fetch:

<fetch>
    <entity name="annotation">
        <attribute name="documentbody"/>
        <attribute name="filename"/>
        <filter type="and">
            <condition attribute="objectid" operator="eq" value="eb614e48-4c4f-e911-a9a5-000d3a37da27" />
            <filter type="or">
                <condition attribute="subject" operator="eq" value="question_name" />
                <condition attribute="subject" operator="like" value="question_name$%" />
            </filter>
        </filter>
    </entity>
</fetch>

If the question belongs to a repeatable group, the fetch could look like this. The problem above cannot happen.

<fetch>
	<entity name="annotation">
		<attribute name="documentbody"/>
		<attribute name="filename"/>
		<filter type="and">
			<condition attribute="objectid" operator="eq" value="eb614e48-4c4f-e911-a9a5-000d3a37da27" />
			<condition attribute="subject" operator="like" value="sig#001% " />
		</filter>
	</entity>
</fetch>

To finalize this case (annotations on Dynamics CRM) documentbody field contains a Base64 encoded binary image.

That’s it. Other platforms (such as SharePoint) will use a different procedure, but the basic idea remains the same.

If you want to double-check the results for completeness, you can compare the count of returned images to the value of “c” in the serialized value of the image question.

See also