JDE call an orchestration from excel - RPA it yourself

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"

}

I'm not going to cover off how to create an orchestration, there is a lot of content out there.  It's easy and "point and clicky" and aimed at the functional person.  Hey, us techos are not going to be needed soon. 

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:

https://documenter.getpostman.com/view/4738496/SVtYS71e?version=latest

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:

https://drive.google.com/open?id=119aEP8BAkTlthYCWbZcNEVf93j9KUXil

 You could do some pretty amazing and complex processing in JDE directly from excel.  And... you won't have the haters saying "but you need to do that in JDE", because you actually did.

Enjoy.