Difference between revisions of "How to Run a Batch Update of All Customer Records for Web Access"
| Line 11: | Line 11: | ||
|applicableto=Supportworks ESP | |applicableto=Supportworks ESP | ||
}} | }} | ||
| − | |||
| − | |||
This document gives details of how you can quickly give all customers access to SelfService, by suitably updating their records all at once using SQL, rather than having to go through each customer's record individually in the normal way. | This document gives details of how you can quickly give all customers access to SelfService, by suitably updating their records all at once using SQL, rather than having to go through each customer's record individually in the normal way. | ||
Latest revision as of 13:52, 20 August 2015
| Status: | Published |
|---|---|
| Version: | 1.0 |
| Authors: | HTL QA |
| Applies to: | Supportworks ESP |
This document gives details of how you can quickly give all customers access to SelfService, by suitably updating their records all at once using SQL, rather than having to go through each customer's record individually in the normal way.
An alternative approach would be to use the Data Import Manager, which is provided with a Supportworks installation.
The easiest way to achieve this is to set up one customer's access options as you require using the normal client GUI, then obtain the underlying numerical value of this set of options from the database (via SQL) and finally apply this value to all customer records (again via SQL). This simple method assumes that you want all customers to end up with exactly the same set of access options. If you want different kinds of customers to have different degrees of Web (or Auto Responder) access, then you would have to pick one "model" customer of each kind and repeat the exercise that many times, applying each set of access options according to the relevant customer criteria.
The following instructions are applicable to the simple scenario, and it is assumed that your database is SwSQL. For other databases, you should contact your Database Administrator to see what query tools are available.
Before proceeding, please ensure that you have a full backup of your Customer table (userdb).
- Log into Supportworks via the client and set the required Web access options for a given customer. This information is on the Customer Details Form and the section may be called "SelfService Rights" or "Access to Service Desk". Once the change has been made, make a note of the Customer ID.
- Run the Interactive SQL program by selecting Start > Programs > Hornbill Core Services > Interactive SQL.
- Enter the following SQL statements (replacing Customer-ID with the Customer ID you noted in Step 1):
- use swdata;
- select keysearch, webflag from userdb where keysearch = 'Customer-ID';
- Note the WebFlag value from the results returned and enter the following SQL statement (replacing value with the actual returned value):
- update userdb set webflag = value;
- On completion of the update, all customer records will have the same access options.
The changes you have made will now be immediately available.