Ideas... when you try to fit everything in a Case(), Choose(), If() and you have problems understanding what the hell you coded there, then the solution is to explore the magic of Let(), which allows you to set lots of local variables and combine them as you please into a nice meal that you can eat and digest, too.
Understood... nested if's didn't digest and gave me gas... now looking for some Rolaids
Maybe something like this:
~s = start_date;
not IsEmpty(completionDate1) and completionDate1 <= ~s + 2; “On Schedule”;
not IsEmpty(completionDate1) and completionDate1 > ~s + 2; “Behind Schedule”;
not IsEmpty(completionDate2) and completionDate2 <= ~s + 3; “On Schedule”;
not IsEmpty(completionDate2) and completionDate2 > ~s + 3; “Behind Schedule”;
“Milestone 1: “ & ~m1 & ¶ &
“Milestone 2: “ & ~m2 & ¶ &
Thank you, your answers/ideas have ALWAYS been helpful and I truly appreciate it!
When I stated that the start day is known, that is not always true, eventually it will be but if that date has not been filled in at the point that milestone one is evaluated, it is behind.
God forbid it is still empty at milestone 2 but it is possible.
Also, milestone two has three dates that must be filled in with the allotted time frame for it to evaluate as "on schedule". if any of the three are empty, "Behind Schedule".
milestones 4 and 6 have two dates that must do the same.
you can see why I was in a nightmare for most of yesterday and well into the night trying to use nested if's.
I am considering calculating each milestone in an unstored calc field so the results could be analyzed for global business reporting purposes, but am concerned about performance on the server using unstored calcs within unstored calcs for reports that are doing the heavy lifting.
Those calc fields could be used within the Status field so that the status field notified the user when anything is Behind schedule
thoughts on any or all of the above?
Thanks again, Todd!
I would break it down into small chunks. Decide on the formula for each milestone, whether you are setting the value in a variable or a field. It sounds like the formula will be different for some milestones.
To deal with the empty startDate you could do this:
not IsEmpty(completionDate1) and not IsEmpty(startDate) and completionDate1 <= startDate + 2; “On Schedule”;
If you are going to create fields for each milestone, I would not use unstored calculations. You will pay a price with performance. If the milestones fields aren’t related records, just use stored calculations. It will improve performance. If the milestone fields will be in a related table, set the values using script triggers.
Yes, small and re-usable chunks as there are actually 5 different Milestone time frames depending on the type of Customer, type of product and promised manufacturing, testing and shipping timelines. They all function in basically the same way so once I have one mapped out the rest SHOULD come together quickly.
That is why I reached out to our community. Nested IFs and Case were failing miserably , especially as soon as I introduced the isEmpty.. but I digress.
Am beginning to like storing the Milestone Evaluation results as that really keeps thing in small chunks and simplifies the evaluation formulas within the Status Field which is displayed in a portal showing less than 200 active jobs, filtered via a relationship and not portal filtering. so performance shouldn't take too much of a hit since the Milestone field will be stored calcs.
am thinking that the Status field will look something like:
If(M2_date<M3_date and M2_date > M1_date ;M2_result ;
Thoughts? leave the Nest and visit Let() world??
Thanks again, Todd