Hello
I have been looking at the NCOA utility for our Box Office Manager. However I have come across a problem during the processing stage. I believe the field of sstate must reference TR_STATE, however TW_NCOA_INCOMING only accepts 2 characters in the sstate field (which does not match our TR_STATE table which features ID's with more than 2 characters).
Firstly I was unable to import the data as sstate contained more than two characters, so for testing purposes I amended the table to allow more than two characters.
I then tried to process the data however I recieved an error relating to FK_T_ADDRESS_TR_STATE_state.
Has anyone else experienced this problem or does anyone have a solution to this.
We would like to begin cleaning our data using QAS however QAS will return a state value of more than two characters.
Any help would be much apprechiated.
Thanks
Mark
Hi Mark,
We'd like to step in and take a look at this in more detail. Would you please get a support ticket opened up? Someone in development is waiting to pick it up when it comes in.
Richie
Thanks Richie, will do
Hi Guys,
I'm also having this problem after having cleaned our data with QAS. This is what I've found that I had to do atm, (all on test!).
1. Modify the tw_ncoa_incoming table as many of the fields are too small, specifically the zip/postcode fields.
2. Make sure that there is a rule in the TX_NCOALINK_RESULT_ACTION for the QAS result codes.
3. Make sure all the states in the return data are in the TR_States table.
I've even run the following query to make sure that there are no states there shouldn't be, but still throws that error.
select tw_ncoa_incoming.*from tw_ncoa_incominginner join t_address on tw_ncoa_incoming.address_no = t_address.address_nowhere not exists (select 1 from tr_state where id = tw_ncoa_incoming.nstate and country = t_address.country)
If you could pass on any details you find from the ticket would be grateful.
Thanks!
I think I've found the problem.
Looking at the AP_NCOA_PROCESS procedure, when it tries process the data it loads the data from the cursor into multiple variables. I've noticed the state variable is only two characters, so even though all of the states are within the TR_State table, they get truncated down. I'm in the process of modifying the stored procedure and going to test it.
Will let you know how it goes.
Hi Simon
Ive opened a support call, ill let you know what the outcome is.
Hi both,
We are currently trying to import data cleansed by Capscan using the NCOA utility. Did you get a solution to the state problem? Also, I'm unsure what needs to be added to the tx_ncoalink_result_action table so that our capscan cleansed data will import successfully. Can you shed some light on this?
Any help appreciated as always.Thanks,Rathi
Ignore the comment about the link table... the system table documentation shed light on that.