Update filter from vba?

Hi, I’m using vba in Access to update a filter - changing the date; the PUT returns {“status”:false,“error”:“Unknown method .”}

Filter json works;
{“glue”:“and”,“conditions”:[{“glue”:“and”,“conditions”: [{“object”:“organization”,“field_id”:“3997”,“operator”:"<",“value”:“2018-06-25 12:00:00”},{“object”:“organization”,“field_id”:“3998”,“operator”:">",“value”:“2018-06-25 12:00:00”}]},{“glue”:“or”,“conditions”:[]}]}

strURL = https://MyCompany.pipedrive.com/v1/filters/{25}?api_token=MyToken

'Set filter
With CreateObject(“MSXML2.XMLHTTP”)
.Open “PUT”, strURL, False
.setRequestHeader “Content-Type”, “application/json”
.Send (strSql)
txt = .responseText
End With

The answer Dear Reader is; You have to send the whole filter JSON - Not just the conditions.
Nowhere I can see is this documented!

This is the syntax to Create/Update a Filter from Stackoverflow.com/questions/43493333

{ “name”:“OrgUpdated”, “type”:“org”, “visible_to”:1, “conditions”:{ “glue”: “and”, “conditions”:[ { “glue”: “and”, “conditions”: [ { “object”: “organization”, “field_id”: “3997”, “operator”: “<”, “value”: “18/08/2018”, “extra_value”: null },{ “object”: “organization”, “field_id”: “3998”, “operator”: “>”, “value”: “18/08/2018”, “extra_value”: null } ] }, { “glue”: “or”,“conditions”: [] } ] } }

In vba;

'added + updated

'added
‘strSql = "{ ‘name’:‘OrgCreated’, ‘type’:‘org’, ‘visible_to’:1, ‘conditions’:{ ‘glue’: ‘and’, ‘conditions’:[ { ‘glue’: ‘and’, ‘conditions’: [ { " _
’ & “‘object’: ‘organization’, ‘field_id’: ‘3997’, ‘operator’: ‘>’, ‘value’: '” & dteCreated & "’, ‘extra_value’: null } ] }, { ‘glue’: ‘or’," _
’ & “‘conditions’: [] } ] } }”

‘Updated
strSql = "{ ‘name’:‘OrgUpdated’, ‘type’:‘org’, ‘visible_to’:1, ‘conditions’:{ ‘glue’: ‘and’, ‘conditions’:[ { ‘glue’: ‘and’, ‘conditions’: [ " _
& “{ ‘object’: ‘organization’, ‘field_id’: ‘3997’, ‘operator’: ‘<’, ‘value’: '” & dteCreated & "’, ‘extra_value’: null }," _
& “{ ‘object’: ‘organization’, ‘field_id’: ‘3998’, ‘operator’: ‘>’, ‘value’: '” & dteUpdated & “’, ‘extra_value’: null } ] }, { ‘glue’: ‘or’,” _
& “‘conditions’: [] } ] } }”

Here is a set of functions to do Filters with;

Public Function CreateFilter(FilterJSON As String) As String
’ Returns Filter ID if created or error if not
Dim strURL As String, strToken As String, txt As String
Dim strSql As String, s As String
Dim i As Long, j As Long

strURL = DLookup("[URL]", "tblCRMDetails")
strToken = DLookup("[Token]", "tblCRMDetails")

strURL = strURL & "/filters?api_token=" & strToken

’ Debug.Print strURL

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "POST", URL, False
    .SetRequestHeader "Accept", "application/json"
    .SetRequestHeader "Content-Type", "application/json"
    .Send (FilterJSON)
    txt = .ResponseText
End With

’ Debug.Print txt

CreateFilter = txt
    
i = InStr(txt, "id")
If i > 0 Then
    s = "0"
    j = 0
    Do Until Not IsNumeric(s)
        j = j + 1
        s = Mid(txt, i + 3 + j, 1)
    Loop
    s = Mid(txt, i + 4, j - 1)
    CreateFilter = s
End If

End Function

Public Function DeleteFilter(ID As Long) As Boolean
’ Returns True if Filter Deleted
Dim strURL As String, strToken As String, txt As String
Dim strSql As String, s As String
Dim i As Long, j As Long

strURL = DLookup("[URL]", "tblCRMDetails")
strToken = DLookup("[Token]", "tblCRMDetails")

strURL = strURL & "/filters/" & ID & "?api_token=" & strToken

With CreateObject("WinHttp.WinHttpRequest.5.1") 'WinHttp.WinHttpRequest.5.1
    .Open "DELETE", URL, False
    .SetRequestHeader "Accept", "application/json"
    .SetRequestHeader "Content-Type", "application/json"
    .Send
    txt = .ResponseText
