Creating a Collection

[mrizz]

I have a SharePoint List with 3 columns, a location, Date and Value column. I want to create a collection, with the same 3 columns, but only has one row for each distinct location and Date, with the sum of the values corresponding to that location and Date.

Here is an example:

in SharePoint:  Location            Date         Value

                       New York      12/23/2020       5

                       New York      12/23/2020       7

                       New York      12/25/2020       2

                            L.A            12/23/2020       3

                            L.A            12/23/2020       1

                            L.A            12/26/2020       10

Desired Collection Output:   Location            Date          Value

                                            New York       12/23/2020      12

                                            New York       12/25/2020       2

                                                  L.A            12/23/2020      4

                                                  L.A            12/26/2020     10

Is this possible, and if so how would I do it?

The goal is to have the collection be created either OnStart of the app, or OnVisible of the first screen.

Thanks

[Drrickryp]

Hi @mrizz 

Although it is not exactly what you are requesting, I think I would use the GroupBy() function to group on cities and show the data in a gallery with a subgallery.  Something like the screenshot below.  Here the GroupBy() is used on a contacts list to create the collection in the OnVisible property of the screen, as shown in the Header.  The Gallery Items property is "contactsbystate" and the subgallery Items property is "restofdata".  The company name and city are items in the subgallery.  With your data, you would put the Sum() function in the main gallery and sum the values in the subgallery.

[v-qiaqi-msft · Accepted solution]

Hi@mrizz,

Based on the issue that you mentioned, do you want to create a collection to store the grouped result?

Here is a workaround, please check as follows.

In my scenario, I have a SP like this:

Set the OnStart property of the App as below:

Collect(
    ColTest,
    ShowColumns(
        AddColumns(
            GroupBy(
                Test1224,
                "Location",
                "Date",
                "Combine"
            ),
            "SumValue",
            Sum(
                Combine,
                Value
            )
        ),
        "Location",
        "Date",
        "SumValue"
    )
)

 

Regards,

Qi 

[mrizz]

Hello, thank you this formatting works for me but i have run into an issue. There will only be a max of 1,850 rows in the collection, under the 2000 row limit i have set, but the sharepoint list it is reading through has 28,000 rows, and I think it is not reading through the whole list. Is there a work around for this?