More information

GetDynamicQuery

GetFacadeObjectList

GetDynamicQueryDefinition

Initial Build

 2007.01

xWEB

 Web Method

Response Type

XML Node

This web method is a companion web method to GetDynamicQuery. This method provides additional information about ask-at-runtime parameters of a query. It can be used in a real-time application to define how a query is presented to a user, or it can be used for informational purposes during application development.

Parameters

Name Type Occurs Description
szObjectName string 1..1

The object name. You can obtain the name by using the GetFacadeObjectList method.

szQueryName string 1..1 The name of the query.

Response

The response for GetDynamicQueryDefinition gives you every ask-at-runtime parameter. The ParameterName must be passed back when you actually call GetDynamicQuery and set the Parameter/Name. 

<ParameterName>bbcdb8c8-3811-4f6b-9549-4b92badeda9b_0</ParameterName>

The 36-character GUID corresponds to underlying application metadata for the query definition, and the integer counter suffix is a further internal definition.

Each <QueryParameter> node is an ask-at-runtime parameter. The additional elements in this node can be used to present a graphical user interface to a user who will want to call this method, or for the developer to understand what each parameter is when constructing an integration.

The request returns the following information:

  • que_key - Key of the query
  • que_description - the name of the query
  • szObjectName - object of the query
  • Parameters - a single node. The <Parameters> node contains one or more <QueryParameter> nodes.
  • QueryParameter - the <QueryParameter> node contains the following elements:
  • ParameterName - the name of the parameter. This must be passed back when calling GetDynamicQuery
  • ParameterDescription - description of parameter
  • Operator - see list above for operators
  • ControlClass - the Control Class of the column. See Control Class for descriptions of these values.
  • DataType - see Data Type for descriptions. Example: String, Integer, Date, etc.
  • Description - the plain English Column description. Example, "First Name" is the description of the ind_first_name column.
  • IsExtended - if this is a column in an Extender Table then true, else false.
  • IsNullable - if nullable then true else false.
  • Name - the underlying columnName.

If the parameter can contain a list of certain fixed values (that is, a DropDownList or ListBox) then the allowable values are listed within the <ListOptionCollection> node, as <Value> / <Text> pairs in one or more <ListOption> node(s). Whatever is in the <Value> element will need to be passed back in the Parameters/Parameter/Value element when GetDynamicQuery is called. The <Text> is the description of that particular code, which should be presented to an end-user.

  • ListOptionCollection - a node containing one or more <ListOption> nodes.
  • ListOption - a node that contains a <Value> and <Text> element.
  • Value - the internal value; this needs to be passed back to the Parameter/Value in GetDynamicQuery.
  • Text - the plain English value that should be shown to the end user.

In most cases, these <ListOption> values are derived from an underlying database table. For example, the Prefix codes (Mr., Mrs., Dr., etc.) are derived from the co_prefix database table. For these kinds of <ListOption> values, the following three addition elements are populated in the <QueryParameter> node:

  • TableName - name of the Table from which the allowable values are drawn.
  • TableDescription - plain English description of the table.
  • TableAlias - alias of the table, if any, that drives the allowable values of the column. This might be blank.

Sample Request

Here is the request to get the definition of a specific query (within a particular object): 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>21ac575e-6f17-441e-9570-aba6cc263585</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:GetDynamicQueryDefinition>
<ns:szObjectName>Individual</ns:szObjectName>
<ns:szQueryName>Individuals on Committee</ns:szQueryName>
</ns:GetDynamicQueryDefinition>
</soapenv:Body>
</soapenv:Envelope>

Sample Response

