== OnContact Configuration ==
=== Configuration File ===
Verify the configuration file: ''C:\Program Files\Oncontact Software\bin\oncontact.cfg'':
<code xml>
<Oncontact>
  <profiles>
    <current_profile>oncontact</current_profile>
    <profile_list>oncontact</profile_list>
    <profile profile_name="oncontact">
      <connection_type>MSSQL</connection_type>
      <vendor>MSS</vendor>
      <connect_string>server=CRMSERVER\ONCONTACT;uid=sa;pwd=orlando;database=oncontact;application name=oncontact;pooling=true;</connect_string>
      <lock>RU</lock>
    </profile>
  </profiles>
  <environment>
    <application_authentication>Oncontact</application_authentication>
    <login_dialog_user>jgrabbe</login_dialog_user>
  </environment>
  <parms>
    <log_exceptions>Y</log_exceptions>
    <serial_suffix>CUS</serial_suffix>
    <agents_dir>agents</agents_dir>
    <agents_clear>true</agents_clear>
  </parms>
  <reportprofiles>
    <reportprofile profile_name="oncontact">
      <connection_type>OLDEDB</connection_type>
      <connection_provider>SQLOLEDB</connection_provider>
      <server>CRMSERVER\ONCONTACT</server>
      <database>oncontact</database>
      <credentials_source>db</credentials_source>
      <userid>sa</userid>
      <password>orlando</password>
    </reportprofile>
  </reportprofiles>
</Oncontact>
</code>

