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
