(PowerShell) SugarCRM Filtering Records

Export records and filter.

Chilkat .NET Downloads Chilkat .NET Assemblies

Add-Type -Path "C:\chilkat\ChilkatDotNet47-9.5.0-x64\ChilkatDotNet47.dll"

$rest = New-Object Chilkat.Rest

$success = $rest.Connect("your.site.domain",443,$true,$true)
if ($success -ne $true) {
    $($rest.LastErrorText)
    exit
}

$rest.AddHeader("Cache-Control","no-cache")
$rest.AddHeader("OAuth-Token","<access_token>")

# The following code creates the JSON request body.
# The JSON created by this code is shown below.
$jsonReq = New-Object Chilkat.JsonObject
$jsonReq.UpdateString("filter[0].$or[0].name.$starts","A")
$jsonReq.UpdateString("filter[0].$or[1].name.$starts","b")
$jsonReq.UpdateNumber("max_num","2")
$jsonReq.UpdateNumber("offset","0")
$jsonReq.UpdateString("fields","id")
$jsonReq.UpdateString("order_by","date_entered")
$jsonReq.UpdateBool("favorites",$false)
$jsonReq.UpdateBool("my_items",$false)

# The JSON request body created by the above code:

# {
#   "filter": [
#     {
#       "$or": [
#         {
#           "name": {
#             "$starts": "A"
#           }
#         },
#         {
#           "name": {
#             "$starts": "b"
#           }
#         }
#       ]
#     }
#   ],
#   "max_num": 2,
#   "offset": 0,
#   "fields": "id",
#   "order_by": "date_entered",
#   "favorites": false,
#   "my_items": false
# }

$sbReq = New-Object Chilkat.StringBuilder
$jsonReq.EmitSb($sbReq)

$rest.AddHeader("Content-Type","application/json")

$sbJson = New-Object Chilkat.StringBuilder
$success = $rest.FullRequestSb("POST","/rest/v10/Accounts/filter",$sbReq,$sbJson)
if ($success -ne $true) {
    $($rest.LastErrorText)
    exit
}

if ($rest.ResponseStatusCode -ne 200) {
    $("Received error response code: " + $rest.ResponseStatusCode)
    $("Response body:")
    $($sbJson.GetAsString())
    exit
}

$json = New-Object Chilkat.JsonObject
$json.LoadSb($sbJson)

# The following code parses the JSON response.
# A sample JSON response is shown below the sample code.

$next_offset = $json.IntOf("next_offset")
$i = 0
$count_i = $json.SizeOfArray("records")
while ($i -lt $count_i) {
    $json.I = $i
    $id = $json.StringOf("records[i].id")
    $date_modified = $json.StringOf("records[i].date_modified")
    $v_module = $json.StringOf("records[i]._module")
    $i = $i + 1
}

# A sample JSON response body that is parsed by the above code:

# {
#   "next_offset": 2,
#   "records": [
#     {
#       "id": "f16760a4-3a52-f77d-1522-5703ca28925f",
#       "date_modified": "2016-04-05T10:23:28-04:00",
#       "_acl": {
#         "fields": {}
#       },
#       "_module": "Accounts"
#     },
#     {
#       "id": "ec409fbb-2b22-4f32-7fa1-5703caf78dc3",
#       "date_modified": "2016-04-05T10:23:28-04:00",
#       "_acl": {
#         "fields": {}
#       },
#       "_module": "Accounts"
#     }
#   ]
# }

$("Example Completed.")