Documenting BAQs

Kinetic ERP
, ,
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