cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UB400
Kudo Kingpin
Kudo Kingpin

ForAll and Patch. Split a Column into three and patch the resulting three values into new Columns

I'm using a Sharepoint list as a Datasource. In my List I have a Column whose value i want to split into three, and then save the three values into individual columns, how can I do that with ForAll and Patch, or is there another way?

 

Basically I have a SingleText Column called ReminderDates, which has as it's value "20/07/2017;25/07/2017;30/07/2017"

 

I can separate that into three separate values using the Len and Mid functions, but I can't see how I can then patch these values into three seprate columns in the same Record.

 

I was thinking along the lines of

 

ForAll(Datasource,Patch(Datasource,

 

Syntax for Patch is Patch(Source,Record,update,...) So I can't see how to add the record value as it would be different for each record in the DataSource.

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Community Champion
Community Champion

Hi again @UB400

 

The much more elegant and faster performing syntax is very simple, try this:

 

UpdateIf(Datasource, true, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})

Please let me know how you get on

View solution in original post

7 REPLIES 7
Meneghino
Community Champion
Community Champion

Hi @UB400

 

You are 99% there, try this:

 

ForAll(Datasource,
Patch(Datasource, {ID: Datasource.ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})
)

Please let me know if this does not work, since I am not sure you can patch to the same source you refer to in the ForAll.


Thanks @Meneghino for getting back to me so quickly.

When I enter the formula, I'm getting an error to the effect "The type of this arguement 'ID' does not match the expected type 'Number'. Found type 'Table'.

With a Gallery one would be able to specify the record using Gallery.Selected, is there a way to do that using ForAll?

If this doesn't work, would doing a Collect to a Collection and then a Collect back to the DataSource work?

I've not tried to "Collect" to a populated DataSource so not sure if it would update correctly or end up appending records?

@Meneghino wrote:

Hi @UB400

 

You are 99% there, try this:

 

ForAll(Datasource,
Patch(Datasource, {ID: Datasource.ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})
)

Please let me know if this does not work, since I am not sure you can patch to the same source you refer to in the ForAll.

Meneghino
Community Champion
Community Champion

Hi @UB400

OK, there are two issues;

1) The correct syntax is without reference to the table, my oversight, apologies.  To answer you question, you just use the column name and it takes the current value in the ForAll loop.

So the correct syntax would be this:

Patch(Datasource, {ID: ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})

2) The problem is that, as I suspected, you cannot patch to the same list that you are referring to in the ForAll, so you get the below error (see picture).  To get around this you can collect a copy in the collection and use that as the basis of your ForAll.  Something like this:

ClearCollect(CopyDataSource, DataSource);
ForAll(CopyDatasource,
Patch(Datasource, {ID: ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})
)

I know the ID:ID looks silly, but I just tried it and it works.

 

However, there may be an even better solution using Update, I will try and post back in 5 minutes if that works.

 

Cheers.

 

AAA.png

Meneghino
Community Champion
Community Champion

Hi again @UB400

 

The much more elegant and faster performing syntax is very simple, try this:

 

UpdateIf(Datasource, true, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})

Please let me know how you get on

View solution in original post

@MeneghinoDude, you're a GENIUS!!! Awesome it worked really well. Thanks, I really appreciate it, not just the code but also the speed of your response .

 

As I was converting to Date format as well, here's the Formula I used if anyone else finds themselves in a similar situation

 

UpdateIf(DataSource, true, {AlertDate1:DateValue(Left(AlertDates,10)), AlertDate2:DateValue(Mid(AlertDates,12,10)), AlertDate3:DateValue(Mid(AlertDates,23,10))})

@MeneghinoI have another scenario and I could really do with help here:

 

I have three lists:

 

List Customers: which has CustomerID and CustomerName Columns

List InvoiceHeaders: which has CustomerID and InvoiceID Columns

List InvoiceDetails: Which has InvoiceID Columns

 

I want to Create a new InvoiceDetails2 list into which I want to add a Column CustomerName and add the corresponding CustomerName value from List Customers.

 

List Customers is associated with List Invoice Headers by CustomerID, while Invoice Header and InvoiceDetails are associated by InvoiceID

 

How can I get the Customer Name into the InvoiceDetails list?

 

Thanks.

Meneghino
Community Champion
Community Champion

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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
Top Kudoed Authors
Users online (948)