Collection showing how many records per Created By value AND per Environment

(2) Share

Report

Posted on by CCEP_Mike

219

Hello,

If i have a sharepoint list called "Assignments" and within the list i have the following data fields/records

Sharepoint List = Assignments
ID	User_Email	Assigned_Outlet	Environment
1	my@email.com	12	Horeca
2	jack@jack.com	64	Convenience
3	my@email.com	85	Convenience
4	my@email.com	578	Convenience
5	jack@jack.com	83	FOTG
6	me@microsoft.com	2345	FOTG

How would i best create a collection which will look at the records, and will count how many records there are PER "User_Email" value but also count how many records there are per "Environment" value? for example, based on the above I'd have a collection which looks like the following:

User_Email	Total	Horeca	Convenience	FOTG
my@email.com	3	1	2	0
jack@jack.com	2	0	1	1
me@microsoft.com	1	0	0	1

I have the following code which lists each user and the total number of records, but I'm struggling to work out how best to add the additional columns for the Environment and the total records for each one by user.

If(
    vartoghoreca = true && vartogfotg = true,
    AddColumns(
        GroupBy(
            Filter(
                Questionnaire,
                Created >= FromDate_2.SelectedDate && Created <= ToDate_2.SelectedDate && StartsWith(
                    'Account Name 2',
                    txt_search_2.Value
                ) && Record_Status = "COMPLETED"
            ),
            Created_by_Email,
            UserGroup
        ),
        Count,
        CountRows(UserGroup)
    )

Any help would be much appreciated. Thank you in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

WarrenBelz 154,797 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi CCEP_Mike

Probably something like this

If( vartoghoreca && vartogfotg, AddColumns( GroupBy( Filter( Questionnaire, Created >= FromDate_2.SelectedDate && Created <= ToDate_2.SelectedDate && StartsWith( 'Account Name 2', txt_search_2.Value ) && Record_Status = "COMPLETED" ), Created_by_Email, UserGroup ), Total, CountRows(UserGroup), Horeca, CountRows( Filter( UserGroup, Environment = "Horeca" ) ), Convenience, CountRows( Filter( UserGroup, Environment = "Convenience" ) ), FOTG, CountRows( Filter( UserGroup, Environment = "FOTG" ) ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
CCEP_Mike 219 on at

Like (1)

Report
Copy link

Link copied!

Hi @WarrenBlez,

Thank you very much for the support on this, worked perfectly!

Much appreciate the support. Thank you.

Was this reply helpful? Yes No