SQL Data Entry Editor

Data that have been entered to the Bulkloader may be manipulated using two forms:

1) Browse Bulk-->SQL

The SQL option from Browse Bulk opens a form where data administrators may review records entered by members of their data entry group and make changes to multiple records at once.

The initial page load contains all records that fit criteria specified on the Browse Bulk page (enteredby and accession number).

To filter records, enter up to three rules in the top (Create Filter) box. Click the Filter button to apply rules. The page will reload with only those records that meed your criteria. You may skip this step; updates will apply to whatever you see in the specimen grid at the bottom of the page. Filters are simply a way to reduce the number of records to which you apply updates.


Once only the records you are interested in appear in the Specimen Grid, use the Update box to apply updates to them all simultaneously. To update Accn for all records:



To flag all records as ready to load, pick Loaded in the Column side and Update To NULL. There is a link supplied, or you may type "NULL" in the Value field.


2) Browse Bulk --> View Table

The View Table option loads a Java applet which allows manipulation to individual cells in entered records. Simply enter any cell in the grid, change data as appropriate, and click Save Changes in Grid.

There is also a limited bulk operations widget at the top of this form. You may alter multiple record's LOADED value by selecting a new value and one filter option. The following updates all records LOADED value to NULL where specific locality, trimmed of leading or trailing spaces, is "fairbanks." This example will not get those records with the following locality information:
  • "Fairbanks" (this is a case-sensitive operation)
  • "Fairbanks Area" substrings are not considered)
However, terms with spaces will be affected:
  • "Fairbanks "
  • " Fairbanks"
  • " Fairbanks "

Agent Groups

