PowerBuilder

SugarCRM Filtering Records

See more SugarCRM Examples

Export records and filter. PowerBuilder

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

li_Success = 0

loo_Rest = create oleobject
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
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
li_rc = loo_SbReq.ConnectToNewObject("Chilkat.StringBuilder")

loo_JsonReq.EmitSb(loo_SbReq)

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

loo_SbJson = create oleobject
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
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