ms-access - Pipedrive:PUT从vba更新过滤器?
问题描述
我正在尝试更新 Pipedrive CRM 中的过滤器; Pipedrive API 参考
使用;
strURL = strURL & “/filters/{25}?api_token=” & strToken
'https://xx.pipedrive.com/v1/filters/{25}?api_token=xx
'Set filter
With CreateObject(“MSXML2.XMLHTTP”)
.Open “PUT”, strURL, False
.setRequestHeader “Content-Type”, “application/json”
.Send (strSql)
txt = .responseText
End With'
哪个返回;
{“status”:false,“error”:“Unknown method .”}
Pipedrive API 非常新,以前从未 PUT!
过滤器 JSON 是;
strSql = "{" & Chr(34) & "glue" & Chr(34) & ":" & Chr(34) & "and" & Chr(34) & "," & Chr(34) & "conditions" & Chr(34) & ":[{" & Chr(34) & "glue" & Chr(34) & ":" & Chr(34) _
& "and" & Chr(34) & "," & Chr(34) & "conditions" & Chr(34) & ": [{" & Chr(34) & "object" & Chr(34) & ":" & Chr(34) & "organization" & Chr(34) & "," & Chr(34) _
& "field_id" & Chr(34) & ":" & Chr(34) & "3997" & Chr(34) & "," & Chr(34) & "operator" & Chr(34) & ":" & Chr(34) & "<" & Chr(34) & "," & Chr(34) & "value" & Chr(34) _
& ":" & Chr(34) & VarCreated & Chr(34) & "},{" & Chr(34) & "object" & Chr(34) & ":" & Chr(34) & "organization" & Chr(34) & "," & Chr(34) & "field_id" & Chr(34) & ":" & Chr(34) _
& "3998" & Chr(34) & "," & Chr(34) & "operator" & Chr(34) & ":" & Chr(34) & ">" & Chr(34) & "," & Chr(34) & "value" & Chr(34) & ":" & Chr(34) & VarUpdated & Chr(34) _
& "}]},{" & Chr(34) & "glue" & Chr(34) & ":" & Chr(34) & "or" & Chr(34) & "," & Chr(34) & "conditions" & Chr(34) & ":[]}]}"
解决方案
亲爱的读者,答案是;您必须发送整个过滤器 JSON - 不仅仅是条件。我在任何地方都看不到这是记录在案的!
这是从Stackoverflow.com/questions/43493333创建/更新过滤器的语法
{“名称”:“OrgUpdated”,“类型”:“org”,“visible_to”:1,“条件”:{“胶水”:“和”,“条件”:[{“胶水”:“和”, “条件”:[{“对象”:“组织”,“field_id”:“3997”,“操作员”:“<”,“值”:“18/08/2018”,“extra_value”:空}, {“对象”:“组织”,“field_id”:“3998”,“操作员”:“>”,“值”:“18/08/2018”,“extra_value”:null } ] },{“胶水” :“或”,“条件”:[] } ] } }
在 vba 中;
'added
‘strJSON = "{ ‘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
strJSON = "{ ‘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’: [] } ] } }”
strJSON = Replace(strJSON, "'", """")
Debug.Print strJSON
请注意,Pipedrive 使用相对时间,因此日期变量来自可用列表;今天,昨天,last_week 等。
这是一组用于过滤器的函数;
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
推荐阅读
- javascript - 赛普拉斯自动化:基于域的 Javascript 执行问题
- windows - SXS: BasepCreateActCtx() BasepSxsCreateStreams() failed 运行时发生错误
- android - 应用程序在前台时不显示通知
- javascript - 如何过滤大量数组以获得唯一值?
- excel - 如果单元格匹配无效,则将粘贴行复制到多个工作表
- python - How to put multiple layout themes on 'slide_layouts' on python-pptx
- wireshark - 在 MacOS X Catalina 上安装 TShark
- javascript - 在 for 循环中处理的 JavaScript var 在循环外不会改变
- r - 如何使用 R 样本从 procsurveyselect 复制 SAS 样本?
- python - 按键时 Telnet 断开连接