topic Re: Issue with Date Parameter in API call filter in Workato Pros Discussion Board

Issue with Date Parameter in API call filter

camaliontij — Wed, 12 Feb 2025 21:09:58 GMT

Hello,

I have an API call with a date filter and I want to get the information from the last 30 days. So I created a parameter like this:

today-30.days

and called it "Last30days". Then I added the parameter to my API call to be like this:

my_API_call_?$filter=PostingDate gt {Last30Days}

but it's not working and I'm getting the following error:

"A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'GreaterThan'."

Any idea on what would be the right way to do this?

Thanks!

Re: Issue with Date Parameter in API call filter

gary1 — Wed, 12 Feb 2025 21:31:39 GMT

You need to figure out how to encode/format an Edm.Date value. You're passing "YYYY-MM-DD", which is a string. In order for the application to recognize the string as an Edm.Date, you might need to encode it differently, like "YYYY-MM-DDT00:00:00" (this is a guess, as an example only).

When the API returns date info, how are they formatted? Try to match that format.

Re: Issue with Date Parameter in API call filter

camaliontij — Thu, 13 Feb 2025 12:28:38 GMT

Hi gary1,

The format in the API call is "2025-01-31T23:59:59Z", the parameter I created is set to Date Time and the formula is this "=today-30.days"

Re: Issue with Date Parameter in API call filter

Prudvi — Thu, 13 Feb 2025 13:15:39 GMT

Hi @camaliontij,
Can you share the request URL along with headers which you are passing.

I created a variable X which is of DateTime Type and
in Request URL, I changed it to formula mode and passed it in the following way.

Regards,
Prudvi

Re: Issue with Date Parameter in API call filter

camaliontij — Thu, 13 Feb 2025 13:19:08 GMT

Not the exact request but it's something like this:

https://api.businesscentral.dynamics.com/v2.0/ID/Production/api/DF/prod/v2.0/Companies(ID)/DFSPIFFDetCustLEntrys?$filter=PostingDate gt {Last30Days}

Where the formats are:
- PostingDate = YYYY-MM-DD
- If I use "YYYY-MM-DDTHH:mm:SSZ" it works fine
- But it doesn't accept the date_time parameter I created in Workato with =today-30.days

This is the Path I'm using:

Re: Issue with Date Parameter in API call filter

Prudvi — Thu, 13 Feb 2025 13:54:37 GMT

Hi @camaliontij,
You might have to use the last30Days datapill{can be found in the properties datatree} instead of {last30Days}
Did you try the above step :
I created a variable X which is of DateTime Type and
in Request URL, I changed it to formula mode and passed it in the following way.

Regards,
Prudvi

Re: Issue with Date Parameter in API call filter

shivakumara — Thu, 13 Feb 2025 14:49:00 GMT

Hi @camaliontij,
Please use below formula in your case and let me know if you able to get the required response.

We get (Today - 30 days) = 2025-01-14. Hence, to add time, we need to use to_time and strfTime formula.

Thanks and Regards,
Shivakumara K A

Re: Issue with Date Parameter in API call filter

camaliontij — Fri, 14 Feb 2025 13:00:12 GMT

Hi all,

We might be in the right direction but not quite there yet. Here is what I tried:

- Path as Text:
My understanding is that if I need to use the Last30Days formula, I should set the path to Formula as well, otherwise I get the EDM.date error I mentioned before.

Path as Formula:
I've tried changing it to formula and it seems like Workato considers the URL as formula as well, that's why it has colors. In the example below I get this error:

bad URI(is not URI?): "=https://api.businesscentral.dynamics.com/v2.0/(ID)/Production/api/DF/prod/v2.0/Companies(ID)/DFSPIFFDetCustLEntrys?$filter=PostingDate gt {Last30Days}"

I've tried to create another parameter to hold the URL and add the URL PArameter and Date Parameter to a Formula Path, that didn't work.
Then I've tried adding the URL between " " so it considers it as a String and also replaced spaces to %20 but that didn't work either.

In all cases now I'm getting the "bad URL" error.

Re: Issue with Date Parameter in API call filter

Prudvi — Fri, 14 Feb 2025 13:25:50 GMT

Hi @camaliontij,
You might have used a HTTP connection which will have hostname : https://api.businesscentral.dynamics.com
In the path which is enabled as formula mode you need to provide quotes for static value :
"https://api.businesscentral.dynamics.com/v2.0/ID/Production/api/DF/prod/v2.0/Companies(ID)"+{Last30Days}[which is a parameter]
If there are any spaces, use %20 for URL encode.
Regards,
Prudvi

Re: Issue with Date Parameter in API call filter

camaliontij — Fri, 14 Feb 2025 13:36:29 GMT

Hi Prudvi,

You are correct about the HTTP and that is what I explained in my last comment. Let me breakdown here:

This is the current API call in the Formula:
"https://api.businesscentral.dynamics.com/v2.0/(ID)/Production/api/DF/prod/v2.0/Companies(ID)/DFSPIFFDetCustLEntrys?$filter=PostingDate%20gt%20"+{Last30Days}

The Last30Days parameter now is like I was advised:
=(today - 30.days).to_time.strftime("%Y-%m-%dT%H:%M:%S") + "Z"

And the result is this error:

