Questionnaire OData service

From Resco's Wiki
Jump to navigation Jump to search
Integration with third-party systems

Resco CRM Connector (web APIs):

This is a read-only OData v4 API service for accessing questionnaire results from Resco Cloud.

Due to the way how questionnaire data is saved, using the universal REST API to retrieve questionnaire results can be fairly complicated for many users. Also, the resulting data is hard to digest in analytical tools, for example, Power BI or Excel, whereas OData is natively supported by many analytics apps.

When using OData, one record in the response corresponds to one answered questionnaire. The response is in a tree structure, answer groups are sub-records. Tools like Power BI can flatten these into one line.

Authentication

The service uses standard BASIC authentication with the organization's username and password.

Service document

Lists all questionnaire templates available.

Each url in the list is a relative data request url to get the questionnaire results.

URI

The service URI depends on server settings; whether the server uses domain organization selection or simple URL organization selection.

  • https://[baseURI]/odata/questionnaires/v4/[organization]/
  • https://[organization].[baseURI]/odata/questionnaires/v4/

Examples

  • Resco Cloud: https://my_org.rescocrm.com/odata/questionnaires/v4/
  • Resco Cloud US: https://my_org.us1.rescocrm.com/odata/questionnaires/v4/
  • Resco Inspections: https://inspections.resco.net/odata/questionnaires/v4/my_org/

Adjustment of names

Because of limitations for OData keys, the names are adjusted by replacing spaces and special characters with underscore (_) character.

Versioned templates

There is a separate entity for each version of the template. Each versioned template has a postfix of _vXY where XY is a version number. For each group of versioned templates there is one merged template without a version postfix. This merged template contains merged questions from all versions of the template. See, for instance, Template_Generic_Questionnaire and Template_Generic_Questionnaire_v1 in the example below.

Example

Request: https://inspections.resco.net/odata/questionnaires/v4/[organization]/

Response: 

{
  "@odata.context": "http://inspections.resco.net/odata/questionnaires/v4/[organization]/$metadata",
  "value": [
    {
      "name": "Template_Regular_Inspection",
      "kind": "EntitySet",
      "url": "Template_Regular_Inspection"
    },
    {
      "name": "Template_Generic_Questionnaire_v1",
      "kind": "EntitySet",
      "url": "Template_Generic_Questionnaire_v1"
    },
    {
      "name": "Template_Generic_Questionnaire",
      "kind": "EntitySet",
      "url": "Template_Generic_Questionnaire"
    }
  ]
}

Data request

Request for results of questionnaires. The resulting dataset depends on query applied. Without a query a single page of questionnaire results is returned. Default (and max) page size is 1000.

URI

  • https://[baseURI]/odata/questionnaires/v4/[organization]/[AdjustedTemplateName]
  • https://[organization].[baseURI]/odata/questionnaires/v4/[AdjustedTemplateName]

Detailed info

The result shows a list of questionnaire results or a single questionnaire result or a subset of properties, depending on a query applied.

A questionnaire result item contains a structured view of answers provided for the questionnaire and question groups, along with some technical columns.

Answers of question groups are placed in a nested complex type, see, for instance, final_notes in the example below.

Answers of repeatable question groups are in an array of nested complex type, see, for instance, floor.

Lookup answers are modeled as navigation properties. However, for convenience and performance reasons, they are partially expanded by default to id, name, and target. See accountlkp in the example below.

Adjustment of keys

Because of limitations for OData keys, the answer and group keys are adjusted by replacing spaces and special characters with underscore (_) character.

Queries

To see how the queries can be constructed, check the OData protocol.

The service is compliant to OData 4.0 Minimal Conformance Level and partially to OData 4.0 Intermediate Conformance Level.

To see a list of unsupported stuff, check Limitations.

Paging