=== Backup Databases ===
  * Install  [[http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796|MS SQL Server Management Studio Express]] (free download from Microsoft).
  * Connect to database ''CRMSERVER\oncontact''
  * Perform a [[http://msdn.microsoft.com/en-us/library/ms187510.aspx|database backup]].


=== Fixing Failed Imports ===












==== Check Data ====

===== Comparing Data =====
* If the data in the Oncontact database looks fine, but there are still issues, then start by comparing this data: <code sql>
  SELECT onci_xref_W5NQN3QCUS.s_id, 
         oncd_company.account_number, 
         onci_xref_W5NQN3QCUS.s_ship_to, 
         oncd_company.ship_to, 
         onci_xref_W5NQN3QCUS.t_company_id, 
         oncd_company.company_id 
    FROM oncd_company, onci_xref_W5NQN3QCUS 
   WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id
</code>Or for a specific account:<code sql>
  SELECT onci_xref_W5NQN3QCUS.s_id, 
         oncd_company.account_number, 
         onci_xref_W5NQN3QCUS.s_ship_to, 
         oncd_company.ship_to, 
         onci_xref_W5NQN3QCUS.t_company_id, 
         oncd_company.company_id 
    FROM oncd_company, onci_xref_W5NQN3QCUS 
   WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id
     AND oncd_company.account_number like 'GA7%'
</code> Check for invalid record: <code sql>
SELECT * FROM onci_xref_W5NQN3QCUS 
WHERE s_id = 'ID' 
AND s_ship_to = 'Ship-To'
ORDER BY s_ID
</code> Delete it if bad record exists: <code sql>
DELETE FROM onci_xref_W5NQN3QCUS 
WHERE s_id = 'ID' 
AND s_ship_to = 'Ship-To'
</code> Run the previous statements to compare the fields ''s_ship_to'', ''account_number'' and ''company_id'' between the main company table ''oncd_company'' and the cross reference table ''onci_xref_W5NQN3QCUS''.  The only thing that might look unusual is the leading 0's in ''oncd_company'' that are not in ''onci_xref_W5NQN3QCU'', but it does not seem to matter. 0 = 0000, 0024 = 24, and so on, so if ''account_number'' and ''s_ship_to'' match, the ''company_id'' is updated in ''onci_xref_W5NQN3QCUS'' to match ''oncd_company''.

* The dedup utility only targets ''oncd_company.account_number'' and ''oncd_company.s_ship_to'' (assuming that this is the same criteria as the import).

* Verify that that the cross ref table is not empty when running the import.

===== Update =====
* Perform the following queries, but test completely on the test database first.  Before running this against your production database, be sure to back it up just as a safeguard.
* Fix the integrity issue.  Perform this query to update the data: <code sql>
  update onci_xref_W5NQN3QCUS 
    set t_company_id = (select max(company_id) from oncd_company 
                        where onci_xref_W5NQN3QCUS.s_id = oncd_company.account_number 
                          and onci_xref_W5NQN3QCUS.s_Ship_To = oncd_company.ship_to)
</code>

* Verify the count.  When the update is completed, run the following to see how the row count has changed: <code sql>
  select * from oncd_company 
  where 0 = (select count(t_company_id) from onci_xref_W5NQN3QCUS 
             where oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id)
</code> Or by identifying a specific account number:<code sql> 
  SELECT * FROM oncd_company 
  WHERE 0 = (SELECT count(t_company_id) FROM onci_xref_W5NQN3QCUS 
             WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id)
  AND oncd_company.account_number LIKE 'GA76%'
  ORDER BY company_name_1
</code>**Note**: The result of this statement should be smaller but it may not be 0. Companies can be entered manually in ''oncd_company'', and may not be in the cross reference table.



==== Remove Bad Characters ====

Removing bad characters from the file ''\\FILESERVER\Act\Weekly Updates\weeklyupdate.txt''.

  * Remove the '/ /' and '\ \' from around some text in the ''notes'' field, so that the import works correctly.  
  * Verify that there are no spaces before the '/ /', in case the system sees it as a “Tab”.
 
Verifying the data:
  * Look at the 'Loading file..' window. It is a good way of checking to see if the data is even making it to the import, and import agents.

NOTE: It is possible to increase the column length of the source columns in the data source to be equal to that of the target OnContact columns. Example: Increase your source columns ''A/R_Comments'' and ''Sales_Comments'', both, from 100 char to 250 char, since the target custom OnContact ''comments'' columns are both nchar 250. 

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

From: Adam W. Howell [mailto:ahowell@audina.net] 
Sent: Tuesday, March 18, 2008 1:50 PM
To: Wessel, Elizabeth
Subject: RE: weeklyupdate file

Wow I feel really silly now.  I never caught that.  It makes total since now that I think about it too b/c every time they ask me to fix something it is always an account number that starts with a letter after P from what I can recall.  I ran the import with the file and got 4015 rows.  Thanks again for all your help.  I will do some investigating into that record to see what the problem is.  I have a feeling it may have something  to do with the '/ /' and '\ \' around that phone number.  I will let you know.



From: Wessel, Elizabeth [mailto:Liz@oncontact.com] 
Sent: Tuesday, March 18, 2008 2:29 PM
To: Adam W. Howell
Subject: weeklyupdate file

Hi Adam,

I tested this file and found that there was a 'bad' row of data in the file that appeared to prevent ~ 1300 rows from importing. When I tested here, I noticed that the initial 'Loading file.." window showed that it only loaded 2738 / 4016 rows (please see screenshot below) loaded. So, the problem lies not with the import, but the data. 

I then let the import run the 2738 rows in, then found where the import stopped import (on the PA3272 records) by selecting from oncd_company and order by updated_date. 'Better Hearing Centers (2083)' was at the top, so I found it in the text file, and removed the row under it, "PA3272","AUDIOLOGY & HEARING    (H1013)"

The specific row is:

  "PA3272","AUDIOLOGY & HEARING    (H1013)","AID DISPENSERS","642 BROAD STREET","CLIFTON","NJ","07013","0418","PAT BIONDI","02","9737775335","12/27/07","","REC. BY MIKE POOLE    //(973)777-0011\\","","N30"

I am not entirely sure what is wrong with this row. If you DTS this file to a SQL table, you'll see that the rows are off-kilter at some point. 

Please re-run the attached file, and let me know if you still are not able to import all 4015 (I removed the 'bad' one) or not, and specifically the row with account WA8600. Please also let me know if you have anyt questions.