Here is the SOAP definition or the response. Note the repeating <QueryParameter> node(s) within the <Parameters> node. 

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>string</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<GetDynamicQueryDefinitionResponse xmlns="http://www.avectra.com/2005/">
<GetDynamicQueryDefinitionResult>
<que_key>string</que_key>
<que_description>string</que_description>
<szObjectName>string</szObjectName>
<Parameters>
<QueryParameter>
<ParameterName>string</ParameterName>
<ParameterDescription>string</ParameterDescription>
<Operator>Like or NotLike or Equals or NotEquals or GreaterThan or LessThan or GreaterThanOrEqualTo or LessThanOrEqualTo or IsIn or IsNotIn or IsNull or IsNotNull or SoundsLike or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or EndsWith or DoesNotEndWith</Operator>
<ControlClass>string</ControlClass>
<DataType>string</DataType>
<Description>string</Description>
<IsExtended>boolean</IsExtended>
<IsNullable>boolean</IsNullable>
<Name>string</Name>
<TableAlias>string</TableAlias>
<TableDescription>string</TableDescription>
<TableName>string</TableName>
<ListOptionCollection xsi:nil="true" />
</QueryParameter>
<QueryParameter>
<ParameterName>string</ParameterName>
<ParameterDescription>string</ParameterDescription>
<Operator>Like or NotLike or Equals or NotEquals or GreaterThan or LessThan or GreaterThanOrEqualTo or LessThanOrEqualTo or IsIn or IsNotIn or IsNull or IsNotNull or SoundsLike or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or EndsWith or DoesNotEndWith</Operator>
<ControlClass>string</ControlClass>
<DataType>string</DataType>
<Description>string</Description>
<IsExtended>boolean</IsExtended>
<IsNullable>boolean</IsNullable>
<Name>string</Name>
<TableAlias>string</TableAlias>
<TableDescription>string</TableDescription>
<TableName>string</TableName>
<ListOptionCollection xsi:nil="true" />
</QueryParameter>
</Parameters>
</GetDynamicQueryDefinitionResult>
</GetDynamicQueryDefinitionResponse>
</soap:Body>
</soap:Envelope>

Example of a parameter that should contain a list of Events in a drop-down list: 

<QueryParameter>
<ParameterName>e6b7af49-eb96-4db3-875e-9eef859d1d31_1</ParameterName>
<ParameterDescription>[Event Registrant::Event] Is Equal To [] (Ask At Run-Time)</ParameterDescription>
<Operator>Equals</Operator>
<ControlClass>DropDownList</ControlClass>
<DataType>av_key</DataType>
<Description>Event</Description>
<IsExtended>false</IsExtended>
<IsNullable>false</IsNullable>
<Name>reg_evt_key</Name>
<TableAlias/>
<TableDescription>Event Registrant</TableDescription>
<TableName>ev_registrant</TableName>
<ListOptionCollection>
<ListOption>
<Value>72db64c1-63d2-4f70-88cb-cc38003e0beb</Value>
<Text>2007 Government Affairs Conference</Text>
</ListOption>
<ListOption>
<Value>a55cba19-6f87-40b9-a488-0a5f84e390a7</Value>
<Text>2007 Annual Seminar</Text>
</ListOption>
<ListOption>
<Value>8309eaa5-a60b-43bb-9b7f-87b53d9c118f</Value>
<Text>2007 Leaders Summit</Text>
</ListOption>
</ListOptionCollection>
</QueryParameter>

If you were to render this result as a DropDownList to an end user, the html would be like this: 

Event:
<select name = "e6b7af49-eb96-4db3-875e-9eef859d1d31_1">
<option value="" selected>-- Select --</option>
<option value="72db64c1-63d2-4f70-88cb-cc38003e0beb">2007 Government Affairs Conference</option>
<option value="a55cba19-6f87-40b9-a488-0a5f84e390a7">2007 Annual Seminar</option>
<option value="8309eaa5-a60b-43bb-9b7f-87b53d9c118f">2007 Leaders Summit</option>
</select>

