Formula for DigEplan markup data pull

In the Workflow Manager in Operations and Regulations, add a formula to the BeforeUpdate event of the CDR.Planning.PlanningApplication object.

' ===================================================================
' Formula: Fetch DigEplan Markups - Single Item
' Purpose: Call DigEplan API and store one markup in Comments
' Event: PlanningApplication BeforeUpdate
' Note: Uses Response Mapping fixed index - only returns one item
' Customize: Service Profile name, folder ID format, field names
' ===================================================================
' STEP 1: Load the Service Profile
' The Service Profile contains the API URL, authentication headers, and response mapping
dim profile as Hansen.Core.Net.IServiceProfile = ServerApplication.NewComponent(of Hansen.Core.Net.IServiceProfile)
profile.ServiceProfileName = "DigEplanMarkups"
dim loadResult as Result = profile.LoadByID()
if not loadResult.IsSuccess or profile.ServiceProfileKey <= 0 then
    return new Result(0, ResultSeverity.Error, "Service profile 'DigEplanMarkups' not found")
end if
' STEP 2: Build the DigEplan Folder ID
' Format: {ApplicationKey}~PLANNING (e.g., "2752~PLANNING")
dim folderId as string = oPlanningApplication.PlanningApplicationKey.ToString() + "~PLANNING"
' STEP 3: Create data source - replaces {{folderId}} in Service Profile Contents
dim mappings as IDictionary(of String, Object) = New Dictionary(of String, Object)()
mappings.Add("folderId", folderId)
dim ds as Hansen.Core.Net.IWebRequestDataSource = Hansen.Core.Net.WebRequestDataSource.Create(mappings)
' STEP 4: Call the DigEplan API via Service Profile
dim context as Hansen.Core.Net.IServiceProfileContext = Hansen.Core.Net.NetService.Send(profile.WebRequestConfig, ds)
' STEP 5: Handle API errors
if context.Response.StatusCode <> 200 then
    return new Result(0, ResultSeverity.Error, "API failed. Code: " + context.Response.StatusCode.ToString())
end if
' STEP 6: Extract values from Response Mapping
' Gets ONE item - values come from the fixed JSONPath index in Service Profile Response Mapping
' e.g., $.items[0].custom.AreaUsageType → AreaUsageType
dim areaUsageType as string = ""
dim areaUsageTax as string = ""
dim areaUsageTaxCalc as string = ""
if context.ResponseEntries IsNot Nothing then
    areaUsageType = context.ResponseEntries.GetValue("AreaUsageType")
    areaUsageTax = context.ResponseEntries.GetValue("AreaUsageTax")
    areaUsageTaxCalc = context.ResponseEntries.GetValue("AreaUsageTaxCalc")
end if
' STEP 7: Store in Comments field
' No Update() needed - BeforeUpdate persists this with the save operation
oPlanningApplication.Comments = "Type=" + areaUsageType + ", Tax=" + areaUsageTax + ", Calc=" + areaUsageTaxCalc
return new Result(1, ResultSeverity.Success, "Done. Type=" + areaUsageType + ", Tax=" + areaUsageTax + ", Calc=" + areaUsageTaxCalc)

' ===================================================================
' Formula: Fetch DigEplan Markups - All Items
' Purpose: Call DigEplan API and store all markups in Comments
' Event: PlanningApplication BeforeUpdate
' Note: Parses raw JSON - handles multiple markup items per folder
' Customize: Service Profile name, folder ID format, field names
' ===================================================================
' STEP 1: Load the Service Profile
' The Service Profile contains the API URL, authentication headers, and response mapping
dim profile as Hansen.Core.Net.IServiceProfile = ServerApplication.NewComponent(of Hansen.Core.Net.IServiceProfile)
profile.ServiceProfileName = "DigEplanMarkups"
dim loadResult as Result = profile.LoadByID()
if not loadResult.IsSuccess or profile.ServiceProfileKey <= 0 then
    return new Result(0, ResultSeverity.Error, "Service profile 'DigEplanMarkups' not found")
