cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
PowerApps Staff CarlosFigueira
PowerApps 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
PowerApps Staff CarlosFigueira
PowerApps 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

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,313)