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

How to create a collection from SQL data grouped together in one field?

Hello all,

 

I'm attempting to break up a string that I've brought into PowerApps from SQL as collection. The string contains data surrounded by quotes. For example, the string below has three useful values to me; 2, 510, and 516.

 

a:3:{i:0;s:1:"2";i:1;s:3:"510";i:2;s:3:"516";}

 

Screenshot_1.jpg

 

Is there a way to create a collection from the data that is surrounded by quotes?

 

I would like to create a collection like the following:

 

Record1      2

Record1      510

Record1      516

Record2      2

Record3      2

.

.

.

I'm thinking that this may need to be done in SQL. My PowerApp is utilizing some Sharepoint lists if there is a way to collect the data in sharepoint.

 

Thanks,

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to create a collection from SQL data grouped together in one field?

Hi @AlexHobbs,

You would use the Split function to do that.  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-split.  The syntax is 

Split( Text, Separator )

  • Text - Required. Text to split.
  • Separator - Required. Separator to use in splitting the string. Can be zero, one, or more characters.

The example in the documentation is 

Split( "Apples, Oranges, Bananas", "," ) Splits the different fruits apart, based on the comma separator. The split is performed based on only the comma and not the space after it, resulting in a space at the front of " Oranges" and " Bananas".

 

The result is a table of substrings that you can use as input where ever a table can be used, for example in the Items property of a gallery or dropdown control.

If you are going to use your data for a lookup table and do not require subsequent modifications or addition of new data, I would suggest exporting it to Excel, using PowerQuery to shape the table and importing into PowerApps as static data.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: How to create a collection from SQL data grouped together in one field?

Hi @AlexHobbs,

You would use the Split function to do that.  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-split.  The syntax is 

Split( Text, Separator )

  • Text - Required. Text to split.
  • Separator - Required. Separator to use in splitting the string. Can be zero, one, or more characters.

The example in the documentation is 

Split( "Apples, Oranges, Bananas", "," ) Splits the different fruits apart, based on the comma separator. The split is performed based on only the comma and not the space after it, resulting in a space at the front of " Oranges" and " Bananas".

 

The result is a table of substrings that you can use as input where ever a table can be used, for example in the Items property of a gallery or dropdown control.

If you are going to use your data for a lookup table and do not require subsequent modifications or addition of new data, I would suggest exporting it to Excel, using PowerQuery to shape the table and importing into PowerApps as static data.

View solution in original post

AlexHobbs
Level: Powered On

Re: How to create a collection from SQL data grouped together in one field?

Thanks Drrickryp.

 

 

 

 

Super User
Super User

Re: How to create a collection from SQL data grouped together in one field?

Hi @AlexHobbs,

You cannot change the underlying structure of a datasource in PowerApps, but you can create a collection from the data and reshape the collection using AddColumns(), RenameColumns(), DropColumns() and ShowColumns() functions, see table-shaping

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: How to create a collection from SQL data grouped together in one field?

Hello,

This is a good scenario for the new functions that were just added in this week's release that deal with regular expressions. By using the MatchAll function, you can extract the data between the quotes, with an expression like this one:

learCollect(
    Coll,
    { record: 1, scope_id: "a:3:{i:0;s:1:""2"";i:1;s:3:""510"";i:2;s:3:""516"";}" },
    { record: 2, scope_id: "a:1:{i:0;s:1:""2"";}" },
    { record: 3, scope_id: "a:1:{i:0;s:1:""2"";}" },
    { record: 4, scope_id: "a:2:{i:0;s:1:""3"";i:1;s:1:""5"";}" },
    { record: 5, scope_id: "a:2:{i:0;s:1:""4"";i:1;s:2:""27"";}" }
);
ClearCollect(
    Coll2,
    AddColumns(
        Coll,
        "BetweenQuotes",
        MatchAll(
            scope_id,
            "\""(?<betweenquotes>[^\""]+)\"""
        ).betweenquotes
    )
)

The attached app shows how you would use that expression in a gallery, for example.

AlexHobbs
Level: Powered On

Re: How to create a collection from SQL data grouped together in one field?

Thanks Carlos. 

 

This is much easier that the work around that I came up with. I'll check out the new functions and incorporate them into my PowerApp.

 

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