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

Storing a collection to an SQL as blob

Hello,

 

I am thinking about storing a Collection (GalleryXYZ.Allitems) to an SQL Database Field with PowerApps.

But unfortunately I cannot find a way, neither by a simple patch nor by using the SaveData function. (or combine both).

 

Is there a (simple) way to get a collection (all items of a gallery element) in a blob data field, as I can do with an image?

 

As I have understood, a collection has the form of a data table and therefore cannot be stored or patched simply as I can do that with a simple image.

 

Any ideas to manage such a transaction?

 

Any experiences or suggestions?

Thank you very much in advance!

R.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Storing a collection to an SQL as blob

There's no way currently to store a collection in a blob - feel free to create a new feature request in the PowerApps Ideas board for this.

 

You can "serialize" a collection into a string, using the Concat function; but you'll need to specify the format of the data, and to "deserialize" it back into a collection, you'll need to parse (possibly using the Split function) it back.

 

For example, if you have this simple table:

ForumPost001.png

Then you can "serialize" the gallery values by using a custom format like this one. Notice that I'm using '-' and '$' as separators; if your data has those, then you'll need to use something else, or escape them if they appear:

Set(
    serialized,
    Concat(
        Gallery1.AllItems,
        Name & "-" & Species & "-" & Age,
        "$"))

That string can now be saved into a SQL column. When it's time to deserialize the value, you can use something similar to the expression below:

ClearCollect(
    deserialized,
    ForAll(
        ForAll(
            Split(serialized, "$"),
            Split(Result, "-")),
        {
            Name: First(Value).Result,
            Species: Last(FirstN(Value, 2)).Result,
            Age: Last(Value).Result
        }
    )
)

That will "re-create" the collection that you can then bind to another (or the same) gallery.

View solution in original post

2 REPLIES 2
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Storing a collection to an SQL as blob

There's no way currently to store a collection in a blob - feel free to create a new feature request in the PowerApps Ideas board for this.

 

You can "serialize" a collection into a string, using the Concat function; but you'll need to specify the format of the data, and to "deserialize" it back into a collection, you'll need to parse (possibly using the Split function) it back.

 

For example, if you have this simple table:

ForumPost001.png

Then you can "serialize" the gallery values by using a custom format like this one. Notice that I'm using '-' and '$' as separators; if your data has those, then you'll need to use something else, or escape them if they appear:

Set(
    serialized,
    Concat(
        Gallery1.AllItems,
        Name & "-" & Species & "-" & Age,
        "$"))

That string can now be saved into a SQL column. When it's time to deserialize the value, you can use something similar to the expression below:

ClearCollect(
    deserialized,
    ForAll(
        ForAll(
            Split(serialized, "$"),
            Split(Result, "-")),
        {
            Name: First(Value).Result,
            Species: Last(FirstN(Value, 2)).Result,
            Age: Last(Value).Result
        }
    )
)

That will "re-create" the collection that you can then bind to another (or the same) gallery.

View solution in original post

ReinerDD
Level: Powered On

Re: Storing a collection to an SQL as blob

Many thanks, that solved the issue perfectly!

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps 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

Top Solution Authors
Top Kudoed Authors
Users online (5,222)