Solved: Re: I have a form and I want to populate a sharepo...

Anonymous · ‎05-19-2020

Typically you create the sharepoint list first and then make a form based off that in powerapps.

I did it backwards. I created the app and placed a form inside the app, then saved all the fields from the form to a collection. Is there an easy way to take the information that is inside the collection and add it to a sharepoint list?

I'm trying to assign the submission for review to another user and I think if I have the submission in a sharepoint list it would be easier. Is this the right way to go about it?

Here is a rundown of my app for you to get a better understanding of what I'm working with.

I have a login page. Regardless of what user is logged into PowerApps, they can use anyone's credentials to log into the app.

Validation for the user is based on an Excell document that holds Username, User Password, and User Type.

If the user is a Business Unit, then the BU dashboard will show upon login.

If the User is an Actuarial then the Actuarial dashboard will show upon login.

If the User is a Treaty Admin then the Treaty Admin dashboard will show upon login.

It starts with the BU. The BU can make a submission using this form.

All the information inside this form is saved to a collection called 'FormSubmission'

From there the submission should be passed to an Actuarial where they can review the initial submission to see if it is all good or if more information needs to be added. The Actuarial will be able to see the above form in display mode and have 2 fields that they need to add if they are going to approve it.

If the initial submission is approved by the Actuarial, then it gets sent to the Treaty Admin. The Treaty Admin will see the BU's submission with the added information from the Actuarial and can approve or deny the request.

Pretty Much what I need help with is, would the best way to go about this to be adding the information from the BU's submission to a sharepoint list instead of a collection? And how do I assign the submission to the different groups based on if it gets approved or denied?

I'm completely lost right now and I thought I was nearly done but I can't get the assignment down.

v-xida-msft · ‎05-20-2020

Hi @Anonymous ,

It's easy. Firstly, you need to add two Choice type columns for storing approval status for Actuarial and Treaty Admins in your SP List -- "ApprovalStatus_ForActuarial" and "ApprovalStatus_ForTreatyAdmins".

Above Choice type column both have the following options:

Pending
Approved
Rejected

set default value for these two Choice type column to "Pending" in your SP List.

Then within your canvas app, you should add a Gallery control (as you mentioned) to list these submission which are needed to approve. Within the ActuarialGallery Screen, set the Items property of ActuarialGallery to following:

Filter(
       'SP List',
       ApprovalStatus_ForActuarial.Value = "Pending"
)

If you want to preview these submission approved by Actuarial, please try the following filter formula:

Filter(
       'SP List',
       ApprovalStatus_ForActuarial.Value = "Approved"
)

For updating the "ApprovalStatus_ForActuarial" column value, just as I mentioned before, you could add buttons in Actuarial Display form screen, set the OnSelect property to following:

Approve Button:

Patch(
      'Your SP List',
      LookUp('Your SP List', ID = Value(ItemID)),
      {
         ApprovalStatus_ForActuarial: {
                                        Value: "Approved"
                                      }
      }
)

Rejected Button:

Patch(
      'Your SP List',
      LookUp('Your SP List', ID = Value(ItemID)),
      {
         ApprovalStatus_ForActuarial: {
                                        Value: "Rejected"
                                      }
      }
)

Please try above solution, check if the issue is solved on your side. If the solution is helpful in your scenario, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

More details about firing a flow from a canvas app, please check the following video resource;
https://www.youtube.com/watch?v=1wl9AtxWdkg

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

WarrenBelz · ‎05-19-2020

Hi @Anonymous ,

To answer your question on the SharePoint list. Make a SharePoint list with the same field names and types and connect it to your app. Run the collections and put a button on the screen with the code

Collect(YourSPListName, YourCollectionName)

and it will populate the collection into the list as new records.

You can control navigation and visibility with the built-in functions of User().FullName and User().Email.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

v-bofeng-msft · ‎05-20-2020

Hi @Anonymous :

Do you want to save the records from from to the SharePoint list?

Do you want to filter records based on different status (approved or disapproved)?

Firstly,I suggest use Patch function to update the record, for example:

Patch(yourdatasource,Defaults(yourdatasource),{field1:Dropdown1.SelectedText.Value,field2:Dropdown2.SelectedText.Value})

Secondly,To filter records based on status, you only need to add a status field to the data source, and update this field when the user changes its status to "Approved." Then use the filter function to filter the records.

If I understand your question correctly, the above solution should solve your problem.

If you still have problems, please hesitate to let me know.

Best Regards,

Bof

Anonymous · ‎05-20-2020

@WarrenBelz

Hello again, you've helped me out a lot.

I ran into an issue. Using collect. It says collect has some invalid arguments.

I decided not to use my collection and instead just assign SP fields straight from the form fields and this is my formula.

My SP list looks like this.

Any idea where I went wrong? If you need more info I'm more than happy to provide it.

Anonymous · ‎05-20-2020

@v-bofeng-msft

