XLS Update Tool

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

Table of Contents [Hide/Show]

   General Information
Using the Tool
Template Fields
   Examples
      Case 1 - You have the values for all the filters.
      Case 2 - You have Item or Custno

General Information¶

Default Menu Location: N/A

Screen Name: XLSUPDATE

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 123

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