Versions Compared

Version Old Version 4 New Version 5
Changes made by

Alisha Quagliana

Alisha Quagliana

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Select the list you want by clicking on it then doing Search Records (see Image 1 below).

Image 1Image RemovedImage 1Image Added


Now you build your query for the list. You can use the Classic or Enhanced version of the query builder (Enhanced is new so not sure if there are any glitches with it). I'll show the Classic one in this example. Most the query will be exactly the same no matter if you are doing the print or online versions and no matter the Team it's for.

...

Then tell it to look for the fund codes you need and you'll have multiple lines here and the first will be AND and the others will be OR (see Image 2 below for details)
Image 2Image RemovedImage 2Image Added

Then once you have all the funds entered you need to group that part of the query (from the AND line to the last OR line) together by selecting them all and hitting the Group button (see Image 3 for details)

Image 3Image RemovedImage 3Image Added

Once you do that you should see open and closed parenthesis on the first and last lines of the fund codes like Image 4 below

Image 4Image RemovedImage 4Image Added

Now you can hit Search and then find your list by scrolling down and looking for the name, once Status says complete you can select it and hit the Export Records button at the top to do an export of the list (see Image 5 below)

Image 5Image RemovedImage 5Image Added

When the export window opens click on Apply Saved Export at the bottom and then click on the Export Name to sort and choose ACQ_byFund (see Image 6 below) and hit Select.

Image 6Image RemovedImage 6Image Added

Note that the Field delimiter is a tilde (~), and then click on Browse to select a location and name for your export file (I usually match the list file) so ACQ_COLL_printRev in this case (Image 7).

Image 7Image RemovedImage 7Image Added

Then hit OK and once the file exports you can go to Excel and open the file from there make sure the data type is delimited (Image 8)

...

  1. Sort the entire sheet by ORD Type
    1. Select entire sheet
    2. Hit Data menu at top, then select Sort
    3. Click "My data has headers" at top right
    4. Use Sort by pull-down to select ORD TYPE and hit OK
  2. Replace the Order Type codes with the real types
    1. Select the entire ORD Type column 
    2. Do a Find and Replace (Ctrl+F)
    3. Under Search choose By Columns
    4. Now work your way through the codes to replace them with the types using your cheat sheet (see Image 10) by filling in the code and replacement text and hitting Replace All
      Image 10Image RemovedImage 10Image Added
  3. Replace the codes in the Status column with the Status text the same way.
  4. Cut the entire Record # column and paste it in front of the Title column so the record numbers are in Column A (see Image 11)

 

...

Image 11Image Added

Comparing Master List to Sierra

...

  1. Insert a blank column in front of Column A (Order #) in the pmaster tab.
  2. Do a VLOOKUP formula in Cell A2: =VLOOKUP(B2,psierra!$A$2:$A$140,1,FALSE)
    1. The red numbers in the formula above will have to be changed based on the number of rows in your psierra tab
  3. Then copy that formula into each row with data in the pmaster tab (see Image 12)
    1. Image 12Image RemovedImage 12Image Added
  4. Now sort that sheet (pmaster) by Column A using the Data > Sort functions that you used before

  5. Scroll down to the bottom and you should see all the non-matched rows at the bottom (#N/A will be listed in Column A rather than an order number)

    1. Highlight those non-matched rows in yellow (see Image 13)
    2. Image 13Image RemovedImage 13Image Added
  6. Delete Column A (matching column) 
  7. Now repeat the process to compare the sierra tab to the master tab for each type.

Checking the Non-Matched Titles

Next you'll need to check each non-matched title from both tabs (pmaster and psierra in this case). Start with the master tab and look up the order records to see why it might not have matched.

In our example collection both order records revealed that the titles were cancelled so the highlight was left and the text was turned to red (see Image 14 below)

Image 14Image Added

Now move on to the sierra tab to check those titles, sometimes the Order Status or Order Type might offer a clue in our example (see Image 15 below) one of the non-matched orders from Sierra is marked Fully Paid and another is On Order which could explain them not matching.

Image 15Image Added

So now you'll look up each of these orders and make corrections.  Below are listed some common issues and what should be done to mark them on the lists.

  1. Order cancelled
    1. master tab
      1. Leave row highlighted and make text red to indicate cancelled title
    2. sierra tab
      1. delete row
  2. Order fully paid (monographic order) used serial fund code (sierra tab most likely)
    1. Delete the row
  3. Order is valid (sierra tab)
    1. Add title to the bottom of the master  tab, we'll fill in the additional data later, and highlight those lines in light green on both tabs

So in our example collection one of the order can be removed since it falls into Scenario #2 from above, and one will be added to the master tab since it falls into Scenario #3. The title marked On Order should have actually been cancelled so it's status was changed and it was removed from the sierra tab.  If you are not sure what to do with a title just leave it highlighted and I'll review it, you can also add notes in last column.