CountRows with Filter and in function

[akbarmahfuzalam]

Hey Team,

I am stuck with one of the formulas wherein I am trying to get the countRows with Filter and use in the function.

I have one master list for Employee and I have one Sharepoint list so what I am trying to build here is with SharePoint I have one field called Team Lead and another field called status.
Check the Employee list and see who is the manager of the Team Lead who has uploaded the data in SP List. Need Manager wise count of pending approval.

Employee List

SP List

ID	Enterprise_ID	LOB	Queue	Reason	Team Lead
65	abc	zxy	axc	Blah Blah	Mr X
66				Blah Blah	Amir D
67				Blah Blah	Akbar

CountRows(Filter(HBAdherenceLabelling,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.Result,TeamLead).TeamLead) And Status.Value= "Pending Approval"))

[Anonymous]

Try adding another filter instead of using And.

CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.Result,TeamLead).TeamLead)))

[akbarmahfuzalam]

Thanks or your Prompt response.

It's not giving me any result

CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.Result,TeamLead).TeamLead)))

Do i need to tweet somewhere in the second filter by changing it to the Manager field?

[Anonymous]

You might need the dropdown field name instead of ddTeam_22.Selected.Result so it could be ddTeam_22.Selected.TeamLead.

CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.TeamLead).TeamLead)))

[akbarmahfuzalam]

My Field name is ddTeam_22

[Anonymous]

Is this a gallery? If so you should use ThisItem.TeamLead instead of ddTeam_22.Selected.TeamLead.

[akbarmahfuzalam]

@Anonymous 

It is not given me the result even though I used the above-mentioned formula

CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ThisItem.Result).TeamLead)))

[RandyHayes]

@akbarmahfuzalam 

I'm not entirely sure how your logic is flowing here.  It seems that you have a dropdown with a list of TeamLeads in it, then you are trying to Filter the WHOSWHO list to get the TeamLead out of the values that match the TeamLead selected.  Aren't they one and the same?  And since you want this from the WHOSWHO list, It seems your formula should just be this:

CountRows(
    Filter(WHOSWHO,
        RequestedBy = ddTeam_22.Selected.Result
        && Status.Value= "Pending Approval"
    )
)   

But your definition of the logic seems somewhat different. 

And I've read your logic here and in your message saw a little different way as well of what you are doing.

I read it as perhaps WHOSWHO is your employee list that you mention and that HBAdherenceLabelling is the list you want a count from.

If you are listing the WHOSWHO in your SummaryHB Gallery and then want to get a count of the HBAdhearenceLabelling for the TeamLead, then consider this formula:

CountRows(
    Filter(HBAdhearanceLabelling, 
        RequestedBy = ddTEam_22.Selected.Result
        && Status.Value = "Pending Approval"
    )
)

But then again, I also saw your pictures that showed ShiftLead in the WHOSWHO list.  Is that what you want to get or the TeamLead of what?

See if I am getting close to what you are looking for, if not.  Perhaps be clear on the list names and some sample data and expected results.  Please mock up the data to not release names.  But provide some correlating data.  In your original post you have Team Leads that look mocked up, but then the picture of the App has only one name shown which doesn't correlate to anything in the other lists.

[akbarmahfuzalam]

@RandyHayes 

Please accept my apology for replying to you back Late.

Basically, there are SP list and need report kind of a summary wherein I want shiftlead wise Count whose Team lead is using the App and uploading the Data.

And I have WHOSWHO SP List where TeamLead and their ShiftLead are mentioned.

Have attached the Report and SP List for your Kind Reference.

FYI... WHOSWHO Field TEAM is equal to LOB of HBAdherenceLabelling

[RandyHayes]

@akbarmahfuzalam 

I am still trying to digest completely what you are trying to really get at.  I believe what you are trying to do is:

1) Dropdown with list of Teams from the WHOSWHO list (ddTeam_22).  If I was guessing, I would say your Formula for the Dropdown Items property is :  WHOSWHO  and you are displaying the Team column. (However I don't believe this is your formula since you probably have a distinct list there).

2) Your Gallery is a list of the People (FullName column perhaps) that are in that Team selected.  Expecting Items property to be something like:  Filter(WHOSWHO, Team=ddTeam_22.Selected.Team)

3) You have a Label in your Gallery Template that you wish to have display the number of "Pending Approval" rows that are in the HBAdherenceLabelling list where the RequestedBy is equal to the Team Lead of the Team that is selected in the DropDown

HOWEVER, you added something in your last message that makes me scratch my head a little - you said that the TEAM column in the WHOWSWHO list is equal to the LOB in the HBAdherenceLabelling list.

So if that is the case, then I believe it is not so much about the RequestedBy that you want to count rows as much as the LOB for the Team that is selected in the DropDown.

I am really not quite getting exactly what you want because I do not see a correlation in the data to the display - there is too much that is crossed out in your screen shots. And what is visible, NONE of it matches anything in any other list. 

I really need to see this data and how it correlates to what you are trying to do.  As mentioned before, you can easily mock up some data to try and convey what you need.

Like this:

WHOSWHO list (only needed columns shown)

Team	FullName	TeamLead
TeamA	John Doe	Leader1
TeamA	Mary Jane	Leader1
TeamB	Bill Gates	Leader2
TeamC	Donald Duck	Leader3

HBAdherenceLabelling list (only needed columns shown)

LOB	RequestedBy	Status
TeamA	Leader1	Approved
TeamB	Leader2	Pending Approval
TeamC	Leader3	Pending Approval
TeamA	Leader1	Pending Approval

And for your App Screen

Dropdown is a list of What?  (what is the formula on the Items?)

Gallery is a list of What? (what is the formula on the Items?)

ONCE we can get the above clear, I believe we can get your count correct.  But, I keep guessing at how this all connects and I just need to see mocked up data like above to be able to say.  If TeamA is chosen, then we should see "X" in the Gallery and a count of "Y" in the label.

Please fill in some of the details and we'll certainly get this nailed down.