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

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

Highlighted
Frequent Visitor

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

Thanks Drrickryp.

 

 

 

 

Highlighted
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

Highlighted
Power Apps
Power Apps

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.

Highlighted
Frequent Visitor

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,462)