Getting Names and their count from Single Text Column (Volunteer) from my SPList

[Anonymous]

Hey! Guys. I have created a gift scan app. The whole purpose of which is to assign Ham or Turkey to a particular badge number that we enter in the badge number badge. So, I have a login page where there are two logins, one for Volunteers that could be any one as I have to hand over the app to someone else. Second login is for the admin that will take the admin to report page where there are two data reports. One is in the form of Pie chart that shows the counts of Ham and Turkey and how many people left without ham or turkey. I am done with the pie chart thing. Now, as far as the second report is concerned I am using a gallery where I have to show the volunteers whoever are assigning the Gifts(Ham and Turkey) to respective badge numbers. Now I need to show each volunteer name from the SharePoint list where the volunteer is being updated whenever the volunteer is logging in and also show the counts (in the form of how many gift assignments are done by a particular person) . e.g., Let's say if Ali logged in as a volunteer and assigned a gift to an employee, Ali will update in the  SP list in the rows of employee to who gift is assigned. So, now let's say Ali did 7 assignments and then Joseph another employee did 8 and then another Haley did 12 gift assignments. So in the gallery of Volunteers I need to show the names Ali, Joseph and Haley right next to their total assignment counts from the SP list. I am trying to work up with this mixed formula as I am confused how to work it out. So, if anyone can help me out, that would be great. Below is my formula:

Set(varDistVolunteer,
Distinct('Associate Record', Volunteer));
 ForAll(varDistVolunteer, Collect(colVolunteerWithRecords,{Name: Value, RecordCount:With({wComplete:Filter('Associate Record',Volunteer = Value)},CountRows(wComplete)}))
Clear(colVolunteerWithRecords);

ForAll(varDistVolunteer,
Collect(colVolunteerWithRecords,
{
Name: Value,
//RecordCount:
CountIf('Associate Record', Volunteer = Value)
RecordCount :With(
{
wComplete:
Filter(
'Associate Record',
Volunteer = Value
)
},
CountRows(wComplete)
)
}
)
) 

[michelle4 · Accepted solution]

Hi MOALI, 

Yes this is possible. First thing I would update is updating the set to a collection at the beginning. For the volunteer name to be captured are you using the Modified column or another column to capture their name? If you are patching the badge number to the Volunteer column you will need a separate list to associate the Name of the volunteers with their badge number. The code below should work as far as getting you the value in the volunteer columns and the count of records that have that value. 

Clear(colVolunteerWithRecords);

ClearCollect(varDistVolunteer,
Distinct(Filter('Associate Record', !IsBlank(Volunteer)), Volunteer));

ForAll(varDistVolunteer,

Collect(colVolunteerWithRecords,

{Name: Value,

RecordCount: CountRows(Filter('Associate Record', Volunteer = Value))}));

If the volunteer column is the volunteer name then you are done. If not the next step to find the volunteer name from their badge number would look something like this.

ForAll(colVolunteerWithRecords, 

Collect(colVolunteerNamewithRecords, {

VounteerName: Lookup(<VolunteerList>, ThisRecord.Name = <BadgeNumber>).<VolunteerName>,

RecordCount: ThisRecord.RecordCount

}))

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[Anonymous]

Thanks my man. But how can I use it on a bigger data set? Do I have to make a flow?

[michelle4]

How big is your data set? If you are under 2k rows you should be able to accomplish in Power Apps.

If you have more you could run a flow to get all items using a For Each action to capture all rows in the list and then evaluate for each Volunteer, I would recommend having the list of volunteers to start the process and make it easier. You will likely need to create a string of your volunteer results and then once returned to the app parse into your collection.