I'm trying to write a sandboxed custom workflow activity that would go to another list and get a value of a field based on values of multiple other fields combined.
List A
- State - lookup to States list
- City - lookup to Cities list
- Population - Number
- ... other columns
(essentially the combination of State and City makes the row uniquely identifiable)
List B
- Company - lookup to Companies list
- State - lookup to States list
- City - lookup to Cities list
- Affecting % of population - number (percent)
- Population Affected - Number field (to be filled in by the custom activity workflow)
- ... other columns
Constrains:
- I can't add Company field to List A due to unique / different permissions
- The lookup fields need to stay lookup fields and can not be transformed into single line of text fields.
- Due to the fact that the fields are of type lookup, I can't concatenate them with a simple calculated column.
- The solution must be sandboxed and not farm. We aren't allowed to deploy dlls, only .wsp
Problem: What I would need is a sandboxed custom workflow activity that triggers on List B item added/changed. When triggered, activity grabs the State and City values from the item in list B, goes to List A and searches for a combination of the same State and City. When found, it grabs the associated population. Lastly it multiplies the population by the % from "Affecting % of population" and pastes that result into the "Population Affected" field.
Bonus: At some point, I will also need a sandboxed custom workflow activity that triggers on List A item changed (the population field). When the population for specific State+City combination changes, the activity would go to List B and update all "Population Affected" fields that satisfy the same State+City for all companies (multiple rows).
Thanks in advance!
