6 Replies Latest reply on Feb 4, 2016 2:21 PM by thebridge

    Help with Nested IF/Case/Let and a Multi Milestone evaluation

    thebridge

      Greetings Everyone,

       

      This project is FileMaker 11

       

       

      I have a 14 day time period  and we know the start date

       

      there are 8 milestones in between start date and end date(which is calculated)

       

      as time passes, each milestone needs to be evaluated for On schedule or behind schedule

       

      the result of each milestone evaluation does not need to permanently captured (although this may be a feature needed at a later time so I would love some ideas on this as well)

       

      The result of the evaluation does need  to be displayed in the calculated Status field until the next milestone arrives and is evaluated for "On Schedule/Behind Schedule"

       

      There are manually entered and calc date fields that the evaluations are test against:

       

      Example,

      Milestone 1 is On Schedule if the date of its completion is Equal or less than start_date +2, if greater than, it is Behind Schedule

       

      Milestone 2 is On Schedule if the date of its completion is Equal or less than start_date +3, if greater than, it is Behind Schedule

       

      and so on until the 14 days have been evaluated.

       

      I have had no success using Nested IF statements which I thought were the only way to keep the calculation going after the first result tested true.

       

      Ideas?

       

      Thank you,

      Robert

        • 1. Re: Help with Nested IF/Case/Let and a Multi Milestone evaluation
          siplus

          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.

          • 2. Re: Help with Nested IF/Case/Let and a Multi Milestone evaluation
            thebridge

            Thanks

            Understood... nested if's didn't digest and gave me gas... now looking for some Rolaids

            • 3. Re: Help with Nested IF/Case/Let and a Multi Milestone evaluation
              mtwalker

              Maybe something like this:

               

              Let([

              ~s = start_date;

              ~m1=

                  Case (

                      not IsEmpty(completionDate1) and completionDate1 <= ~s + 2; “On Schedule”;

                      not IsEmpty(completionDate1) and completionDate1 > ~s + 2; “Behind Schedule”;

                      “”);

              ~m2=

                  Case (

                      not IsEmpty(completionDate2) and completionDate2 <= ~s + 3; “On Schedule”;

                      not IsEmpty(completionDate2) and completionDate2 > ~s + 3; “Behind Schedule”;

                      “”);

              etc

              ];

               

              “Milestone 1: “ & ~m1 & ¶ &

              “Milestone 2: “ & ~m2 & ¶ &

              etc

               

              )

              • 4. Re: Help with Nested IF/Case/Let and a Multi Milestone evaluation
                thebridge

                Hi Todd'

                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!

                Robert

                • 5. Re: Help with Nested IF/Case/Let and a Multi Milestone evaluation
                  mtwalker

                  Robert,

                   

                  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”;

                  etc.

                   

                  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.

                   

                  ~Todd

                  • 6. Re: Help with Nested IF/Case/Let and a Multi Milestone evaluation
                    thebridge

                    Todd,

                     

                    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(M1_date<M2_date;M1_result;

                    If(M2_date<M3_date and M2_date > M1_date ;M2_result ;

                     

                    etc

                     

                    Thoughts? leave the Nest and visit Let() world??

                     

                    Thanks again, Todd