Skip to end of metadata
Go to start of metadata

Update February 12, 2010: A basic solution for UCMP has been developed and was shown to Mark Goodwin and David Lindberg in January.  Other staff in UCMP now have access this read-only system. 

System description

The solution is a read-only system that contains the combined loans information that is managed currently by Pat Holroyd and Diane Erwin via a dBase system. Data from the two databases are combined and imported into lists on CalShare (based on Microsoft Sharepoint technology). Data in the CalShare lists will be updated approximately on a monthly basis. The system (link below) is only available to authorized staff in UCMP (via CalNet ID).

The functionality is pretty basic here. From the Invoice list, you can click on the "loaned specimens" link in the Specimen-link column to pull up the list of specimens on that invoice. Note that some older invoices do not have the associated specimen records. On the list of specimens, there is a column that reads "Query UCMPDB"; it will attempt to pull up the record in the specimen database. Unfortunately, there are a few records in the dBase files where the location prefix is not entered, and that prevents the search on the specimen database from working correctly.

CalShare is based on Microsoft Sharepoint technology, so it has some nice built-in capabilities for filtering and sorting and exporting. Needless to say, these work better in Internet Explorer than Firefox! However the site does work pretty well in Firefox.

Processing steps

Draft

  • Note: Both Pat's and Diane's files use INVNO as the link between the invoice and specimen tables. Unfortunately, the INVNO values and ranges overlap, so a combined key will need to be created (e.g., V2526) in both the invoice and specimen tables in order to provide a join but still allow searching on invoice numbers.  This is accomplished in Excel (template to be attached) using calculated columns.  The unique invoice number might be used behind the scenes but not be displayed.
  • Need to combine the data files in Excel and do some processing there prior to uploading. Need to ensure creation of unique ID key field across both sets as well.

Invoice files (invoices.dbf)

  • Combine 2 invoice files into one.  (Work in Windows, since Excel on Mac does not understand the DBF format.)
  • Add 2 initial columns
    • Column A: A calculated column to create a unique invoice number KEYINVNO (like "V3012")  [=concatenate("V",cellref) and =concatenate("P",cellref) for the file coming from PH and the file coming from DE respectively]
    • Column B: A filename indicator DBOWNER ("DE" or "PH") to indicate whose file this was. 
  • Sort by YEARSENT descending, INVNO descending so most recent invoices will sort to the top.
  • In CalShare (using Internet Explorer so you have the spreadsheet behaviors), go to the All Items data view, select all rows, and hit your delete key.  CalShare will take awhile to process this.
  • In Excel, select all rows except header; copy and paste into All Items datasheet view for invoices.  Pasting is a little challenging!  Select the cells (not the rows) in Excel.  In the CalShare datasheet view, select the cell you want to start with (but do not go to the point where you have an insert cursor in that cell).

Loaned specimens files (loansps.dbf)

  • Combine 2 loansps (specimen) files into one.
  • Add one initial column:
    • Column A: A calculated column to create a unique invoice number KEYINVNO (like "V3012")  [=concatenate("V",cellref) and =concatenate("P",cellref) for the file coming from PH and the file coming from DE respectively]
  • Sort by INVNO, LOCPREF, and LOCNO ascending.
  • In CalShare (using Internet Explorer so you have the spreadsheet behaviors), go to the All Items data view, select all rows, and hit your delete key.  CalShare will take a long time to process this.  It might be best to delete 3000 rows at a time rather than all at once.
  • Note: This is by far the most time-consuming step!  In Excel, select rows, maybe 2k to 3k at a time, and paste into All Items datasheet view for loansps.  Pasting is a little challenging!  Select the cells (not the rows) in Excel.  In the CalShare datasheet view, select the cell you want to start with (but do not go to the point where you have an insert cursor in that cell).

Confirm all links are working!

Schedule

Update monthly.  Request invoice and loansps files from Pat and Diane.

  • No labels