Imagine you go to your boss and explain:
"I just made myself redundant. I created a pile of orchestrations and macro's in spreadsheets that does all of my mundane tasks and now I have 20 hours a week free. and guess what, I'm not making ANY mistakes!"
You'd get promoted right? RIGHT! I'd promote you.
Do you ever get a spreadsheet from someone and they say “just punch all of this into JDE”. Are you like me and when you need to do something more than 6 times (I think that this is my repetition threshold) you have a burning desire for automation? Actually, you cannot physically bring yourself to doing the task because you know you can make it easier and more rewarding...
Well, this post might help you!
Here is everything you need to create a spreadsheet that can call JDE functionality via orchestration.
First I create an orchestration that takes the following input
{
"orch_input_TimeOfReading" : "163403",
"orch_input_dateMeasurement" : "10/01/2019",
"orch_input_remarkForReading" : "Latest Temp",
"orch_input_Temperature" : "36.25",
"P1204_Version" : "",
"orch_input_szAssetNumber" : "1007"
}
The orchestration client screen look like this - I do some testing to ensure that it's recording the data in JDE in the correct place.
Nice, it's working.
So then I use postman to do some independent testing - get the exact syntax. Know the headers I need to set, get my auth correct...
Wow, postman is too cool – what about this for the docs:
It is amazing!!!
Back to excel:
My sheet looks like this, I have a single activeX button and a single field (for the obfuscated password). Wow!
My code is like this:
This is super simple and readable, this is why I did it. Also I'm no expert at vbscript'ing - so... This is the results of 1 hour of google and some testing.
Private Sub CommandButton1_Click()
Sheet1.Cells(11, 4).Value = "Processing"
Sheet1.Cells(11, 5).Value = 0
CallJDEOrchestration
End Sub
Sub CallJDEOrchestration()
Dim URL As String
Dim JSONString As String
Dim objHTTP As New WinHttpRequest
Dim stringme As String
stringme = "A" & "B" & Sheet1.Cells(10, 2).Value
Dim Username As String
Dim password As String
Dim auth As String
Username = Sheet1.Cells(1, 2).Value
password = passwordTxtBox.Value
auth = EncodeBase64(Username & ":" & password)
'MsgBox auth, vbCritical, "Hello World"
URL = "https://f5dv.mye1.com/jderest/orchestrator/orch_AddTempReadingForAsset"
objHTTP.Open "POST", URL, False
objHTTP.SetRequestHeader "Authorization", "Basic " & auth
objHTTP.SetRequestHeader "Content-Type", "application/json"
JSONString = "{""orch_input_TimeOfReading"" : """ & Sheet1.Cells(10, 2).Value & _
""",""orch_input_dateMeasurement"" : """ & Sheet1.Cells(9, 2).Value & _
""",""orch_input_remarkForReading"" : """ & Sheet1.Cells(7, 2).Value & _
""",""orch_input_Temperature"" : """ & Sheet1.Cells(8, 2).Value & _
""",""P1204_Version"" : ""ZJDE0001"",""orch_input_szAssetNumber"" : """ & Sheet1.Cells(6, 2).Value & _
"""}"
objHTTP.Send JSONString
Sheet1.Cells(11, 4).Value = objHTTP.ResponseText
Sheet1.Cells(11, 5).Value = objHTTP.Status
End Sub
Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Set objXML = New MSXML2.DOMDocument
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
You will need to ensure that your project has the following add-ins enabled (tools -> references):
You should then be able to change your URL and username and password (note that the field for the password is called passwordTxtBox)
This is using basicAuth, so that needs to be enabled on the AIS server if it’s going to work
You can find a copy here – if you want to rip it apart:
Enjoy.
