Custom field calculating difference between SOW hours and actual hours does not calculate properly | Community
Skip to main content
M
MariaSh3
Level 2
June 22, 2023
Solved

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

  • June 22, 2023
  • 2 replies
  • 1924 views

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-1

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by MariaSh3

Hi @j_mas @vicsellers @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!

2 replies

lgaertner
lgaertner
Level 9
June 22, 2023

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

M
MariaSh3Author
Level 2
June 22, 2023

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
lgaertner
Level 9
June 22, 2023

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?

VicSellers
Community Advisor
VicSellersCommunity Advisor
Community Advisor
June 22, 2023

Hi @mariash3 - 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
    J_Mas
    Level 5
    June 22, 2023

    I agree with @vicsellers, 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)

    M
    MariaSh3AuthorAccepted solution
    Level 2
    June 23, 2023

    Hi @j_mas @vicsellers @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!

    Terms & ConditionsAccessibility statement

    Loading footer...