So I have a form where a user can submit a form. I want to save the entries from that form into a SP list.

From there in other areas of the form, I want to filter by status, by creation date, and the user who submitted it.

I also want to take the submission that is saved in the SP list and assign it to a specific group.

If you look at my reply to Warren I provided what my SP list looks like.

If you need additional info I'm more than happy to provide it.

WarrenBelz · ‎05-20-2020

Hi @Anonymous ,

Can you please tag whoever you want to respond to this as three-ways conversations just confuse everyone.

Anonymous · ‎05-20-2020

@WarrenBelz

I was asking, you questions based on the information you sent to me. Sorry for the confusion. Since the other member also replied asking for information I was just offering them any piece that I thought would help them help me. I don't have a solution as of yet so I figured both of you would be able to help, but I guess for right now, let's focus on your solution.

v-xida-msft · ‎05-20-2020

Hi @Anonymous ,

Firstly, for your first question, if you want to save your form submission into a collection, you could consider try the following formula:

ClearCollect(FormSubmission, BUForm.Updates)

Actually, based on the needs that you mentioned, I think it is not necessary to store the form submission to a collection. I guess you want a specific Actuarial to review the form submission once your BUForm has been submitted successfully. The Actuarial could approve this form submission, if he approve this submission (Change 2 fields value of the submitted item), the form submission would be set to the Treaty Admin.

Based on the needs that you mentioned, I think the combination of PowerApps and Power Automate could achieve your needs. Firstly, when you submit form data from your canvas app, you could consider send an Outlook email to the Actuarial, within the email body, embed the Web URL Link (e.g. https://apps.powerapps.com/play/xxxxxxxx-886b-4a9e-xxxx-dfebc48171dd?tenantId=xxxxxxxx-f6a5-xxxx-89a9-9785a9d6c706) of your canvas app along with some parameters (e.g. ?ItemID=1).

When the Actuarial receive the email, and click the embedded URL Link from the email body, he would be navigate to specific Display form screen of your canvas app (based on the passed parameters). Within this Display form screen, you have added Buttons to change the 2 field values of the current reviewed item.

Please check the following thread for more details:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Include-EditForm-link-for-particular-item-in-email-SharePoint/m-p/420582

Then you could set up a Power Automate flow, add "When an item is created or modified" trigger of SharePoint connector as the Trigger, then add a Condition action below the trigger to check if the Actuarial has approved the initial submission, if yes, send an email to the Treaty Admin through "Send an email" action of Outlook connector, then embed same Web URL Link of your canvas app along with some parameters within the email body.

On your side, the flow's configuration should look like below:

Within the Body field of "Send an email" action, click "Code View" option, type the following HTML code:

<p>Hi xxxx, please approve the following form submission:<br>
<a href="https://apps.powerapps.com/play/xxxxxxxx-886b-4a9e-xxxx-dfebc48171dd?tenantId=xxxxxxxx-f6a5-41a2-xxxx-9785a9d6c706&ItemID=@{triggerBody()?['ID']}">App url link</a>&nbsp;</p>

Note: The 'https://apps.powerapps.com/play/xxxxxxxx-886b-4a9e-xxxx-dfebc48171dd?tenantId=xxxxxxxx-f6a5-41a2-xxxx-9785a9d6c706' represents the Web Url Link of your canvas app.

Please also check and see if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Editing-a-record-from-a-flow-powerapp/m-p/265416

https://powerusers.microsoft.com/t5/Building-Power-Apps/Embedded-link-to-a-Sharepoint-List-item-in-PowerApp/m-p/303910

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

WarrenBelz · ‎05-20-2020

@Anonymous ,

No problems - I just try to help as many people as I can in the spare time I give to the forum. I am not in the least offended if my methods do not align with those of another user - I just need to know.

Since your post @v-xida-msft has posted in addition to @v-bofeng-msft so you have an abundance of help.

I would only be repeating a lot of this, so I will bow out.

Just one thing that helps us a lot - please post your code as text in a Text box (the </> above) and user Format Text where possible.

Anonymous · ‎05-20-2020

@v-xida-msft

Hi thanks for the reply, before I jump into that to see if it works for me, could you help me configure something Warren was trying to have me do to get the info into my sharepoint list?

Collect('POC Submissions', {Segement: SegmentDrop.SelectedText.Value, SubSegment: SubSegDrop.SelectedText.Value, Category: CategoryDrop.SelectedText.Value, BusinessType: DropDownListCol, NatureOfTreaty: NatureRadio.SelectedText.Value, CancellationType: CancellationRadio.SelectedText.Value, Premium: If(IsBlank(FixedPremium.Text), PercentPremium.Text, FixedPremium.Text), Creator:UserNameText.Text, CreationDate:Today()});

This is the formula I'm using, it looks like it's going to work but for BusinessType it's expecting a multiline text field and I'm passing a table in, how could I get it to accept the table instead.