2 Replies Latest reply on May 6, 2011 3:30 AM by JohnnyKennedy

    How to create a recurring set of records?



      How to create a recurring set of records?



      I am building a "simple" advertising booking system with FM Pro 11.

      Each record contains the ad details and the week ending (Saturday) date. We record four senarios -

      1. Ad occurs once

      2. Ad occurs for the next number of weeks

      3. Ad occurs every alternate week or month for a number of weeks/months

      4. Ad occurs every week until cancelled.

      The details captured are publication name (we run eight publications), section, publication date - this is always the week ending date (Saturday), which is then converted to a Week Number.

      My scripts search for a publication code and week number to produce a report for each publication and each week etc - all this is working well.

      My problem is (and lack of experience) how do I get a record to automatically generate for senarios 2 to 4. Example, I record an Ad for Week 19, and want it to appear for the next four weeks. How do I get FM to generate records for these additional weeks.

      Thanks in advance for any suggestions

        • 1. Re: How to create a recurring set of records?

          Looks like you need a table for the ad, its status (cancelled or active) and its shedulling options and then another table for scheduling each appearance. Finding all the records for a specific date in the scheduling table will then tell you what ads to include in that issue of your publication. With that design, you can set up a script that finds all non cancelled ad records with options 2 - 4 and then loops through them checking fields in the ad record to determine whether to create a new matching record in the scheduling table.

          You'd need at least this table structure (and likely several more for other aspects of your business):

          Ads----<Schedule  (---< means a one to many relationship)

          Ads::AdID = AdSchedule::AdID

          A layout based on AdSchedule can include any fields you need from Ads to show details about that particular scheduled advertisement.

          • 2. Re: How to create a recurring set of records?

            Thanks PhilModJunk

            Your reply is where I thought I'd have to go, and has sparked more thoughts. I'm off to rebuilt and will let you know how I get on.

            Thanks again