Limiting a delivery method

Not easily.  LTR_TNEW_DYN_SHIPPING_METHOD works in two ways.  If a performance is not listed, then all shipping methods are available.  If at least one shipping method is listed, then only listed shipping methods are available.  Managing that table is such a bear (for instance, we only turn on print-at-home shipping for all shows in our new season after our initial unseated subscription sales period has ended) that I built an management utility to help our Box Office.

But basically if you have any kind of global restriction, it means that every show has to have all of its legal shipping methods explicitly entered into the table.

Thanks Gawain, that's what I feared! 

You can try coding a checkout customization to interrupt and correct things that are wrong; either by changing the delivery method on them if they choose that delivery method for a performance that should not have it, or you can force them into that delivery method if they DO have that performance (and possibly additional criteria), but that is obviously not the greatest solution.  That said, we are getting ready to go live with our own TNEW site soon, and we will be doing that for some things because we have similar situations.

What I have is a system that lets the Box Office control shipping methods on performances using Keywords (which is how it should be set up, frankly).  Since that involves running a sql job that re-writes the LTR_TNEW_DYN_SHIPPING_METHOD based on keywords that it finds, I also had to create a secondary table to configure other, standard shipping method controls, particularly for things that didn't accept keywords (we needed to at least be able to specify Gift Certificates' shipping restrictions).  I can share it if people are interested.

That'd be wonderful Gawain.  My LTR_TNEW_DYN_SHIPPING_METHOD is almost as out of control as my LTR_TNEW_DYN_PAYMENT_METHOD

I've been reviewing it, and it needs some work, actually.  For convenience, if I detected a change, then I just deleted all of the current entries in LTR_TNEW_DYN_SHIPPING_METHOD  and regenerated it.  Not the most efficient thing, but I didn't think it would be a problem.

But now I see that the id column is approaching 3 million, so I think I need to make it just a little more sophisticated...  Maybe in time for the conference!

Does anyone remember if v7 handles this differently/better?

Thanks John, this is really helpful!

Heath WilderMichaela Donnelly

I fixed the problems I had excessive churn on LTR_TNEW_DYN_SHIPPING_METHOD.  I'm not sure what the best way is to share projects like this, let me know if you have any ideas.  Here's the readme text:

TNEW Dynamic Shipping Fix

Warning: this is a custom procedure set developed for Cal Performances internal use.  No warranty of any kind is attached: use at your own peril.

Manifest

Functions:

LF_CPSMA_TNEW_FIX_UPDATE_DYN_SHIP.sql

LFT_CPSMA_PROD_ELEM_PKG_SELECT.sql

Procedures:

LP_CPSMA_TNEW_FIX_DYN_SHIP.sql

LP_CPSMA_TNEW_FIX_DYN_SHIP_MAIN.sql

Tables

LTR_CPSMA_TNEW_FIX_SHIP_KW.sql

LTR_CPSMA_TNEW_FIX_SHIP_METHOD.sql

How It Works

First you set up a set of keywords for each shipping method that you use online.  You can, if you choose separate out the keywords by organization so that you can control a performance separately based on the site it is being sold through.  When these keywords are created, they are then connected with the appropriate shipping method with LTR_CPSMA_TNEW_FIX_SHIP_KW.

Now your performances (or other production elements) and packages can be flagged with keywords, and the keywords will then be translated into rows in LTR_TNEW_DYN_SHIPPING_METHOD.  To simplify things for the procedure, whatever production elements are keyworded, only the associated performances are entered into LTR_TNEW_DYN_SHIPPING_METHOD. Since there are two other items that can feed into LTR_TNEW_DYN_SHIPPING_METHOD (price types and payment methods), and also to allow other “standard” production element configurations, there’s a second table, LTR_CPSMA_TNEW_FIX_SHIP_METHOD, which is an expanded and improved version of LTR_TNEW_DYN_SHIPPING_METHOD.  Configurations in this table also feed into LTR_TNEW_DYN_SHIPPING_METHOD.

LFT_CPSMA_PROD_ELEM_PKG_SELECT is a function used as a selection table for LTR_CPSMA_TNEW_FIX_SHIP_METHOD, displaying text values for production elements or packages, and limiting them to current products (based on product dates or associated seasons’ end dates).

LP_CPSMA_TNEW_FIX_DYN_SHIP now should be configured to run as a SQL Server Agent Job (alternatively you could set it up in a utility if you only want manual updates.  It takes two parameters: @dry_run (0 = run with output, -1 run silently, anything else is a dry run with output) and @force_update (1 = force update even if no tables have changed, anything else, only update on changes, see below).

Unless @force_update is set to 1, LP_CPSMA_TNEW_FIX_DYN_SHIP will first run LF_CPSMA_TNEW_FIX_UPDATE_DYN_SHIP.  This looks at every table that might feed into dynamic shipping configuration, and if it detects a recent update (vs the last update on LTR_TNEW_DYN_SHIPPING_METHOD) it will trigger an update (this is actually LP_CPSMA_TNEW_FIX_DYN_SHIP_MAIN), otherwise it will simply end.

If it doesn’t, it review all keywords on current (as defined above) production elements and packages, as well as any current production elements and packages in LTR_CPSMA_TNEW_FIX_SHIP_METHOD.  It will also pull in price types and payment methods from the table. Then if anything in LTR_TNEW_DYN_SHIPPING_METHOD isn’t in that list it is deleted, and if anything is missing from that table, it is now added.

One note about LP_CPSMA_TNEW_FIX_DYN_SHIP_MAIN: it uses a custom function, not supplied, to fill created_by and last_updated_by.  This should just be replaced by dbo.fs_user(), or any custom function used to do the same (basically, you need a function to enter that value, as the triggers on LTR_TNEW_DYN_SHIPPING_METHOD will fail if the database user in question has a name longer than 8 letters.