Answered

Help me create: Send an email every 24 hours with responses sent in the last 24 hours via Forms to Sharepoint list?

(0) Share

Report

Posted on by ThomasKh

Hi, I need someone's help to create a flow in Power Automate.

I have a form in Micosoft Forms where people have to fill out a survey on their status of "recovery" (level 1-5 within three different questions) in the last 24 hours.

The forum lesson consists of the following questions:

- Name (choice from list of fixed names)

- Date (choice from list of fixed dates)

- Sleep (choice 1-5)

- Diet (choice 1-5)

- Mood (choice 1-5)

The responses from the Forms are then automatically imported via a separate Power Automate into a Sharepoint list (with different filter views, e.g. All, Choice 1-2-3, Per Person etc) that all trusted users can see and choose from.

As a compliment to the Sharepoint list, I would like an automatic email to be sent at 21:00 (9:00 PM) every day to selected recipients with a summary of all responses received in the last 24 hours.

I've been trying to write a flow in Power Automate, but I'm getting errors on some parts.

Among other things

- Filter Queries

- Condition

One of the error messages that comes is:

The flow could not be saved with the code InvalidTemplate and the message The template validation failed: 'The action(s) 'Get_items' referenced by 'inputs' in action 'Conditions' are not defined in the template. '..'

654321

Can some kind soul help me.

I need to finish it by Friday 19/6 at the latest.

Many thanks! Thomas K:h Andersson

Categories:

Building flows

I have the same question (0)

All responses (17)

Answers (2)

Sort by

HenWang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @ThomasKh ,

I notice that there is an "Apply to each" action outside the condition action, I think you only need to change the condition and your get items action. Below is my demo flow, almost same as yours:

1. Get items:
You need to add filter query here:

Formula:
Created gt 'addHours(utcNow(),-24)'

Please make sure the addHours(utcNow(),-24) is formatted as formula here:

Just copy:
addHours(utcNow(),-24)

And past it in the expression window and press OK.

2. condition:
Not sure but just in case, please make sure you are using this condition:

length(outputs('Get_items')?['body/value'])

Hope this can help.

Best Regards,
Hen Wang

Was this reply helpful? Yes No
ThomasKh 33 on at

Like (0)

Report
Copy link

Link copied!

Hi Hen!
Thanks for your help.
Unfortunately, I still get the error message. See my picture below.

I tried with a completely different Shareponint list with simpler data, but unfortunately the same problem.

The flow could not be saved with the code InvalidTemplate and the message The template validation failed:
'The action(s) 'Get_items' referenced by 'inputs' in action 'Conditions' are not defined in the template.'..

Hope you (or some other kind soul) has a solution that will help me.

Many thanks for so long.
Thomas K:h Andersson

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @ThomasKh ,

Your get items action itself is ok, you just need to change the length formula inside the condition, please follow below steps and try again:
1. type the "length()" in expression section firstly and move the mouse inside the brackets ()

2. Select dynamic content:

3. Select the value dynamic content from the get items action:

4. you should get this result and please press ok button to finish the expression

BR,
Hen Wang

Was this reply helpful? Yes No
ThomasKh 33 on at

Like (0)

Report
Copy link

Link copied!

Hello Hen, and many thanks for your help.

Unfortunately, I get an error on the filter query in the "Get Items" section.

Error message:
The expression Created gt 2023-05-21T06:33:24.6444108Z is not valid. Creating query failed.
clientRequestId: 69c1f335-5d6c-4b45-80be-e070748e6402
serviceRequestId: 69c1f335-5d6c-4b45-80be-e070748e6402

Do you have any solution?

//Thomas K:h

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @ThomasKh ,

Did you add the ' character around the time expression? It should look like below schema:
Created gt 'addHours(utcNow(),-24)'

Best Regards,
Hen Wang

Was this reply helpful? Yes No
ThomasKh 33 on at

Like (0)

Report
Copy link

Link copied!

Hi @HenWang

Yes 🙂

I work in a Swedish version of Forms, Sharepoint and Power Automate. Can it affect anything?

//Thomas K:h

Was this reply helpful? Yes No
ThomasKh 33 on at

Like (0)

Report
Copy link

Link copied!

@HenWang Another thing that might affect?

The people answer the questions in the Forms and then I import their answers from the Forms into a Sharepoint list.

If there is an error in the column "Created" in Sharepoint, can the format of the time and date affect?

//Thomas K:h

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @ThomasKh ,

No need to put ' character inside the formula bar. You just need to type below code only:

addHours(utcNow(),-24)

And then you will get below result:

After this, you need to type the ' character around the purple formula part:

The point is that ' character is not inside the formula, it's outside the formula part, just like the other words gt,,,,

Just let me know if the flow still return error after the change, would be happy to help.

BR,
Hen Wang

Was this reply helpful? Yes No
ThomasKh 33 on at

Like (0)

Report
Copy link

Link copied!

@HenWang It worked 🙂

I bow deeply and thank you most humbly. I hope to be able to help you at some point.

A last question:
An email with the summary is sent to the recipients for each person who submitted a response.
In my example 10 people:

What condition or step do I add to only get ONE email with a total summary send to the recipients?

//Thomas K:h

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @ThomasKh ，

You are welcome and if you think my suggestion answered your question, you can just mark it so that other users who have the same issue can benefit as well.

For the following question, actually I get a little confused. I might need to understand your request again about this email to be sent. Basically, the recipients you put inside the send an email action decides who to be sent about this email:

And all the recipients will receive one same email, which is expected result for power automate flow.

From the screenshot you provided, I can see that there are multiple email sent and I believe they are the same email with the summary information, could you let me know whether they are sent to same recipient or different person?

Also, it would be better if you can explain more about the thing you want to do and the actual result that is happening from your side.

BR,
Hen Wang

Was this reply helpful? Yes No