(PowerBuilder) SugarCRM Filtering Records

Export records and filter.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

integer li_rc
oleobject loo_Rest
integer li_Success
oleobject loo_JsonReq
oleobject loo_SbReq
oleobject loo_SbJson
oleobject loo_Json
integer li_Next_offset
integer i
integer li_Count_i
string ls_Id
string ls_Date_modified
string ls_V_module

loo_Rest = create oleobject
// Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
li_rc = loo_Rest.ConnectToNewObject("Chilkat.Rest")
if li_rc < 0 then
    destroy loo_Rest
    MessageBox("Error","Connecting to COM object failed")
    return
end if

li_Success = loo_Rest.Connect("your.site.domain",443,1,1)
if li_Success <> 1 then
    Write-Debug loo_Rest.LastErrorText
    destroy loo_Rest
    return
end if

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

// The following code creates the JSON request body.
// The JSON created by this code is shown below.
loo_JsonReq = create oleobject
// Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
li_rc = loo_JsonReq.ConnectToNewObject("Chilkat.JsonObject")

loo_JsonReq.UpdateString("filter[0].$or[0].name.$starts","A")
loo_JsonReq.UpdateString("filter[0].$or[1].name.$starts","b")
loo_JsonReq.UpdateNumber("max_num","2")
loo_JsonReq.UpdateNumber("offset","0")
loo_JsonReq.UpdateString("fields","id")
loo_JsonReq.UpdateString("order_by","date_entered")
loo_JsonReq.UpdateBool("favorites",0)
loo_JsonReq.UpdateBool("my_items",0)

// 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
// }

loo_SbReq = create oleobject
// Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
li_rc = loo_SbReq.ConnectToNewObject("Chilkat.StringBuilder")

loo_JsonReq.EmitSb(loo_SbReq)

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

loo_SbJson = create oleobject
// Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
li_rc = loo_SbJson.ConnectToNewObject("Chilkat.StringBuilder")

li_Success = loo_Rest.FullRequestSb("POST","/rest/v10/Accounts/filter",loo_SbReq,loo_SbJson)
if li_Success <> 1 then
    Write-Debug loo_Rest.LastErrorText
    destroy loo_Rest
    destroy loo_JsonReq
    destroy loo_SbReq
    destroy loo_SbJson
    return
end if

if loo_Rest.ResponseStatusCode <> 200 then
    Write-Debug "Received error response code: " + string(loo_Rest.ResponseStatusCode)
    Write-Debug "Response body:"
    Write-Debug loo_SbJson.GetAsString()
    destroy loo_Rest
    destroy loo_JsonReq
    destroy loo_SbReq
    destroy loo_SbJson
    return
end if

loo_Json = create oleobject
// Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
li_rc = loo_Json.ConnectToNewObject("Chilkat.JsonObject")

loo_Json.LoadSb(loo_SbJson)

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

li_Next_offset = loo_Json.IntOf("next_offset")
i = 0
li_Count_i = loo_Json.SizeOfArray("records")
do while i < li_Count_i
    loo_Json.I = i
    ls_Id = loo_Json.StringOf("records[i].id")
    ls_Date_modified = loo_Json.StringOf("records[i].date_modified")
    ls_V_module = loo_Json.StringOf("records[i]._module")
    i = i + 1
loop

// 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"
//     }
//   ]
// }

Write-Debug "Example Completed."


destroy loo_Rest
destroy loo_JsonReq
destroy loo_SbReq
destroy loo_SbJson
destroy loo_Json