Sharepoint Two lists - How to count registered users in list and add count to specific training row in 1st list.

[test1111112]

Hi all,

I have two SharePoint lists, one is a list of all trainings which has a row for each training and a unique ID column.

The second List is a list of all registrants and uses the Lookup column in SharePoint to ensure a relationship with the first list based on the ID column.

I'd like to do a daily count of the registrants and then add that count total to that specific training ID row column called "Registered". So the end user will be able to see 15 people have registered and there are 5 slots left for instance.

I've attached some examples of the list.

Training Registration list:

Would like to add the count to the Registered Column for the specific training based on the ID column.

Registered Attendees List:

This list would contain all the registrants and would count them and recognize the Training column is the lookup column from the first list and add the count to that specific training.

If someone could help walk me through this in power automate that would be amazing.

[grantjenkins · Accepted solution]

I've created a quick example on how you can do this. Note that I've gone with your requirement to do a daily update of capacity, but you might want to think about improving this, so it gets updated each time a training attendee record is created/updated/deleted, etc.

My Training Registration list is below. Note that I also added a column of type Calculated (Remaining) that equals Capacity minus Registered.

My Training Attendee list is below. Note that for the lookup field I use Title and additionally Start Date and End Date, and don't include ID as it will be available to us within Power Automate anyway.

The full flow is below. I'll go into each of the actions.

Reoccurrence is set to run daily.

Get items retrieves all the Training Attendees items.

Select will output a simple array of Training Ids that we will use to sum the attendees for each.

Compose removes any duplicate values from our Select output so we have an array of unique Training Ids. The expression used here is:

union(body('Select'),body('Select'))

We then use an Apply to each to loop through each of the unique Training Ids.

Filter array will give us only the records that match the current Training Id. We can then use the length expression to count how many items were returned.

We use Send an HTTP request to SharePoint to update the column Registered with the length of the Filter array.

Method: POST

Uri: (Note that you would need to replace 'Training Registration' with the name of your list).

_api/web/lists/GetByTitle('Training Registration')/items(@{items('Apply_to_each')})/validateUpdateListItem

Body: (Note that you would need to replace '"Registered" with the name of your column).

{
    "formValues":[
	{
	    "FieldName": "Registered",
	    "FieldValue": "@{length(body('Filter_array'))}"
	}
    ]
}

[test1111112]

This is fantastic, thank you so much for explaining how each step works that was very helpful.

As you suggested, I included this to run each time a new row is added to the training attendees list rather than daily.

I am getting an error with the Send an HTTP request to Sharepoint

My lists are called "Training Registration" and "Registered Training Attendees"

Here is my Send an HTTP request step:

I used the same URI and Body as you since my list name is the same as is the Column "Registered"

Any ideas what I did wrong?

Edit: I figured it out, the Select action, Map field I needed to click the button on the right to switch the field type and then put in the Training Id field. This resolved my issues.

Thanks again!

[SKHart]

Hi Grant,

I am working on something very similar where I am trying to get the count of submissions with the same ID from one list (items generated by MS form submissions) onto another SharePoint list tracking the number of submissions for that unique ID. I have tried many different flow updates and even created another SharePoint list without spaces in the title to see if the spaces were causing the error however, I'm still having no luck. Below is the error that I am receiving in the last step. Any guidance would be greatly appreciated!!

The expression "Web/Lists/GetByTitle('SecondaryClassroomTeacher')/Items(1ClassroomTeacher)/ValidateUpdateListItem" is not valid.
clientRequestId: d0a4a420-2ac0-461f-8f5e-f693b17dca2f
serviceRequestId: 4c1a0ba1-3062-4000-e3e5-850233c35ba4