Split data into a SharePoint from a Concat field within a form

[ClarkyPA365]

Hi,

I'm using a Combobox for a people picker search (Office365Users)

From the search criteria I'm using Concat in my form but I then want to save the data into SP on separate rows.  As i need to send Email out to these people and get approvals from each one that a task has been completed. At the moment its displaying the data in one row

Is this possible??

Regards

Chris

[Adrian_Celis]

Hi @ClarkyPA365 

You can use the Split function to separate data using a delimiter (in your case it's a comma). Then use ForAll to patch each item.

Example:

ForAll(
Split(YourEmailAddressesHere,","),
Patch(YourDataSourceHere,Defaults(YourDataSourceHere),
   {
     ColumnName1: Result
   }
)

[ClarkyPA365]

Hi @Adrian_Celis 

Thank you for your reply.

So using this formula against the OnSelect function of the Save button I need to use the Split for all my fields that have multiple data in them and for the fields which only contain one bit of data do I still use the patch function?

Can you provide a little bit more of how I would set the formula to enable me to save the data contained in the below screen shot on.  Is it possible to have the single bits of data such as Supervision Type, Supervisor email, Other reason to show on in the separate rows for each of the Staff Names.  See Screen of SP site of how is now and how would like 😊

SP as is

SP would like

Regards

Chris

[Adrian_Celis]

Hi @ClarkyPA365 

Okay so are the Email, Staff Name, Ward Name, Division fields always the same number of items?
Like if there are 3 Emails, there should always be 3 Staff Names?
And then Supervisor, Supervisor Email and Supervision Type is always one item?

If so then you can get the count of one of fields that have multiple items. And from that count, use a ForAll to
patch each of them.

With({RowCount: CountRows(Split(EmailFieldControlName,","))},

ForAll(Sequence(RowCount),
   Patch(YourDataSourceHere,Defaults(YourDataSourceHere),
      {
        Supervisor: SupervisorControlName.Text,
	SupervisorType: SupervisorTypeControlName.Text,
	Email: Last(FirstN(Split(EmailFieldControlName,","),RowCount))
	SupervisorEmail: SupervisorEmailControlName.Text,
	StaffName: Last(FirstN(Split(StaffNameFieldControlName,","),RowCount)),
	WardName: Last(FirstN(Split(WardNameFieldControlName,","),RowCount)),
	Division: Last(FirstN(Split(DivisionFieldControlName,","),RowCount)),
      }
   )
)
)

In this example code, you will get the same Supervisor for all rows because you set it the same for all 3. And
then in the fields such as Email, it will get a specific row in the split according to the number used in the ForAll. In this formula I am using the With function to set a variable so that I don't have write the whole countrows over again

[ClarkyPA365]

Hi @Adrian_Celis ,

Yes the the Supervisor, Group Type, Supervisor Email and Supervision Subtype will be the same for all records.  The Supervision Subtype is a Choice field. would this require SelectedItem. SelectedResult??

And yes if the Staff Name, Ward Name Division Email will have the same number of records.  So if three selected then three records will be seen in those fields.  Same if 4, 5, 6 , etc

I've tried your formula and getting an error message

The type of this argument 'Division' does not match the expected type 'Text'. Found type 'Record'.  The Patch function has some invalid arguments.

The Division column in SP is a Single line of text

With({RowCount: CountRows(Split(Email,","))},
ForAll(Sequence(RowCount),
Patch('Clinical Supervision Form',Defaults('Clinical Supervision Form'),
{
Supervisor:Supervisor.Text,
'Supervision Subtype':SupervisionSubtype.Selected,
'Other Reason':OtherReason.Text,
'Supervision Type':SupervisionType.Text,
SupervisorEmail: SupervisorEmail.Text,
'Ward Name':Last(FirstN((Split(WardName,","),RowCount)),
Division:Last(FirstN(Split(Division,","),RowCount)),
'Reflections Actions':Last(FirstN(Split(ReflectionsActions,","),RowCount)),
Email:Last(FirstN(Split(Email,","),RowCount)),
'Staff Name':Last(FirstN(Split(StaffName,","),RowCount)),
}
)
)
)

Regards

Chris

[Adrian_Celis]

Hi @ClarkyPA365 

My bad. It is expecting text so please add a .Result at the end of each split.

'Ward Name':Last(FirstN((Split(WardName,","),RowCount)).Result,
Division:Last(FirstN(Split(Division,","),RowCount)).Result,
'Reflections Actions':Last(FirstN(Split(ReflectionsActions,","),RowCount)).Result,
Email:Last(FirstN(Split(Email,","),RowCount)).Result,
'Staff Name':Last(FirstN(Split(StaffName,","),RowCount)).Result,

[ClarkyPA365]

Hi @Adrian_Celis 

Thank you.

I still get an error message of 
Unexpected characters.  The formula contains 'ParentClose' where 'Comma' is expected .  This appears when no Comma is added after the last .Result
If I add a Comma then the error message reads The Specified column '_' does not exist.  The Column with the most similar name is 'ID'

[Adrian_Celis]

Hi @ClarkyPA365 

Must be just a missing comma or parenthesis. I tried it now on PowerApps and it did miss a parenthesis or two. Can you try this?

With({RowCount: CountRows(Split(Email,","))},
ForAll(Sequence(RowCount),
Collect(collText,
{
Supervisor:Supervisor.Text,
'Supervision Subtype':SupervisionSubtype.Selected,
'Other Reason':OtherReason.Text,
'Supervision Type':SupervisionType.Text,
SupervisorEmail: SupervisorEmail.Text,
'Ward Name':Last(FirstN(Split(WardName,","),RowCount)).Result,
Division:Last(FirstN(Split(Division,","),RowCount)).Result,
'Reflections Actions':Last(FirstN(Split(ReflectionsActions,","),RowCount)).Result,
Email:Last(FirstN(Split(Email,","),RowCount)).Result,
'Staff Name':Last(FirstN(Split(StaffName,","),RowCount)).Result
}
)
)
)

[ClarkyPA365]

Hi @Adrian_Celis 

How does the information get added to SP as the Patch functions is now removed and Collect has been added?

[Adrian_Celis · Accepted solution]

Hi @ClarkyPA365 

Sorry I was testing it that's why I replaced it with a collect. Just put the patch back on.

With({RowCount: CountRows(Split(Email,","))},
ForAll(Sequence(RowCount),
Patch('Clinical Supervision Form',Defaults('Clinical Supervision Form'),
{
Supervisor:Supervisor.Text,
'Supervision Subtype':SupervisionSubtype.Selected,
'Other Reason':OtherReason.Text,
'Supervision Type':SupervisionType.Text,
SupervisorEmail: SupervisorEmail.Text,
'Ward Name':Last(FirstN(Split(WardName,","),RowCount)).Result,
Division:Last(FirstN(Split(Division,","),RowCount)).Result,
'Reflections Actions':Last(FirstN(Split(ReflectionsActions,","),RowCount)).Result,
Email:Last(FirstN(Split(Email,","),RowCount)).Result,
'Staff Name':Last(FirstN(Split(StaffName,","),RowCount)).Result
}
)
)
)