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.