Recharge CenterChartfield String Validation Query Guide

3/28/16

Abstract

The Recharge CenterChartfield String Validation Queryis intended to reduce the number of budget checking errors that are generated during the processing of ISPjournal upload transactions. The recharge center query accomplishes this by comparing the list of expense chartstrings from the Journal Upload spreadsheetwith a list of valid budget chartstrings. On a daily basis the Financial Help Line will generatea new recharge center query file containing up-to-date budget data. The spreadsheet will be made available for local download via the Reporting Center. Users will have two options to enter their chartstrings into the tool; 1) copy the chartstrings directly from the journal spreadsheet upload file and 2) assemble the list of expense chartstrings to be validated in a condensed format. Users will copy the expense chartstrings into the recharge center query. A series of Excel formulas will execute and determine which expense chartstrings have a valid budget and which do not.

Recharge Query Process Flow

Details

  1. Download the recharge center query from the Reporting Center. The query is updated daily by the Financial Help Desk and is in the form of an .xls file. Save the file to a local or network directory.

  1. Copy the chartstrings to be validated using one of the two entry methods; a) Copy chartstrings from the journal spreadsheet upload file b) Assemble the data into a condensed format.
  1. Copy all of the data cells from column C through column IC to your clipboard.
  2. Make sure that no columns are hidden.
  3. Do not include the column headers in rows 1-10.
  1. Paste the data sheet into the “Spreadsheet Upload” tab of the Recharge Center Chartfield String Validation tool.
  2. Put the cursor in cell C11, right click and select paste special “values”.
  1. The formulas will begin to function. When complete column B will be populated with one of three values for each row of data

  1. “Revenue” – this row contains a revenue account. Revenue accounts are not budget checked.
  2. “OK” – the expense chartstring has a valid budget and will pass budget checking.
  3. “Budget Not Found” – no budget was found for the expense chartstring and will not pass budget checking.

Rows for which no budget was found should be reviewed. Chartstring values can be updated in the Recharge Chartstring Validation tool and the row will immediately be rechecked. Remember to update the journal spreadsheet upload file with correct chartstring values.

  1. Assemble the expense chartstrings from the data set to-be budget checked. In this step you will organize (format) your data so that it is compatible with the recharge query. It is assumed that the ISP data exists in spreadsheet form such as the Journal Upload spreadsheet.
  1. It is necessary that the columns of data be arrange in exactly this order:

Reference* - F

Fund Code - HS

Dept ID - GS

Account - GQ

Project ID - IC

Program Code - HU

Chartfield 1 - HZ

Chartfield 2 - IA

Chartfield 3 - IB

*The Reference field can contain any value. It is not used by the report to determine valid budgets. It is an optional field and is intended to be an aid in troubleshooting invalid chart strings. Invoice numbers and PO numbers are examples of values that might be used in the Reference field.

  1. Copy each of the columns listed above from the source spreadsheet. Following is an example of the columns to be copied. The source in this example is the GL Spreadsheet upload.

  1. Paste the columns onto a blank worksheet. The columns must be ordered exactly as indicated above.

Note: the above example does not include data in the Reference column A.

  1. Only expense lines will be checked. Your data set may include revenue lines (this is true of the Journal Upload spreadsheet). Revenue lines included in the report data set will generate the “CS Not Found” error, which may make it difficult to distinguish the expense line errors. Therefore it is recommended that the revenue lines be filtered out of the final data set.

If your source data includes a distribution line number then you may be able to utilize this column to filter on expense rows. Typically, rows with a distribution line number equal to “1” are revenue lines, and rows with a distribution line number equal to 2 are expense lines.

Alternately, use a filter on the Account column. Generally, a revenue account begins with the number 5 (for example “500101) and an expense account begins with the number 7 (for example “720403”). Create a filter on the account column of your spreadsheet and select all of the expense account values from those available.

  1. Select and copy the data from your worksheet. Do not include column headers. Be sure to scroll to the bottom of the data set.
  1. Paste the data into recharge query. If your worksheet includes Reference data then highlight cell C3. Past the data. If your worksheet does not include any reference data then highlight cell D3 and paste the data. Note: the gray shaded columns are locked to user input.

The Budget Account is determined and populated in column L

Next the chart fields will be compared to valid budget strings. Column B will be populated with a value of “OK” for valid chart strings or “CS Not Found” for invalid chart strings.

  1. Review error rows. Review the chart field values for each row highlighted“CS was not found” and correct chart fields as necessary. The Recharge Query will allow you to edit chart field values. The chart string will immediately re-test an updated row for a valid budget.

Note: Be sure to change any updated chart stings on your original workbook (that is, the source that will be uploaded into EFS).