2 Replies Latest reply on Mar 26, 2013 9:29 PM by joshdt

    Magazine database - generating shipping

    joshdt

      Hi All

       

      Just a quick overview... a magazine business sells single issues and subscriptions. The subscription requires multiple future issues to be generated in a related shipments table and to be held untill the issue is ready for dispatch. On some occasions clients purchase a subscription and single issues to give to friends.

       

      Now my issue - I am able to convert an order into a list of issues numbers that are to be inserted into the related child shipping table i.e ( 24, 25, 26, 27, 27, 14, 25 ) with a seperate list of Quantitys that relate to the issues list (as business customers often order several subscriptions) i.e. (2, 2, 2, 2, 1, 1, 1) - this is 2 yearly subscriptions and 3 single issues.

       

      I have set it up that each issue will have its own dispatch line in the shipping table. The problem arrises when trying to condense the lists and make them more intuitive ie. (14x1, 24x2, 25x3, 26x2, 27x3) rather than doubling up the issue on two seperate dispatch lines. NB. I dont really need the condensed list to be IssueXqty - I am just using it as an example.

       

      How could I use a calculation to condence my lists? Or a script? I will be using a script to populate the shipping table from either a condensed list or using the script to condense and populate at the same time... I am just really stuck on the approach to take.

       

      Thanks

        • 1. Re: Magazine database - generating shipping
          usbc

          Hi joshdt,

          You might consider letting the calendar handle some of the load. Subscriptions usually have a expiration date. Once that date is set you can then define what each recipient gets, every month until the expiration month arrives. That will make it much easier for your system to react when the recipient, for example, decides to take advantage of the "early renewal" bonus issue, etc. As opposed to having to unwind a pre-populated table. Consider it as a monthly batch process.

          • 2. Re: Magazine database - generating shipping
            joshdt

            Hi usbc

             

            Thanks for the reply. I initially considered this however the solution then would require two shipping tables. One to handle subscriptions that depend on calendar triggered events and the other by single once off purchases. It got messy with generating a shipping report on an order with a mix of subscription and once off purchases. Thats why I decided to make it simple for the end user of the database and combine them into a simple to read straighforward list of shipments current and future. I dont know if this makes sense or I may be over complicating the process...