Use Relational Table criteria within the Create Query API call to pull a range of data from a Relational Table.

Overview

Criteria type and example calls that use Relational Table criteria.

Relational Table Criteria

Operator

Column Name

Values

is blank

first name

 

is not blank

first name

 

is between

age

10 and 20

is equal to

age

10

is not equal to

age

10

is equal to one of the following

zipcode

30052, 30033

is not equal to one of the following

zipcode

30052, 30033

is within the last range of

anniversary

5 and 10 days

is within the next

anniversary

10 days

is not within the last

anniversary

5 days

Example Call Number 1

Using Relational Table Criteria

<Envelope>
  <Body>
    <CreateQuery>
      <PARENT_LIST_ID>348328</PARENT_LIST_ID>
      <QUERY_NAME>xli_test_create_Q7</QUERY_NAME>
      <VISIBILITY>1</VISIBILITY>
      <CRITERIA>
        <EXPRESSION criteria_type="rt_profile">
          <CONJUNCTION>AND</CONJUNCTION>
          <ID>348449</ID>
          <OPERATOR>match</OPERATOR>
          <RT_EXPRESSIONS>
            <EXPRESSION criteria_type="rt_profile">
              <COLUMN>age</COLUMN>
              <CONJUNCTION>AND</CONJUNCTION>
              <OPERATOR>is blank</OPERATOR>
            </EXPRESSION>
          </RT_EXPRESSIONS>
        </EXPRESSION>
      </CRITERIA>
    </CreateQuery>
  </Body>
</Envelope>

This criteria creates Editable Criteria:

WHEN

In Relational Table: testRt1

WHEN age is blank

Example Call Number 2

<Envelope>
  <Body>
    <CreateQuery>
      <QUERY_NAME>Females who purchased single item greater than 100</QUERY_NAME>
      <PARENT_LIST_ID>2740754</PARENT_LIST_ID>
      <VISIBILITY>1</VISIBILITY>
      <CRITERIA>
        <EXPRESSION criteria_type="profile">
          <CONJUNCTION>AND</CONJUNCTION>
          <PARENS>(</PARENS>
          <OPERATOR>is equal to</OPERATOR>
          <COLUMN>Gender</COLUMN>
          <VALUE>
            <![CDATA[Female]]>
          </VALUE>
        </EXPRESSION>
        <EXPRESSION criteria_type="profile">
          <CONJUNCTION>AND</CONJUNCTION>
          <OPERATOR>is equal to one of the following</OPERATOR>
          <COLUMN>Zip Code</COLUMN>
          <VALUES>
            <VALUE>
              <![CDATA[30318]]>
            </VALUE>
            <VALUE>
              <![CDATA[30306]]>
            </VALUE>
            <VALUE>
              <![CDATA[30308]]>
            </VALUE>
            <VALUE>
              <![CDATA[30314]]>
            </VALUE>
          </VALUES>
          <PARENS>)</PARENS>
        </EXPRESSION>
        <EXPRESSION criteria_type="relational_table">
          <CONJUNCTION>AND</CONJUNCTION>
          <OPERATOR>match</OPERATOR>
          <ID>9864803</ID>
          <RT_EXPRESSIONS>
            <EXPRESSION criteria_type="rt_profile">
              <CONJUNCTION>AND</CONJUNCTION>
              <OPERATOR>is today</OPERATOR>
              <COLUMN>Date Purchased</COLUMN>
            </EXPRESSION>
            <EXPRESSION criteria_type="rt_profile">
              <CONJUNCTION>AND</CONJUNCTION>
              <OPERATOR>is greater than</OPERATOR>
              <COLUMN>Item Price</COLUMN>
              <VALUE>
                <![CDATA[100.00]]>
              </VALUE>
            </EXPRESSION>
          </RT_EXPRESSIONS>
        </EXPRESSION>
      </CRITERIA>
    </CreateQuery>
  </Body>
</Envelope>

This would result in a Database Query containing profile criteria and RT criteria

WHEN (Gender is equal to Female
AND Zip Code is equal to one of the following (30318 | 30306 | 30308 | 30314))
AND
Relational Table: Orders
Date Purchased is today
AND Item Price is greater than 100.00

When Will this Call Fail?

This call fails when:

  • The Call is malformed.
  • Missing required elements such as the Visibility field.
  • Query name is already in use.

Example Error Malformed Call and Response

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q8</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION
        criteria_type="relational_table">
      <CONJUNCTION>AND</CONJUNCTION>
      <ID>348449</ID>
      <OPERATOR>not match</OPERATOR>
      <RT_EXPRESSIONS>
      <EXPRESSION criteria_type="rt_profile">
      <COLUMN>age</COLUMN>
      <CONJUNCTION>AND</CONJUNCTION>
      <OPERATOR>is blank</OPERATOR>
      <EXPRESSION>
        </RT_EXPRESSION>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Response

<Envelope>
  <Body>
    <RESULT>
      <SUCCESS>false</SUCCESS>
    </RESULT>
    <Fault>
      <Request/>
      <FaultCode/>
      <FaultString>Invalid XML Request</FaultString>
      <detail>
        <error>
          <errorid>51</errorid>
          <module/>
          <class>SP.API</class>
          <method/>
        </error>
      </detail>
    </Fault>
  </Body>
</Envelope>

Example Error Response: Missing Visible Field

<Envelope>
  <Body>
    <RESULT>
      <SUCCESS>false</SUCCESS>
    </RESULT>
    <Fault>
      <Request/>
      <FaultCode/>
      <FaultString>
        <! [CDATA[ Parameter VISIBILITY was not provided in API call ]]>
      </FaultString>
      <detail>
        <error>
          <errorid>100</errorid>
          <module/>
          <class>SP.API</class>
          <method/>
        </error>
      </detail>
    </Fault>
  </Body>
</Envelope>

Example Error Message for Query name that exists

<Envelope>
  <Body>
    <RESULT>
      <SUCCESS>false</SUCCESS>
    </RESULT>
    <Fault>
      <Request/>
      <FaultCode/>
      <FaultString>
        <! [CDATA[ A List With This Name (xli_test_create_Q1 Already Exists. ]]>
      </FaultString>
      <detail>
        <error>
          <errorid>256</errorid>
          <module/>
          <class>SP.ListManager</class>
          <method/>
        </error>
      </detail>
    </Fault>
  </Body>
</Envelope>

Join The Discussion

Your email address will not be published. Required fields are marked *