Order export /import utility

The thing is we need to do it for this weekend(!) and the webinar is next week. But I'll let you know how it goes!!!

Please consider the environment before printing this email.
=====This message is intended for the addressee(s) named and may contain confidential information.
If you are not the intended recipient, please delete it and notify the sender.
Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====

Hey there

We've used this functionality for just this purpose this past October. I would be more than happy to talk with you and help out as much as I can. Email me or call me

Jeffrey Cass

Ticket Services Manager

Chicago Shakespeare Theater

312-595-5638

Hi Jeffrey,

Yes please - I'll give you a call today! Thank you!

Hi,

I also have a problem with the section during order import.

Our stalls have 4 sections (one for each door). e.g. Stalls Door 1, Stalls Door 2, Stalls Door 3, Stalls Door 4.

I'm importing an order import file from an agent who has our venue setup in their software with all the stalls as one section.

This is causing a problem during the Ticket Order Import Utility run.

If I put a row in TR_IMPORT_SECTION_MATCHUP for "Stalls" to match "Stalls Door 1", it works for seats in that section but not the others.

Is there any way around this?

I'm thinking I may need to create a script to update the stalls section based on the seat number in TW_ORDER_IMPORT, and maybe put a exec to a local SP to this somewhere in the import process.

Any suggestions appriciated.

Sadly, this post came through email but didn't make it onto the site, so i'm recording it here.  Back to the forum troubleshooting....

From Ryan Creps:

"This is an example of "one to many" that can’t be handled directly by the import.  The import uses a combination of Section, Row and Seat to determine the unique seat_no within a facility.  There is no way to map a single section description “Stalls, e.g.” to more than a single section using TR_IMPORT_SECTION_MATCHUP.  This matchup table was designed for one-to-one matchup (“Orch Left” is named “OR LEFT” in our system) and many-to-one matchups (“Orch Left” and “Orch Right” from the import are mapped to “Orchestra”).

This is going to be an example of where you need to massage the data slightly before passing it to the Order Import.  You can either do this manually or in a more automated fashion by bypassing the bulk load data import process that the utility uses.

There is information in the help documentation for this utility: http://www.tessituranetwork.com/Help_System/Content/Order%20Import/Order%20Import%20Technical%20Processing.htm

Start with the "NOTE:" section at the top of this page.

+Ryan Creps
+Tessitura Network"

We are going into our 3rd year of doing this. Each year has been a little different, with this upcoming year to be the most challenging. 

Thanks Todd for that info.

I understand what you’re saying about the 'one to many' relationship for the section and that I will have to modify the data slightly myself.

In order to keep the process automated, I think I'll add a call to a local SP (something like LP_ORDER_IMPORT_PRE_VALIDATE), just before the 'Validating constituent import data' section at around line 913 in TP_ORDER_IMPORT_MAIN.

(I know this may get overwritten in future Tess updates)

Would it be a reasonable enhancement request to ask for a local procedure call here so that modifications can be made to TW_ORDER_IMPORT here for cases such as this?

Also, another question, Can you configure the xml format file to ignore a column from the source file?

Again, I understand I could open up the source file in excel and delete the column but I'm trying to avoid any excel modification if possible as it increases the risk of human error.

many thanks for all your help,

Dara

Dara,

It was always intended that the avenue for customization be in place of the data import process (data file to TW_ORDER_IMPORT) itself, prior the standard set of checks and validations.  What you can do is get a new Session ID (see the doc), pre-load the data into TW_ORDER_IMPORT using another process (which bypasses the standard Bulk Load process of the utility), alter the data to a "state that the utility expects" and then run the utility as usual, except by passing the session ID that you have already prepared.  The utility bypasses the standard file import if a Session ID is passed.  This gives you the intermediate steps necessary to alter your section data according to your needs.

This would allow you to put a process in place without altering standard procedures.  Hope that helps.

Feel free to submit an enhancement request (we're always open to any request!), but the approach above has always trumped the need for internal "local procedural hooks" in the utility.

Best,

-Ryan 

One small nuance to this that I wanted to mention after reading my post:

For nearly all of the fields in TW_ORDER_IMPORT, there is the "import data field" and the "matching field".  So for section, there is the section column, which gets mapped to the section_id column in the utility matchup process.  If the section_id column is provided a value prior to matchup (aka, in the data import before the utility is called), this value is used over the section text value provided (if at all), thus the matchup is bypassed for this.  The section_id value is still validated against the facility and TR_SECTION table.

Thanks Ryan,

That solution sounds perfect.

Can I just double check that I understand the process correctly, as follows:

1. I setup my own BCP to import source file to TW_ORDER_IMPORT  (Possibly execute this from a custom report in Tess and display the Session ID to the user)

2. Run the Order Import Utility as normal, passing in session id from step 1.  (Currenly the session ID parameter is disabled unless its a reprint, so I'll have to change this in Report Setup? )

many thanks,

Dara

1. I setup my own BCP to import source file to TW_ORDER_IMPORT  (Possibly execute this from a custom report in Tess and display the Session ID to the user)

Ryan:  Exactly.  Or use another Import process of your own choosing (SSIS, OpenRowSet, etc)...

2. Run the Order Import Utility as normal, passing in session id from step 1.  (Currenly the session ID parameter is disabled unless its a reprint, so I'll have to change this in Report Setup? )

 Ryan:  This can be altered by copying the standard report setup definition to your own custom version and removing the disable clause for the Session Id parameter.  It was set up this way to keep it simple for sites that don't customize this process.  In most cases, since you're front-ending your own import process, it's probably easiest to just wrap the TP_ORDER_IMPORT_MAIN with a new custom procedure that does your own custom import then calls TP_ORDER_IMPORT_MAIN with the supplied session id.

Good luck!

-Ryan

Thanks Ryan.

Wrapper idea sounds good. I'll give it a go.