I have 2 custom lists, one is a lookup list which contains a 'score' and an 'upper' and 'lower' value. I need to retrieve the score value by comparing a record from the other list.

i.e. Lookup list has 2 records

Score - upper - lower

1 - 50 - 1

2 - 100 - 51

In the second list, I have a number that I need to compare between the upper and lower values, and return the score.

So if the number from the second list is 75, I need to compare that to the upper and lower to return the score to apply. which in the example above would be 2

I am trying to do this in a list workflow, which will run each time a record changes. Can this be done? I am using SharePoint designer workflow 2013

2 Answers 2



Here is an alternative solution using workflow. However, it assumes that the Scores will never change but the thresholds will. It also assumes that the number of records that needs to be processed is not too large (e.g. in the 100s and not 1000s)

In my worked example the list that has the value that needs to be processed has the following columns;

  • [Value] - this will be the value that gets tested
  • [Result] - this is a number field where the result will be stored
  • [Workflow] - this is a yes/no field that will be used to trigger a batch of records; more on this later

The solution will be created in workflow (I am using 2010 workflows in SPD 2013 - for reasons to do with how workflow farm queues are managed; 2013 workflows can often encounter significant delays which may become irritating for the end-users; of course this comes at the expense of loss of 2013 functionality).

Here is the workflow; enter image description here

In Load Levels I am making use of the Action Set Workflow variable to pull the upper and lower test thresholds from the lookup list (LU_scores) and store them in workflow variables.

The workflow variables are set as type Number and the value is set by looking up the corresponding Lower or Upper level from LU_score where Score = the score we are interested in. Here is a screen shot as an example;

enter image description here

As you can see the Value used to lookup the Score in LU_score is a fixed value (i.e. 1 in this case). This is the reason this will only work if you have fixed scores but variable thresholds. In this example the workflow variable level1_lower will be loaded with the Lower threshold from LU_score where the Score in LU_score equals 1.

You will need to repeat this for each of your scores.

In Map Values to Scores I am simply testing the current item [Value] to see if it lies between each of the score thresholds. If it does then the score is returned to [Result].

You will need to ensure that the Workflow is set to run on Create or Modify. enter image description here

If you change the thresholds and want to re-calculate the results then use the [Worklfow] variable to trigger a re-calculate for all of the records. Set it to Yes for all records, then when you click Stop the workflow will be triggered for all of the records.

enter image description here

In the SPD workflow the [Workflow] variable is reset to No on completion. My understanding is that 100 records can be triggered in this way; therefore you will have to batch if there is more than this.


A simpler solution would be to have a calculated field in the list. The formula would be something like

=IF([Value]>=1 And [Value]<=50,1,IF([Value]>=51 And [Value]<=100,2,...))

This would automatically calculate your score when the [Value] changes. The number of nested IF statements is 7 - so this may be a limiting factor.

In this case you could create a calculated variable for each IF;

Score1 = IF([Value]>=1 And [Value]<=50,1,0) Score2 = IF([Value]>=51 And [Value]<=100,1,0) etc

Then; Final Score = Score1 + Score2 + ...

  • Thanks for your post. however, i need the upper and lower values to be variable, so that a user can maintain them without editing the list columns. Do you have any other ideas? Commented May 12, 2018 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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