cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Hellow
Level: Powered On

Combine rows in collection, separated by commas, so that Patch/Collect only one row

The current method of saving collection data to a datasource (sharepoint list, excel in one drive) 

via patch or collect is extremely slow. Past suggestions of using drop box save only a few seconds.

 

Someone mentioned in a past post it is slow because excel (in my case) will create a new version for every record.

 

Is it possible to combine every row into one row, separated by commas, so that patch or collect will only act on one row? I intend to separate the rows via VBA in excel afterwards.

 

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

You can use the Concat function to combine multiple records into a single string. For example, if you have this collection:

ClearCollect(
    Contacts,
    { Name: "John Doe", Age: 33 },
    { Name: "Jane Roe", Age: 31 },
    { Name: "Joan Coe", Age: 34 },
    { Name: "Jim Poe", Age: 32 })

Then you can use something like this expression:

Concat(Contacts, Name & "|" & Age, "$")

to combine all the rows in a string like this one:

John Doe|33$Jane Roe|31$Joan Coe|34$Jim Poe|32

Notice that you can use other separators (instead of '|' and '$' that I used above).

6 REPLIES 6
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

You can use the Concat function to combine multiple records into a single string. For example, if you have this collection:

ClearCollect(
    Contacts,
    { Name: "John Doe", Age: 33 },
    { Name: "Jane Roe", Age: 31 },
    { Name: "Joan Coe", Age: 34 },
    { Name: "Jim Poe", Age: 32 })

Then you can use something like this expression:

Concat(Contacts, Name & "|" & Age, "$")

to combine all the rows in a string like this one:

John Doe|33$Jane Roe|31$Joan Coe|34$Jim Poe|32

Notice that you can use other separators (instead of '|' and '$' that I used above).

shrinkTech
Level: Powered On

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

Hi carlos

You have no idea how helpful was your post. I am new to Powerapps, but old to sharepoint. I never got along with well with excel Smiley Tongue and its formulas which led to our brokeup. Smiley Very Happy

 

My requirement was to save the tabular data present in powerapps to the same list in multiline field and then retrieve back from collection while showing as display form. I had been rubbing my head for 4 days until i came across your post. now I am able to save it to SharePoint list multiline field and now have to figure how well can I pull the information and show back it to a powerapps gallery. 

If you have an idea about that you can please guide me with formula. Appreciate your help.

 

Regards

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

Glad to help Smiley Happy

To recreate the collection from the "serialized" value, you can use the Split and ForAll functions, along with some of the functions that will help breaking down the strings, such as Left/Mid/Right. So if the value is stored in the variable (or column) called 'stored', then you can use a logic similar to this one below to recreate a collection:

ClearCollect(
    Contacts2,
    ForAll(
        Split(stored, "$"),
        {
            Name: Left(Result, Find("|", Result) - 1),
            Age: Value(Mid(Result, Find("|", Result) + 1))
        }))

If you have more than two items in each row, then it may be worth looking into using the Split function again for the rows, even if it may make it a little more complicated:

ClearCollect(
    Contacts2,
    ForAll(
        Split(stored, "$"),
        {
            Name: First(Split(Result, "|").Result).Result,
            Age: Value(Last(Split(Result, "|").Result).Result)
        }))

The attached app shows both wais in use.

 

shrinkTech
Level: Powered On

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

Hi carlos

thanks for the code. I did try split but for some reason it does not work and gives weird error like - Invalid argument(table). Expecting text.

 

Actually my multiline field with contain data like - abc,12#def,34#dfg,65

Here I have already split the field value based on '#' seperator. Now I need to split down further based on ',' and populate the values to gallery data source (collection) and show the values 'abc' and '12' in different columns. So the final structure should look like -

 

Item     Count

abc       12

def       34

dfg      65

 

Let me know if it is possible or not. I have wasted too much of my time already. this was way simpler in Infopath or is simpler using Jquery/SPfx or any other custom coding mechanism.

shrinkTech
Level: Powered On

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

you know what...  YOU ROCK!! Smiley Happy

The second one worked like charm. that's exactly what i needed. and I found error in my code as well. I was supposed to use result after split and then perform first or last on it. and then get the result of first or last.

 

thanks a lot Carlos. 

shrinkTech
Level: Powered On

Re: Combine rows in collection, separated by commas, so that Patch/Collect only one row

Hi carlos

I would appreciate your help again for answer to one question.

 

As of today, is it still required to publish powerapps for list form to be published every 6 months or is that issue been fixed already?