Generate a single sheet custom MARC21 framework in 2 minutes

For intermediate Koha ILS users who wish to quickly generate a single tab MARC framework.

Last Thursday, Ashish Kumar Barik, librarian at our new client-partner Midnapore City College filed a support ticket asking for a custom single sheet MARC21 framework or what is more commonly referred to by LIS professionals as a “worksheet“. He wrote that he wanted the following tags 000, 003, 005, 008, 020, 040, 041, 044, 082, 100, 245, 250, 260, 300, 440, 490, 500, 504, 650, 700, 942 and that the sub-tags/fields should be set as in the default marc framework shipped with Koha. We promised him his new framework. Being new to this side of Koha, he of course had missed out two key fields without which his system would be rendered practically useless i.e. the two local use tags952 and 999. Koha uses 952 to handle holdings (item) information and 999 is purely an internal tag used to track the bibliographic records.

Now anyone who has ever setup a new MARC framework knows that it can be a laborious and time consuming task. Further, there are chances of introducing inadvertent human errors that may lead to error or bad data when used as a part of the framework. As a result, at L2C2 Technologies we have developed several well defined strategies to manage custom marc frameworks for our clients. In today’s blog, we are going to share the simplest of the techniques we use in cases like this. The outcome of this exercise is a 100% error free marc framework generated in less than 2 minutes.

LEGAL DISCLAIMER: The next steps involve directly accessing and making changes in the the Koha database. So use these instructions at your own risk, if you face any data loss, corruption or system errors we are not responsible.

The Steps

  1. We used a regex capable editor like Notepad++ to quote the fields mentioned by Ashish, so that 000, 003, 005, 008, 020, 040, 041, 044, 082, 100, 245, 250, 260, 300, 440, 490, 500, 504, 650, 700, 942 became ‘000’, ‘003’, ‘005’, ‘008’, ‘020’, ‘040’, ‘041’, ‘044’, ‘082’, ‘100’, ‘245’, ‘250’, ‘260’, ‘300’, ‘440’, ‘490’, ‘500’, ‘504’, ‘650’, ‘700’, ‘942’. And while we did that, we also added the following fields missing in his list i.e. ‘952’, ‘999’.
  2. Next we defined a new framework MCC1 (MCC Framework) by visiting Home -> Administration -> MARC bibliographic framework -> New Framework
  3. Next we copied the default framework into MCC1 as its base, since that is what Ashish had wanted. At this point, the MCC1 framework is exactly same as the default framework of Koha.
  4. Next we fired up the MySQL console and logged in with the user id and passwd from MCC’s koha-conf.xml, and chose Ashish’s database in this case koha_mcc for the next steps.
  5. Fired the following SQL query :
    UPDATE
       `marc_subfield_structure` 
    SET
       tab=0
    WHERE 
       `frameworkcode`='MCC1' 
    AND 
       `tagfield` IN ('000', '003', '005', '008', '020', '040', '041', '044', '082', '100', '245', '250', '260', '300', '440', '490', '500', '504', '650', '700', '942')
    AND
       `tab`!=0;

    MySQL client told us 152 rows were affected.

    EXPLANATION: This moved all 1XX to 9XX (except 952 and 999) marc fields into Tab 0. The images below help illustrate the condition after this step:

  6. The next step was to set the rest of the fields outside the list supplied by Ashish *plus* 952 and 999 to be ‘ignored’ by Koha when using the MCC1 framework. And thus the following SQL query:
    UPDATE 
        `marc_subfield_structure` 
    SET 
        `tab`='-1' 
    WHERE
        `frameworkcode`='MCC1'
    AND 
        `tagfield` NOT IN ('000', '003', '005', '008', '020', '040', '041', '044', '082', '100', '245', '250', '260', '300', '440', '490', '500', '504', '650', '700', '942', '952', '999') 
    AND
        `tab`!=0;

    This time MySQL reported that 3416 rows were updated.

  7. Our last step at the MySQL command line was the following query that removed the unwanted 0XX fields from Tab 0 :
    UPDATE 
        `marc_subfield_structure` 
    SET 
        `tab`='-1' 
    WHERE
        `frameworkcode`='MCC1'
    AND 
        `tagfield` NOT IN ('000', '003', '005', '008', '020', '040', '041', '044', '082', '100', '245', '250', '260', '300', '440', '490', '500', '504', '650', '700', '942', '952', '999');

    MySQL reported 341 rows were affected.

  8. Coming back to MCC’s Koha staff client, we did the most important thing i.e. running MARC Bibliographic framework test. The test came out clean without any error.
  9. That’s it! MCC’s custom MARC framework is ready for use. Click on the image below and then zoom in to see the details up close.

