Conditional Fields and Calculations Based on Text Values
Hello
I am trying to create a business process in Wrike that is currently managed in MS Excel. We require people from across our business to submit a public request form to request a new project and we would like to add more questions to this form related to project prioritisation. The questions are all single select and each value corresponds to a score, i.e. 1 to 5 which we will use the SUM of to determine a priority score for that project. I have created custom fields corresponding to each of these new questions, all single select fields with text values (the end user does not need to see the score for the value they select for each question).
What I am trying to do is build an analytics report that can display a score based on these custom field responses. The issue is as the values being summed are text values not numeric, I am looking for a way to either have these custom fields automatically update another field that will show the score which can then be summed in a report to show the total score, OR use an IF function within a pivot table in analytics report to sum the score for each question based on the user response (e.g. if(field1=value1,1,if(field1=value2,2,...) etc,) OR update the request form so that question can be linked to a custom field without the values displayed having to be the same (i.e. available answers to select on form are A, B, C, D, E but this corresponds to custom field with values 1, 2, 3, 4 ,5) OR another appropriate solution that will allow this to be done in an automated way. We get many new project requests so if I have to manually update a numeric custom field for each question on every request it will be unworkable. Table below provides some more context on the process and structure (lines in bold showing example user response). Doing this in Excel is meaning this process is completely disjointed from our portfolio view which Wrike is the main source of so we are having to spend time overlaying this info to our Wrike data outside the system to make any use of it.
| Project 1 Request | |||||
| Question1 | Question2 | Question3 | |||
| Custom field 1 | Score | Custom field 2 | Score | Custom field 3 | Score |
| Value a | 1 | Value f | 1 | Value k | 1 |
| Value b | 2 | Value g | 2 | Value l | 2 |
| Value c | 3 | Value h | 3 | Value m | 3 |
| Value d | 4 | Value i | 4 | Value n | 4 |
| Value e | 5 | Value j | 5 | Value o | 5 |
| Report | |
| Project Name | Prioritisation Score |
| Project 1 | 8 |
| Project 2 | # |
Cool request. Having a similar task on my bucket list.
However, I'd love to have a new feature, that fills a custom field based on the selection in the request form.
Hi Minilik Asfaw,
unfortunately you cannot sum value taken from a single select because it will be considered as a text.
The only way I'm figured to resolve this is use Wrike Integrate, take a Value, convert to number and save it in another field to allow you to do a sum.
If you doesn't have Wrike Integrate you can use something like make.com or Zapier.
Hi Minilik Asfaw
With the approach of a "translation table" of Pietro you could also try to create the translations in Datahub tables and consider these values in another calculated field.
Hi Raimund Dienst - Regarding your first query about mapping responses to CF's from request forms:
Rohan V Community Team at Wrike Wrike Product Manager Erfahren Sie mehr über Wrikes leistungsstarke Funktionen und lernen Sie Anwendungsbeispiele kennen
Rohan V Wrike Team member Erfahren Sie mehr über Wrikes leistungsstarke Funktionen und lernen Sie Anwendungsbeispiele kennen