I have a UBAQ on table UD08. I have exposed this via the API the same way I would if I was going to just pull data. I want to push data back to the UBAQ, but I am having trouble with the API call. Can you confirm what is needed for the key fields when calling an update on UBAQ?
Here is the metadata for this UBAQ:
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
<edmx:DataServices>
<Schema Namespace="Epicor" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityType Name="QueryItem">
<Key>
<PropertyRef Name="RowIdent" />
</Key>
<Property Name="UD08_Company" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="Company" />
</Property>
<Property Name="UD08_Key1" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="Key1" />
</Property>
<Property Name="EmpBasic_Name" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="This is the employee's full name. This is not directly maintainable. It is a concatenation of the FirstName + MiddleInitial + LastName fields. It exists so that it can be used in browses or where ever the complete name in a first, middle, last fashion is required." />
</Property>
<Property Name="UD08_Key2" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="Key2" />
</Property>
<Property Name="UD08_Character01" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="Character01" />
</Property>
<Property Name="UD08_Number01" Type="Edm.Decimal">
<Annotation Term="Org.OData.Core.V1.Description" String="Number01" />
</Property>
<Property Name="UD08_Date01" Type="Edm.DateTimeOffset">
<Annotation Term="Org.OData.Core.V1.Description" String="Date01" />
</Property>
<Property Name="UD08_Date02" Type="Edm.DateTimeOffset">
<Annotation Term="Org.OData.Core.V1.Description" String="Date02" />
</Property>
<Property Name="UD07_Character01" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="Character01" />
</Property>
<Property Name="UD07_Number01" Type="Edm.Decimal">
<Annotation Term="Org.OData.Core.V1.Description" String="Number01" />
</Property>
<Property Name="UD07_ShortChar01" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="ShortChar01" />
</Property>
<Property Name="RowMod" Type="Edm.String">
<Annotation Term="Org.OData.Core.V1.Description" String="UBAQ specific field" />
</Property>
<Property Name="RowIdent" Type="Edm.String" Nullable="false">
<Annotation Term="Org.OData.Core.V1.Description" String="UBAQ specific field" />
</Property>
<Property Name="SysRowID" Type="Edm.Guid">
<Annotation Term="Org.OData.Core.V1.Description" String="UBAQ specific field" />
</Property>
</EntityType>
<Action Name="GetNew">
<ReturnType Type="Collection(Epicor.QueryItem)" />
</Action>
<EntityContainer Name="DynamicQuery">
<EntitySet Name="Data" EntityType="Epicor.QueryItem" />
<ActionImport Name="GetNew" Action="Epicor.GetNew" />
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>
At first I was trying to use Company, and Key1, Key2 as the keys for the update, passing something like:
Right, I am passing the valid API key for sure. Which is why I am back at the syntax of the update call. I use the same API key for lots of calls and it works fine for all get requests. I dont see any update restrictions on the API key or the Access Scope.
Public Sub UpdateEpicorBAQ(RowIdent As String, QualRating As Variant)
Dim Http As Object
Set Http = CreateObject("MSXML2.XMLHTTP")
Dim url As String
url = "https://MYSERVER/api/v2/odata/VTAERO/BaqSvc/EmpQuals(guid'" & RowIdent & "')"
Dim ratingValue As String
If IsError(QualRating) Then
MsgBox "Invalid value in QualRating: error value", vbExclamation
Exit Sub
ElseIf IsNumeric(QualRating) Then
ratingValue = QualRating
ElseIf IsEmpty(QualRating) Or IsNull(QualRating) Then
ratingValue = "null" ' send null to Epicor
Else
MsgBox "Invalid value in QualRating: " & CStr(QualRating), vbExclamation
Exit Sub
End If
Dim json As String
json = "{""UD08_Number01"": " & ratingValue & "}"
Dim ApiKey As String
ApiKey = "MYAPIKEY"
Http.Open "PATCH", url, False
Http.setRequestHeader "Content-Type", "application/json"
Http.setRequestHeader "Authorization", "EpicorApiKey " & ApiKey
Http.Send json
Debug.Print Http.Status
Debug.Print Http.responseText
If Http.Status >= 200 And Http.Status < 300 Then
Debug.Print "Updated successfully: " & RowIdent
Else
MsgBox "Error updating Epicor: " & Http.responseText, vbCritical
End If
End Sub
Public Sub UpdateEpicorBAQ(RowIdent As String, QualRating As Variant)
Dim Http As Object
Set Http = CreateObject("MSXML2.XMLHTTP")
Dim url As String
url = "https://MYSERVER/api/v2/odata/VTAERO/BaqSvc/EmpQuals(guid'" & RowIdent & "')"
Dim ratingValue As String
If IsError(QualRating) Then
MsgBox "Invalid value in QualRating: error value", vbExclamation
Exit Sub
ElseIf IsNumeric(QualRating) Then
ratingValue = QualRating
ElseIf IsEmpty(QualRating) Or IsNull(QualRating) Then
ratingValue = "null"
Else
MsgBox "Invalid value in QualRating: " & CStr(QualRating), vbExclamation
Exit Sub
End If
Dim json As String
json = "{""UD08_Number01"": " & ratingValue & "}"
Http.Open "PATCH", url, False
Http.setRequestHeader "Content-Type", "application/json"
Dim credentials As String
credentials = "MYUSER:MYPASS"
Http.setRequestHeader "Authorization", "Basic " & Base64Encode(credentials)
Http.setRequestHeader "X-API-Key", "MYPAIKEY"
Http.Send json
If Http.Status >= 200 And Http.Status < 300 Then
Debug.Print "Updated successfully: " & RowIdent
Else
MsgBox "Error updating Epicor: " & Http.Status & " - " & Http.responseText, vbCritical
End If
End Sub
I am getting a 405 error and no status text to explain it.
As long as there’s a REST method, if it can HTTP GET or POST or whatever it can use the REST method. VBA gets a lot of disrespect but it’s bonkers how powerful it is if you’re not averse to using it.
Now, if you need to parse the JSON results in VBA that’ll be an adventure. Which I’ve done before and have notes (yay, git!) if they’d be helpful.
I can’t get any POST or PATCH to work in Postman. Has anyone had luck with UBAQ updates via the API?
EDIT: I keep getting 405 Method not allowed on POST and PATCH.
The PATCH in my REST helper, shows a differnt color than the other methods, and when I open it, it shows it calls the updatable BAQ “testUbaq”. All the other methods reference my UBAQ EmpQuals. I am guessing PATCH/POST is not supported yet?
I got it in postman! I was missing the /Data in my URL.. Uggh..
This is my PATCH URL:
//MYSERVER/api/v2/odata/VTAERO/BaqSvc/EmpQuals/Data
And I sent the whole kit of data that PATCH showed in REST helper. I made a couple of edits, like changing rowmod to U, and editing the Number01 value.
{
"UD08_Company": "VTAERO",
"UD08_Key1": "1148",
"EmpBasic_Name": "Nathan Schoonover",
"UD08_Key2": "HorizMill",
"UD08_Character01": "nate took this course",
"UD08_Number01": 1,
"UD08_Date01": "2025-09-15T00:00:00-05:00",
"UD08_Date02": "2026-09-15T00:00:00-05:00",
"UD07_Character01": "Learn how to run the horizontal mill.",
"UD07_Number01": 12,
"UD07_ShortChar01": "Horizontal Mill",
"RowMod": "U",
"RowIdent": "9d3723a1-eaca-47d1-b90f-454867147159",
"SysRowID": "9d3723a1-eaca-47d1-b90f-454867147159"
}
So yea, I got this working. In the end I had to use PATCH and I had to pass the whole chunk of data above. I had to perform a separate lookup to get the rowident, and if it isn’t there, then I set rowmod to A. Thanks for the pointers @klincecum and @kananga!
