2018.1 install custom form calculations don't seem to work anymore

[pco88]

Is anybody else seeing this in their instance? I have custom form calculations that's just simple, mostly SUM. On older projects where the custom form has been applied and used, the calculations are still there. When added to new projects, the custom form fields no longer calculates/sum. Polly Co

[Doug_Den_Hoed__AtAppStore]

Hmm. Thanks Polly, That's a bad one, if it's widespread: we use tons of calculated parameters. Several releases ago, I had a similar situation, where formulas that used A + B + C instead of SUM(A,B,C) stopped calculating. In that case, it turned out that "B" was (incorrectly) defined as a Text field, which apparently tripped up the latest release at that time, which started treating the "+" as an append in that case, vs an "add" of the numeric equivalent. To elaborate (as I now remember this Super Frustrating Day) these were custom currency data fields on million dollar Tasks, so $1,250,000 + $2,100,000 + $3,400,000 became 1250000210000002100000, which is to big to store in the resulting (desired) currency parameter, which instead, rendered as blank. To solve it, we changed the datatype of "B" to a currency field (which takes a number of careful, cautious steps, when there is Real Data Involved), and the result then became $6,750,000 as intended. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[VicAl]

I had 3 people report issues with the buttons not appearing, so I've reached out to one of them to see if this is also a symptom. I tested it myself and my calcs are working. However, my buttons are also appearing. So I don't think I'm a valid test. I'll let you know when I hear back from one of my users that is having issues. 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

[pco88]

@Doug Den Hoed , I've just created and tested a simple 2 number field sum on a custom form and it calculated. I have to review the custom forms to check whether I have mixed variable fields in there. I'll send an update later. Polly Co

[VicAl]

Yeah I just heard back from one of my users that was having a separate issue and they showed me that the calcs are working for us. 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

[DavidRu1]

To test: I just tried a new project with custom form and it calculated correctly. Dave Rulon Great Dane Savannah, GA 912-644-2452

[pco88]

@Doug Den Hoed - AtAppStore and @Vic Alejandro - it's the mixed variable! ARRRRGGHH! I have a few in currency, the rest in numbers. The system won't let me make the currency field into a numbers field because there's data in it. @Doug Den Hoed - AtAppStore, how soon did they revert back to allow mixed variables? I'm wondering whether I make the effort to remove data in the currency fields (if they're few) and then convert the field to numbers. Polly Co

[pco88]

I still couldn't get the calculations to work. I've actually tested the concept first on the preview site and removed all data from the currency variable on the form via a report, blanking those fields out. Then I went to the custom form to change those fields to number. I then went to a project, pulled in the custom form and tested the fields out, I saw the calculations go so I thought I could apply to the production site. On production site, I did the same thing. I tested on one project, added the custom form and then filled in fields. I thought I remember seeing calculations hence I went back to the report which was on another open tab and filled in the data I've just removed - they're showing up as currency though so I blanked those out again and tested on another project. And now it doesn't work! Polly Co

[Doug_Den_Hoed__AtAppStore]

Hi Polly, Ehm...as far as "how long until [Workfront] fixed the calculation engine to allow mixed data type [of numbers + strings in my case]" goes, I suspect "they didn't" (untested, but easily confirmed). When I realized the problem - that "B" was a Text field - I figured out a hack to convert it to a number (eg A + cdbl(B) + C, or some such despairation), but given how many calcs we have, we decided to suck it up and convert the datatype, which was the root cause. Under the hood, I would guess that WF was tweeking the "+" behavior to make it more efficient (otherwise, why touch it?), such as by removing extra logic to convert string to numbers (if possible) and then adding them as if they all were numbers. So: to your case, which sounds eerily similar. From what you describe, my guess is that last night's roll took away some code that made the SUM command smart enough to convert the currency into a number and add it to the numbers correctly (again, untested, but provable). As with my case, and for the same reasons, if all the variables really should be either currency or number, I'd recommend you fix it. A second set of eyes is a good idea, too - especially if there is any chance of dropping real data. We have some kung fu that I'd be happy to use to help you get unstuck; so if you'd like a hand, drop me a line at doug.denhoed@atappstore.com Regards, Doug

[Doug_Den_Hoed__AtAppStore]

Oh, and btw Polly... I've just remembered that reason I had to dig in and find a solution for my A + B + C in the first place was that every project that did have (text) data in "B" wouldn't even save - even an edit as simple as changing the Project Description - because Oracle failed and rolled back trying to stuff 125000022500003200000 into a calculated number field (which could't hold it; too big). Vexingly, until I figured it our, it of course just seemed like certain random projects wouldn't save, and that the behavior was slowly (as "B" got entered; via the API, btw) getting worse. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[pco88]

@Doug Den Hoed - AtAppStore , I think it may be a somewhat different issue this time. I've actually created 2 number fields A,B,C and then a calculated field (also number) D. When I tested and entered numbers on A,B and C, D calculated. Entered A and B, D also calculated. The weirdest thing is if you leave A blank (seems to be the first field in the SUM list), D does not calculate. Could someone else verify the behavior? Polly Co

[pco88]

Actually, I've verified the behavior. I've added zeros to the first fields in all my SUM calculation and now my calculations are working. What a pain to try to troubleshoot this bug!!! Polly Co

[Doug_Den_Hoed__AtAppStore]

Hi Polly, Thanks for confirming that as long as you poked zeroes into "A", that SUM(A,B,C) = D correctly. That's consistent with the type of code simplification that I suspect went out with the 2018.1 code release: where there might used to have been a check for IF(ISBLANK(A),0,A) in the SUM routine, if that's now been removed (for performance reasons), it's behaving as if the missing A is being treated like a string, and resulting in a blank result (e.g. SUM(BLANK,B,C) = BLANK). If you're certain that A (and B, or C...) will never be Blank going forward, you should be fine. Otherwise, you might want to consider doing the check within your formula, like this: SUM(IF(ISBLANK(A),0,A),B,C) And, sigh, I suppose I better do some testing to see if my A + B + C = D formulas are affected similarly, and do the same thing. I hope I won't have to, though: I have thousands of such calculations across different client environments. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[pco88]

I'm trying to go through all my calculations now to see what else can be broken but my fix of sorts was more simplified SUM("0",A,B,C). Polly Co

[Doug_Den_Hoed__AtAppStore]

Interesting, Polly; nice workaround. I suspect that by passing a zero in as the first parameter, you're coercing Workfront's (latest) SUM logic to treat the result as a numeric. Although redundant, this could become a best practice. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[AdinaPi1]

Thank you for troubleshooting, Polly! I was trying to figure out why a certain calculation didn't work (and this was a valueexpression calculation on a report) and you solved the puzzle for me. It was because one of the values that was in the formula was blank, so it didn't calculate. I changed all the blanks to 0, then it magically worked! Adina

[pco88]

@Adina Pierce , you're welcome! It has been a weird one to troubleshoot for sure. I've already given the helpdesk the root cause of the bug so hopefully they'll fix the problem without breaking the workaround. Polly Co

[pco88]

Just an update for those who are affected by the SUM bug - I've received an email from helpdesk that said they've pushed code to our instance to fix the issue so don't know if code is applied for all. Anyway, I'm not changing my calculations as they're still working with my workaround. Polly Co