TR_COUNTRY

This is an older thread, but I wanted to reply to indicate that we're interested in this as well. We need to be able to handle the entry of foreign addresses and phone numbers better than we do today. I'm also wondering if the lack of some configuration in TR_COUNTRY has something to do with our difficulties processing some non-US credit cards.

Thanks,
David 

We recently found out that use_avs field in the TR_COUNTRY table needs to be checked for TranScend to work.  The Transcend_and_Tessitura_Setup.doc reads:

"...in the TR_COUNTRY table, the use_avs field must be set to Y for all countries. Even if the Address Verification box is checked, if the
use_avs field is not set to Y for the country of the address being submitted, address data will not be sent and TranScend will reject the
transaction."

From what I understand, this is necessary but may not be be sufficient in all cases for transactions to succeed. Checking use_avs made it possible for us to successfully accept transactions from Australia.

With this note, I am hoping to bring the thread to back to life.  I am particularly interested in proper formatting related to postal addresses.  If someone from a few different countries provide the proper format in their country and one additional country they do most business with, we would cover a lot of real estate.  Here is how the formating should be for the US and Australia (if I got it right from Internet searches):

---------------------------------------------------------------------------------------------------------------------------------------------------

Description/Short Desc:  USA

Phone Edit String:  (###) ###-#### ~x####

Zip Mask:  @@@@@-@@@@

Zip Editstring:  #####-####

Use State Field:  Yes

Req pcode:  Checked

Zip Valid Lengths: 5,9

Phone Valid Lengths:

Use Avs: Checked

Req City: Checked

---------------------------------------------------------------------------------------------------------------------------------------------------

Description:  Australia

Short Desc: AUT

Phone Edit String: +61 @@ @@@@ @@@@ @@@@

Zip Mask: @@@@

Zip Editstring: ####

Use State Field: Yes

Req pcode: Checked

Zip Valid Lengths:  4

Phone Valid Lengths:

Use Avs: Checked

Req City: Checked

---------------------------------------------------------------------------------------------------------------------------------------------------

By the way, "state"  values for the drop-down in Tessitura address entry must be provided in a table somewhere, which I have not yet looked into.  They were available for Australia by default.

Unknown said:

With this note, I am hoping to bring the thread to back to life.  I am particularly interested in proper formatting related to postal addresses. 

the best resource I know of for international postal address standards is this great site:

http://www.columbia.edu/~fdc/postal/

Unknown said:

By the way, "state"  values for the drop-down in Tessitura address entry must be provided in a table somewhere, which I have not yet looked into.  They were available for Australia by default.

Take a look at the system table TR_STATE.

TR_STATE is the table, thanks.  It comes populated for Australia, Canada, UK, and USA.

Tessitura really ought to ship with the entire ISO 3166 standard loaded into T_COUNTRY and T_STATE, and updates for it ought to be a standard part of the upgrade process.  When we converted from Raiser's Edge this past year I based my mapping on the ISO standard, although I haven't yet had a chance to load the whole thing.

Most organizations will never need 90% of those codes, but I think it's the sort of thing that you're better off having completely set up so that it's there when you need it.

Unknown said:

Tessitura really ought to ship with the entire ISO 3166 standard loaded into T_COUNTRY and T_STATE,

That would be cool.

Unknown said:

and updates for it ought to be a standard part of the upgrade process.

That would be nice, too, but I suspect the network would leave these up to each org. When we started doing monthly Zip updates to TR_CITYSTATE a few years ago I don't think the Zips that came with Tessitura had been updated since 2001, iirc.

There is also the TR_CITYSTATE table.  None of these tables are local tables in Tessitura, and one would expect that Tessitura ships with all of them loaded, and updating them are a standard part of the software upgrade process.

We came across an issue, where one of the patrons did not like the locale that came up as the default for the  postal code he provided.  It turned out that the locale he insisted on was the preferred locality, as declared by the USPS.  Tessitura's NCOA utility did not correct this, either.  I touched on this issue in one of the recent support tickets we had.

Tessitura should be able to contract out this task easily for cost effectiveness reasons, if necessary.  The cost would then be minimized for each member organization.

We are one of those organizations who do not need most entries in the TR_COUNTRY table.  However, once in a while an exception comes up and creates an unexpected havoc.  Luckily, most credit card operations, which use the addresses in the database for verification, seem to be quite robust against most variations.

There is also the TR_CITYSTATE table.  None of these tables are local tables in Tessitura, and one would expect that Tessitura ships with all of them loaded, and updating them are a standard part of the software upgrade process.

We came across an issue, where one of the patrons did not like the locale that came up as the default for the  postal code he provided.  It turned out that the locale he insisted on was the preferred locality, as declared by the USPS.  Tessitura's NCOA utility did not correct this, either.  I touched on this issue in one of the recent support tickets we had.

Tessitura should be able to contract out this task easily for cost effectiveness reasons, if necessary.  The cost would then be minimized for each member organization.

We are one of those organizations who do not need most entries in the TR_COUNTRY table.  However, once in a while an exception comes up and creates an unexpected havoc.  Luckily, most credit card operations, which use the addresses in the database for verification, seem to be quite robust against most variations.