Quicktip – Ordering the display of your custom patron fields

An easy-peasy way to order the display of Koha’s ExtendedPatronAttributes fields.

This is going to be a brief tutorial, ExtendedPatronAttributes provide the capability to add custom fields to Koha’s patron management e.g. if we wish to capture say Program enrolled or Roll No. or other demographic details that we may be required to maintain under (a) law of the land or (b) governing rules of our library, this feature of Koha is friend we need to take help of.

If you have just landed on this blog and have no idea what we are talking about, we suggest that you may like to first read this earlier post – Harnessing Koha’s ExtendedPatronAttributes (aka patron custom fields). And while you are here, you may also like to visit this article.

Ok! Enough backstory, now back on topic! 🙂 When we define new custom fields to Koha using Home › Administration › Patron attribute types, the key thing to remember here is that these are ordered and displayed using alphabetic ascending sorting order of the defined Patron attribute type code.

Which means, when we defined a list of custom fields to capture student registration details e.g. as given in the screenshot below, we want them to be displayed in the order of the codes are numbered in the blue circles i.e. Student code, Registration No. and Roll No. in that order respectively.

Instead what we get is this:

The Solution

We simply need to number our codes. The way to do this is simply to prefix them with a serial number. Here we chose to use 00_, 01_, 02_”. Of course, this also meant that we had to dump off our old codes and put in these as replacement.

Hint: Do this before you start using the defined fields while entering your patron records. While it can be done later, you need to know what you are doing if you don’t want to mess up your patron data.

You can see what we did below:

And as you can see below, we have the fields displayed exactly in the order we had wanted in the first place.

Managing patrons with same permanent & local address

Setting your borrower’s local address same as their permanent address with just a single click during patron entry into Koha ILS.

Often folks when unable to find some nifty feature that was present in their erstwhile LMS but not there in Koha, is found to be exclaiming – “But we can’t do that with Koha!”. Well, we have news for you – Koha is open source and that means, you can build / modify the parts that you need or are missing. But you do not know how to do that. Well… *that* is not really Koha’s problem. But fear not, if you are willing and have the aptitude for poking around code you can do it too. There are plenty of open access resources that show you how to do so, just waiting for you to pick up and start working on your skills. After all, it is said:

If you give a hungry man a fish, you feed him for a day, but if you teach him how to fish, you feed him for a lifetime.

Why this post

At L2C2 Technologies, we work with a lot of academic libraries where they need to record both the permanent as well as local address of their students. Koha allows for recording more than a single address for a patron since donkey’s years. If you look at the schema of the borrowers table in the Koha database, you will see that there are fields to record both the primary address as well as alternate address. These two set of fields fit nicely into our permanent and local address requirement.

2017-06-19_01

However, the library staff often complain that it is useless extra work to re-enter the same data over in both set of fields as many users often have one and the same address for both. As a result, we are sometimes asked how to cut down this extra work. In this post, we are going to share one of the ways by which you too can do the same, should you need to do this.

Choosing our tools

All we use are snippets of JavaScript, jquery and css to achieve our objective. All of which go in into the Koha database as part of the IntranetUserJS system preference. We do not touch any template file or change any underlying PERL code. This way our tweak is guaranteed to survive Koha version upgrades without any further effort on our part.

The steps… as easy as 1-2-3

Since we do not want to re-type the same information, the only option is to copy it from first set of fields and that what we do by adding a checkbox HTML form input element. We give this checkbox the id copypermaddress and insert this into the DOM just before the first li element belonging to the parent fieldset memberentry_address on the Add Patron screen.

$('<li><input type="checkbox" name="copypermaddress" id="copypermaddress" value=""><label for="copypermaddress">Same as permanent address:</label><div class="hint">Click to copy permanent address data</div></li>').insertBefore(' #memberentry_address > ol > li:first-child ');

While the above insertion gets us the following screen, it still does not do anything i.e. if you clicked the checkbox, nothing would happen yet. In the next step we cover that.

2017-06-19_02

So we add a listener that will wait for state-change of the checkbox. In plain English, that means it will detect when a user clicks that checkbox and then based on whether it was selected or unchecked, appropriate action would be taken. And that exactly what happens below. The first part goes into action if the checkbox was checked and the part coming after the else kicks in when it is unchecked. In the first instance we copy over the values from the permanent address field and in the second part we undo the copy and blank out the local address fields.

