cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UB400
Level 10

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
Level 10

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

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
Level 10

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

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.

UB400
Level 10

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


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
Level 10

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

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
Level 10

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

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

UB400
Level 10

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

@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))})
UB400
Level 10

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

@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
Level 10

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,170)