5 Replies Latest reply on Nov 13, 2013 8:54 AM by philmodjunk

    a script to populate projected stage completion dates



      a script to populate projected stage completion dates


           Let us assume that a salesman has 6 months to go from initial presentation to a contract.

           The salesman goes through a set process that always includes the following stages:

           1. the initial presentation

           2. begin study phase

           3. end study phase

           4. sign contract

           Each stage has two fields, one for actual date the event took place, the other for when it is projected to occur given the 6 month cycle.

           I would like to allow a salesman to type in X months he thinks is appropriate to make a sale, then activates a script with a button which will subdivide X months into sections that could be adjusted by me (like say I want to adjust the stage 2 to begin at 20% rather than at 25% of the X months). The goal is to give the sales guy a rough estimate of where he should be versus where he is at.

           I wrote a few very simple scripts before but I'm at a loss as to where I even begin!

           Thank you all for your input.


        • 1. Re: a script to populate projected stage completion dates

               Do you want to adjust the percentages before or after the script is run to produce these dates?

          • 2. Re: a script to populate projected stage completion dates

                 The percentages would be determined and adjusted before the script is run. I imagine myself adjusting the percentages in the script occasionally to better reflect the actual sales cycles that develop as the sales guys start using this tool.

            • 3. Re: a script to populate projected stage completion dates

                   Your "timeline" could be a set of related records or a set of fields in the same record. Either way, you can set up your percentages to mirror the same structure and your script can loop through them to compute the needed "milestone" dates.

                   Let's say that you create related records for your time line, one record for each milestone.


                   Schedule::anyField x Projects::anyField

                   Projects::__pkProjectID = TimeLine::_fkProjectID

                   Schedule has one records for each milestone percentage.

                   Set Variable [$Pcts ; value: List ( Schedule::Pcts ) ]
                   Set Variable [$ProjectID ; Value: Projects::__pkProjectID ]
                   Set Variable [$StartDate ; Value: Projects::StartDate ]
                   Set Variable [$Months ; Value: Projects::MonthsToComplete ]
                   Freeze Window
                   Go to Layout ["TimeLine" (TimeLine)]
                      Exit Loop If [ $K > ValueCount ( $Pcts ) ]
                      Set Variable [$K ; value: $K + 1 ]
                      New Record/Request
                      Set Field [TimeLine::_fkProjectID ; $ProjectID ]
                      Set FIeld [TimeLine::mileStone ; Date ( Ceiling ( $Months * GetValue ( $Pcts ; $K ) ) ; Day ( $StartDate ) ; Year ( $StartDate ) ) ]
                   End Loop
                   Go to Layout [Original Layout]

                   And a very small alteration of this can put each milestone date on the first of the calculated month rather than the same day as the Project Start date. Make sure that your percentages are either entered as their decimal equivalents or modify the calculation I've used to divide by 100.

                   Note: using a set of fields in one record, while it can work, is less flexible than using a set of related records.

              • 4. Re: a script to populate projected stage completion dates

                     Thank you for your help. I spent a lot of time yesterday making myself familiar with the code but I am still confused on a few basic things.

                     The existing setup that I am modifying has a client table with a record for each client, milestone fields are in the same table. So I would need to use the less flexible method of using a set of fields in one record. The way I understood this is that I need to create a new table with one record with fields identical to milestones and enter the decimals representing percent values.

                     But if that's the case, how do I declare that in place of Set Variable [$Pcts ; value: List ( Schedule::Pcts ) ]

                I'm not sure how to set a variable to more than one field or if that even makes any sense.

                • 5. Re: a script to populate projected stage completion dates

                       I would redesign your database to replace those separate fields with a set of related records. A script could be used to move the data from existing records into such a set of related timeline records.

                       It makes it possible to create new and very different timelines for special projects or to adapt to changing business models simply by adding more records instead of defining more fields in the table.

                       Whether you do that or not, you can still create the schedule table I described exactly as I described it. It does not need to be a single record with multiple fields. What will change will be how the script loops through fields of a single record instead of looping to create a set of new related records.