cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oyster
Frequent Visitor

Convert String to Collection

I have a formula that will convert a collection to a string, like so:

 

Mid(Concat(OptionsCollection, "," & Option & ":" & QTY), 3, 1000)

which converts my collection:

OptionsCollection

|     Option    |    QTY     |

|  7147637    |       1       |

|  84622741   |      2       |

 

To this string:
7147637:1,84622741:2

I plan to store this string in a single cell on a connected data source to recall later. I would like to take a string like that and somehow "load" it into OptionsCollection, basically reversing the conversion i just did, allowing me to store and retreive collection data from a single cell. Any help?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Oyster

The likely cause of this problem is that the data types of the fields in in item that you're adding doesn't match the data types of the fields where you used OptionsCollection originally.

For example, if 'Option' were a numeric field in the original place where you used OptionsCollection, you could resolve this problem by converting option to a number like so:

ClearCollect(
    OptionsCollection,
    ForAll(
        Split("3052260:1", ","),
        {
            Option: Value(Left(Result, Find(":", Result) - 1)),
            QTY: Value(Mid(Result, Find(":", Result) + 1))
        }))

The code that you posted is syntactically correct because if you were to ClearCollect the data to a new collection (OptionsCollection2 for example), you should find that you don't receive the error.

 

View solution in original post

3 REPLIES 3
timl
Super User
Super User

Hi @Oyster

In the thread beneath, @CarlosFigueira provides an excellent example on how you can you recreate a collection from a concatenated string.

https://powerusers.microsoft.com/t5/General-Discussion/Combine-rows-in-collection-separated-by-comma...

Carlos uses the | and & symbols to separate the fields and rows. In your example, you would replace these references with the , and : symbols.  Hopefully, this will make sense but feel free to post back if you need any further clarification.

Oyster
Frequent Visitor

Thanks for the speedy reply!

Using that link i came up with this:

ClearCollect(
OptionsCollection,
ForAll(
Split("3052260:1", ","),
{
Option: Left(Result, Find(":", Result) - 1),
QTY: Value(Mid(Result, Find(":", Result) + 1))
}))

The string "3052260:1" is just what im using for a test, ill reference a cell later, but i am getting this error:
Incompatible type. The item you're trying to put into a collection has a type that's not compatible with the collection

Hi @Oyster

The likely cause of this problem is that the data types of the fields in in item that you're adding doesn't match the data types of the fields where you used OptionsCollection originally.

For example, if 'Option' were a numeric field in the original place where you used OptionsCollection, you could resolve this problem by converting option to a number like so:

ClearCollect(
    OptionsCollection,
    ForAll(
        Split("3052260:1", ","),
        {
            Option: Value(Left(Result, Find(":", Result) - 1)),
            QTY: Value(Mid(Result, Find(":", Result) + 1))
        }))

The code that you posted is syntactically correct because if you were to ClearCollect the data to a new collection (OptionsCollection2 for example), you should find that you don't receive the error.

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,417)