Build Queries Using the Data Model

The CampusNexus data model provides most of the information that is displayed in the Property Settings pane in Form Designer. The CampusNexus data model is parsed and its metadata is extracted to populate the Property Settings. The data model is exposed and can be used as a reference to build OData queries in Forms Builder.

Note: Anthology Student 21.2.0 and later requires users to be authorized to execute OData queries. For more information, see Security Enhancement for OData Queries.

Important OData calls must be made from within Forms Builder or Workflow. OData calls via ApiProxy outside of Forms Builder or Workflow are not supported.

View the Metadata

Search the metadata for the entity you are working, e.g., "Students". The metadata for the "Student" entity provide several prebuilt OData queries. Note that all pre-built queries contain "select" options for the Code, Name, and Id columns and are ordered by Name.

Example: Student Entity Metadata (Excerpt)

-<EntityType cmcedm:Description="Students" cmcedm:PluralDisplayName="Students" cmcedm:DisplayName="Student" cmcedm:Browsable="true" Name="Student">
-<Key>
<PropertyRef Name="Id"/>
</Key>
<Property cmcedm:DisplayName="Id" cmcedm:Browsable="true" Name="Id" cmcedm:Editable="true" Nullable="false" Type="Edm.Int32" cmcedm:Required="true"/>
<Property cmcedm:Description="Campus" cmcedm:DisplayName="Campus" cmcedm:Browsable="false" Name="CampusId" cmcedm:Editable="true" Nullable="false" Type="Edm.Int32" cmcedm:Required="true" cmcedm:Lookup="true" cmcedm:LookupQueryValueColumn="Id" cmcedm:LookupQueryName="Campuses?$select=Code,Name,Id&$filter=IsActive eq true&$orderby=Name"/>
<Property cmcedm:Description="Current Employer" cmcedm:DisplayName="Current Employer" cmcedm:Browsable="false" Name="EmployerId" cmcedm:Editable="true" Type="Edm.Int32" cmcedm:Lookup="true" cmcedm:LookupQueryValueColumn="Id" cmcedm:LookupQueryName="Employers?$select=Code,Name,Id&$filter=IsActive eq true&$orderby=Name"/>
<Property cmcedm:Description="Employment Status" cmcedm:DisplayName="Employment Status" cmcedm:Browsable="false" Name="EmploymentStatusId" cmcedm:Editable="true" Type="Edm.Int32" cmcedm:Lookup="true" cmcedm:LookupQueryValueColumn="Id" cmcedm:LookupQueryName="EmploymentStatuses?$select=Code,Name,Id&$filter=IsActive eq true&$orderby=Name"/>

Execute a Query

You can copy a query from the metadata, append the query to the Student Base URL, and view the query results in a browser.

1. Access the Student Base URL in a browser. In our example, the Student Base URL is as follows:

   http://cltqafb3.campusmgmt.com:8080/Cmc.Nexus.Web/ds/campusnexus/$metadata

2. Remove $metadata from the URL

3. Copy a query from the metadata to the clipboard. In our example, the query is as follows:

   PreviousEducationCodes?$select=Code,Name,Id&$filter=IsActive eq true&$orderby=Name

   This query selects the Code, Name, and ID from the PreviousEducationCode field, filters by Active, and sorts by Name.

4. Append the query to the Student Base URL.

   p>http://cltqafb3.campusmgmt.com:8080/Cmc.Nexus.Web/ds/campusnexus/PreviousEducationCodes?$select=Code,Name,Id&$filter=IsActive eq true&$orderby=Name

5. Press Enter to run the query in the browser.

   Note: Use Chrome or Firefox to run the query. Internet Explorer will download the query.

   The browser displays the query results. In our example, the list of active Previous Education Codes is displayed.

   

Modify a Query

You can modify the query in the URL of the browser to obtain the desired results.

Change the Sort Order

For example, you can change sort order to sort by Id instead of Name.

Remove the "select" Option

To retrieve the data in all columns of a field, you could remove the "select" option for the columns.

For example, change the query from:

p>PreviousEducationCodes?$select=Code,Name,Id&$filter=IsActive eq true&$orderby=Name

to:

PreviousEducationCodes?$filter=IsActive eq true&$orderby=Name

Use the "$expand" Option for Navigation Properties

For entities that have navigation properties, you can build a query that joins a query for navigation property to the query for the entity.

For example, the Campus entity contains the Buildings navigation property. Navigation properties are related resources for an entity. Use the "$expand" option to expand related resources in line with the retrieved resources.

The following query retrieves all buildings for every campus.

Campuses?$filter=IsActive eq true&$orderby=Id&$expand=Buildings

The basic pattern is as follows: Entities?$option1= …&$option2=…

• Use ? after Entities
• Specify query options such as filter, expand, and orderby. Each option should be prefixed with $.
• Combine attributes using &.

Change the "$filter" Option

To retrieve the buildings for one campus only, you could filter by Active campuses and by Campus Id as follows:

Campus?filter=IsActive eq true and ID eq 5 orderby=Id $expand=Buildings

Build a Cascading Query Using AngularJS

Forms Builder supports two-way binding using AngularJS expressions. Angular expressions can be part of an OData queries adding flexibility and scope.

Example:

The following query retrieves Buildings in Active status for Campus Id=5.

Buildings?filter=IsActive eq true and CampusId eq 5

The CampusId is represented by the Model property in the Property Settings. The Model value is vm.models.studentEntity.CampusId. The Model value can be added to the LookupQuery using AngularJS syntax (enclosed in double curly braces).

Buildings?filter=IsActive eq true and CampusId eq {{vm.models.studentEntity.CampusId}}

The resulting query is a cascading filter that retrieves Buildings in Active status for any campus that is represented by the Model value.

This query is added to a drop-down control for Buildings. The control is added to a form that also contains a drop-down control for Campuses. On the rendered form, the user can select a Campus, and based on the selected Campus, the drop-down for Buildings will list only buildings associated with the selected campus.

OData Syntax Reference

To learn more about the OData query syntax, see http://www.odata.org/ and look for “Basic Tutorial” and “Advanced Tutorial”.

Populate the Lookup Query in Form Designer

After testing the OData query in the browser, paste it in the Lookup Query field of the list control in the Property Settings pane of Form Designer.

Lookup Queries for CampusNexus CRM Metadata

For any drop-down or search controls that will be populated via a lookup query, the CampusNexus CRM user needs to enter values for the Lookup Display Member and Lookup Sort Member attributes. The Lookup Query and Lookup Value Member property settings should have default values (if applicable for the selected property) as these are currently specified in the metadata.