Expand my Community achievements bar.

Help with calculated field in custom form

Avatar

Level 3
One of the calculation I am trying is not working, can someone please help me identify what I am doing wrong. Below is the calculation: IF((Project-Cost='> 250K',100,0)+IF(Project- Duration='> 6 months',100,0)+IF(Project- Complexity='Medium-High',100, 0)+IF(Project-Impact='Medium- High',100,0)+IF(Project Delivery Risk='Medium-High',100,0)+IF( Project-Cost='50K to 250K',10,0)+IF(Project- duration='3 - 6 months',10,0)+IF(Project Complexity='Medium-High',100, 0)+IF(Project Impact='Medium-High',100,0)+ IF(Project Delivery Risk='Medium-High',100,0))>= 300,"Project Type 3",IF(IF(Project-Cost='> 250K',100,0)+IF(Project- Duration='>6 months',100,0)+IF(Project- Complexity='Low-Medium',100,0) +IF(Project-Impact='Low- Medium',100,0)+IF(Project Delivery Risk='Low-Medium',100,0)+IF( Project-Cost='25K - 250K',10,0)+IF(Project- Duration='3 -6 months',10,0)+IF(Project Complexity='Low-Medium',10,0)+ IF(Project Impact='Low-Medium',10,0)+IF( Project Delivery Risk='Low-Medium',10,0))>=200, "Project Type 2",IF(IF(Project-Cost='Less Than 50K',100,0)+IF(Project- Duration='1-3 months',100,0)+IF(Project Complexity='Low',100,0)+IF( Project-Impact='Low',100,0)+ IF(Project Delivery Risk='Low',100,0)+IF(Project- Cost='25K-250K',10,0)+IF( Project-Duration='1-3 months',10,0)+IF(Project Complexity='Low',10,0)+IF( Project Impact='Low',10,0)+IF(Project Delivery Risk='Low',10,0))>=20,"Project Type 1","Project Type 0")). Thanks,
10 Replies

Avatar

Level 10
You're missing a paren on your 19th IF statement. Just kidding �� . I didn't comb through to match parens or anything but at a glance I'd question the field names. If these names are supposed to be WF fields, they're definitely not correct (you'll want to copy the exact names). If they're supposed to be your own defined fields from a form, I believe you need to have a DE: in front of them. For example, DE:Project-Complexity If you don't believe that's the case, what I typically do is break it down to smaller chunks. Try the first IF statement by itself to see if it works. If it does, keep adding on until it breaks, then you know which part is breaking it and can debug from there. But I'm 82.6% confident in the field names theory �� . Vic Alejandro, PMP, CSM | IT | Sr. IT Project Manager Denver Water | t: (303-628-7262) | c: (303-319-6473) "http://www.denverwater.org/"> http://www.denverwater.org INTEGRITY | VISION | PASSION | EXCELLENCE | RESPECT

Avatar

Level 8
At first glance, the one thing I noticed is you're using a + symbol when it should be a comma. Adina Pierce Cisco

Avatar

Level 8
Ignore what I said, it looks like you're adding up the values. I see a double (( in the first line, should it be a single paren? Adina Pierce Cisco

Avatar

Level 10
I think he's trying to add up all the results of the IF statements. I'm assuming it's some kind of project scoring for priority or ranking. I think it can work with the + sign (assuming everything else is correct). Is there a Project Complexity value of 'Medium-High' ? Or are you trying to do a range? It won't do a range in that fashion. So that might be an issue. What is your result that's not working? It doesn't give you the proper value? It doesn't give you any value? Do you get an error? Vic Alejandro, PMP, CSM | IT | Sr. IT Project Manager Denver Water | t: (303-628-7262) | c: (303-319-6473) "http://www.denverwater.org/"> http://www.denverwater.org INTEGRITY | VISION | PASSION | EXCELLENCE | RESPECT

Avatar

Level 8
You're right, Vic. Usually if I have a special character in a field name, I have to surround it with { }. Does that fix anything? Adina Pierce Cisco

Avatar

Level 10
Yeah good point. I started putting { } on all of the fields just in case now. Vic Alejandro, PMP, CSM | IT | Sr. IT Project Manager Denver Water | t: (303-628-7262) | c: (303-319-6473) "http://www.denverwater.org/"> http://www.denverwater.org INTEGRITY | VISION | PASSION | EXCELLENCE | RESPECT

Avatar

Level 3
Yeah this is a project scoring method. Medium-High is a dropdown value in a field range. Shoudl I start putting {} in field name. Supriya Pandey PMP New York University

Avatar

Level 3
I am trying to create a calculated field. The formula in the calculation is giving an error and workfront is not letting me save the field. Supriya Pandey PMP New York University

Avatar

Level 10
OK yes, then I would definitely start with the curly brackets { }. Try one field first to see if that stops the error. Then you'll know if that was it. Then you can add from there. Vic Alejandro, PMP, CSM | IT | Sr. IT Project Manager Denver Water | t: 303-628-7262 | c: 303-319-6473 "http://www.denverwater.org/"> http://www.denverwater.org INTEGRITY | VISION | PASSION | EXCELLENCE | RESPECT

Avatar

Level 4
Just a bit of general advice for building these kinds of calculated fields. INCREMENT: Create a small form that has just the fields you need applied to a small number of test objects. Put the first IF statement in, test it to see that it works. Put the next element in, test, etc. It's tedious but will result in a well tested branch tree instead of a monster. Use a code editor: Anything from vim, to code-runner to dream-weaver or any other environment that matches your parenthesis for you. And check that each parenthesis matches where you think it should. Also use spacing to align your IF statements to quickly spot things that don't fit the right pattern. If it's not working, pull it out to your code editor, save the form with a blank field and go back to step one of pasting it back in piece by piece. TEST, TEST, TEST, TEST While you have what it should do clearly in your head, check all the forks and make sure you are getting the right answer. Good luck and happy coding -- Melinda Layten, Senior Consultant Work Management Improvement CapabilitySource Phone: (484) 505-6855 site: www.capabilitysource.com email: melinda.layten@capabilitysource.com - we simplify your work so you can run your business -