Trying to capture date from SharePoint list Date column for use in Power Automate variables.

[cmckay]

I have a Power Automate flow that is triggered from a new entry on a SharePoint list. One of the columns of the SharePoint list is a date column named "Last Day Worked"
In the Power Automate, I need to have 2 variables calculated from the date in "Last Day Worked" one of them 1 day after the Last Day Worked date, and the second 14 days after the Last Day Worked date.

I attempted to create these variable using these expressions:

'addDays(triggerOutputs()?['body/value']?['last_x0020_day_x0020_worked'],1)'

and 

'addDays(triggerOutputs()?['body/value']?['last_x0020_day_x0020_worked'],14)'

the resulting error is:

Unable to process template language expressions in action 'Calculate_Start_Date' inputs at line '0' and column '0': 'The template language expression 'addDays(triggerOutputs()?['body/value']?['last_x0020_day_x0020_worked'],1)' cannot be evaluated because property 'last_x0020_day_x0020_worked' cannot be selected. Array elements can only be selected using an integer index. 

How do I correctly calculate these variables?

[Nived_Nambiar]

Hi @cmckay 

I have tried your case by creating the column called Last Day Worked in a sample sharepoint list and when i check the json structure of the Last Day Worked dynamic content variable, it looks like below :

so it looks like triggerOutputs()?['body/LastDayWorked'].

So to calculate the 1 day and 14 days from last day worked date, i used the following expression in compose action:

Expression used for calculating date 1 day after Last Worked Day as per the above screenshot is:

addDays(triggerOutputs()?['body/LastDayWorked'],1)

Simmilarly expression to calculate date 14 days after last working date is 

addDays(triggerOutputs()?['body/LastDayWorked'],14)

The overall flow design is 

This will trigger when a item is added in the given sharepoint list.

Hope it helps 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[cmckay]

Hi @Nived_Nambiar 
Thank you for your response. I attempted to follow your example:


However, when I attempt to run it, I get this error:


I have confirmed that the information coming from the list does contain data:

Can you tell me where I'm making a mistake?

[Nived_Nambiar]

Hi @cmckay 

One query , how you are selecting input parameters for addDays , for the input date, by manually typing json in expression or selecting the dynamic content of Last Day Worked ?

Also show me the datatype of sharepoint column of last day worked, I tried your design and it worked for me perfectly 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[cmckay]

Hi @Nived_Nambiar 

I am manually typing json in the expression.

If I select from the dynamic content the Last Day Worked is not an option.


The column settings in the SharePoint list are:

[Nived_Nambiar]

Hi @cmckay 

Could you try this as triggerOutputs()?['body/LastDayWorked'] expression for LastDay Worked and see if it works for you or not ?

Kindly let me know 

[cmckay]

Hi @Nived_Nambiar 

Attempting your change results in this error:

If I look at the "Last Day Worked" Dynamic content, it shows 'Last_x0020_Day_x0020_Worked'

Changing the expression to this: 

addDays(triggerOutputs()?['body/Last_x0020_Day_x0020_Worked'],1)

results in this error:

[Nived_Nambiar]

Hi @cmckay 

As per the error, it is saying that expression : triggerOutputs()?['body/Last_x0020_Day_x0020_Worked'] is null,

Could you use compose action to check what is value of above expression to confirm whether it is null or not ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[cmckay]

Hi @Nived_Nambiar ,

I tried adding triggerOutputs()?['body/Last_x0020_Day_x0020_Worked'] directly into the inputs for a compose, and it did not convert it, only output the same text. 
I then tried additing it as an expression, the end result was:

I tried adding the Dynamic content "Last Day Worked" and it converted it to a "Apply to each"

The results of the compose in the "Aplly to each" shows correctly though:

[Nived_Nambiar]

Hi @cmckay 

This seems strange, for trigger when item is created   it does not have any dynamic content named values.

Are you using any get items action before this ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[cmckay]

Hello @Nived_Nambiar 

There is no get items in the power automate.
The list columns are as follows:

The current power automate is as follows:

I am getting around the issue by initalizing the LDW variable, and then, in the "Apply to each" loop, I assign the results of the compose to that variable. I'm then able to use that variable in the calculations. 

This seems less than optimal for accomplishing this, but it gets the job done. If you have ideas about a better method for this, I would be happy to try a better way.

Thank you for your continued assistance with this, I appreciate the help!

[SamLed]

Hi,

Give a try with this expression: triggerOutputs()?['body/value'][0]['Last_x0020_Day_x0020_Worked']

When you used first expression: "addDays(triggerOutputs()?['body/value']?['last_x0020_day_x0020_worked'],1)", error told us : "Array elements can only be selected using an integer index." 

[Edit] Usually this trigger outputs an object because of Split-On feature. But if this feature has been disabled once, trigger outputs an array ['body/value'].

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[cmckay]

So, if I turn on the "Split On" it will not generate a "Apply to each" loop? I have a number of steps later on that are used to create tasks, and each of them generates a "Apply to each" loop. If I could cut that out, that would be better.

[Nived_Nambiar]

Hi @SamLed 

Thanks for pointing it out, i wasn't aware of this feature and haven't explored before, but after you said i came to know about this feature and this made me to understand why certain times we get array of responses in case when form is submitted trigger starts.  

Thank you, Leanred something new today !

[SamLed]

Hi,

Once I disabled it, I never managed to re-enable it without generating an error at trigger level. I had to remove it and add it again.  

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.