cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMR80
Helper I
Helper I

[PowerApps + SharePoint] Add multiple line items to a SharePoint list through PowerApps Form

I have a training site on SharePoint that houses a "Completed Training" list. That list gets populated by a trainer, as they train people out in the field. Each person is a new line item. The only information that really changes per line item is the person's name. If the trainer has 5 people to enter, he has to enter the Plant/Location, Date, Trained Material Name and Employee name 5 separate times.

 

Ex. 

John Doe  |  Beverly Hills  |  7/5/19  |  TrainedMaterialName

Joe Schmo  |  Beverly Hills  |  7/5/19  |  TrainedMaterialName

Susie Sue  |  Beverly Hills  |  7/5/19  |  TrainedMaterialName

Billy Bob  |  Beverly Hills  |  7/5/19  |  TrainedMaterialName

Greg Day  |  Beverly Hills  |  7/5/19  |  TrainedMaterialName

 

I'm attempting to create a PowerApp so that the trainer can easily input the Plant/Location, Date and Trained Material Name only once, and then insert as many names as they'd like at one time. On submit, I want this to generate a different line item in the SP list for each Employee Name with all the same Plant/Location, Date and Trained Material Name.

 

PowerApp Ex.

 

Plant/Location:  Beverly Hills

Date:  7/5/19

Trained Material Name: BasicTraining1

Employee Name: <add multiple items> 

- Susie Sue 

- Billy Bob

- Greg Day

 

[Submit Button]

 

Is there a formula to accopmlish this? 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @AMR80 ,

Which type column do you used to store the Employee Name within your SP List? A Text type column or Person type column?

How do you choose the Employee Name within your app? Could you please share a screenshot about your app's configuration?

 

Based on the needs that you mentioned, I think the combination of Patch function and ForAll function could achieve your needs (The SubmitForm() function could not achieve your needs). 

1. If the "Employee Name" is a Text Type column in your SP List (use Single Text column to store the Employee Name):2.JPG

 

3.JPG

Set the OnSelect property of the "Submit" button to following formula:

ForAll(
      Trim(Split(DataCardValue11.Text, ",")),
      Patch(
            '20190708_TrainingList',
            Defaults('20190708_TrainingList'),
            {
                Title: DataCardValue7.Text,
                'Employee Name': Result,
                'Plant/Location': DataCardValue8.Text,
                Date: DataCardValue9.SelectedDate,
                'Trained Material Name': DataCardValue10.Text
            }
      )
)

On your side, you should type:

ForAll(
      Trim(Split(EmployeeNameDataCard.Text, ",")),
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: DataCardValue7.Text,
                'Employee Name': Result,
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMeterialNameDataCard.Text
            }
      )
)

When the "Submit" button is pressed, the data would be inserted into your SP List as below:4.JPG

 

2. If the "Employee Name" is a Person Type column in your SP List, which has been enabled "Allow Multiple selections" (Use Person type column to store the Employee Name):5.JPG

 

6.JPG

Set the OnSelect property of the "Submit" button to following:

ForAll(
      DataCardValue14.SelectedItems,
      Patch(
            '20190708_TrainingList',
            Defaults('20190708_TrainingList'),
            {
                Title: DataCardValue7.Text,
                'Employee Names': Table({
                    Claims:"i:0#.f|membership|" & Lower(Email), 
                    DisplayName: DisplayName,
                    Email: Email, 
                    Department:"", 
                    JobTitle:"", 
                    Picture:""
                }),
                'Plant/Location': DataCardValue8.Text,
                Date: DataCardValue9.SelectedDate,
                'Trained Material Name': DataCardValue10.Text
            }
      )
)

On your side, you should type:

ForAll(
      EmployeeNameComboBox.SelectedItems,   /* <-- The EmployeeNameComboBox represents the ComboBox within the Employee Name data card in your Edit form */
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: TitleDataCard.Text,
                'Employee Name': Table({
                    Claims:"i:0#.f|membership|" & Lower(Email), 
                    DisplayName: DisplayName,
                    Email: Email, 
                    Department:"", 
                    JobTitle:"", 
                    Picture:""
                }),
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMaterialNameDataCard.Text
            }
      )
)

When you press the "Submit" button, the data would be entered into your SP List as below:7.JPG

 

More details about the Patch function and ForAll function in PowerApps, please check the following article:

Patch function

ForAll function

 

In addition, if you custom a form within your SP list using PowerApps, please consider set the OnSave proeprty of the SharePointIntegration control to above ForAll formula. Set the OnSave property of the SharePointIntegration control to following:

ForAll(
      Trim(Split(EmployeeNameDataCard.Text, ",")),
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: DataCardValue7.Text,
                'Employee Name': Result,
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMeterialNameDataCard.Text
            }
      )
);
RequestHide()

Or

ForAll(
      EmployeeNameComboBox.SelectedItems,
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: TitleDataCard.Text,
                'Employee Name': Table({
                    Claims:"i:0#.f|membership|" & Lower(Email), 
                    DisplayName: DisplayName,
                    Email: Email, 
                    Department:"", 
                    JobTitle:"", 
                    Picture:""
                }),
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMaterialNameDataCard.Text
            }
      )
);
RequestHide()

 

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

6 REPLIES 6
tugba
Helper I
Helper I

You can use patch for adding data to sharepoint. For multiple data use concurrent with patch.For example