end if
' STEP 2: Build the DigEplan Folder ID
' Format: {ApplicationKey}~PLANNING (e.g., "2752~PLANNING")
dim folderId as string = oPlanningApplication.PlanningApplicationKey.ToString() + "~PLANNING"
' STEP 3: Create data source - replaces {{folderId}} in Service Profile Contents
dim mappings as IDictionary(of String, Object) = New Dictionary(of String, Object)()
mappings.Add("folderId", folderId)
dim ds as Hansen.Core.Net.IWebRequestDataSource = Hansen.Core.Net.WebRequestDataSource.Create(mappings)
' STEP 4: Call the DigEplan API via Service Profile
dim context as Hansen.Core.Net.IServiceProfileContext = Hansen.Core.Net.NetService.Send(profile.WebRequestConfig, ds)
' STEP 5: Handle API errors
if context.Response.StatusCode = 401 then
    return new Result(0, ResultSeverity.Error, "Authentication failed. Check X-PAT-TOKEN header.")
elseif context.Response.StatusCode = 404 then
    return new Result(0, ResultSeverity.Error, "Folder not found in DigEplan: " + folderId)
elseif context.Response.StatusCode = 500 then
    return new Result(0, ResultSeverity.Error, "DigEplan server error. Contact DigEplan support.")
elseif context.Response.StatusCode <> 200 then
    return new Result(0, ResultSeverity.Error, "API call failed. Code: " + context.Response.StatusCode.ToString())
end if
' STEP 6: Parse raw JSON to extract ALL markup items
' Unlike Formula A which uses Response Mapping (fixed index), this loops through
' the entire JSON response and finds every item that has AreaUsageType
dim json as string = context.Response.Content.ToString()
dim comments as string = ""
dim itemCount as integer = 0
dim searchPos as integer = 0
do while json.IndexOf("AreaUsageType", searchPos) >= 0
    dim typePos as integer = json.IndexOf("AreaUsageType", searchPos)
    ' Find the custom object boundaries around this AreaUsageType occurrence
    dim customStart as integer = json.LastIndexOf("{", typePos)
    dim customEnd as integer = json.IndexOf("}", typePos)
    dim segment as string = json.Substring(customStart, customEnd - customStart + 1)
    ' Extract AreaUsageType (string value with quotes)
    dim keyPos as integer = segment.IndexOf("AreaUsageType")
    dim colonPos as integer = segment.IndexOf(":", keyPos) + 1
    dim quoteStart as integer = segment.IndexOf(Chr(34), colonPos) + 1
    dim quoteEnd as integer = segment.IndexOf(Chr(34), quoteStart)
    dim usageType as string = segment.Substring(quoteStart, quoteEnd - quoteStart)
    ' Extract AreaUsageTax (string value with quotes)
    dim usageTax as string = ""
    if segment.Contains("AreaUsageTax" + Chr(34)) then
        dim taxPos as integer = segment.IndexOf("AreaUsageTax" + Chr(34)) + 13
        dim taxValStart as integer = segment.IndexOf(Chr(34), taxPos) + 1
        dim taxValEnd as integer = segment.IndexOf(Chr(34), taxValStart)
        usageTax = segment.Substring(taxValStart, taxValEnd - taxValStart)
    end if
    ' Extract AreaUsageTaxCalc (numeric value - no quotes in JSON)
    dim usageCalc as string = ""
    if segment.Contains("AreaUsageTaxCalc") then
        dim calcPos as integer = segment.IndexOf("AreaUsageTaxCalc") + 16
        dim calcValStart as integer = segment.IndexOf(":", calcPos) + 1
        dim calcValEnd as integer = segment.IndexOfAny(New Char(){","c, "}"c}, calcValStart)
        usageCalc = segment.Substring(calcValStart, calcValEnd - calcValStart).Trim()
        ' Round to 2 decimal places to avoid floating point noise (e.g., 70.35000000000001)
        dim calcDecimal as decimal = 0
        if decimal.TryParse(usageCalc, calcDecimal) then
            usageCalc = Math.Round(calcDecimal, 2).ToString()
        end if
    end if
    itemCount = itemCount + 1
    comments = comments + "Markup " + itemCount.ToString() + ": Type=" + usageType + ", Tax=" + usageTax + ", Calc=" + usageCalc + " | "
    searchPos = typePos + 1
loop
' STEP 7: Clean up and store in Comments field
' Remove trailing separator
if comments.EndsWith(" | ") then
    comments = comments.Substring(0, comments.Length - 3)
end if
if string.IsNullOrWhiteSpace(comments) then
    comments = "No DigEplan markup data found for: " + folderId
end if
' No Update() needed - BeforeUpdate persists this with the save operation
oPlanningApplication.Comments = comments
return new Result(1, ResultSeverity.Success, "Loaded " + itemCount.ToString() + " markups for: " + folderId)