This describes the cross-check process between the master lists and Sierra to ensure we have all orders documented.

Team File

The team files can be found on Box (Sierra Recon) under the folder called Team Lists to Check.

The Team file will have 4 tabs pmaster, emaster, psierra and esierra  The ones that have master will have abbreviated data pulled from the master list for the cross check process. You will need to run 2 Lists from Sierra, one for print and one for electronic and you'll put that data in the corresponding sierra tabs.

First, you'll need to sort the pmaster and emaster tabs in the Team File by the fund code and note the various fund codes. I'm using the Collections team list as an example.  

For print I have the following funds:

For electronic I have these:

If you have any entries with multi as the code just leave those off and highlight them pink.

Running Lists

You'll use the Create Lists function in Sierra to run your lists.

Use the Function pull-down menu in Sierra and select Create Lists


Now sort by Name (just click on the name column) to see the empty lists.  Choose a list that's 5000 or more records, you won't need this much really but just in case.

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

Image 1


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.

First, set your Review File name, I'm starting with the Collection teams print order so I'll use: ACQ_COLL_printRev as the Review File Name. I'll set the Store Record Type to Order.

Then in the query section you first need to tell it not to search for cancelled orders:

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 2

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 3

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 4

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 5

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 6

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 7

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)

Image 8

And hit Next, then make sure you but the tilde (~) in the Other box under delimiters (Image 9)

Image 9

Then hit Finish.

Cleaning Sierra Data

Once the review files is open in Excel (ACQ_COLL_printRev) copy the entire sheet and paste it into the corresponding sierra tab. In this case the psierra tab.

Once you do that you should do the following data clean up steps to make the process easier:

  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 10
  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 11

Comparing Master List to Sierra (print and electronic)

Next you'll go to your master tab (pmaster) in this case and do a match to the corresponding sierra tab (psierra) by following these steps:

  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 12
  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 13
  6. Delete Column A (matching column) 
  7. Now repeat the process to compare the sierra tab to the master tab for each type.
  8. Then repeat the entire process to compare the emaster and the esierra tabs to each other

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 14

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 15

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
  4. Order Type is w/Comp Sub and w/Other Sub (sierra tab only)
    1. These can be deleted
  5. Order indicates it's part of a System deal (see Order Code 3)
    1. These can be deleted from the list as well as well
  6. Fund code on order doesn't match or is multi (master tab most likely)
    1. Update fund code if it changed on master tab and highlight the row pink

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.

Submit File for Review

Once you finish comparing both print and electronic and reviewing any titles that don't match you can save the file and add move it to the folder in Box (Sierra Recon) called Team Lists for Review.