bad URI(is not URI?): "=\"https://api.businesscentral.dynamics.com/v2.0/(ID)/Production/api/DF/prod/v2.0/Companies(ID)/DFSPIFFDetCustLEntrys?$filter=PostingDate%20gt%20\"+{Last30Days}"

Btw, than you all for the support so far.

Re: Issue with Date Parameter in API call filter

Prudvi — Fri, 14 Feb 2025 16:17:00 GMT

Hi @camaliontij,

Regards,
Prudvi

Re: Issue with Date Parameter in API call filter

gary1 — Fri, 14 Feb 2025 16:20:31 GMT

Can you please show full URL including the query exactly as Workato is sending it?

Re: Issue with Date Parameter in API call filter

gary1 — Fri, 14 Feb 2025 17:37:22 GMT

This seems odd to me. Workato should be automatically URL encoding the parameters. I don't think you should be doing it manually in the input fields.

Re: Issue with Date Parameter in API call filter

camaliontij — Fri, 14 Feb 2025 22:24:41 GMT

@gary1 I'm sorry I just saw your message asking for the request. Just so we are in the same page, this is what I'm using:
Path as formula:
"https://api.businesscentral.dynamics.com/v2.0/ID/Production/api/DF/prod/v2.0/Companies(ID)/DFSPIFFDetCustLEntrys?$filter=PostingDate%20gt%20"{Last30Days}

Parameter:
Name: Last30Days
Value: (today - 30.days)
Type: Date
I get the same error when I try changing it to String, or date_time, or trying the formulas listed in this thread.

Which results in this error:

If I add the URL from HTTPS, it overrides base URI so it should be fine. But it seems like when I set it to formula, it adds a =\ at the beginning and \ at the end. I've also tried adding + or & between the string and formula, but all goes to the same error.

The URI is correct, if I run it without a filter it shows all records.

Re: Issue with Date Parameter in API call filter

gary1 — Fri, 14 Feb 2025 23:08:11 GMT

Ok, this image makes things a bit clearer.

The problem is you're not passing a date or even a date string, you're literally passing the name of your variable "{Last30Days}" as a string. This needs to be fixed.

In Workato, you don't reference variables as a string, you reference them by using their data pill.

Please follow these instructions. Let us know if you run into any issues.

Step 1: Create a variable action

Step 2: Add a variable called "last_30_days" and use this formula

Step 3: Create the HTTP action

Step 4: Configure the HTTP action like this. This is how to use the variable properly (by using its data pill). Note: Pruvdi might be right that you have to URL-encode the spaces, but let's see if it works without the encoding

Alternate Step 4: You can also configure the HTTP action like this

Re: Issue with Date Parameter in API call filter

gary1 — Sat, 15 Feb 2025 01:31:38 GMT

@Prudvi I was looking at your screenshot again and this is incorrect.

Setting a param name to "$filter=PostingDate%20gt%20" and a value to "today" will create a result with two "=" characters:

$filter=PostingDate%20gt%20=2025-02-14

Workato places "=" between the param name and the value.

The correct way is to set the param as "$filter" and the value as "PostingDate%20gt%20" + a date

Re: Issue with Date Parameter in API call filter

camaliontij — Sat, 15 Feb 2025 21:37:41 GMT

Hi @gary1 and @Prudvi,

Thanks for explaining the data pill, that makes a lot of sense now!

I've tried the last options you sent, created the variable and selected it from the data pill.

1 - If I use the Path as text, add the URI and the variable from data pill I get this:
code: BadRequest,
message: Expression expected at position 14 in 'PostingDate gt'. CorrelationId: 0a857be3-5129-4966-87ac-71652e8df1e5.

2 - If I use the Path as formula (the =\ and \ are added anyways) )and create a parameter called "$filter" and value as "PostingDate%20gt%20" + a date (I've also tried to not add the Unicode) it ignores the filter... this is what I call (I've also tried removing the "?" from the last character):

This doesn't have the parameter which is on the following screen:

From here, the date from the data pill doesn't appear, and when I go to the next screen, it just ignores everything and gets all data. I also noticed that the parameter name is "$Filter" but on this screen it ignores the "$".

I wonder if this is happening because I have a connection set for all my recipes, so in case it's updated, all recipes will also be updated. I'm selecting this connection instead of using the In App HTTP element you mentioned where I would need to add the token,Id, etc.

Re: Issue with Date Parameter in API call filter

camaliontij — Sat, 15 Feb 2025 21:50:09 GMT

I don't know if that rings a bell but I've tried to move the whole call (URL and filter) to the variable, and regardless if I use text or formula, the Path ignores it and shows bad request like I was adding nothing 🤔

Re: Issue with Date Parameter in API call filter

gary1 — Sun, 16 Feb 2025 17:02:57 GMT

Hi, a few requests:

Can you please post additional screenshots of how you have the action configured? Please use black to redact any sensitive information. Using white makes it difficult to tell if you're missing information or trying to hide it.
Can you also post screenshots of the error messages you're receiving from Workato? This is essential. The Request URL preview may not be the final URL being using in the action/API call, so I don't really trust it.

Some other notes:

Under certain circumstances when you switch between formula mode and text mode, it will add an "=" and some double quotes. This is a bug (as far as I can tell), so when it happens you need to fix it.
The configuration of the connection shouldn't affect the action beyond the authentication.

Re: Issue with Date Parameter in API call filter

Prudvi — Mon, 17 Feb 2025 12:34:49 GMT

Thanks @gary1.
Regards,
Prudvi