Concurrent(
Patch(
DatabaseName;
Defaults(DatabaseName);
{
Plant/Location: txtplant.Text;
Date: txtDate.Text;
TrainedMaterialName: txtmetarial.Text;
EmployeeName: txtEmpName1;
}
);
Patch(
DatabaseName;
Defaults(DatabaseName);
{
Plant/Location: txtplant.Text;
Date: txtDate.Text;
TrainedMaterialName: txtmetarial.Text;
EmployeeName: txtEmpName2;
}
);

Patch(
DatabaseName;
Defaults(DatabaseName);
{
Plant/Location: txtplant.Text;
Date: txtDate.Text;
TrainedMaterialName: txtmetarial.Text;
EmployeeName: txtEmpName3;
}
);

Patch(
DatabaseName;
Defaults(DatabaseName);
{
Plant/Location: txtplant.Text;
Date: txtDate.Text;
TrainedMaterialName: txtmetarial.Text;
EmployeeName: txtEmpName4;
}
)

)

v-xida-msft
Community Support
Community Support

Hi @AMR80 ,

Which type column do you used to store the Employee Name within your SP List? A Text type column or Person type column?

How do you choose the Employee Name within your app? Could you please share a screenshot about your app's configuration?

 

Based on the needs that you mentioned, I think the combination of Patch function and ForAll function could achieve your needs (The SubmitForm() function could not achieve your needs). 

1. If the "Employee Name" is a Text Type column in your SP List (use Single Text column to store the Employee Name):2.JPG

 

3.JPG

Set the OnSelect property of the "Submit" button to following formula:

ForAll(
      Trim(Split(DataCardValue11.Text, ",")),
      Patch(
            '20190708_TrainingList',
            Defaults('20190708_TrainingList'),
            {
                Title: DataCardValue7.Text,
                'Employee Name': Result,
                'Plant/Location': DataCardValue8.Text,
                Date: DataCardValue9.SelectedDate,
                'Trained Material Name': DataCardValue10.Text
            }
      )
)

On your side, you should type:

ForAll(
      Trim(Split(EmployeeNameDataCard.Text, ",")),
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: DataCardValue7.Text,
                'Employee Name': Result,
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMeterialNameDataCard.Text
            }
      )
)

When the "Submit" button is pressed, the data would be inserted into your SP List as below:4.JPG

 

2. If the "Employee Name" is a Person Type column in your SP List, which has been enabled "Allow Multiple selections" (Use Person type column to store the Employee Name):5.JPG

 

6.JPG

Set the OnSelect property of the "Submit" button to following:

ForAll(
      DataCardValue14.SelectedItems,
      Patch(
            '20190708_TrainingList',
            Defaults('20190708_TrainingList'),
            {
                Title: DataCardValue7.Text,
                'Employee Names': Table({
                    Claims:"i:0#.f|membership|" & Lower(Email), 
                    DisplayName: DisplayName,
                    Email: Email, 
                    Department:"", 
                    JobTitle:"", 
                    Picture:""
                }),
                'Plant/Location': DataCardValue8.Text,
                Date: DataCardValue9.SelectedDate,
                'Trained Material Name': DataCardValue10.Text
            }
      )
)

On your side, you should type:

ForAll(
      EmployeeNameComboBox.SelectedItems,   /* <-- The EmployeeNameComboBox represents the ComboBox within the Employee Name data card in your Edit form */
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: TitleDataCard.Text,
                'Employee Name': Table({
                    Claims:"i:0#.f|membership|" & Lower(Email), 
                    DisplayName: DisplayName,
                    Email: Email, 
                    Department:"", 
                    JobTitle:"", 
                    Picture:""
                }),
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMaterialNameDataCard.Text
            }
      )
)

When you press the "Submit" button, the data would be entered into your SP List as below:7.JPG

 

More details about the Patch function and ForAll function in PowerApps, please check the following article:

Patch function

ForAll function

 

In addition, if you custom a form within your SP list using PowerApps, please consider set the OnSave proeprty of the SharePointIntegration control to above ForAll formula. Set the OnSave property of the SharePointIntegration control to following:

ForAll(
      Trim(Split(EmployeeNameDataCard.Text, ",")),
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: DataCardValue7.Text,
                'Employee Name': Result,
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMeterialNameDataCard.Text
            }
      )
);
RequestHide()

Or

ForAll(
      EmployeeNameComboBox.SelectedItems,
      Patch(
            'YourSPList',
            Defaults('YourSPList'),
            {
                Title: TitleDataCard.Text,
                'Employee Name': Table({
                    Claims:"i:0#.f|membership|" & Lower(Email), 
                    DisplayName: DisplayName,
                    Email: Email, 
                    Department:"", 
                    JobTitle:"", 
                    Picture:""
                }),
                'Plant/Location': PlantLocationDataCard.Text,
                Date: DateDataCard.SelectedDate,
                'Trained Material Name': TrainedMaterialNameDataCard.Text
            }
      )
);
RequestHide()

 

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

AMR80
Helper I
Helper I

Thank you so much; sorry for the delay. I wasn't getting the email notifications on replies. I'm going to try this and get back to you. To confirm... the Trainee name is a person column.

Well, I now see that the Trainee person column in SharePoint is being Indexed and will not allow me to choose 'Allow Multiple Selections'. Do you see any harm in not indexing it, so I can work your proposed solution? 

This is great!

 

Can you have multiple splits?

For example Description, Price, and Quantity?

 

thanks

Tony

Hi! your provided solution worked perfectly. I was wondering if it's possible to split more than one multivalue items using one patch function. 

For example in the solution you provided you split Employee name using delimiter into new rows. 

What if I had multiple values for employee name and plants. Is it possible to achieve it in one patch function?

 

Thanks in advance! 

 

Regards

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,829)