Efficient Count If for 90+ Records / Increment a Variable / Sequence

[pp365]

Hi Power Apps Community,

I am stuck on an issue which unusually I can't find the answer to despite extensive searches on the Community and YouTube combined with trial and error.

I have a collection, for ease, named colOne. Inside that collection are multiple personnel records. For each personnel record there is a column named FacultyDeptID and that column contains values depending on the Faculty and Department names for the particular record. That column has been created by a lookup on another collection, called colTwo.

Currently, there are 89 possible combinations (1 - 89) in FacultyDeptID, but, this could change in the future. A combination can be used more than once, e.g.

Name: Example One, FacultyDeptID: 1

Name: Example Two, FacultyDeptID: 2

Name: Example Three, FacultyDeptID: 2

I am trying to produce a summary statement that inspects FacultyDeptID and provides a total count for the number of times an ID number is found. For example, "There is one staff member in the [1] Faculty, and there are two staff members in the [2] Faculty" etc. I will also want to use this data later in the App (IE for all cases where there is more than 1 instance found, take an action).

I could have an If statement for every single instance from 1 - 89 (and then a LookUp if the value is greater than 0), but clearly this is inefficient, prone to error (if the statement is repeated 89 times etc) and if the possible combination of 89 instances changes later, the App will not be future proof.

My current line of thinking is that this could be done by incrementing a variable; use CountRows on colTwo (which is the source for the combinations available)). So, the variable starts at '1', looks for how many records there are that match the variable's value (1) in colOne within FacultyDeptID, then that variable increments to '2', looks again, etc. But I cannot see how to do this.

My other approach was to try something using Sequence. I got as far as setting a variable that contains the possible number of combinations (89) taken from colTwo, and then using Sequence to make a table of every integer from 0 to the total number associated with that variable (89), but then can't see how to leverage that data in a formula. In other words, I can't then use that list of numbers: 1, 2, 3, etc in a succint and accurate way to inspect colOne for the number of times there is a value of '1' in FacutlyDeptID, etc.. I am assuming this is within a ForAll but am not sure.

Please let me know if anything needs clarification and a huge thanks in advance to the experts on this community for your help!

Many thanks,

Edd

[timl · Accepted solution]

@pp365 

One way to accomplish this is to use formula that looks like this.

AddColumns(colTwo, 
           "FacultyDeptIDCount",
           CountRows(Filter(
                            colOne,
                            FacultyDeptID=colTwo[@FacultyDeptID]
                     )
            )
)

Based on the assumption that colTwo is the collection that contains the 89 FacultyDept records, this will return a table with a FacultyDeptIDCount column with the row count of matching records from colOne.

[pp365]

Hi @timl ,

Many thanks for your quick reply.

Based on the assumption that colTwo is the collection that contains the 89 FacultyDept records, this will return a table with a FacultyDeptIDCount column with the row count of matching records from colOne.

---

That's correct.

I've just tried that formula and am getting the error "Name isn't valid. 'FacultyDeptID' isn't recognised' on the final '@FacultyDeptID' part of your statement.

I've checked the spelling and FacultyDeptID is definitely correct for the name of that column in colOne. (The = between FacultyDeptID=colTwo says 'these types can't be compared: Number, Error').

Apologies as it's probably an error on my side, but, I can't spot it. Could you advise?

Thanks again,

Edd

[timl]

@pp365 - Just to confirm, does colTwo include a column called FacultyDeptID?

[pp365]

Hi @timl ,

It doesn't, but, it does include a column named 'ID'. I tried to substitute 'FacultyDeptID' for 'ID', as follows, and although I don't see any errors in the formula editor, on running it, no actions seem to take place (there is no appended column in colTwo).

Many thanks for your help.

AddColumns(colTwo, 
           "FacultyDeptIDCount",
           CountRows(Filter(
                            colOne,
                            FacultyDeptID=colTwo[@ID]
                     )
            )
)

[pp365]

Hi again @timl ,

I have taken another look inside the formula editor, and when I select all that formula, in the preview underneath the FacultyDeptITCount column does show, with expected values populated. (This is using the "@ID" substituted column as per my reply above, in case helpful).

However, the formula doesn't seem to be running whether I add to to 'On Visible' of that screen (ideal) or even on an OnSelect on a button. In other words, the preview looks good, but the column and data is not appending to the collection.

Any help on this would be much appreciated and thank you again. Please let me know if you need any more info.

[timl]

@pp365 

You would add that formula to the Items property of a gallery or data table control to show the output. Alternatively, you could build a collection (for example, colThree) using that formula, and you can then work with the results in colThree.

[pp365]

Many thanks for your time and expertise @timl , understood. I've marked as Completed and liked the solution post. Thanks again!

[timl]

You're welcome @pp365 - glad to help!