9 Replies Latest reply on Oct 25, 2012 10:32 AM by jayGamel

    Date series calculations



      Date series calculations


           We publish 22 issues of a paper a year, publishing only a Jan 15 and a July 1 and on the 1st and 15th of every other month. I want a calculation that could 

      •           know today's date
      •           increase the issue number by 1
      •           change the day to "1" or "15"
      •           change the year every jan 15

           I'm having no luck whatsoever even getting started on this since dates throw me for a complete loop.


           I have Issue_number, and Issue_date. I suspect it's easier to work with numbers and convert them to a specific date to do this, but  nothing has worked for me.


           Any help gratefully accepted.

           macintel - fmp 11.x

        • 1. Re: Date series calculations

               Ignore the first two. THat's a simple serial number. its the dates that are stumping me.

          • 2. Re: Date series calculations

                 What determines whether the day will be 1 or 15?

                 Do you want to use the current date to determine that? Something like: If day is less than 15 return 1 of the preceding month, otherwise, return 15 of this month?

                 I would think that you'd want to determine this date from the record for the preceding issue instead of today's date.

            • 3. Re: Date series calculations

                   It's every 1st and 15th with two exceptions: Only July 1 and Only Jan 15. There is no July 15 or Jan 1 issue.

              • 4. Re: Date series calculations

                     I am assuming that you have one record for each issue.

                     THis script would take the date in the most recently created record and computes a date for the next issue, allowing for the two skipped dates, and then creates a new record with that info.

                     I will assume that your table is named Issues.

                     Show All Records
                     Sort Records [ No Dialog ; Restore ] //sort on IssueDate in ascending order
                     Go To Record/Request/Page [Last] // after sorting the most recent record will be the last record in the found set.
                     Set Variable [ $Date ; Let ( [ isd = Issues::IssueDate ;
                                                               m = Month ( isd ) ;
                                                               d = Day ( isd ) ;
                                                               y = Year ( isd )
                                                              Case ( m = 12 and d = 15 ; Date ( 1 ; 15 ; y + 1 ); // issue date is for Dec 15th
                                                                         m = 7 and d = 1 ; Date ( 8 ; 1 ; y ) ; // issue date is for July 1st
                                                                         d = 1 ; Date ( m ; 15 ; y ) ; // issue day is the 1st
                                                                         d = 15 ; Date ( m + 1 ; 1 ; y ) ; // issue day is the 15th
                                                                        ) //case
                                                               ) // Let
                     New Record/Request
                     Set Field [ Issues::IssueDate ; $Date ]

                • 5. Re: Date series calculations

                       I'm getting an error here (see pic), but I may be doing it wrong. I've never tried to construct a script this complex:

                  • 6. Re: Date series calculations

                         Don't type in the final square bracket. It represnts the closing bracket in the typical set variable script step:

                         Set Variable [$VariableName ; value: ExpressionGoes Here ]

                         It will be supplied by the script editor when you click OK on the two dialog boxes shown here.

                    • 7. Re: Date series calculations


                           Once again, you have been incredibly helpful. This works beautifully and it has been hugely instructive. I am taking a beginner course in Python (5 weeks in) and I'm starting to get what you're doing here. I'm guessing FM Case is like a 'while' in Python.

                           I owe you for this one (and many others). Let me know if you ever get up to wine country north of San Francico. (Sonoma County).



                      • 8. Re: Date series calculations

                             I live in Central Cal (near Modesto) so you aren't terribly far away...

                        • 9. Re: Date series calculations

                               I'm in Kenwood and you are welcome anytime. We have great food and wine and it's a great getaway destination. I can help find you a great place to stay, too.