Calculation of "DateTime" type and "Duration" type in 'Call a webhook'

[leinharte]

How can I add a value of type "Duration" to a value of type "DateTime" in 'Call a webhook'?

As shown below, if you add the 'Duration' value to the 'DateTime' value in the column expression, you will get the desired result.

However, the following code in 'Call a webhook' does not add the 'Duration' value.

"Rows": [
<<START: filter("reservationItem", [reservationGroupID]="g_A99D452A")>>
{
"vendorID": "<<[vendorID]>>",
"dateTime": "<<(EOMONTH(index(DateCurrent[date],1), -1)+[duration])>>"
}
<<END>>
]

Where am I making a mistake?

Thank you in advance.

[Suvrutt_Gurjar]

Maybe more details will be required about certain parameters in your expression.

Based on current understanding could you try the following expression?

"Rows": [
<<START: filter("reservationItem", [reservationGroupID]="g_A99D452A")>>
{
"vendorID": "<<[vendorID]>>",
"dateTime": "<<(EOMONTH(index(DateCurrent[date],1), -1)+([duration]-"00:00:00"))>>"
}
<<END>>
]

Edit: Added one missing apostrophe in the expression.

[leinharte]

@Suvrutt_Gurjar, thanks for your reply.

"<<(EOMONTH(index(DateCurrent[date],1), -1)+([duration]-"00:00:00"))>>" causes an error.
Also, "<<(EOMONTH(index(DateCurrent[date],1), -1)+([duration]-"000:00:00"))>>" does not work.

Since my app is not made in English, it is difficult to explain in detail, but I will explain it again in a concise manner.

I want to get the value of adding a certain amount of time to a specific time with 'Call a webhook'.

First of all, I will show you the following example to explain that there is nothing wrong with the other parts of 'Call a webhook'.

The following 'DateTime' type values are given.

The following 'Duration' type values are given.

If you run the following 'Call a webhook', which retrieves each value without calculating the sum to the given value, you can get the desired result as follows.

{
"Action": "Add",
"Properties": {
"Locale": "en-US"
},
"Rows": [
<<START: filter("Item", [GroupID]="g_A99D452A")>>
{
"dateTime": "<<(EOMONTH(index(DateCurrent[date],1), -1)+20)>>",
"duration": "<<[duration]>>"
}
<<END>>
]
}

The results are as follows:

However, if you try to sum the duration values by changing the code of the dateTime column as follows, the sum does not work.

{
"Action": "Add",
"Properties": {
"Locale": "en-US"
},
"Rows": [
<<START: filter("Item", [GroupID]="g_A99D452A")>>
{
"dateTime": "<<(EOMONTH(index(DateCurrent[date],1), -1)+20+[duration])>>",
"duration": "<<[duration]>>"
}
<<END>>
]
}

The results are as follows:

Thanks.

[Suvrutt_Gurjar]

Thank you. Could you mention what is the field type of "Duration" column? Is it duration or time type column?

[leinharte]

@Suvrutt_Gurjar

The field type of "Duration" column is duration.

[Suvrutt_Gurjar]

Okay, thank you. I am stepping out of my desk for next few hours. I will respond after returning to my desk. Hope that is fine. In the meantime , someone else may also respond to you.

[Suvrutt_Gurjar]

Please try an expression of 

DATETIME(EOMONTH(INDEX(DateCurrent[date],1), -1))+(TIME(INDEX(DateCurrent[date],1))+[Duration] -"00:00:00")

{
"Action": "Add",
"Properties": {
"Locale": "en-US"
},
"Rows": [
<<START: filter("Item", [GroupID]="g_A99D452A")>>
{
"dateTime": "<<DATETIME(EOMONTH(INDEX(DateCurrent[date],1), -1))+(TIME(INDEX(DateCurrent[date],1))+[Duration] -"00:00:00")>>"
}
<<END>>
]
}

[leinharte]

@Suvrutt_Gurjar I appreciate your kind reply.

BTW, it does not work for me.

When it comes to 'duration' or 'time' types, 'Call a webhook' is very tricky for me.

Instead, I completed the app by only getting 'date' and 'duration' with 'Call a webhook' and then adding those two values through 'Initial value'.

Thank you very much.

P.s. I wonder if it worked for you.

[Suvrutt_Gurjar · Accepted solution]

---

@leinharte wrote:

P.s. I wonder if it worked for you.

---

Yes @leinharte . It works perfectly for me. I may add that 90% of the times in my solution suggestions, especially for such tricky requirements, I test before suggesting. Just in case , I have not tested a suggestion, I typically mention so.

Please see the table screenshot below, highlighted record. [Delivery Date] is original date (equivalent to your [date] column) [Duration_Test] is equivalent to your [Duration] column. [New_DateTime] column is updated by the webhook.

Here is my webhook code that updated the [New-DateTime] column above.

And here is audit log for successful running of the webhook.

[leinharte]

Wow, I'll dig some more tomorrow.

Thanks a lot.

[leinharte]

It works for me too. 😀

Here's what I discovered:

1. It seems that the "Locale" in the Call a webhook code needs to match Google Sheet.

2. Also, if we do not use the "eomonth()" function, it appears that it is possible to add the duration type value to the datetime type value.

I learned a lot. Thank you again.

[Suvrutt_Gurjar]

---

@leinharte wrote:

It works for me too. 😀

---

Excellent. Thank you for sharing the update.

---

@leinharte wrote:

Also, if we do not use the "eomonth()" function, it appears that it is possible to add the duration type value to the datetime type value.

---

You are correct. EOMONTH() converts a datetime value to just date value. However in suggested expression,  I had again applied DATETIME casting to EOMONTH() value to convert it back to Datetime type. Please see the DATETIME() wrapping below

<<DATETIME(EOMONTH(INDEX(DateCurrent[date],1), -1))

Of course using EOMONTH() looses any time value in the original datetime column value. So EOMONTH() cannot be used if datetime column has a non zero time value. In your shared screenshot of [Date] column values , the time component was always zero. So EOMONTH() wrapping was okay.  Hope this helps.