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

How to add a Lookup column to a Collection with AddColumns

Hello Everyone!

 

First of all you need to know that the Inventory SharePoint List has more than 2000 items.

And I also know that there is no need to create that much collections, but I wanted to learn step by step.

I would like to achieve the following:

 

There is a SharePoint list called inventory.

 

kép.png

I have created a new collection called InventoryFiltered, which is filtered to only to Sales

 

DepartmentName = "Sales" (This is a Variable and the type is Text)
ClearCollect(InventoryFiltered; Filter(Inventory; Department.Value = DepartmentName))

 

 kép.png

So I needed an another Collection with only the Quantity and the Device

 

ClearCollect(InventoryFilteredAgain; ShowColumns(InventoryFiltered; "Device"; "Quantity"))

 

So I got the Collection on the left and I would like to achieve the results on right.
(I need the Device value as text and not as a Table/Record.)

kép.png

 

I have tried this to get only the Value to my new Collection, but its not working

 

ClearCollect(InventoryFilteredAgain; ShowColumns(InventoryFiltered; "Device".Value; "Quantity"))

 

 

After that I would like to GroupBy the Device and Sum the Quantity

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby#aggregate-re...

Currently I also have no luck with this.

 

kép.png

 

Thank you for your help in advance.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Tibor4 ,

How many records whose Department field value is "Sales" in your Inventory List? More than 2000?

 

Firstly, the ShowColumns function is not a Delegable function in PowerApps, so if there are more than 2000 records whose Department field value is "Sales" in your Inventory List, you may face a Delegation issue with your ShowColumns function.

 

If the amount of records whose Department field value is "Sales" is not more than 2000, I think the ForAll function could achieve your needs. Please consider take a try with the following formula:

Clear(InventoryFiltered);;
ForAll(
       Filter(Inventory; Department.Value = DepartmentName);
       Collect(
               InventoryFiltered;
               {
                 Device: Device.Value;
                 Quantity: Quantity
               }
       )
);;

When you execute above formula, the InventoryFiltered collection would be populated with two columns -- "Device" & "Quantity".

 

In addition, if you want to group by above collection based on Device column, and Sum the Quantity column value, please take a try with the following formula:

ClearCollect(
             InventoryFilteredAgain;
             DropColumns(
                   AddColumns(
                        GroupBy(
                                 InventoryFiltered;   // Above filtered collection
                                 "Device";
                                 "GroupData"
                        );
                        "Quantity";
                        Sum(GroupData; Quantity)
                   );
                   "GroupData"
             )
)

then group by and sum result would be saved into the InventoryFilteredAgain collection.

 

Note: Please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
GarethPrisk
Super User II
Super User II

If I understand this correctly, you are basically looking to produce a sum (aggregate) of a list column, that has been filtered.

I replicated your SP structured, and confirmed the following approach. My language is different, so update the syntax (as-needed).

ClearCollect(
    colInventory,
    GroupBy(
        ShowColumns(
            Filter(
                AddColumns(
                    Inventory,
                    "appDeviceName",
                    Device.Value
                ),
                Department.Value = "Sales"
            ),
            "appDeviceName",
            "Quantity"
        ),
        "appDeviceName",
        "appDeviceGroup"
    )
)

This results in a collection grouped by the Device name, with an array of rows.

Then, you can simply aggregate that row in something like a Data Table, by adding the colInventory as the source.

Then update the "appDeviceGroup" column to be an aggregation.

Sum(ThisItem.appDeviceGroup,Quantity)

 

v-xida-msft
Community Support
Community Support

Hi @Tibor4 ,

How many records whose Department field value is "Sales" in your Inventory List? More than 2000?

 

Firstly, the ShowColumns function is not a Delegable function in PowerApps, so if there are more than 2000 records whose Department field value is "Sales" in your Inventory List, you may face a Delegation issue with your ShowColumns function.

 

If the amount of records whose Department field value is "Sales" is not more than 2000, I think the ForAll function could achieve your needs. Please consider take a try with the following formula:

Clear(InventoryFiltered);;
ForAll(
       Filter(Inventory; Department.Value = DepartmentName);
       Collect(
               InventoryFiltered;
               {
                 Device: Device.Value;
                 Quantity: Quantity
               }
       )
);;

When you execute above formula, the InventoryFiltered collection would be populated with two columns -- "Device" & "Quantity".

 

In addition, if you want to group by above collection based on Device column, and Sum the Quantity column value, please take a try with the following formula:

ClearCollect(
             InventoryFilteredAgain;
             DropColumns(
                   AddColumns(
                        GroupBy(
                                 InventoryFiltered;   // Above filtered collection
                                 "Device";
                                 "GroupData"
                        );
                        "Quantity";
                        Sum(GroupData; Quantity)
                   );
                   "GroupData"
             )
)

then group by and sum result would be saved into the InventoryFilteredAgain collection.

 

Note: Please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

I would highly recommend that you do not do a Collect pattern as outlined in the reply above, and as posted below.

Clear(InventoryFiltered);;
ForAll(
       Filter(Inventory; Department.Value = DepartmentName);
       Collect(
               InventoryFiltered;
               {
                 Device: Device.Value;
                 Quantity: Quantity
               }
       )
);;

Fundamentally, this would execute a single Collect function for each of the rows in your list that meet the criteria. Not only will this be slow in execution, but it would be a long-running process in the app (i.e. app would feel 'frozen'). This would be further exacerbated by the fact that it would be a VERY large number of API calls for a single function. Given that MSFT is moving to an API-call-based model, this would be a VERY expensive call to simply collect records.

Delegation is an issue with something like a SharePoint list. The original posted indicated they are aware of this. That being said, there are more efficient ways to overcome this, including paging approaches using the ID column, or others. However, efforts should be made to Collect as many records as possible per call.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (15,115)