$(document).ready(function(){
$('#copypermaddress').change(function() {
  if(this.checked) {
    $('#B_address').val($('#address').val());
    $('#B_address2').val($('#address2').val());
    $('#B_city').val($('#city').val());
    $('#B_state').val($('#state').val());
    $('#B_zipcode').val($('#zipcode').val());
    $('#B_country').val($('#country').val());
  } else {
    $('#B_address').val('');
    $('#B_address2').val('');
    $('#B_city').val('');
    $('#B_state').val('West Bengal');
    $('#B_zipcode').val('');
    $('#B_country').val('India');
  }
});
});

In the two following screenshots we get to see how exactly this works. In the first one, only the permanent address has been added. While in the second screenshot, we see how the data has been copied over when the checkbox is clicked.

2017-06-19_03

2017-06-19_04

References

  1. .insertBefore()http://api.jquery.com/insertbefore/
  2. :first-child Selectorhttps://api.jquery.com/first-child-selector/
  3. .change()https://api.jquery.com/change/
  4. .val()http://api.jquery.com/val/
  5. Koha DB Schema – http://schema.koha-community.org/master/

The 5-Minute Series : Downloading your Koha SQL Report

Presenting a fast and flexible option to download your report.

One of the best things about Koha is the complete freedom it provides its users to create a custom SQL report of any level or degree of complexity as long as the query is compliant to MySQL SQL syntax – reports like e.g. accession registers, detailed circulation statistics etc.

Of course, once you have your report, if you are anything like *us*, you would want to grab the data and the do further analysis or use it to make that fancy-schmancy report or use a part of it in a presentation. Of course to do any of that, the data has to be downloaded locally. Koha provides you with three (03) options:

  1. As comma separated value (.CSV) file
  2. As a tab separated (.txt) file
  3. As OpenDocument Spreadsheet (.ODS) file

Now, if you are an OpenOffice.org / LibreOffice user, you may be tempted to go for the third option i.e. download the data as an .ods file. While this may look like the best option. It is *not* always so. The biggest problem is that if your report returns a lot of records, then generating the .ODS file is going to take a bit of time. Also, if your system is low on RAM, this can get *really* slow.

On the other hand, the files in .CSV and the .TXT formats are generated and downloaded almost instantly OR at least faster in comparison by several order of magnitude! Given that, which one among the two are your best bet? Well, in our considered opinion, the tab-separated .TXT file is the best. Here is why: if the single quotes in your data are not properly escaped, then your CSV data may be misinterpreted due to the un-escaped single-quote. leading it to split the data that should have been together into separate columns during an import. In contrast, a “tab separated” file will usually work without any issue unlike the CSV file.

So, if you want the data download to be fast and easy to split into columns correctly, the tab-separated .TXT file provides us the best overall option.

The 5-Minute Series: Setting default messaging preferences for SMS alerts.

Presenting an use-case requiring setting the default messaging preference for SMS alerts sent by Koha.

The problem

Earlier in the day, my professional colleague and friend Joydeep Chanda asked a question. His Koha v3.22.10 installation (at Gurudas College Library) had turned on SMS alert service for its users. The library being a long time Koha user, presented a challenge. He had ~ 6000+ users, so now he and his staff had the unenviable task of having to update the smsalertnumber field in the borrowers table for these 6000+ users. I pointed him to a recent Facebook post by another friend Vimal Kumar Vazaphally. That solved part of his problem, but he still had to choose the default types of SMS messages to be delivered.

Messaging-preferences

So, I pointed him to another post by Vimal – “Use of borrowers-force-messaging-defaults script“. But after a while he complained that while the borrowers-force-messaging-defaults script did set the SMS options, it also enabled *all* of them, he wanted SMS send option to be set only for CHECK-IN and CHECK-OUT.

Description of borrowers-force-messaging-defaults as included in its source:

If the EnhancedMessagingPreferences syspref is enabled after borrowers have been created in the DB, those borrowers won’t have messaging transport preferences default values as defined for their borrower category. So you would have to modify each borrower one by one if you would like to send them ‘Hold Filled’ notice for example.

This script create transport preferences for all existing borrowers and set them to default values defined for the category they belong to.

The solution

The answer lies in correctly assigning advanced messaging preferences by default to a patron category *and then* running the borrowers-force-messaging-defaults script. In order for the patron category edit page display the “Default messaging preferences for this patron category” sub-form, the system preference EnhancedMessagingPreferences needs to be enabled.

