Retrieving a value from a PowerShell action

Overview

This is just a small post on an approach to retrieve a value which has been generated by a PowerShell action. It’s based on this question in the WEBCON Community.

Short version: We write the variable values to the log and assign them to fields in another action.

The sample PS script multiplies the input value by 2 and 4 which result is stored in the output fields.

PowerShell Script

The below PS script is an example how a script can be written to copy it from PS ISE to the Designer Studio and back in total. The ISE will use the test value of 123 while the script executed by WEBCON BPS will use the value of field Input value. The only important part though is to mark the beginning and ending of each returned variable. I used RV#> for start and <RV# as the end.

A sample PS script which can be copy pasted from Designer Studio to ISE and backwards.

if ($psISE.Options.AutoSaveMinuteInterval -ne $null){    
    #$username = Read-Host -Prompt "Provide user name"
    #$password = Read-Host -Prompt "Enter password"      
    $value = 123
}

# mapping BPS field values outside the {} so that they will be assigned, otherwise they won't be replaced.
$tempValue = "{126}"
if ($psISE.Options.AutoSaveMinuteInterval -eq $null){   
    Write-Output "Executing PS script from BPS by user $($env:USERNAME)"
    $value = [System.Convert]::ToInt16($tempValue )
}

Write-Output "Return value 1 RV1>$($value*2)<RV1"
Write-Output "Return value 2 RV2>$($value*4)<RV2"

Remark: Using $value = "{126}" instead of $value = [System.Convert]::ToInt16($tempValue) won’t work because it’s already nested within other {}. If you are using constants or rules they will work. You can read this for more details.

Info: There’s no $psISE.Options.AutoSaveMinuteInterval in Visual Studio Code but you will find a suitable replacement if you look for one.

Assigning the field values

After the PS Script action has been executed the change values of multiple fields action will be started.

The fields are updated after the PS Script execution.

The values can be extracted from the log entry created by the PS action. The correct entry is identified using the inner select. It returns all log entries which have been created in the last second for this workflow which contain our unique markers. In this case its RV1>. The outer select extracts the string within our markers.

Extracting the variable values from the log.

select logMessage,[Log_ID],
substring(logMessage,CHARINDEX('RV1>',logMessage)+4,CHARINDEX('<RV1',logMessage)-CHARINDEX('RV1>',logMessage)-4) as ReturnValue1,
substring(logMessage,CHARINDEX('RV2>',logMessage)+4,CHARINDEX('<RV2',logMessage)-CHARINDEX('RV2>',logMessage)-4) as ReturnValue2
from
(SELECT [LOG_ID],LOG_Description logMessage
  FROM [dbo].[WFLogs]
  where LOG_WFDID = {WFD_ID}
  and LOG_TSInsert > DATEADD(SECOND,-1,GetDate())
  and LOG_Description like '%RV1>%') logEntry

You can also add a where condition which restricts the results to the PS action itself using this and LOG_ACTID = 103. If you are using it, you will have to use a constant which stores the id of the action for each environment. This may be necessary in case you are running multiple scripts with the same markers during one path transition. Otherwise the time condition and LOG_TSInsert > DATEADD(SECOND,-1,GetDate()) should be sufficient.

The log entries can be filtered by a specific action.

Remarks

This approach will only work with simple values. If you want to send an object to the log, you could convert it to JSON. The -replace removes the linebreaks.

$complexObject =  @{id="1234";Label="Name"}
Write-Output "My Object start: $((ConvertTo-Json -InputObject $complexObject -Compress) ))"

The output will be:

My Object start: {"id":"1234","Label":"Name"})