AddDays Dynamic days according to sharepoint list column, possible?

[ferby2023]

So I have my flow below to automatic send a reminder 30 days out date:

I am using the formula addDays(utcNow(),30,'MM/dd/yyyy') to the variable.

But now, i want to change this number '30' to my 'Days to Notify' Sharepoint List column. In this column, people will type different number of days.

I tried several things but none of them worked. Any suggestion?

Many thanks!

[SudeepGhatakNZ]

@ferby2023 

1) Ask users to save their notification preference in the 'Days to Notify' column within the SharePoint List.

2) Utilize the 'Get items' action in Power Automate with a Filter clause to retrieve the 'Days to Notify' column from the SharePoint List. Ensure to store this value as an integer variable.

3) Use the retrieved integer variable directly in the 'addDays' function alongside 'utcNow()' to calculate the date for notification. For instance:

addDays(utcNow(), variables('Days to Notify'), 'MM/dd/yyyy')

[ferby2023]

Thanks for helping!

Unfortunately, I tried this before and did not work, this is the issue message:

 'The variable action 'Initialize_variable' of type 'InitializeVariable' cannot be nested in an action of type 'For_each'.'.

[SudeepGhatakNZ · Accepted solution]

The 'Initialize variable' action can't be used inside loops. It should be placed at the beginning of the flow, typically right after the trigger. This action initializes variables and should remain empty. 

 Instead, use the 'Set variable' action within the loop to modify the variable's value as needed.

[SamLed · Accepted solution]

Hi @ferby2023,

I have a slightly different approach using a calculated column to determine the notification date. The formula for this column is different depending on the need:

• If you want to notify for items based on status (ex: not completed), formula will be "=[created]+[Days To Notify]"
• If you want to notify for items based on a due date, formula will be "=[Due Date]-[Days To Notify]"

Once done, I fetch items using "Get items" but as I can't filter on NotifyDate, because OData filter do not accept calculated column, I filter on status or on Due date greater or equals utcNow().

Next I use a filter action to filter on NotifyDate and finally work on only items that has NotifyDate equals to utcNow():

filter condition expressions:

• Left: formatDateTime(item()['NotifyDate'], 'yyyy-MM-dd')
• Right: formatDateTime(utcNow(), 'yyyy-MM-dd')

______________________________________________________________

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

[ferby2023]

@SudeepGhatakNZ  @SamLed  thanks for helping! both seems to work now. Thank so much for helping guys! 🙂