Count Items in SharePoint list based on specified criteria

[tonyareznor]

I am developing a rather complicated tool using the Power Platform (PBI, Automate and Apps).  The data entry form submits to a SharePoint list and there are a couple of other data sources for querying data that the app uses as well.  I have a need to count the number of items a VISN (a field in the form) has and if the items are Active (another field in the form) and if the value is at 20, not allow the user to submit the form.  I have seen a number of suggestions to use CountIf and Filter functions but nothing I am doing seems to work.  I have attached a Word doc with my screen shots.  The VISN field and the ItemCount fields are what I need to filter by and then I need to count the rows that meet that criteria and force a stop at 20 per visn.

I would appreciate any input.

Thank you

[cha_cha]

Hello @tonyareznor 

Can you please share screenshots? It is easier to understand your goal if you can share your goal visually.

Name of the field and what kind of UI output you would like to achieve.

-I'll be clocking out now but will definitely check your reply tomorrow, or maybe someone can help you soon too.

[tonyareznor]

I updated my original post with a doc that has screenshots

[cha_cha]

Hello @tonyareznor 

Your code should probably look like this to count the VISN + On whether active. I'm not sure what your Active column so update that part.

CountRows(Filter(Readiness,'CDT Staff VISN Number'=[ADD_THE_FIELD_HERE],Active = Active))

On the Submit button DisplayMode property you can set it to something like this to prevent user from submitting.

If(
CountRows(Filter(Readiness,'CDT Staff VISN Number'=[ADD_THE_FIELD_HERE],Active = Active)) = 20,
DisplayMode.Disabled,
DisplayMode.Edit)

Also, what do you mean by 'force a stop at 20 per visn'?

[tonyareznor]

the Active field is a checkbox so either true or false.  There are 18 VISNs each VISN should have no more than 20 Active users.

[tonyareznor]

I got the count to work and drop into a text input and its working correctly using CountRows(Filter(Readiness,'CDT Staff VISN Number:'.Value = "07" && Active = 1)) but that is only 1 VISN and I need to count each VISN.

[cha_cha]

@tonyareznor 

where do you put the count numbers? or how do you apply it? can you please highlight it? Sorry, just want to make sure I don't wander with my replies. I'm clocking out now so expect a reply tomorrow.

[tonyareznor]

right now I am just dumping the value into a text input on the form.  I was uncertain whether to create another field or not. 

[tonyareznor]

I got it!!! In case someone else has a similar issue here is what I did

On the field itself CountRows(Filter(Readiness,'CDT Staff VISN Number:'.Value = VISNDD.Selected.Result && Active = 1))

On the submit button IF(DataCardValue15.Text) = "20",
Disabled, Edit)

I could use some help on the next one though which is essentially the same thing only there is another condition/field involved.  I have attempted the following but it does not work like the one above 

CountRows(Filter(Readiness,'CDT Staff Occupation'.Value = "RN(Med Surg)" &&'CDT Staff VISN Number:'.Value = VISNDD.Selected.Result && Active = "1"))

[cha_cha]

@tonyareznor 

Is it showing any error?