Custom field calculating difference between SOW hours and actual hours does not calculate properly

[MariaSh1]

Hi Community, 

Can I get your help with the following please:

I have created a custom form that contains a single calculated field "Difference SOW hours vs Actual hours" containing the following expression SUB({DE:SOW Hours},{actualWorkRequired}). However, when I attach it to a column in a report, the column remains empty. Can anyone assist with a resolution for this? @admin

[lgaertner]

Hello Maria,

as fields starting with DE: are custom fields, it's a bit difficult to troubleshoot.

Does the column remain completely empty?

Is the field DE:SOW Hours already filled with any value?

Can you please post the text mode of the column in your report?

Thanks in advance.

Regards

Lars

[MariaSh1]

Hi Lars, 

Thanks so much for getting back to me - please see responses to your Qs below:

Does the column remain completely empty? - yes, it did initially, then I tweaked the text mode in the column and this is what I got:

 

This is the text mode for Difference SOW hours vs Actual hours:

displayname=
linkedname=direct
namekey=Difference SOW hours vs Actual hours
querysort=DE:Difference SOW hours vs Actual hours
textmode=true
valueexpression=SUB({DE:SOW Hours},{actualWorkRequired})
valuefield=Difference SOW hours vs Actual hours
valueformat=hour

SOW hours:

displayname=
linkedname=direct
namekey=SOW Hours
querysort=DE:SOW Hours
valuefield=SOW Hours
valueformat=customNumberAsString

Actual hours:
linkedname=direct
namekey=actualWorkRequired
querysort=actualWork
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=minutesAsHoursString(actualWorkRequired)
styledef.case.0.comparison.lefttext=actualWork
styledef.case.0.comparison.operator=gt
styledef.case.0.comparison.operatortype=double
styledef.case.0.comparison.righttext=valuefield=SOW Hours
styledef.case.0.comparison.trueproperty.0.name=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=
textmode=false
valuefield=actualWorkRequired
valueformat=compound
viewalias=actualworkrequired

Thanks!

[lgaertner]

Hmm, that is really strange.

At the moment I do not really have an idea...

How is the custom field DE:SOW Hours set up in the custom form?

[MariaSh1]

Hey Lars, 

I thought it strange as well; none of the options I tried seems to be fixing the issue. Here is a screen of the field setup, seems to be pretty standard:

[VictoriaLinn]

Hi @MariaSh1 - The field containing SOW Hours is a number field, right? This calculation might help: 

SUB({DE:SOW Hours},({actualWorkRequired}/60))

Actual Work Required is a minute field that needs to be divided by 60 to get the format into hours

[J_Mas]

I agree with @VictoriaLinn, I think you need the division to get it to work correctly. Depending on how DE:SOW Hours is setup, you may also need the division at that level as well. Potentially like this:

CONCAT(SUB({DE:SOW Hours},{actualWorkRequired})/60)

[MariaSh1 · Accepted solution]

Hi @J_Mas @VictoriaLinn @lgaertner - thank you so much for helping me solve the issue. I was able to make the calculation work and I think I was also able to figure out why it was not working initially and I would be grateful if you could provide your thoughts as well.

When I created the "Difference between SOW hours vs Actual hours" field, I set it up as a calculated field, containing the following expression SUB({DE:SOW Hours},{actualWorkRequired}) and it was not working at all, e.g. the column remained empty. Then I proceeded by editing the text mode of the column in the report to contain the same calculation e.g. SUB({DE:SOW Hours},{actualWorkRequired}) which still didn't work. Mind you at this point, I have both expressions, one in the field setup and one in the report column with the same name. I then proceeded to amend the expression by adding the /60 to account for Actual hours being a minute field which resulted in the column being populated finally, but still it was not showing the correct hours. Finally I deleted the expression from the calculated field set up, thus leaving the expression only the text mode of the column. That's when it returned the correct amount of hours in the colum. 

That being said, I wonder why WF works in this way; my initial thinking was that if you are creating a calculated field and type the expression you want there, it should be enough to have the calculation work when you add a column in a report; in my case it seems that I should have made a standard field (not a calculated one) and then add whatever calculated expression I wanted for it in the text mode directly. Apologies if it's a bit confusing, I am just trying to figure out how to proceed with issues like this moving forward. 

Looking forward to your thoughts on this!

[J_Mas]

@MariaSh1 just to clarify, are you saying that you had both a calculated custom field (e.g. created in the Setup >> Custom Forms section) and a report-only calculated field with the same name?

[MariaSh1]

hi J_Mas - I had a calculated custom field created in the Setup >> Custom Forms section which I was adding as a column in the report, definitely not "a report-only calculated field with the same name" (not even sure how this works). Does that make sense?

[J_Mas]

Thanks for the clarification. A calculated field created through Setup >> Custom Forms is different than a calculated field you create directly in a report or view. Here's a good explanation of the differences.

It's possible that your original calculation was not being triggered correctly. My understanding is that calculated fields don't just update whenever their source data changes (the way a formula in Excel updates whenever the cells it's referencing change). Instead, only specific actions in Workfront will cause a calculated field to actually calculate (or re-calculate). Per the link I referenced above, these actions include:

• On an object’s main page, clicking the More icon (3 dots), then clicking Recalculate Expressions

• Bulk editing multiple objects when Recalculate Custom Expressions is enabled

• Editing a custom form when Update previous calculations is enabled for the calculated custom field

When you edit Text Mode on a report and manually add a calculation, that formula calculates whenever the report is loaded. So you're ensuring that your calculation is running at that exact moment. That may be why having the calculation on the report, as opposed to the original field, gave you the desired result.

I may be wrong (I'm still learning myself) but that's my current understanding.