• Replies 7 replies
  • Subscribers 338 subscribers
  • Views 24 views
  • Users 0 members are here
Related

NCOA in the UK

Mark Steel (Past Staff Member)
$organization

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

Parents Reply Children
  • $organization in reply to Mark Steel (Past Staff Member)

    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_incoming
    inner join    t_address
        on    tw_ncoa_incoming.address_no = t_address.address_no
    where    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!

     

  • $organization in reply to Simon Davidson

    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.

  • $organization in reply to Simon Davidson

    Hi Simon

    Ive opened a support call, ill let you know what the outcome is.

    Thanks

     

    Mark

  • $organization in reply to Mark Steel (Past Staff Member)

    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

  • $organization in reply to Rathi Kumar

    Ignore the comment about the link table... the system table documentation shed light on that.