PowerShell doesn't like REST $Filter

estm8ben · August 23, 2019, 3:10pm

Hello EpiUsers
I have an email being generated by PowerShell of REST data. You can find the details here:
REST to email

When I try to add a REST $filter, PowerShell errors out.
This works without any parameter limitation - wide open:
Invoke-RestMethod -Uri https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/ -Credential $Cred -ContentType "Application/Json; charset=UTF-8"

This works with certain columns selected $select:
Invoke-RestMethod -Uri https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/?%24select=JobAsmbl_JobNum%2CJobHead_PartNum -Credential $Cred -ContentType "Application/Json; charset=UTF-8"

I can even $filter based on numeric values successfully:
Invoke-RestMethod -Uri https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/?%24filter=Calculated_Total_Actual_Hours%20eq%200 -Credential $Cred -ContentType "Application/Json; charset=UTF-8"

But when I try to $filter with a string it errors out:
Invoke-RestMethod -Uri https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/?%24filter=ECORev_ECO%20eq%20'Eric' -Credential $Cred -ContentType "Application/Json; charset=UTF-8"

Here is the error message in PowerShell:

At line:9 char:9
+ $data = Invoke-RestMethod -Uri https://XXXX/E ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (Method: GET, Reques\u2026YnN0b2hyOm1pZGk=
}:HttpRequestMessage) [Invoke-RestMethod], HttpResponseException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand```


I'm copying directly from the swagger page and it works there.
Any thoughts?

ckrusen · August 23, 2019, 3:21pm

Have you tried using the $ instead of the escape code of %24?

estm8ben · August 23, 2019, 3:23pm

If you look at the numeric example that works, it doesn’t mind the %24

And when I change the %24 to $ on the numeric version it errors.

ckrusen · August 23, 2019, 3:26pm

Ahhh, missed that.

What about escaping the single quotes' -> %27

estm8ben · August 23, 2019, 3:28pm

Well done! That works!
Thank you

Mark_Wonsil · August 23, 2019, 4:00pm

You can also use a PowerShell function to encode this for you. From Browse code samples | Microsoft Learn

$Encode = [System.Web.HttpUtility]::UrlEncode($URL)

estm8ben · August 23, 2019, 4:23pm

I tried to use your suggestion, @Mark_Wonsil
Here is my snippet of code:

$uri = Read-Host "https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/"
$encode = [System.Web.HttpUtility]::UrlEncode($uri)
$data = Invoke-RestMethod -Uri $encode -Credential $Cred -ContentType "Application/Json; charset=UTF-8"

Powershell produced nothing; not even an error. This was the wide open REST call - no $select or $filter
Do you see an error in how I used it?

ckrusen · August 23, 2019, 4:49pm

echo $encode to the console to see what it contains after being set.

And what is the Read-Host mdifier in the $uri = Read-Host "https..." ?

estm8ben · August 23, 2019, 5:33pm

https%3a%2f%2fXXXX%2fEpicorERP10%2fapi%2fv1%2fBaqSvc%2fEstimatorEmail%2f

The Read -Host was what was in the website Mark linked. I removed that.

This is the current code snippet:

$encode = [System.Web.HttpUtility]::UrlEncode("https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/")
$data = Invoke-RestMethod -Uri $encode -Credential $Cred -ContentType "Application/Json; charset=UTF-8"

This is the powershell error:

Invoke-RestMethod : This operation is not supported for a relative URI.
At line:10 char:9
+ $data = Invoke-RestMethod -Uri $encode -Credential $Cred -ContentType ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [Invoke-RestMethod], InvalidOperationException
+ FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

estm8ben · August 23, 2019, 5:53pm

It seems to me that the Swagger Url is already partially encoded/decoded.
https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/?**%24**filter=ECORev_ECO%20eq%20’Bert’

When it has already changed $ to %24. And then THAT gets sent to the UrlEncode utility it goes further into chaos because it changes the “%” of %24 through another round of decoding that results in “%2524”. I don’t think that helps get a usable result in the Invoke method.
I feel like I’m way in the weeds now…

Mark_Wonsil · August 23, 2019, 6:10pm

Try calling the UrlDecode first (same example page) so that you remove any encoding that’s coming in and then do the Encode afterwards.

estm8ben · August 23, 2019, 6:33pm

So, an interesting thing happens on the way back to the Encoder. The $select and $filter get recognized and ignored. This renders the URL unusable. The result skips right over the green highlighted text and moves on to the next character.
Ugh…
Seems I’ve hit the overlap between the Powershell “uses $ for the variable” and ignores it and REST “uses $ for a command”

Mark_Wonsil · August 23, 2019, 6:49pm

Sorry this is such a PITA, but for a quick fix, just do a string replace to add your ‘$’ back.

estm8ben · August 23, 2019, 6:57pm

But it drops the words “select” and “filter”, too.That’s what I’m attempting to show in the screen shot.

ckrusen · August 23, 2019, 7:10pm

I think power shell is seeing those as variables and replacing them with their content (which is a null value).

I ran into something similar when using powershell with command line DMT.

$first = 'Kevin'
$last = 'Marquette'
$message = "Hello, $first $last."

is valid and actually returns Hello, Kevin Marquette

See the following:

ckrusen · August 23, 2019, 7:34pm

It looks like you should precede the $ with a tick, when you want it as a dollar sign in the string. Ex:

$i = 5
"The value of `$i is $i."

outputs:

The value $i is 5.

estm8ben · August 23, 2019, 7:58pm

Well, here’s where I’m at with all this.
If I only replace ’ with %27 around the text of my $filter like you showed, then I’m doing very little to the Swagger URL and I’m happy.
It’s only when I tried to also use $select to massage the columns that I got into trouble.
I’m weeding out unwanted data columns in the ConvertTo-Html step instead using -Property

$message.Body = $data.value | ConvertTo-Html -Property JobHead_PartNum,JobHead_RevisionNum,JobHead_PartDescription,Calculated_Total_Est_Hours,Calculated_Total_Actual_Hours,JobAsmbl_JobNum,JobHead_ProdQty,ECORev_ECO -Head $Header

I’m golden!
Moving on.

Thank you both for showing me a LOT more about this subject, though @ckrusen and @Mark_Wonsil!