End With

’ Debug.Print txt

    'check success
If InStr(txt, "success"":true") > 0 Then
    DeleteFilter = True
End If

End Function

Public Function UpdateFilter(ID As Long, FilterJSON As String) As String
’ Returns Filter ID if created or error if not
’ txt = UpdateFilter(40, strSql)
Dim strURL As String, strToken As String, txt As String
Dim strSql As String, s As String
Dim i As Long, j As Long

strURL = DLookup("[URL]", "tblCRMDetails")
strToken = DLookup("[Token]", "tblCRMDetails")

strURL = strURL & "/filters/" & ID & "?api_token=" & strToken

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "PUT", strURL, False
    .SetRequestHeader "Accept", "application/json"
    .SetRequestHeader "Content-Type", "application/json"
    .Send (FilterJSON)
    txt = .ResponseText
End With
Debug.Print txt

UpdateFilter = txt
    
i = InStr(txt, "id")
If i > 0 Then
    s = "0"
    j = 0
    Do Until Not IsNumeric(s)
        j = j + 1
        s = Mid(txt, i + 3 + j, 1)
    Loop
    s = Mid(txt, i + 4, j - 1)
    UpdateFilter = s
End If

End Function

@David

Note that the JSON entered here /Filters/put_filters_id is different to the JSON to Create/Update filter from API.
Entering the full JSON gives;
image

Required for webform;
{“glue”:“and”,“conditions”:[{“glue”:“and”,“conditions”: [{“object”:“organization”,“field_id”:“4002”,“operator”:"=",“value”:17594}]},{“glue”:“or”,“conditions”:[]}]}

Required for API;
{ “name”:“OrgCreated”, “type”:“org”, “visible_to”:1, “conditions”:{ “glue”: “and”, “conditions”:[ { “glue”: “and”, “conditions”: [ { “object”: “organization”, “field_id”: “3997”, “operator”: “>”, “value”: “1_week_ago”, “extra_value”: null } ] }, { “glue”: “or”,“conditions”: [] } ] } }

Thanks for pointing this out.
I’m looking into this further to see if we can get this updated.

Would you be able to add some more clarity to what you mean by “Entering the full JSON”?

I mean ‘full JSON’ = as required by API, there are more fields in it.

Required for API;
{ “name”:“OrgCreated”, “type”:“org”, “visible_to”:1, “conditions”:{ “glue”: “and”, “conditions”:[ { “glue”: “and”, “conditions”: [ { “object”: “organization”, “field_id”: “3997”, “operator”: “>”, “value”: “1_week_ago”, “extra_value”: null } ] }, { “glue”: “or”,“conditions”: [] } ] } }

Required for webform;
{“glue”:“and”,“conditions”:[{“glue”:“and”,“conditions”: [{“object”:“organization”,“field_id”:“4002”,“operator”:"=",“value”:17594}]},{“glue”:“or”,“conditions”:[]}]}

It would be good if you could test the filter in webform and copy straight to application to send to API?

From doing testing, I’m not finding the same results.
Created filter with POST payload, filter was created correctly and updated filter with PUT payload, change was handled correctly.

{
  "conditions": {
    "glue": "and",
    "conditions": [{
      "glue": "or",
      "conditions": [{
        "object": "deal",
        "field_id": "12443",
        "operator": "!=",
        "value": "deleted"
      }, {
        "object": "deal",
        "field_id": "12443",
        "operator": "!=",
        "value": "Open"
      }]
    }]
  }
}

The same structure as defined in API reference, so I don’t see what needs to be fixed?

in PUT, fields can be omitted, so you can only include “conditions” if you did not want to change other fields

Yes, I have it now, thanks for your assistance.
I have much more experience with filtering after working with it for a while, my apologies.

Could you please provide an example of using and & or together?

{“success”:true,“data”:{“id”:42,“name”:“DealUpdated”,“active_flag”:true,“type”:“deals”,“temporary_flag”:null,“user_id”:719076,“add_time”:“2018-08-24 05:29:03”,“update_time”:“2018-08-29 10:47:42”,“visible_to”:“1”,“custom_view_id”:null,“conditions”:{“glue”:“and”,“conditions”:[{“glue”:“and”,“conditions”:[]},{“glue”:“or”,“conditions”:[{“object”:“deal”,“field_id”:“12461”,“operator”:"!=",“value”:“deleted”,“extra_value”:null},{“object”:“deal”,“field_id”:“12461”,“operator”:"!=",“value”:“Open”,“extra_value”:null}]}]}}}