Here is a complete sample response with some repeating nodes shortened for brevity: 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>975e0480-9b56-47d8-a936-4598eded3ae7</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<GetDynamicQueryDefinitionResponse xmlns="http://www.avectra.com/2005/">
<GetDynamicQueryDefinitionResult>
<que_key>40794c6e-a417-4c7c-859b-3bc580adbe5d</que_key>
<que_description>Individuals on Committee</que_description>
<szObjectName>Individual</szObjectName>
<Parameters>
<QueryParameter>
<ParameterName>bbcdb8c8-3811-4f6b-9549-4b92badeda9b_0</ParameterName>
<ParameterDescription>[Committee Participant::committee] Is Equal To [] (Ask At Run-Time)</ParameterDescription>
<Operator>Equals</Operator>
<ControlClass>DropDownList</ControlClass>
<DataType>av_key</DataType>
<Description>committee</Description>
<IsExtended>false</IsExtended>
<IsNullable>false</IsNullable>
<Name>cmc_cmt_key</Name>
<TableAlias/>
<TableDescription>Committee Participant</TableDescription>
<TableName>mb_committee_x_customer</TableName>
<ListOptionCollection>
<ListOption>
<Value>b9ae781c-b9c1-4636-a05c-f61835be49c2</Value>
<Text>Test Committee</Text>
</ListOption>
</ListOptionCollection>
</QueryParameter>
<QueryParameter>
<ParameterName>e6b7af49-eb96-4db3-875e-9eef859d1d31_1</ParameterName>
<ParameterDescription>[Event Registrant::Event] Is Equal To [] (Ask At Run-Time)</ParameterDescription>
<Operator>Equals</Operator>
<ControlClass>DropDownList</ControlClass>
<DataType>av_key</DataType>
<Description>Event</Description>
<IsExtended>false</IsExtended>
<IsNullable>false</IsNullable>
<Name>reg_evt_key</Name>
<TableAlias/>
<TableDescription>Event Registrant</TableDescription>
<TableName>ev_registrant</TableName>
<ListOptionCollection>
<ListOption>
<Value>72db64c1-63d2-4f70-88cb-cc38003e0beb</Value>
<Text>2007 .01 event</Text>
</ListOption>
<ListOption>
<Value>a55cba19-6f87-40b9-a488-0a5f84e390a7</Value>
<Text>2007 Annual Seminar</Text>
</ListOption>
<ListOption>
<Value>8309eaa5-a60b-43bb-9b7f-87b53d9c118f</Value>
<Text>2007 Event</Text>
</ListOption>
<ListOption>
<Value>ae23c888-a790-41a2-8b62-03f4fcffd414</Value>
<Text>Avectra event</Text>
</ListOption>
<ListOption>
<Value>7f18b35a-b70c-479c-9e61-73fa9861fda6</Value>
<Text>WsMeeting</Text>
</ListOption>
</ListOptionCollection>
</QueryParameter>
<QueryParameter>
<ParameterName>40794c6e-a417-4c7c-859b-3bc580adbe5d_2</ParameterName>
<ParameterDescription>[Individual::Last Name] Begins With [Enter Value] (Ask At Run-Time)</ParameterDescription>
<Operator>Equals</Operator>
<ControlClass>TextBox</ControlClass>
<DataType>nvarchar</DataType>
<Description>Last Name</Description>
<IsExtended>false</IsExtended>
<IsNullable>false</IsNullable>
<Name>ind_last_name</Name>
<TableAlias/>
<TableDescription>Individual</TableDescription>
<TableName>co_individual</TableName>
<ListOptionCollection/>
</QueryParameter>
<QueryParameter>
<ParameterName>40794c6e-a417-4c7c-859b-3bc580adbe5d_3</ParameterName>
<ParameterDescription>[Customer::Record Number] Greater Than or Equal To [Enter Value] (Ask At Run-Time)</ParameterDescription>
<Operator>GreaterThanOrEqualTo</Operator>
<ControlClass>TextBox</ControlClass>
<DataType>av_recno</DataType>
<Description>Record Number</Description>
<IsExtended>false</IsExtended>
<IsNullable>false</IsNullable>
<Name>cst_recno</Name>
<TableAlias/>
<TableDescription>Customer</TableDescription>
<TableName>co_customer</TableName>
<ListOptionCollection/>
</QueryParameter>
</Parameters>
</GetDynamicQueryDefinitionResult>
</GetDynamicQueryDefinitionResponse>
</soap:Body>
</soap:Envelope>

Example with Columns

GetDynamicQueryDefinition includes the columns that are returned in the results set, including the raw SQL name, the friendly name, the data type and which table the column is coming from. 

