Complex AND-OR-NOT Searching using Summon

When doing complex librarian-style searching, you often run up against the limits of the Summon interface. Like most library systems, in order to reduce confusion from users the Summon interface limits the number of characters in a search box to 128.

When a librarian or other experienced searcher is attempting to do a sophisticated set of Boolean searches this character limit and the complexity of the nested search terms often stymies a search. A single punctuation mark out of place can ruin a whole search string.

Summon, and many other search systems, usually only place this limit on the content of forms. When applying a search string using the URL (or more precisely in the case of Summon, the Summon API) you can submit much more complex and lengthy search requests

For this example, I created a Google Sheet with two sheets.

The first allows the user to create complex AND, OR or NOT search statements in a string, such as (in the example data)

“college admission” OR “university admission” OR “community college admission” OR “higher education” OR “tertiary education”

Search Example 1

This has extra options for searching sections inside Summon such as Author, Title, Publication Date and Abstract. The Keyword search is indicated with the open parenthesis  (.

After you create this search statement you can copy it from the “Summon Search String” cell and paste it (make sure you paste values only) into the Search String cells on the second spreadsheet.

The second sheet allows you to string multiple search statements together with AND, Or or NOT

(“affirmative action” OR “quota” OR “equal opportunity” OR “nondiscrimination” ) AND (“Asian” OR “Black” OR “Middle Eastern” OR “Hispanic” OR “American Indian” OR “Indian” OR “Pakistani”  ) AND PublicationDate:(“1990” OR “1991” OR “1992” OR “1993” OR “1994” OR “1995” OR “1996” OR “1997” OR “1998” OR “1999” OR “2000”) AND (“college admission” OR “university admission” OR “community college admission” OR “higher education” OR “tertiary education” )

Search Example 2

For more subtle manipulation of the Boolean searches, it’s quite easy to copy the contents of the ‘Test This Search” box into Notepad and adjust it as necessary.

For example, I’d adjust the above statement in this way for clarity

((“affirmative action” OR “quota” OR “equal opportunity” OR “nondiscrimination” ) AND (“Asian” OR “Black” OR “Middle Eastern” OR “Hispanic” OR “American Indian” OR “Indian” OR “Pakistani”  ) AND (“college admission” OR “university admission” OR “community college admission” OR “higher education” OR “tertiary education” )) AND PublicationDate:(“1990” OR “1991” OR “1992” OR “1993” OR “1994” OR “1995” OR “1996” OR “1997” OR “1998” OR “1999” OR “2000”)

Search Example 3

Within the example sheet, I’ve added the settings for ‘Full Text’ and ‘Scholarly’.  On the first example (1), no settings are in place, on both of the more complex examples (2 and 3), ‘Scholarly’ is set to Yes, while ‘Full Text’ is set to No.

Additional options that could be added include limits to Books, Journals or various forms of media.

For this example, I’ve used the Summon instance at the University of Toronto. Please feel free to make a copy of this sheet, and feel free to contact me if you’re running into problems adjusting this for your university’s Summon settings.

The basic principles of this project are applicable to any library system, but the details on the back end will vary considerably. I wrote the initial version of this project to simplify complex searching in an InMagic Presto database using Excel 2016

Sharing is currently set to ‘View’, please make a copy and adjust it to your own circumstances as necessary.

V1 Example Complex  Boolean Search Spreadsheet

The Summon API documentation is really useful for determining which fields are available for creating complex searches.

Summon API Fields Documentation

Have MLIS, Will Travel – Quick note

I’ve been slowly working out the kinks on my new job-sharing site, Have MLIS, Will Travel. While it is not quite ready for general use right now, it is looking very promising

In my test phase, since the API calls to the Google Places API contain so much data, I’ve been saving and logging the Latitude/Longitude of the city for each job posting. I’m attaching a quick visualization for a small selection of the data collected over the last month.


Currency conversion in Google Docs Spreadsheet

One of the complexities of working overseas is keeping a budget. I get paid in two different currencies (Qatari Riyals and Canadian Dollars) and due to the infrastructure in Qatar I have to work with cash for most of our everyday transactions.

While working with my budget spreadsheet on Google Docs, I spotted this cool little formula that has allowed me to seamlessly convert back and forth between Canadian Dollars and Qatari Riyals.

Here’s the formula

=CanadianAmount*(GoogleFinance(“CURRENCY:”CADQAR ; “average”))

In practice here’s how it looks.

I put the two variables QAR and CAD in the spreadsheet itself and call them via &TRIM().  This allows me to reverse the CADQAR to QARCAD variables easily. The “average” modifier gives you a good-enough approximation of the current exchange rate.


What’s cool with this automatic conversion is that I’ve figured out a way to enter an expense in either currency, and have it automatically reflected in an automatically updated dollar amount in both currencies. Spreadsheets FTW!


Here’s a spreadsheet that can help you figure this out on your own.




Free Technology for Teachers: Virtual Cell Animations

CNA-Q is an institution that spends a lot of time and effort on ESL instruction.  Visuals are a huge help in the classroom for increasing comprehension among all students. These cell animations  from North Dakota State University are very well put together and cover a wide range of biological processes.

Free Technology for Teachers: Virtual Cell Animations.