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.
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.
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 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.
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.
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"})
Comments