<GetDynamicQueryDefinitionResponse xmlns="http://www.avectra.com/2005/">
         <GetDynamicQueryDefinitionResult>
            <que_key>6bed2f0e-abc3-460d-adf1-ad6a1083fdb4</que_key>
            <que_description>**Inactive Individuals</que_description>
            <szObjectName>Individual</szObjectName>
            <Parameters/>
            <Columns>
               <QueryColumn>
                  <Name>ind_full_name_cp</Name>
                  <Description>Full Name</Description>
                  <DataType>nvarchar</DataType>
                  <TableAlias/>
                  <TableDescription>Individual</TableDescription>
                  <TableName>co_individual</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>ind_int_code</Name>
                  <Description>Individual Type</Description>
                  <DataType>nvarchar</DataType>
                  <TableAlias/>
                  <TableDescription>Individual</TableDescription>
                  <TableName>co_individual</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>ind_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Individual</TableDescription>
                  <TableName>co_individual</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>cst_ind_full_name_dn</Name>
                  <Description>Full Name</Description>
                  <DataType>nvarchar</DataType>
                  <TableAlias/>
                  <TableDescription>Customer</TableDescription>
                  <TableName>co_customer</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>cst_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Customer</TableDescription>
                  <TableName>co_customer</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>cxa_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Customer Address</TableDescription>
                  <TableName>co_customer_x_address</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>adt_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Address Type</TableDescription>
                  <TableName>co_address_type</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>adr_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Address</TableDescription>
                  <TableName>co_address</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>ixo_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Affiliation</TableDescription>
                  <TableName>co_individual_x_organization</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>org_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Organization</TableDescription>
                  <TableName>co_organization</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>dmv_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias>Demographics</TableAlias>
                  <TableDescription>Demographic Value</TableDescription>
                  <TableName>co_demographic_value</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>mbr_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias>Membership</TableAlias>
                  <TableDescription>Membership</TableDescription>
                  <TableName>mb_membership</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>mbt_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Member Type</TableDescription>
                  <TableName>mb_member_type</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>mbr_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias>ChapterMembership</TableAlias>
                  <TableDescription>Membership</TableDescription>
                  <TableName>mb_membership</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>mbr_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias>OrgMembership</TableAlias>
                  <TableDescription>Membership</TableDescription>
                  <TableName>mb_membership</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>cmc_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Committee Participant</TableDescription>
                  <TableName>mb_committee_x_customer</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>reg_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Event Registrant</TableDescription>
                  <TableName>ev_registrant</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>mld_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Mailing List Detail</TableDescription>
                  <TableName>mk_mailing_list_detail</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>eml_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias>emlMld</TableAlias>
                  <TableDescription>Email</TableDescription>
                  <TableName>co_email</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>fac_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Event Faculty</TableDescription>
                  <TableName>ev_event_faculty</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>eml_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Email</TableDescription>
                  <TableName>co_email</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>cal_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Customer Alias</TableDescription>
                  <TableName>co_customer_alias</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>rgt_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Registrant Type</TableDescription>
                  <TableName>ev_registrant_type</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>rca_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Customer x Related Association</TableDescription>
                  <TableName>co_customer_x_related_association</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>rla_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Related Association</TableDescription>
                  <TableName>co_related_association</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>itr_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Interest</TableDescription>
                  <TableName>co_interest</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>itc_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Interest Code</TableDescription>
                  <TableName>co_interest_code</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>sta_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>State Territory</TableDescription>
                  <TableName>co_state_territory</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>rgn_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Region</TableDescription>
                  <TableName>co_region</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>vst_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date_small</DataType>
                  <TableAlias/>
                  <TableDescription>Customer Receives Member Benefits (view)</TableDescription>
                  <TableName>vw_customer_member_flag</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>phn_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Phone</TableDescription>
                  <TableName>co_phone</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>inv_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Invoice</TableDescription>
                  <TableName>ac_invoice</TableName>
               </QueryColumn>
               <QueryColumn>
                  <Name>pay_change_date</Name>
                  <Description>Change Date</Description>
                  <DataType>av_date</DataType>
                  <TableAlias/>
                  <TableDescription>Payment</TableDescription>
                  <TableName>ac_payment</TableName>
               </QueryColumn>
            </Columns>
         </GetDynamicQueryDefinitionResult>
      </GetDynamicQueryDefinitionResponse>