Making Autotask API queries from Powershell easier

When you have connected powershell to Autotask you will need to write your queries in QueryXML. To make this a bit easier on myself I use this powershell function:

Update: Added functionality to make a query for User Defined Fields (UDF).

# A small QueryXML function I got from Jon Czerwinski in an autotask forum
# https://community.autotask.com/forums/p/15090/38343.aspx#38343
# I have modified it to handle more fields and Expressions
# The most recent addition is to handle queries for User Defined Fields (UDF)

function New-ATWSQuery {
    [CmdletBinding()]
    param(
        [Parameter(Position=0,Mandatory=$true)]
        [string]$Entity,
        [Parameter(Position=1,Mandatory=$true)]
        [string]$Field,
        [Parameter(Position=2,Mandatory=$false)]
        [string]$Expression,
        [Parameter(Position=3,Mandatory=$false)]
        [string]$Value,
        [Parameter(Position=4,Mandatory=$false)]
        [string]$Condition2,
        [Parameter(Position=5,Mandatory=$false)]
        [string]$Field2,
        [Parameter(Position=6,Mandatory=$false)]
        [string]$Expression2,
        [Parameter(Position=7,Mandatory=$false)]
        [string]$Value2,
        [Parameter(Position=8,Mandatory=$false)]
        [string]$Condition3,
        [Parameter(Position=9,Mandatory=$false)]
        [string]$Field3,
        [Parameter(Position=10,Mandatory=$false)]
        [string]$Expression3,
        [Parameter(Position=11,Mandatory=$false)]
        [string]$Value3,
        [Parameter(Position=12,Mandatory=$false)]
        [string]$Condition4,
        [Parameter(Position=13,Mandatory=$false)]
        [string]$Field4,
        [Parameter(Position=14,Mandatory=$false)]
        [string]$Expression4,
        [Parameter(Position=15,Mandatory=$false)]
        [string]$Value4,
        [Parameter(Position=16,Mandatory=$false)]
        [string]$Condition5,
        [Parameter(Position=17,Mandatory=$false)]
        [string]$Field5,
        [Parameter(Position=18,Mandatory=$false)]
        [string]$Expression5,
        [Parameter(Position=19,Mandatory=$false)]
        [string]$Value5,
        [Parameter(Mandatory=$false)]
        [switch]$UDF

    )
    If ($UDF)
    {
        $query = '<queryxml><entity>$Entity</entity><query><condition><field udf=''true''>$Field<expression op=''$Expression''>$Value</expression></field></condition>'
    }
    Else
    {
        $query = '<queryxml><entity>$Entity</entity><query><condition><field>$Field<expression op=''$Expression''>$Value</expression></field></condition>'
    }

    If (!($Field2 -eq ''))  {
        $query= '$query <condition operator=''$Condition2''><field>$Field2<expression op=''$Expression2''>$Value2</expression></field></condition>'
    }

    If (!($Field3 -eq ''))  {
        $query= '$query <condition operator=''$Condition3''><field>$Field3<expression op=''$Expression3''>$Value3</expression></field></condition>'
    } 

    If (!($Field4 -eq ''))  {
        $query= '$query <condition operator=''$Condition4''><field>$Field4<expression op=''$Expression4''>$Value4</expression></field></condition>'
    }

    If (!($Field5 -eq ''))  {
        $query= '$query <condition operator=''$Condition5''><field>$Field5<expression op=''$Expression5''>$Value5</expression></field></condition>'
    } 

    $query = '$query </query></queryxml>'

    $query

}

Now you may write an Autotask query like this:

# To get all active (status = 1) Time &amp; Materials contracts (ContractType = 1)
$contractQuery = New-ATWSQuery 'Contract' 'ContractType' 'Equals' '1' 'Status' 'Equals' '1'
$contracts = $atws.query($contractQuery)
Foreach ($contract in $contracts.EntityResults)
{
    $contract.ContractName
}

I really recommend using a powershell GUI such as PowerGUI (free) or Powershell Studio (free trial) when you work with Autotask Web Services API. If you do, you will be able to inspect the result of your queries as objects. It will save you a lot of work and frustration. You should also have the Autotask Web Services API documentation available at all times. It lists available entities with their properties.