XLS Update Tool

Modified on Tue, 14 Aug 2018 09:27 by dblacklock Categorized as System Manager
{TOC} 

===General Information===

'''Default Menu Location''':  N/A

'''Screen Name''':  XLSUPDATE

'''Function''':  This screen will allow you to update multiple SQL records at one time.

[imageauto||{UP}/XLS_Updatesmall.png|^{UP}/XLS_Update.png]


==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 123'''

[imageauto||{UP}/XLS_Update_Template.png|^{UP}/XLS_Update_Template.png]

===Examples===

====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
Meta Keywords: xlsupdate
Meta Description: xlsupdate
Change Comment:
Desktop Adjutant

Navigation¶

XLS Update Tool
