How can you send S123 reports to different users via Power Automate?

[Lucas_Halphen]

I created a Power Automate flow to send a report every time a field survey is completed.  It is working but only send the email to the “S123 Creator” account user by using the "email question” variable value.

On the CC there is no option to select another "text question" that has a variable value of username@companyname.com or supervisorname@companyname.com.

I want to send email reports not only to the "S123 Creator" but also send to others via CC to the field personnel that created the report (maybe he or she wants to do some editing before the final report) and, also a notification to the supervisor of this person that might be interested in learning what´s happening in the field.

[ezirhli]

Hi @Lucas_Halphen,

1. Create a csv file with the employee e-mail and manager e-mail.
2. Create a question in the survey123 app as manager email. Automatically fill in the answer to this question according to the employee e-mail in the field with the pulldata function.
3. Then dynamically select the manager email question field in Power Automate

Enjoy your work

[Lucas_Halphen]

Hi @ezirhli,

Thank you for your recommendation, however I tried that solution within the S123 form.  See the highlighted (in yellow) rows.  I aso tryed with an email question, but this type of question seems to use the Creator of the survery email address.

Seems so me the problem is how Power Automate recognize those fields in the "Dynamic Content" pick up table, and these other "questions" are not availabe to be selected, to include other email addresses.  The variable Name (Name of the first result file) contents is the one used or reserved for the "Creator" of the survey.  See the list of available field in Power Automate.

The above fields are the only one available to be selected in the CC and can not add any other email or text question

nor in the *To: 'body/feature/attribute/user_mail';  ????(not possible to add another email address)

[abureaux]

This is a little bit of a loaded question in that there are numerous answers of varying complexity.

As ezirhli mentioned, You most likely want a CSV. I'll describe a generalized process, and you can use what you like:

----------------------

Step 1. Create a CSV containing employees. Columns depend on your company, but likely [Employee Number], [Employee Name], [email], [title]

NOTE 1: You want employee number to be your "name" column because that column should be unique. Employee Names can overlap, which is a bad thing.
NOTE 2: You set up an external select similar to a normal list in that "name" and "label" are required columns. Everything else is extra.

Step 2. Upload the CSV to your portal (either AGO or Enterprise)

Step 3. In S213 Connect, create and publish your survey (You need to publish it to do Step 4. You can technically do this last, but for the sake of simplicity, I'm putting it here)

Step 4. In S123 Connect, along the bottom row of buttons, click "Linked Content" > click "+ Link content" > click "CSV" > search for and click on the CSV you uploaded in Step #2 > Click "OK"

Step 5. Click the "download" button next to the newly linked CSV

Step 6. In your XLSX form, add a select_one_from_file question and two calculates:

Step 7. This part is up to you, but if you just need a single email you can skip this step. You can have multiple questions like Step 6, use repeats, whatever. For this example, I will add a repeat for extra users.

NOTE 1: The join() is used to collect all of the emails from the repeat. This will be used in Step 8.
NOTE 2: When using join(), it is very important to increase the default field length of 255 to something you will not exceed. Only you know what your users are likely to do. I set "1000" for my example above. This has worked for my forms so far. Failure to do this will result in frustrating errors if the user enters a lot of users and exceeds the field length.

Step 8. Finally, add a calculate to combine all of your emails into a single string

NOTE: Don't forget to increase the field length of this field as well.

Step 9. In Power Automate, set up a flow with these steps

Result: A nicely formatted email list! Use this variable in your email's To field.

NOTE: Yes, you could replace the commas with semicolons and skip a few steps. Personally, I transform data a lot and use if for different purposes, so I figured I show the steps needed to complete this task and leave room for expansion.

EDIT: Important Power Automate note... Sometimes Power Automate can bug out, and valid/published fields may not be visible. Do this to bypass the bug and show all fields: Click "Expression" > type "=" > click "Dynamic content" > delete the "=" > scroll through the list and find your missing field (you CANNOT type to search when you do this fix, you must scroll)

[Halphen_Lucas]

Hi @abureaux  Thank you for your time and recommendations.  I am still working on it.  However, I learned several things.

1.  I tried the select_one_from_file filename.cvs option, and get the same results than mr MihaRobar.  The select_one_from_file question did not work.  I got the same image of repeated "label" or a "blank" box with no option at all.  I have checked the cvs file with notpad to verify if the comas (,) are there.  I noticed that all lines ended with a coma (,).  So

I returned to my original "choices" columns with the cvs file in the Media Folder and that the  S123 is working OK

2.  It is sad to learn that "flows in Power Automate will automatically sign out after two weeks".  As my original S123 was working for one email (at least).  Now I am without MSPA, and unable to connect S123 with this application and can not test a new webhook.

Keep on working.  Thank you again.

[abureaux]

Don't worry too much about Power Automate. Unless you leave it unattended for two weeks, the refresh process is very easy and only takes a minute (I just timed myself, and it took 40 seconds from opening web browser to updating connections).

As for the CSV, would you mind uploading both the CSV and XLSX? If they contain sensitive information, I would suggest contacting Esri Tech Support as your next step.

[Lucas_Halphen]

Hi @abureaux , just to let you know that I did deleted/redo the "Power Automate" steps, and finally get the email problem resolved.  I used your steps 6 and 7 but only with emails of the supervisor and inspector as "name variable", not need to concatenate those 2 values in one string.  One email is used in the send *To, and the other in the CC. 

Those fields (or name variables) where available in the selection items from the source.

However, I still struggling with the .csv file and "select_one_from_file" question.  I did a simplified version of only 2 columns and 2 rows but get the same error.  For the first line I get "label" and for the second option I get nothing (blank).

[Lucas_Halphen]

This is the simplified version of the .csv file.  (I don't know how upload files here)

[abureaux · Accepted solution]

I am unfamiliar with the Spanish(?) implementation of S123, but have you tried renaming your column headers?

nombre > name
correo > label

When you say all the options are "label", that sounds like a poorly formatted CSV. This may fix that issue.

[Lucas_Halphen · Accepted solution]

YES, the columns headers shall be the same as in the S123 choices tab.  In this case the .csv file must have the first column header = name, and the second column header = label, the other columns names as needed.

I fixed other .csv files where the first column is a "contract number", but the header should be = "name".

All other issues with MS Power Automate and select_one_from_file questions were resolved, and the S123 is working. 

Thank you for sharing your knowledge and patience.