1

I'm trying to create a scoreboard, of sorts, and I'm having quite a time making it work like I want. Here's the situation:

In my primary list, each Representative puts an item into the list for their sales (each item representing a sale). The item contains details of the sale, of course. Once the item is in the list, the Manager can go into that item and choose from a DDL if the item is "Approved" by them or not. (There are other DDL options, but I won't go into those right now.) What I'd like to do is create a separate list that will display the Representative and their COUNT of records that they have where DDL=Approved. I've tried using a workflow to approach this, but I can't seem to find any options for COUNTs like I need.

Any ideas?

  • Hello @DanJ , Your feedback is very important 1. Are the below answers didn't helped you? 2. do you need any further clarifications? if yes, please comment to the provided answers with your clarifications to can help you or edit your question with these clarifications, else please upvote and mark the correct answer that helped you as accepted to close this question. Thanks! – Mohamed El-Qassas MVP Sep 18 '18 at 0:42
5

In my opinion, No need to create a sperate list with a complex workflow to achieve your requirements,

What I recommend is to do the following:

  • Create a Custom View that filtered by DDL=Approved

enter image description here

  • In Totals setting, beside any fields select Count

enter image description here

I think the above solution should match your requirement in an efficient and simple way :)

  • Forgive me, but wouldn't he then also need to create a filter for each Representative? – C. Dennett Aug 10 '18 at 18:51
  • 3
    Hello @C.Dennett, you are welcome at any time, but he said "Representative puts an item into the list for their sales" so the representative, in this case, is a SharePoint user so you can only add an additional filter with created by = [ME] :) what about your opinion? – Mohamed El-Qassas MVP Aug 10 '18 at 20:44
  • Oh, silly me I had completely failed to consider the =[Me] filter. Good point, sir! :) – C. Dennett Aug 10 '18 at 22:55
2

For this example, I created two lists; Representatives and Sales.

In Representatives, I have a column for the Rep's name, a UniqueID (how you choose to address this part is up to you... I just manually assigned a number), and a Count (or Score) of their Approved Sales. So:

Columns

  • Name
  • UniqueID
  • Count

In Sales, I have a column for the Representative, the Approval DD (with just the two relevant options: Approved and Denied), and a column for the Rep's UniqueID (I called it RepID in the Sales List, and again for this example I just keyed them in manually, matching what I set up for the fictional Rep's). A Unique Identifier of some kind is important for this to work properly (especially if any Rep's have the same first and/or last name). So:

Columns

  • Title (of the Sale)
  • Approval
  • Representative
  • RepID

My workflow is set up as follows:

  • First, I check the Approval status. If it's not Approved, I do nothing.

  • If it is Approved though, I set a workflow variable to the Rep's current Count in the Representatives List. I created the variable, and called it RepScoreCurrent. To do this, I set up the Action as follows:

Field Data to Retrieve

  • Data Source: Representatives (List)

  • Field from source: Count (column)

  • Return field as: As Integer

Find the List Item

  • Field: UniqueID

  • Value: Current Item: RepID

These bottom two conditions are what makes the workflow get the correct number. It will search the Representatives List, and retrieve the Count value from the item where the UniqueID column matches the RepID from the current Sales List item.

  • Next, I do the "Do a Calculation" Action, and calculate Variable: RepScoreCurrent plus 1 (Output to Variable: calc). Note, you don't have to create the "calc" variable, as SP will do it for you.
  • Finally, I choose the "Update List Item" Action, and set it up as follows:

List: Representatives

(skip the middle box for now)

Find the List Item

  • Field: UniqueID
  • Value: Current Item: RepID

Click the "Add..." button, and choose "Count" as the field, and Workflow Variable "calc" as the Value.

I hope this helps, and here are some screenshots of the whole setup:

Sales List

Sales List

Representatives List

Representatives List

Workflow Overview

Workflow Overview

Setting variable to current Score

Getting Current Score

Updating correct Representative's Score

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.