Claims Returned Report, Monthly
Author: / George EverittDate Revised: / 10/09/2008 Revised by Susan Fineman
Page 1 of 4
Context: / This report lists all items that have a status of Claims Returned.
This status is used as a goodwill gesture, giving the patron the benefit of the doubt that the item actually was returned.
Libraries use this report:
- to search or check for the item, and
- to make decisions about possibly replacing the item for collection development and inventory.
Monthly: / The report is to be run and posted monthly.
Prerequisites: / The operator needs:
· Create List permission on IIISY2 – Permission #18.
· Desktop shortcut to Millenium PAC2
Steps:
A. Create and export the Review File in Millenium.
1 / Log on to Millennium on PAC2 with the ‘miladmin’ login.
Username: miladmin
Password: miladmin
2 / Enter your personal initials and password.
3 / Select Create Lists on the tool bar of the left side of the screen. (This should be the default.)
4 / Select an empty list (after #120) by typing that number.
(10,000 Max Records should be large enough.)
If no lists are empty: Highlight a list older than 1 week.
5 / Select the Search Records button at the top of the window.
6 / Select Yes if prompted with “Warning: You are about to over-write the contents of this review file. Would you like to continue?”
7 / Type in the Review File Name: Claims Returned [Date] [your initials]
8 / Select ITEM for Store Record Type.
9 / Select the Retrieved Saved Query button at the bottom of this window.
10 / Select Claims Returned on the list of Queries.
11 / The query should display as shown below:
Term / Operator / Type / Field / Condition / Value A / Value B
1 / ITEM / STATUS / equal to / z
ITEM STATUS equal to "z"
If the criteria are not there as shown, for each entry in the table:
· Double-click the first available box in the Type column.
· Click the desired record type and click OK.
· Double-click the next available box in the Field column.
· Click the field to export and click OK.
The search criteria will display in the white box below the table.
12 / Click Search to start the search. Then click yes to confirm.
13 / The display will switch to the Review Files tab and the list’s status will be in ‘progress’
(This may take several minutes.
While waiting for the search to be completed, start another report.)
14 / When the list’s status changes to ‘complete’ the search is finished.
Write down the number of search results, shown in the Current Records column.
(This number will be entered in a spreadsheet at the end of the procedure.)
If the maximum number of records was found, the file was too small.
Repeat the procedure from step 4 using a list that allows a larger number of records.
15 / With the Claims Returned list highlighted, select Export Records above the list of files.
16 / Construct the following table of Export ITEM information:
Line / Type / Field
1 / ITEM / LOCATION (79)
2 / ITEM / CALL # (c)
3 / BIBLIOGRAPHIC / TITLE (t)
4 / ITEM / BARCODE (b)
5 / ITEM / RECORD # (81)
6 / ITEM / PRICE (62)
7 / ITEM / STATUS (88)
8 / ITEM / INT NOTE (x)
Field delimiter / 9
Text qualifier / “
Repeated field delimiter / ;
Maximum field length (0-1000) / none
If the criteria are not there as shown, for each entry:
· Double-click the first available box in the Type column.
· Click the desired record type and click OK.
· Double-click the next available box in the Field column.
· Click the field to export and click OK.
· Click the Append button to add another line.
17 / Click the Field delimiter button.
18 / Select Control character. Enter 9. Click OK. (9 is the decimal value of the Tab key).
19 / Click the Text qualifier button
20 / Select ASCII character. Enter “ and click OK.
21 / Click the Repeated field delimiter button
22 / Select ASCII character. Enter ; and click OK..
23 / Click the Maximum field length (0-1000) button
24 / Select None. Click OK.
25 / Enter a file name:
· Browse to H:\III Reports\[Year]\Monthly Reports\Item Status Reports\ Status Reports for [YYYY-MM]
· Type a File Name: ClaimReturn[Date][your initials]
· The file type should be Text Files (*.txt)
· Click Save in the Choose Output File window.
· Click OK in the Export Format window.
B. Format the Report in MS Excel.
Start MS Excel
27 / Go to Office Button | Open and browse to
H:\III Reports\[Year]\Monthly Reports\Item Status Reports\!formatingMacro.xls
Double click on the file to open it.
28 / Go to Office Button | Open and browse to the folder H:\III Reports\[Year]\Monthly Reports\Item Status Reports\Specialized Reports for libraries\Status Reports for [YYYY-MM]
Remember to set Files of Type: to All Files (*.*)
Highlight and Open, or double-click the .txt file created in step 25.
The Delimited radio button should be selected.
Windows (ANSI) should be selected for File origin.
Click the Next > button.
Under Delimiters, only Tab should be checked.
32 /For Text qualifier, {“} should be selected. (Braces do not need to be typed.)
33 /Click Next >
Click Finish.Select Column G (STATUS)
36 / Replace all z with Claims Returned.
If the number of replacements doesn’t equal the number recorded in step 14:
· If the difference is less than 100 find and delete the records whose status was not z.
· If the difference is greater than 100 run the report again from step 4.
37 / Go to Developer| Macros…. ( Alt + F8)
38 / Highlight “!formatingMacro.xls”Macro4
39 / Select Run
40 / Select Cells A>H in Row 1 | Merge & Center |
Resize the height of Row 1 to 37.50 (50 pixels)
Click on Cell A1.
· Insert Title: Items with Status of Claims Returned – [Month, Year]
· Use Alt-Enter to start a new line in cell A1.
· In a smaller font insert: (This report was run on [run date].)
Add color to Rows 1 & 2. Choose a light background and black font to enable clear printing.
41 / Select the tab at bottom of spreadsheet page. Rename to Claims Returned [Month Year]
42 / Highlight from A2 to the last cell (Crtl-Shift- End). Be sure that all columns are selected.
43 / Go to Data | Sort...
44 / Sort the spreadsheet by Column A – LOCATION, Values, A to Z. Use Add Level to do a secondary sort by Column B – Call #, Values, A to Z.
45 / Click on Cell A3. Then save this file as [MonthYear]ClaimReturn.xls
Month and year should be in the format Mmm and YY, respectively.
For example, Jun07ClaimReturn.xls.
46 / Using Print Preview:
· Check that column headings are repeating on each page.
· Check that full column widths are displaying on the page -- 8 columns for this report.
· Check that there are no empty extra columns or rows.
· Check that the rows are sorted by LOCATION (Column A), then by CALL # (Column B).
· Check that the page numbers are displaying below the table.
47 / Open!ReviewofReportsByMonth.xls. This spreadsheet is located in the H:\III Reports\[Year]\Monthly Reports\Item Status Reports\\Specialized Reports for libraries\ folder.
· Compare the number of “Claims Returned” items for the current month with the numbers for previous months. If the current month’s number is not similar to the previous months’ numbers, describe this discrepancy when you turn in the report.
· Enter the total number of Claims Returned items for the month.
(This should match the number of records found in Step 36.)
48 / Copy the completed report and comparison spreadsheet to the folder H:\III Reports\2008\Monthly Reports\Monthly Reports for Review & Posting\Item Status. Send the link to this folder in an email to Amy K., Mark M., and Mary Mc. for approval before posting, with a note about any anomaly in the numbers.