3 Replies Latest reply on Jun 2, 2011 10:35 AM by philmodjunk

    irregular sequence



      irregular sequence


      I need to build a table of publishing dates. the product publishes on the first and fifteenth of each month but skips Jan 1 and July 15..

      From that table I need to generate:

      volume and issue numbers (new vol. every year, unique issue numbers: 1 ... xxx

      issue dates

      end dates for subscriptions (start plus 21 issues),

      current issue number and date (to generate text and stories based on release dates)


      do I need to hand build the basic table every year or so or can I set up a formula to do this based on the previous record(s)? would it involve 24 serial if statements? (I just moved up from FM6 to 11 pro advanced so I'm not familiar with all the new bells adn whistles).

      Any help appreciated.

        • 1. Re: irregular sequence

          You can define a table of Volumes--one record for each volume and then an auto-entered serial number can assign the next volume number each time you create a new record.

          You can define a table of issues--one record for each issue linked to the volumes table by the Volume number.

          A script can generate a years worth of records (22) in the issues table. Either the script can use a counter to number each new record consecutively or the script can reset a field's next serial value setting and then each new issues record will automatically number itself in sequence starting with 1 as it is created.

          Go To Layout [Volumes]
          Show All Records
          Sort [Restore ; no dialog ] // not absolutely needed, but makes sure to sort records in ascending order by volume number.
          Go To Record/Request/Page [Last]
          new Record/Request
          Set Field [Volumes::Year ; GetNthRecord [Volumes::Year ; Get ( RecordNumber ) - 1 ) + 1 ]
          Set Variable [$Year ; Volumes::Year ]
          Set Variable [$Vol ; Value: Volumes::VolumeNumber ]
          Go To Layout [Issues]
          Set Next Serial value [Issues::IssueNumber ; 1 ]
             Set Variable [$I ; Value: $I + 1]
             Exit loop If [$I > 24 ]
             If [ ( $I > 1 ) and ( $I ≠ 14 ) // 1 and 14 are Jan 1 and Jul 15 ]
                New Record/Request
                Set Field [Issues::VolNumber ; $Vol ]
                Set Field [Issues::IssueDate ; Date ( ceiling ( $I / 2 ) ; If ( Mod ( $I ; 2 ) ; 15 ; 1 ) ; $Year )
             End IF
          End Loop

            The If block skips creating a record on Jan 1 and Jul 15.
            Ceiling ( $I/2 ) computes the month number.
            IF ( Mod ( $I ; 2 ) ; 15 ; 1 ) computes a day number of 1 for odd numbered values of $I and 15 for even numbered values.
            $Year is computed from the most recently created Volume record to be that year + 1. The first record in this table would need to be created manually.
            The volume records are sorted into the same order they would normally be found if they were unsorted, but if the records in this table are ever imported from another copy of this database, (Say after recovering a damaged copy or when replacing the current copy with an upgraded copy), it's possible for them to be imported in a different order and then the unsorted order might not be correct--so the sort is to make sure of the correct order.

          • 2. Re: irregular sequence

            Phil, I had just starting working with smaller tables and variables to solve this. I'll need some time to digest what you've shown me, but it looks fine to my untrained eyes. i need to grok Nth record concept and deployment.

            the volume and year are actually identities: 2010=vol !, 2011=vol2 -- a publishing conceit that I finally realized was causing duplication.

            I need to keep the year/vol the same for 22 iterations, the dates changing 22 times then resequencing, and the issue numbers need to keep growing one at at time forever. And to be able to pick out the current issue.

            Thanks for your help.

            • 3. Re: irregular sequence

              "...the issue numbers need to keep growing one at at time forever."

              In which case, you can remove the set Next Serial Value step from the above script.