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

(0) Share

Report

Posted on by Community Power Pla...

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

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

CarlosFigueira on at

Like (6)

Report
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).

Was this reply helpful? Yes No
shrinkTech 40 on at

Like (0)

Report

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 😛 and its formulas which led to our brokeup. 😄

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

Was this reply helpful? Yes No
CarlosFigueira on at

Like (3)

Report
Glad to help 🙂

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.

ForumThread163877...

Was this reply helpful? Yes No
shrinkTech 40 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
shrinkTech 40 on at

Like (0)

Report

you know what... YOU ROCK!! 🙂
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.

Was this reply helpful? Yes No
shrinkTech 40 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
dreamweaver9962 on at

Like (0)

Report

Hi
If I'm storing the data as simple HTML table, is there a way to convert that into a collection. I thought of building character separated string initially then I find a easy way of building HTML table from collection (gallery). Now I want to do the reverse process.

Was this reply helpful? Yes No
Valentijn 21 on at

Like (0)

Report

Result is't a Power Function anymore seePower Fx formula reference for Power Apps - Power Platform | Microsoft Learn
Use instead 'Value'

Was this reply helpful? Yes No