The Steps

  1. Check if the EnhancedMessagingPreferences is enabled. If not, then enable it.
  2. Go to Home -> Administration -> Patron categories and select the category to edit.
  3. Scroll down the Modify category form to the “Default messaging preferences for this patron category” section.
  4. Select the checkboxes against the SMSes you want to be sent by default for that category.
  5. Save the changes
  6. Go to a command line terminal and run the borrowers-force-messaging-defaults script. This will ensure that only the defaults are set.

Enjoy!

See also:

[1] “Koha and key lessons of using transactional SMS in India” by L2C2 Technologies

[2] Press release about setting up of SMS alert service at Gurudas College Library.

[3] SMS-Send-IN-Unicel-0.01 at CPAN

Changing languages in the drop-down for Google Transliterate API

Choose the list of languages to transliterate on the Koha OPAC

This tends to be frequent question that we often end up fielding from colleagues and customers. But before we get into this, please remember that this feature is probably at the end of it shelf-life. There are two reasons for this – the Transliterate API that this feature depends on was deprecated many, many years back, and secondly how badly this API was put together – it fetches the necessary JavaScript library from Google’s servers over HTTP! So, if you are using HTTPS on your Koha OPAC, you can kiss this feature good bye right now! Being deprecated there is almost zero chance that Big G will do anything to change that.

Life sucks right?? 😉 But as long as it is still there, here’s “one for the road”! 😀 So, read on!

Turning on Google Transliteration

googleindic

It all starts with setting GoogleIndicTransliteration system preference to “Show” from the default of “Don’t show”.

By default that provides us with this short language drop-down:
googlist

For users from India, a country with 22 official language at the last count, that default list is woefully too little, too less. The good news is that Google’s Transliteration API supports several dozens of languages. Which ones? Well you need to dig around LanguageCode and SupportedDestinationLanguages enumerations in the documentation.

Show me the money!

The code to support the function is loaded via googleindictransliteration.js file that resides under /usr/share/koha/opac/htdocs/opac-tmpl/bootstrap/js/ on a Debian package based installation.

The screen grab below shows exactly that changes we needed to make in order to show on Hindi and Bengali as the list of languages, *and* with Bengali as default language option on the select drop-down.

googchanger

So how did I know that I needed to use “bn” for Bengali? Well, that LanguageCode enum list I’ve linked to above. Simple really!

One more fact about this tip that will bug a few

Change(s) to the LanguagesCode enum like this within the googleindictransliteration.js file would cause *all* instances on a multi-tenanted Koha installation to display the same list of languages. So, if you need different sets of languages lists in different OPACs, you are probably out of luck.

The 5-Minute Series: JavaScript or CSS – what is better at hiding the ‘No cover image available’ markup?

How to efficiently remove the ‘No cover image available’ placeholder using CSS rather than Jquery, when AmazonCoverImages or GoogleJackets can’t retrieve a cover image for your displayed title.

We really like our OPACs to display the cover images of the books and journals which we have often so painstakingly cataloged. Out of the box, Koha allows us to fetch and display book covers from several sources, both local and from data providers over the Internet. The commonest of these being (a) AmazonCoverImages and (b) GoogleJackets. While these two settings (either or both) will work for a large number of books, for users in India, there are also a lot that these two sources do not offer. Especially books in Indian languages or simply books printed without an ISBN (there are a lot of these in India).

When no image is found Koha displays the text “No cover image available” as a placeholder. A lot people would rather not see this. The Koha JQuery Library on the Koha Community wiki offers the following jQuery based approach:

$(document).ready(function(){
    $('.no-image').remove();
});

You simply place this snippet into the OPACUserJS system preference and hey Presto! these pesky “No cover image available” displays are history. Well, yes and no! Yes it works, and “no”, this may not prevent that text to be displayed, at least once. Why? well how about a slow PC and an even slower internet connection? Either one of the two or a combination of both will usually ensure that you get to take a look “No cover image available” before they are “removed” from the displayed page.

The simpler thing IMHO, is to take the Cascading Style Sheet (CSS) route, which as simple as placing the following one-liner into your OPACUserCSS system preference:

.no-image { display: none; }

no-image-removed

Not only will you *never* get to see the “No cover image available” markup anymore, no matter how slow your PC / Internet connection is, this is also more efficient, since rather than use jquery to first select and then .remove() a DOM element with a particular style class attribute, we simply re-define that they shouldn’t be displayed at all. The browser DOM does all the optimization in this approach.