5 Replies Latest reply on Sep 5, 2013 4:13 AM by philmodjunk

    Automatic creation of the last 12 months

    paolobkk

      Title

      Automatic creation of the last 12 months

      Post

           Hi Everyone!

           I have a Customer table and a related table listing dummy records of months/year (every month has a total sales for this customer).

           The list is normally updated with a daily script that creates a new month if the current month is missing.

           I need to change that script to add not only the current month if is missing, but to add also the previous 12 months.

           This is because the staff forgets to put a customer in database and then they have to report every previous month missing after the creation of the Customer record.

           This is the current daily server script: (see screenshot)

           (the "perform find" script step is: Find: customer_months_dummy::date = $date)

           Any idea is very well appreciated!

      Screen_Shot_2013-08-21_at_16.17.02_.png

        • 1. Re: Automatic creation of the last 12 months
          philmodjunk

               Try:

               Set Variable [$Date ; value: Year ( Get ( CurrentDate ) ) ]

               Then your script finds all records for the current year. (and I'd rename that variable as $Year instead of $Date.)

               Sort them by date in ascending order by date, go to the last record and then use a loop to create new records until the month of the newly created record is the same as Month ( Get ( CurrentDate ) ).

               This will not fill in "gaps" in the months should that be necessary.

                

               Set Variable [$Year ; value: Year ( Get ( CurrentDate ) ) ]
               Set Error Capture [on]
               Perform Find [Restore]
               Sort Records [Restore ; no dialog ]
               If [ Get ( FoundCount ) ]
                  Go to Record/Request/Page [Last]

               Else
                   New Record Request
                   Set Field [ customer_months_dummy::date ; Date ( 1 ; 1; $Year ) ]
               End If
               Set Variable [ $Month ; Month ( customer_months_dummy::date )
               Loop
                   Exit Script if [ $Month  = Month ( Get ( CurrentDate ) ) ]
                   New Record/Request
                   Set Field [ customer_months_dummy::date ; Date ( $Month ; 1 ; $Year ]
                   Set Variable [$Month ; Value: $Month + 1 ]
               End Loop

          • 2. Re: Automatic creation of the last 12 months
            paolobkk

                 Hi Phil,

                 This seems to work, but how does it manage the year change?

                 For example, it happens that the users create  a new customer today (August 2013)  and then they fill in the last 10 months reports, will it consider the year as 2012 for November and December?

                 Also they don't really follow a creation order for the reports. First report can be March 2013 or December 2012.

            • 3. Re: Automatic creation of the last 12 months
              philmodjunk

                   The script creates one record for each month for the current year from January to today's month and stops. So in August it produces records from January to August. In September, it will create from January to September and so forth. The code assumes that there are no gaps in the existing records, but a small variation in the script could set it to loop through the existing records, creating new ones to fill gaps. And another small change could set it to do this for all 12 months instead of stopping with the current month.

              • 4. Re: Automatic creation of the last 12 months
                paolobkk

                     Hi Phil, Thank you for your reply.

                     I understand the system, but I need something to take care of the year change also.

                     Another example: In February 2014 the users will input new custmers and also some reports of 2013. Your script will generate only January 2014 and February 2014 right?

                     In that case the script should generate 12 months starting from March 2013 till February 2014.

                • 5. Re: Automatic creation of the last 12 months
                  philmodjunk

                       Yes, the script, as written, only generates records for the current year, but a server scheduled version of this script could be running every night to add new records where needed and thus that scenario (March 2013 till February 2014) would not happen.

                       But here is a modification of the above script that will generate records for the previous 12 months:

                       Set Variable [$Start ; value: Let ( [T = Get ( CurrentDate ) ; D = Date ( Month ( T ) - 112 ; 1 ; Year ( T ) ) ] ; Date ( Month ( D ) ; 1 ; Year ( D ) ) ) ]
                       Set Error Capture [on]
                       Enter FInd Mode []
                       Set Field [ customer_months_dummy::date ; ">" & $Start ]
                       Perform Find []
                       Sort Records [Restore ; no dialog ]
                       If [ Get ( FoundCount ) ]
                          Go to Record/Request/Page [Last]

                       Else
                           New Record Request
                           Set Field [ customer_months_dummy::date ; $Start ]
                       End If
                       Set Variable [ $Month ; Month ( customer_months_dummy::date ) ]
                       Set Variable [$Year ; Year ( customer_months_dummy::date ) ]
                       Loop
                           Set Variable [$Month ; Value: Mod ( $Month ; 12 ) + 1 ]
                           Set Variable [$Year ; Value: If ( $Month = 1 ; $Year + 1 ; $Year ) ]
                           Exit Script if [  Date ( $Month ; 1 ; Year$ ) > Date (  Month ( Get ( CurrentDate ) ) ; 1 ; Year ( Get ( CurrentDate ) ) ) ]
                           New Record/Request
                           Set Field [ customer_months_dummy::date ; Date ( $Month ; 1 ; $Year ]
                       End Loop