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

[AMR80]

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? 

[tugba]

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 · Accepted solution]

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):

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:

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):

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:

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,

[AMR80]

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.

[AMR80]

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? 

[tonyrmclabour]

This is great!

Can you have multiple splits?

For example Description, Price, and Quantity?

thanks

Tony

[Anonymous]

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

[XYZ__]

I have exactly the same question.

Has anyone found an answer to this? If so, can you please share it here?

[XYZ__]

I have exactly the same question.

Has anyone found an answer to this? If so, can you please share it here?

[AS_kls]

Very good solution for inserting multiple lines.
Do you also have an idea how it can be solved, how via the Power App then the lines can be edited again? For example, the title for all lines (but the people etc. should remain the same)?