The data can be paged using $top and $skip query options. Except for that, also a server side paging is applied with a max page size of 1000 items. Whenever the server cuts the response to fulfill server limits, the response contains an [@odata.nextLink](http://docs.oasis-open.org/odata/odata-json-format/v4.0/cs01/odata-json-format-v4.0-cs01.html#_Toc365464689) annotation with an uri that can be used to retrieve next page of data.

Example

Request: https://inspections.resco.net/odata/questionnaires/v4/[organization]/Template_Regular_Inspection

Response: 

{
  "@odata.context": "http://inspections.resco.net/odata/questionnaires/v4/[organization]/$metadata#Template_Regular_Inspection",
  "value": [
    {
      "id": "8d0ee6ad-c722-4fe4-8dbc-f7775978f2ef",
      "schindler_model": null,
      "serial_number": "AAA-123456AA",
      "working_hours_since_last_inspection": null,
      "manufacturer": 0,
      "kone_model": 0,
      "otis_model": null,
      "state": 1,
      "createdon": "2020-02-11T10:07:02Z",
      "modifiedon": "2020-02-11T10:07:02Z",
      "publishedon": null,
      "archivedon": null,
      "createdby": null,
      "modifiedby": null,
      "completionstatus": null,
      "description": "Checklist for inspections of elevators on accounts site.",
      "publishnotes": null,
      "accountlkp": {
        "id": "585ace18-4eec-4288-9294-1286d768c210",
        "name": "Warner Apartment Building",
        "target": "account"
      },
      "cabin_interior": {
        "general_state": null,
        "lcd_display": 0,
        "lights": 0,
        "control_panel": 0,
        "speaker_and_microphone": 0,
        "cabin_walls": 1
      },
      "final_notes": {
        "comments_from_customer": null,
        "customer_signature": null,
        "suggested_next_inspection_date": "2019-11-03T14:37:07Z"
      },
      "floor": [
        {
          "push_buttons": null,
          "floor_number": null,
          "display": null,
          "additional_comments": null,
          "doors": null,
          "photo_documentation_1": null
        }
      ],
      "ownerid": {
        "id": "601d9d17-89b4-e111-9c9a-00155d0b710a",
        "name": null,
        "target": "systemuser"
      }
    }
  ]
}

Metadata document

Provides a standard OData CSDL model description XML.

URI

  • https://[baseURI]/odata/questionnaires/v4/[organization]/$metadata
  • https://[organization].[baseURI]/odata/questionnaires/v4/$metadata

Detailed info

Questionnaire templates are modeled as entities with their properties being either technical columns of a questionnaire, answers, or answer groups.

Each answer group is modeled as a separate complex type with a special name constructed from the question group key. The name pattern is RescoQuestionnaire.QuestionnaireTemplateRef.QuestionGroup. Note the Ref added to the template name. This is to avoid name conflict with the QuestionnaireTemplate type.

The answers with a lookup type are modeled as navigation properties to a separate complex type. If the lookup property has a single navigation target, there is a type for that specific target, for example, RescoQuestionnaire.account. For properties with multiple targets there is a special common type named RescoQuestionnaire._lookup.

There are two special property annotations is the model. * RescoQuestionnaire.OriginalName annotates properties with original names (keys) of questions as provided in DB before adjusting to OData keys. * RescoQuestionaire.Technical annotates properties that are technical, i.e. are coming from questionnaire, not from questions / answers.

Example

Request: https://inspections.resco.net/odata/questionnaires/v4/[organization]/$metadata

Response (shortened): 

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
        <Schema Namespace="RescoQuestionnaire" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="Template_Regular_Inspection">
                <Key>
                    <PropertyRef Name="id" />
                </Key>
                <Property Name="id" Type="Edm.Guid" />
                <Property Name="createdon" Type="Edm.DateTimeOffset" />
                <Property Name="modifiedon" Type="Edm.DateTimeOffset" />
                <Property Name="publishedon" Type="Edm.DateTimeOffset" />
                <Property Name="archivedon" Type="Edm.DateTimeOffset" />
                <Property Name="regardingid" Type="Edm.String" />
                <Property Name="regardingidlabel" Type="Edm.String" />
                <Property Name="regardingidname" Type="Edm.String" />
                <Property Name="folder" Type="Edm.String" />
                <Property Name="languagecode" Type="Edm.Int32" />
                <Property Name="completionstatus" Type="Edm.Int32" />
                <Property Name="description" Type="Edm.String" />
                <Property Name="publishnotes" Type="Edm.String" />
                <Property Name="schindler_model" Type="Edm.Int32" />
                <Property Name="serial_number" Type="Edm.String" />
                <Property Name="working_hours_since_last_inspection" Type="Edm.Int32" />
                <Property Name="manufacturer" Type="Edm.Int32" />
                <Property Name="kone_model" Type="Edm.Int32" />
                <Property Name="otis_model" Type="Edm.Int32" />
                <Property Name="state" Type="Edm.Int32" />
                <Property Name="cabin_exterior" Type="RescoQuestionnaire.Template_Regular_InspectionRef.cabin_exterior" />
                <Property Name="cabin_interior" Type="RescoQuestionnaire.Template_Regular_InspectionRef.cabin_interior" />
                <Property Name="final_notes" Type="RescoQuestionnaire.Template_Regular_InspectionRef.final_notes" />
                <Property Name="engine_room" Type="RescoQuestionnaire.Template_Regular_InspectionRef.engine_room" />
                <Property Name="cabin_issue" Type="Collection(RescoQuestionnaire.Template_Regular_InspectionRef.cabin_issue)" />
                <Property Name="floor" Type="Collection(RescoQuestionnaire.Template_Regular_InspectionRef.floor)" />
                <NavigationProperty Name="createdby" Type="RescoQuestionnaire._lookup" />
                <NavigationProperty Name="modifiedby" Type="RescoQuestionnaire._lookup" />
                <NavigationProperty Name="ownerid" Type="RescoQuestionnaire._lookup" />
                <NavigationProperty Name="owningbusinessunit" Type="RescoQuestionnaire._lookup" />
                <NavigationProperty Name="folderid" Type="RescoQuestionnaire._lookup" />
                <NavigationProperty Name="accountlkp" Type="RescoQuestionnaire.account" />
                <NavigationProperty Name="inspector" Type="RescoQuestionnaire.systemuser" />
                <NavigationProperty Name="appointmentlkp" Type="RescoQuestionnaire.appointment" />
            </EntityType>
            <EntityType Name="_lookup">
                <Key>
                    <PropertyRef Name="id" />
                </Key>
                <Property Name="id" Type="Edm.Guid" />
                <Property Name="name" Type="Edm.String" />
                <Property Name="target" Type="Edm.String" />
            </EntityType>
            <EntityType Name="account">
                <Key>
                    <PropertyRef Name="id" />
                </Key>
                <Property Name="id" Type="Edm.Guid" />
                <Property Name="name" Type="Edm.String" />
                <Property Name="target" Type="Edm.String" />
            </EntityType>
            <EntityContainer Name="DefaultContainer">
                <EntitySet Name="Template_Regular_Inspection" EntityType="RescoQuestionnaire.Template_Regular_Inspection" />
            </EntityContainer>
            <Annotations Target="RescoQuestionnaire.Template_Regular_Inspection/id">
                <Annotation Term="RescoQuestionnaire.OriginalName" String="resco_questionnaireid" />
                <Annotation Term="RescoQuestionnaire.Technical" Bool="true" />
            </Annotations>
            <Annotations Target="RescoQuestionnaire.Template_Regular_Inspection/serial_number">
                <Annotation Term="RescoQuestionnaire.OriginalName" String="serial-number" />
            </Annotations>
        </Schema>
        <Schema Namespace="RescoQuestionnaire.Template_Regular_InspectionRef" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <ComplexType Name="cabin_exterior">
                <Property Name="left_brake" Type="Edm.Int32" />
                <Property Name="other" Type="Edm.String" />
                <Property Name="rope_holders" Type="Edm.Int32" />
                <Property Name="ropes" Type="Edm.Int32" />
                <Property Name="right_brake" Type="Edm.Int32" />
            </ComplexType>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>
```

Limitations

  • $expand does not actually expand the navigation properties yet,
  • $orderBy not yet supported because of the way data is structured in DB,
  • $filter on answers in a group (e.g. $filter=group/answer eq 'something') does not check yet whether the answer belongs to the group, making possible false results when there is also a non-group answer with the same key or other group answer with the same name,
  • $filter does not yet work on non-root levels (e.g. `Somethind(id)/group?$filter=…),
  • not not supported in $filter,
  • only startsWith, endsWith, contains functions supported in $filter,
  • error handling - does not always return json,
  • $format not supported,
  • $count and other aggregations not supported,
  • $value not supported,
  • some headers should be supported and are not.
Retrieved from "https://docs.resco.net/mediawiki/index.php?title=Questionnaire_OData_service&oldid=11508"