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.

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.

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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 212 members 3,628 guests
Please welcome our newest community members: