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
