Page History: XLS Update Tool
Compare Page Revisions
Page Revision: Tue, 14 Aug 2018 09:27
General Information
Default Menu Location: N/A
Screen Name: XLSUPDATE2
Function: This screen will allow you to update multiple SQL records at one time.
Using the Tool
To use the XLS update tool, you will need to have the correct excel template filled out with the information you wish to change.
1. Once your file is ready, open the XLS update screen and click browse.
2. Select your file.
3. Map the fields by clicking the Map Fields button. Any fields that are in blue on the right side will need to be mapped to one from the left side. To map them, double click on a field from the left grid, then double click on the field you want to map it to in the right grid. Both fields should turn green.
4. Once all of your fields have been mapped, click the Update button.
Template Fields
Fields
- TableName - The name of the table in the SQL database you wish to update.
- KeyName# - The column you want to search in the SQL database.
- KeyValue# - The specific record in the column you want the update to apply to.
- IsKey - This acts like an IF statement. Y means if the preceding fields are true, then use the update that follows. N means that if the preceding fields are false, then use the update that follows.
- CID - This is the CID to apply the update to.
- FieldName# - The column you want to update in the SQL database.
- FieldValue# - The new value you want to take place of the old value.
In the example below, the item
ABC123 will have its description updated to be
A Big Coil 123Examples
Case 1 - You have the values for all the filters.
In this case,
TableName = Table to Update
KeyName 1-4 = Names of the Fields to Filter On
KeyValue 1-4 = Filter Values
IsKey = y
KeyType = (Blank)
If the table has a CID, you can enter the CID code, and set UseCID = 'y' as another filter
FieldName 1-4 = Fields to Update
FieldValue 1-4 = Data to Update With
Case 2 - You have Item or Custno
TableName = Table to Update
KeyName1 = The custID or ItemID field in the Table to Update (entid/vendid/custid or ikey in almost all cases)
KeyValue1 = The Custno/Vendno or Item Code
Keyname2-4 = Other Filters
Keyname2-4 = Filter Values
IsKey = 'n'
Keytype = CUSTNO or ITEM depending on which one you have
UseCID is not used, but CID must be filled in
FieldName 1-4 = Fields to Update
FieldValue 1-4 = Data to Update With
Check for max record update will run as a select query first, counting the rows updated for each record in the XLS file.
If any will update more than the entered value, a message will be displayed, and the update will not occur for that row.
The process can be continued at this point, or the rest of the updates can be canceled
