Select (Table) Method

This method allows you to construct a query to obtain records from the table.

Parameters

table

the singular name of the table or its alias as returned by the Describe (Database) method. Please note that this parameter is embedded into the URL before the method name.

column

the name or an alias of the column to query can appear multiple times. If omitted or star (*) is specified, API returns the data for all updateable columns. You can combine star with other column names.

filter

optional, allows you to specify filtering criteria in syntax described in Formula Language Reference.

sort

the name or an alias of the column to sort by can appear multiple times. Sort order can be specified by appending //ASC or //DESC to a column name, ascending order is the default

top

optional, a number of records to return in a range 1…500. Default is 500.

skip

number of records to skip before returning the result. This parameter can be used to organize paginated output, for example: skip=0&top=200 (page 1), skip=200&top=200 (page 2), etc.

Request

GET https://www.teamdesk.net/secure/api/v2/{appid}/{table}/select.{json|xml}?parameters

Query updatable columns from first 500 records in a Test table

GET https://www.teamdesk.net/secure/api/v2/21995/Test/select.json

Query Text, Date columns from records 5-10 in a Test table, sort by Date descending

GET https://www.teamdesk.net/secure/api/v2/21995/Test/select.json?column=Text&column=Date&sort=Date//DESC&skip=5&top=5

Query all updateable columns plus Date Modified column

GET https://www.teamdesk.net/secure/api/v2/21995/Test/select.json?column=*&column=Date%20Modified

Response (JSON)

The data is returned as the array of JavaScript objects. Each object’s field corresponds to a column queried.

• Null values reported as null.
• Checkboxes reported as true or false.
• Numeric columns are JavaScript numbers
• Durations are reported as a number of seconds.
• Text columns are strings.
• Since JavaScript has no literal form for dates we are returning dates, times and timestamps as a string in YYYY-MM-DDTHH:MM:SS[+/-]ZZ:ZZ format. The pattern is easily recognizable and can be then converted to appropriate date and time supporting object. Date columns are always reported as a midnight values with zero offset, e.g. yyyy-mm-ddT00:00:00+00:00, times are reported as 1/1/0001 dates and zero offset, e.g. 0001-01-01Thh:mm:ss+00:00. Timestamps are reported in user’s time zone with offset to UTC to allow further recalculations.
• Users are reported as strings in a form of Name <email>. The name can be then used to render the data while email uniquely identifies the user.
• Object fields starting with @row are row properties - internal ID, actions allowed to perform and the color string if row colorization formula is provided.

 [

  {
    "@row.id": 12,// internal row ID
    "@row.allow": "Edit, Delete",// comma separated allowed actions
    "Id": "60",// autonumber is string
    "Text": "Text",// text is string
    "Multiline": "Multi\r\nText",// line separators are encoded appropriately
    "Checkbox": true, // checkbox is either true or false
    "Date": "2014-11-18T00:00:00+00:00",// date is UTC midnight
    "Time": "0001-01-01T17:26:00+00:00",// time is in 1/1/0001 UTC
    "Number": 1234567,// number is ...well, number
    "Email": "kir@skyeytech.com",
    "Phone": "+12345678",
    "URL": "http://www.teamdesk.net",
    "User": "test user <test@test.com>",// name \<email>
    "Duration": 86400,// number of seconds
    "Timestamp": "2014-11-18T17:26:00-06:00", // in a local user's timezone
  }
  // , {...}
]

Response (XML)

Top level element is named <Response> and contains a set of <row> elements.

• Row properties are reported as attributes of <row> element.
• Subtags of <row> elements are column values.
• Null values reported as elements with no content and i:nil="true" attribute.
• Checkboxes, date, times, durations and null values are reported in a format appropriate for XML.
• Any XML name character that does not conform to the XML 1.0 spec (fourth edition) recommendation is escaped as xHHHH. The HHHH string stands for the four-digit hexadecimal UCS-2 code for the character in most significant bit first order. For example, the name Order Details is encoded as Order_x0020_Details. The underscore character does not need to be escaped unless it is followed by a character sequence that together with the underscore can be misinterpreted as an escape sequence when decoding the name. For example, Order_Details is not encoded, but Order_x0020_ is encoded as Order_x005f_x0020_. No short forms are allowed.

<!-- URL to the row schema is reported for conforming XML readers but not required -->
<Response>
    <row id="12" allow="Edit Delete">
        <Id>60</Id>
        <Text>Text</Text>
        <Multiline>
                 Multiline
                 Text
        </Multiline>
        <Checkbox>true</Checkbox>
        <!-- yyyy-mm-dd -->
        <Date>2014-11-18</Date>
        <!-- hh:mm:ss -->
        <Time>17:26:00</Time>
        <Number>1234567.000000</Number>
        <Email>kir@skyeytech.com</Email>
        <Phone>+12345678</Phone>
        <URL>http://www.teamdesk.net</URL>
        <User>test user &lt;test@test.com &gt;</User>
        <!-- PTsecondsS -->
        <Duration>PT86400S</Duration>
        <!-- yyyy-mm-ddThh:mm:ss+/-ZZ:ZZ -->
        <Timestamp>2014-11-18T17:26:00+02:00</Timestamp>
        <!-- Null value -->
        <Test_Null i:nil="true"/>
  </row>
  <!-- more rows -->
</Response>

Also, XML output can be used to create dynamic read-only link between the data in TeamDesk and Excel. Try Excel’s Data tab, Get External Data ribbon, From Other Sources dropdown, From XML Data Import menu and paste URL as the File Name.