Calculation for number of times a custom date field has been changed

Forum|Forum|5 years ago
February 26, 2020
5 respuestas
979 visualizaciones

Hi! I'm building out some custom calculated fields to be used in reporting. One thing I need to record is the number of times a specific custom date field has been updated. I've gotten everything worked out using one of @Doug Den Hoed - AtAppStore concepts but the sum is adding 2 instead of 1. Ignore the weirdness of my field names The concept is that we have a baseline audit date that stays the same after the first entry. If a reschedule is done it goes into the Reschedule date field which updates the Reschedule History field (a calculated field which is working correctly) and the PPS Audit Reschedules (ie the number of times the reschedule date has changed) will add 1. But it adds 2 every time I make an update. The calculation I've used is PPS Audit Reschedules = IF(LEFT(PPS Reschedule History,8)=1st Reschedule Date,PPS Audit Reschedules,SUM(PPS Audit Reschedules,1)) Reschedule History = LEFT(IF(LEFT(PPS Reschedule History,LEN(IF(ISBLANK(1st Reschedule Date),"-",1st Reschedule Date)))=1st Reschedule Date,PPS Reschedule History,CONCAT(IF(ISBLANK(1st Reschedule Date),"-",1st Reschedule Date)," (on ",$$NOW," ) because",1st Reason for Reschedule," | ",PPS Reschedule History)),2000) Here is how it's working on an object. It's adding 2 every time I edit the reschedule date. Whhhhyyyyyy? Kristy Musgrove Havertys

Este tema ha sido cerrado para respuestas.

K

KristyMu1Autor

Level 4

@Doug Den Hoed - AtAppStore You got any pointers on where I'm going wrong on this? Kristy Musgrove Havertys

J

JustinWh

Level 3

I'm definitely interested in seeing a solution for this! Justin White

Doug_Den_Hoed__AtAppStore

Community Advisor

I solved it, Kristy (and Justin). In your calculated form, the PPS Audit Reschedules calculated parameter (where you're adding 1 to track the number of edits) also referred to the PPS Reschedule History calculated parameter. During some heavy testing when we created our "https://store.atappstore.com/product/force-custom-data-double-recalc/" Recalc Parameters solution, I learned that Workfront's calculation routines are iterative in such cases: the first pass calculated both, then (recognizing the dependency) a second pass recalculates the PPS Audit Reschedules...hence, your 2. Realizing that, I changed the formula to add .5 (rather than one), made an edit, and confirmed that the count was then .5 + .5 = 1, as desired. But that seemed pretty fragile. In practice, I recommend "flattening" such calculations by embedding the entire calculation of the PPS Reschedule History calculated parameter's formula into the PPS Audit Reschedules parameter, or at least, the part that matters, having noticed that you'd used LEFT(...m,8) in one formula vs LEFT(LEN(...)) in the other. My observation is that as long as the syntax is exactly the same logic, Workfront foregoes the second pass (so, less fragile). As a result, here are the two formulas I settled on, using the latter technique, and -- yep -- confirming that the count of the changes goes up 1 at a time, as intended. PPS Audit Reschedules: IF(LEFT(PPS Reschedule History,LEN(IF(ISBLANK(1st Reschedule Date),"-",1st Reschedule Date)))=1st Reschedule Date,PPS Audit Reschedules,SUM(PPS Audit Reschedules,1)) PPS Reschedule History: LEFT(IF(LEFT(PPS Reschedule History,LEN(IF(ISBLANK(1st Reschedule Date),"-",1st Reschedule Date)))=1st Reschedule Date,PPS Reschedule History,CONCAT(IF(ISBLANK(1st Reschedule Date),"-",1st Reschedule Date)," (on ",$$NOW,") because ",1st Reason for Reschedule," | ",PPS Reschedule History)),2000) Regards, Doug Doug Den Hoed - AtAppStore