[ moved to https://arctosdb.wordpress.com/documentation/agent/#group ]

Agent Groups consists of:

  1. An agent of type=group, and optionally
  2. agents (generally of type=person) as group members
Groups may be useful for things like collecting expeditions.

Create groups as Other Agents, then manage group membership from the group's agent record.

Designing Reports

I don't want funky labels anymore! I want nice ones, and I want to print them from Arctos.

This procedure explains how to design your own label, load it to Arctos, create handler(s) for the label, and use it to print almost anything that uses specimen data. You'll need to have mange_collections permissions to complete this procedure.

First, download and install the ColdFusion 8 Report Builder. You'll need a Windows box.

At setup, it will ask you a bunch of information about your ColdFusion environment. Just cancel out, or do whatever it takes to get it to stop bugging you; none of this will be useful. Once that's happy, fire up the Builder and create a new label.

Hint: Set the headers and footers to zero size for creating things like specimen labels. You can size any object precisely with the Properties menu to the right, or just drag things around until they look about right.

Adjust the size of individual labels, column count, etc. as necessary (see Report Properties by right-clicking on any unused part of the report). You should now have a blank label to work with. To add data fields, use the Field wizard (lightning-bolt icon). It will be necessary to know the name of fields that will be returned from the database (more on this later). Type the field into the Expression Builder and click OK. The following example is for a field named "some_database_query_variable."




Now, load your template to Arctos. From the Reports tab, choose Reporter. Click Browse, locate the CFR file on your drive, and upload it. When the page refreshes, you should see your report in the grid. Click the Create Handler link to get started.

Hint: Look at existing report handlers for SQL hints. You'll probably find something appropriate. Do not alter any handlers that are not yours!

A handler will be generated, and you'll be redirected to the Edit Handler page. Change the Report Name to something more intuitive. Make sure the Report Template is what you just uploaded. Select a Report Format. We'll come back to Pre-Function later.

Now, create the SQL you need to generate the data for your report. You may use built-in Oracle functions, some custom functions (ask a developer or see the branches/DDL/functions folder in the Arctos SVN Repository), and most ANSI SQL. Click Test SQL to see the results of your query. Click Save Handler when you're happy with your results.

Hint: See the gray box at the bottom of the Edit Handler page. Your SQL must use one of the pre-defined variables. Talk to a developer if you're having difficulties getting what you want. Oracle SQL may not be quite what you're used to, and we've probably done something like what you need before.


Sometimes it's not practical to use SQL to manipulate data. Concatenating locality elements into a string for label printing can be very tricky, for example. The Reporter has the ability to pass your query off to a ColdFusion Function, manipulate it using custom code (CFML or CFSCRIPT), and return the altered query object to your Report Template. Just contact a developer if you think you need this functionality. If you already have a function, you may simply type it's name into the Pre-Function box from the Edit Handler page.

A quick note about fonts. The designer runs only in Windows, while the template is executed from Linux. Only the fonts available to the Linux server may be printed. To print barcodes, you'll need to download the Free 3 of 9 fonts, install it on the machine you're developing the report from, and choose it as the font of the barcode. Both Free 3 of 9 and Extended 3 of 9 are available on Arctos. Most other common fonts are installed; talk to the development team before using anything uncommon. You will need to select "Embedded Font" from the field properties to print barcodes in PDF documents. FlashPaper documents always embed fonts.

You should now have a Report Template and an appropriate handler. Find some specimens and choose Print Any Report from the Management dropdown. Choose the handler that you just created from the list, click the button, and your report will print.

API

Variables

The reporter will accept three variables:

  1. collection_object_id (=cataloged_item.collection_object_id)
  2. transaction_id (=trans.transaction_id)
  3. container_id
Both are integers or lists of integers.

These must be passed in via URL or form, and may be accessed by the Pre-Function or the SQL_Text. You must wrap variables with ## to access them from the SQL.
...and cataloged_item.collection_object_id IN (#collection_object_id#)...
Loan data are best accessed by simply leaving the SQL_Text block blank and calling function get_loan. A query containing all normal loan data for {transaction_id} will be returned.

Access

You may call reports by accessing the URL
/Reports/report_printer.cfm?[collection_object_id={list] | transaction_id={list}] [report={name}]
[sort={sort column list}]

Omitting the report parameter will allow you to select a report for printing. Including it will print a report without prompting. Report is a case-insensitive character match (no substrings).

Including Sort={list} will append an "order by {list}" clause to your SQL if doing so won't cause errors. If it will (i.e., you try to sort by a column that's not in the query), the sort parameter will simply be ignored.

Arctos Procedures

We have begun this manual by adding responses to some recent questions and common issues. Content development is up to you, the Arctos community. Gordon and Dusty are available to provide technical information and to coordinate effort. Consider the following suggestions:

  • Communicate. Use the Arctos group to "claim" subjects or suggest content. Leave comments for posts that need expansion or correction.
  • Be as generic as possible. Write to a wide audience, and avoid unnecessary particulars that may be affected by minor changes to Arctos.
  • Resolve differences in the Arctos group. There is one best way to do much of what we do; we should strive to find it. If that is impossible, summarize and link to the appropriate subject in the Arctos group.
  • Write atomically and link to other subjects. Don't explain how to create an accession when describing data entry, for example. Create, as a "stub" if necessary, and/or link to the entry for Accessions.
  • Use labels. Click "view all" beside the "Labels for this post" box to see current values. Create a new label only when necessary.
  • Refer to the Arctos Definitions and Standards where appropriate.

MVZ Permit Report

So you want to print a permit report. Great!

First you search for your permit. Then when you have found the permit you want, select "Permit Report".Then you'll come to a screen like this.
If you want all of the specimens that were collected using this permit, hit "Generate the PDF". Otherwise, you may only want items from December of 2003. First, sort by date.
In this case, let's get rid of all the other months. What you want to do is "X" the rest of the dates. Each time you hit an "X" by a date, it will uncheck all specimens that are on the same date. In this case, I hit "X" by all the dates that weren't in December 2003, so I had hit a couple of "X"s
If I accidently hit "O" next to a wrong date (that selects all of them based on date), then I can just hit the "X" button to deselect them again.

Ok then. Let's look at the report.Looks great!

Printing Specimen labels for MVZ

Suppose you want to print a bunch of labels to go along with your specimens in MVZ. Then, depending on your collection, you'll want to use "MVZ narrow labels" or "MVZ wide labels" from the specimen results page.
Then you'll face a page looking like this.
Now is the time to see if your labels are fine as is. Click on "Generate the PDF". On the next page, click "Get the PDF". Then the PDF with your labels will be downloaded. Open it up (if it isn't already opened) and see if it is ready for printing.As seen in this example, the geography is too long for catalog numbers 200045 and 200046, so they need to be modified. In this case, we want to change "Campground" to "Cmpgnd" for catalog numbers 200045-20046, but none of the others.

In this case, we need to use the Geography Modifiers.
Click the "Add new geography modifier" button. This will add a line containing three text inputs and one button. Find is what it looks for. Replace is what it replaces it with. Scope is which labels to affect. The button deletes the modifier.

I also put in the scope input "200045-200046".Then hit "Generate the PDF" and "Get the PDF" and see if your problems are solved.Hoorah! Notice how the other ones geographies were not changed.

In some cases, the parts list is too long. In that case, you need to use the Parts Matrix. If you need to use the Parts Matrix, then you can deselect a checkbox which will make the specimen of that row not have the part of that column.
In this case, Mammal 200040 will not have the "whole animal (alcohol)" part appear in its parts list when you next "Generate the PDF" and "Get the PDF". Additionally, one can hit "Select All" or "Deselect All" to change whether that part appears for all of the specimens, and this will simply check of uncheck all of the boxes in that column.

I hope this gives you an idea on how to use it.

Print labels using Access

How do I print funky little labels for MSB Mammal specimens?

Sign in to Arctos and search for specimens in the usual way. Select Label Data Download in the Manage menu and click Go.



Right-click and save the resulting CSV file. The name and location of this file will be important later. Put it somewhere convenient, and accept the default name.

Download the MSB_Labels.mdb Access template from the Arctos Group Files site.

From the Access Tables tab in the file you just downloaded, click New at the top.

Choose Link Table in the resulting popup and click OK.

Change Files of type to "Text." Find and select the file you just downloaded from Arctos. It should be named ArctosLabelData.csv.
Click Link.

Select Delimited in the resulting window, then click Next.

Change Text Qualifier to quote ( " ). Do this first or you'll get scary but harmless Microsoft errors.

Pick Comma as delimiter.

Check First Row Contains Field Names.

Click Finish.

From the main menu, click Reports. msbLabelReport (the only report currently in the DB) will contain labels for the file you downloaded from Arctos.

To print another group of labels, simply close Access (to prevent lock conflicts), search Arctos, and download the CSV as before. You should be prompted to over-write the existing CSV file. The new data will then automatically be available from the Access report.

If you wish to change anything with the MDB file, you can either request that I make the changes or make them yourself, but make sure to send me the updated version. I'll keep that on the download site so it's always available. I can change the data download as needed.

This is not intended to be a permanent solution. We'll find something better someday.