cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

Add Quantity column to a collection by counting inside collection?

I am trying to count the occurrences of an item inside a collection and add it as column to collection. I am sure the problem is I am doing a self referential call. I can't think of a better way to do it?

 

ClearCollect(
    LateOutTotal,
    AddColumns(
        LateOutList,
        "Quantity",
        CountIf(
            LateOutList,
            DailyAttUID = DailyAttUID
        )
    )
)

 

 What simple fix am I missing?

 

Oh I also tried:

CountRows(Filter(LateOutList,DailyAttUID = DailyAttUID))

Both give just a count of the total number of entries.

3 REPLIES 3
Highlighted
Super User III
Super User III

@BrianHFASPS 

I made a quick demo for you on how to count the rows for each unique item.  Put this code inside the OnSelect property of a new button

 

 

//create a new collection with a single column
ClearCollect(
    LateOutList,
    {DailyAttUID: "0117-0100"},
    {DailyAttUID: "0117-0100"},
    {DailyAttUID: "0117-0100"},
    {DailyAttUID: "0743-0006"},
    {DailyAttUID: "0743-0006"}
);
//group the collection by DailyAttUID
ClearCollect(
    LateOutListGrouped,
    GroupBy(
        LateOutList,
        "DailyAttUID",
        "NewColumn"
    )
);
//add a new column to count the rows
ClearCollect(
    LateOutListCounted,
    AddColumns(
        LateOutListGrouped,
        "Total Count",
        CountRows(Filter(LateOutList,DailyAttUID=LateOutListGrouped[@DailyAttUID]))
    )
);

 

 

The resulting collection LateOutList looks like this.  Go check it out in the main menu within Collections.

 

LateOutListCounted.PNG

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted

Thanks for the solution. Using your example ideally I would like to just add it as a column on LateOutList rather than a table with subtables. This is because I am displaying primarily all the fields in the table LateOutList and just trying to add an additional data point. To this end I tried to take the data in LateOutListCounted back to LateOutList as an additional column. However I am not getting the right data, just I think the first entry every time.

ClearCollect(
    LateOutListGrouped,
    GroupBy(
        LateOutList,
        "DailyAttUID",
        "NewColumn"
    )
);
ClearCollect(
    LateOutListCounted,
    AddColumns(
        LateOutListGrouped,
        "Total Count",
        CountRows(
            Filter(
                LateOutList,
                DailyAttUID = LateOutListGrouped[@DailyAttUID]
            )
        ),
        "LookUpID",
        DailyAttUID
    )
);
ClearCollect(
    LateOutTotal,
    AddColumns(
        LateOutList,
        "Quantity",
        LookUp(
            LateOutListCounted,
            DailyAttUID = LookUpID,
            'Total Count'
        )
    )
)

Can you help me wrap the newly created data back into my original table so I don't have to reference the sub tables in the groups.

Highlighted

@BrianHFASPS 

Sorry that last one didn't work out.  Lets try something else.

Here's a piece of code I used in a past project.  Instead of ADDCOLUMNS the technique here is to loop over the DISTINCT DailyAttUID's and then use COUNTROWS + FILTER.  Let me know if this is any better.  Note: you will have to define all the columns of your table within the curly brackets.

 

ClearCollect(
    LateOutTotal,
    ForAll(
        Distinct(
            LateOutList,
            DailyAttUID
        ),
        {
            DailyAttUID: DailyAttUID,
            Count: CountRows(Filter(LateOutList,DailyAttUID = Result))
        }
    )
)

 

Note:  If this gives you any problems make sure the column name Result is correct.  The output of DISTINCT is usually a single column table with the column name "Result".

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,359)