Documenting BAQs

Kinetic ERP
, ,
41

This, perhaps?

2 Likes
42

Export BAQ is binary so it’s one of those things you can’t inspect diffs. However, it looks like this json schema is sufficient for both create and update.

Here’s a script that exports all BAQs. We use a BAQ called baq_BAQs_For_SCM to filter by name, author, modified date, etc to get those we want to track, then iterate over them to either export data or export design.

# Set environment variables (run this once, store outside this script, don't commit to source control)
# [System.Environment]::SetEnvironmentVariable("API_USERNAME", "YOUR_USERNAME", "User")
# [System.Environment]::SetEnvironmentVariable("API_PASSWORD", "YOUR_PASSWORD", "User")
# [System.Environment]::SetEnvironmentVariable("API_KEY", "YOU_APIKEY", "User")

# Retrieve environment variables inside this script
$Username = [System.Environment]::GetEnvironmentVariable("API_USERNAME", "User")
$Password = [System.Environment]::GetEnvironmentVariable("API_PASSWORD", "User")
$ApiKey = [System.Environment]::GetEnvironmentVariable("API_KEY", "User")
# Write-Host $Username
# Write-Host $Password
# Write-Host $ApiKey

# Define credentials
$Base64Auth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("$($Username):$($Password)"))

# Set headers
$Headers = @{
    Authorization = "Basic $Base64Auth"
    "x-api-key" = $ApiKey
    "Content-Type" = "application/json"
}

$server = "https://YOUR_SERVER.epicorsaas.com"
$instance = "YOUR_INSTANCE"
$company = "YOUR_COMPANY"
$Path = "T:\DMT\EpicorDMTScripts"  

$BAQ = "baq_BAQs_For_SCM"
$TargetFile = $BAQ + ".json"

$body = '{
  "queryID": "' + $BAQ + '",
  "executionParams": {
    "ExecutionFilter": [      
    ],
    "ExecutionParameter": []
  }
}'
# Make the request
$Response = Invoke-RestMethod "$server/$instance/api/v2/odata/$company/Ice.BO.DynamicQuerySvc/ExecuteByID" -Method 'POST' -Headers $Headers -Body $body
# Convert response to JSON format and save to file
$Response | ConvertTo-Json -Depth 10 | Out-File -FilePath $Path\$TargetFile
# Load the JSON file
$jsonContent = Get-Content -Raw -Path $Path\$TargetFile | ConvertFrom-Json

# Loop through the Results array and export QueryHdr_QueryID
$jsonContent.returnObj.Results | Where-Object {
    # $_.QueryHdr_AuthorID -Like 'jbooker'
    $_.QueryHdr_QueryID -Like 'DMT-24*'
} | Foreach-Object {
      Write-Output $_.QueryHdr_QueryID
      $BAQ =  $_.QueryHdr_QueryID
      $TargetFile = $BAQ + ".json"
      #############
      # EXPORT DATA 
      <# 
      $body = '{
        "queryID": "' + $BAQ + '",
        "executionParams": {
          "ExecutionFilter": [      
          ],
          "ExecutionParameter": []
        }
      }'
      # $Response = Invoke-RestMethod "$server/$instance/api/v2/odata/$company/Ice.BO.DynamicQuerySvc/ExecuteByID" -Method 'POST' -Headers $Headers -Body $body
      #>
      ###############
      # EXPORT DESIGN      
      $Response = Invoke-RestMethod "$server/$instance/api/v2/odata/$company/Ice.BO.DynamicQuerySvc/GetByID/?queryID=$BAQ" -Method 'POST' -Headers $Headers -Body $body
      ###############
      # Convert response to JSON format and save to file
      $Response | ConvertTo-Json -Depth 10 | Out-File -FilePath $Path\$TargetFile
  }
2 Likes