Scriptlets
From document provided by Specify:
Scriptlets perform special Java functions that pull data from a Specify database and translate it into a given format in a report or label. Scriptlets are predefined in the Specify 6 code and provide for a range of possible formatting issues that a collection might come across when making labels and reports. Directions for use and a list of existing scriptlet functions in Specify 6 can be found at http://files.specifysoftware.org/SpecifyScriptlet.html. There are some that are not currently on the list, all of which are explained later in this document.
Note: When using scriptlets in a report, make sure that the Scriptlet Class in Report Properties (Edit>Report Properties) is set to edu.ku.brc.specify.config.Scriptlet (see below).
Also: If copying and pasting, keep in mind that some fields are unique to different schema/databases, so make sure to replace those fields with the correct field in SpiReport.
Scriptlets must be added to the Expression Editor in the following format: ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).scriptletName(required fields)
The following examples are common use-case scenarios or further explanation for some existing scriptlets:
- Expression for formatted Latitude/Longitude and direction characters
- Truncating lat/long values
- To truncate lat/long and get direction characters
- To set Latitude and Longitude to appear blank when the value is null
- Scriptlet for mixed concatenation expressions using ampersands
- Better scriptlet for primary collector
- Better scriptlet for secondary collectors
- Scriptlet for primary collector
- Scriptlet for secondary collectors
- Scriptlet for calculating length of loan
- To retrieve agents by Loan Agent Role
- To retrieve agents by Gift Agent Role and Borrow Agent Role
- To format Preparations to match the format on the default invoice
- Scriptlet for summarizing Preparations and combining like-PrepTypes
- Using .replace to Modify Values
- Making US states display abbreviations instead of full names
- Making dates display full month names instead of numbers
- Making dates display 3 letter month names instead of numbers
- Making dates have extra zeros
- To get rid of leading zeros in a catalog number
- To add leading zeros for numeric catalog number format
- Get rid of all extra commas or other extra characters/punctuation
To display latitude and longitude in the original format (as they are shown in Specify) and with direction characters (N, E, S, W), you would use the .formatLatLon and .getDirChar Scriptlets. The query fields necessary for these two scriptlets are latitude1, longitude1, and original latitude longitude unit. ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatLatLon($F{Latitude1}, $F{Original_Latitude_Longitude_Unit}, true) + ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getDirChar($F{Latitude1}, true) + " " + ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatLatLon($F{Longitude1}, $F{Original_Latitude_Longitude_Unit}, false) + ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getDirChar($F{Longitude1}, false)
The above expression displays as:
($F{Latitude1}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatBigDecimal($F{Latitude1},5)+" ")+($F{Longitude1}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatBigDecimal($F{Longitude1}, 5))
($F{Latitude1}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatBigDecimal($F{Latitude1},4)+" "+((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getDirChar($F{Latitude1}, true))
($F{Longitude1}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatBigDecimal($F{Longitude1},4)+" "+((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getDirChar($F{Longitude1}, false))
*Note: If you want the lat/long values to print exactly as they are in Specify (ex: decimal degrees), use the lat1text and long1text fields in the Query. This is easier than using the original lat/long scriptlet above, but it doesn’t include a directional character.
- In the left panel of iReport, right-click Variables and select “Add…” and choose Variable.
- In the Variable form, use these settings:
- The name should be either “Latitude” or “Longitude.” Each needs its own variable.
- Variable Class Type - String
- Calculation Type - Nothing
- Reset Type - Report
- Increment Type - None
- Set the Variable Expression to one of these two, depending on whether the variable is Latitude or Longitude: o ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatLatLon($F{Latitude1}, $F{Original_Latitude_Longitude_Unit}, true) + ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getDirChar($F{Latitude1}, true) o ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).formatLatLon($F{Longitude1}, $F{Original_Latitude_Longitude_Unit}, false) + ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getDirChar($F{Longitude1}, false)
- The Initial Value Expression should be “”
- In the field where you want the latitude and longitude, the expression will look like this:
($V{Latitude}==null?"":$V{Latitude})+($V{Longitude}==null?"":", "+$V{Longitude})
Remember to edit the field’s properties and ensure that the Text Field Expression Class is set to String, or you will get an error when you run the report. There are screen shots of creating a Variable in the next section of this document, if you run into trouble setting up the Lat/Long variables.
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).escapeForHtml($F{Author})
"<style isItalic="true" pdfFontName="Helvetica-Bold">" + ($F{Genus}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).escapeForHtml($F{Genus}))
- " " + ($F{Species}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).escapeForHtml($F{Species}))
- "</style>" + ($F{Species_Author}==null?"":((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).escapeForHtml($F{Species_Author}))
- " " + ($F{Variety}==null?"":" var. "+"<style isItalic="true" pdfFontName="Helvetica-Bold">" + $F{Variety}+"</style>") + ($F{Variety_Author}==null?"":$F{Variety_Author})
- " " + ($F{Subspecies}==null?"":" subsp. " + "<style isItalic="true" pdfFontName="Helvetica-Bold">" + $F{Subspecies}+"</style>") + ($F{Subspecies_Author}==null?"":$F{Subspecies_Author})
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).deaggregateFirst($F{Collectors}, “, “) *where $F{Collectors} is the aggregated Collectors field and “, “ is the separator
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).deaggregateSecondary($F{Collectors}, “, “) *where $F{Collectors} is the aggregated Collectors field and “, “ is the separator
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getFirstCollector($F{Catalog_Number})
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getSecondaryCollectors($F{Catalog_Number})
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).dateStringDifference($F{LoanDate}, $F{CurrentDueDate})
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByLoanAgentRole($F{LoanNumber},”agent role”,”agent.field”)
“agent role” is the role of the agent in the Loan that you are using. For example, if you are using the scriptlet for the agent who is the recipient of the loan, the role will probably be Borrower. Be sure to compare the loan record with the report to make sure you are using the correct role.
“agent” refers to the table in which the field in question is located. For address fields, the table would be “address,” not “agent.” Make sure that the field names match the ones in the schema EXACTLY. To check this, go to Schema Configuration, select the agent or address table (depending on which field you are checking) and look at the Fields box (below the Tables box) to find the name of the field. Some examples of fields you might use are agent.firstName, agent.lastName, address.address, address.city, and address.state. Be sure to compare the loan record with the report to make sure you are using the correct fields.
Examples:
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByLoanAgentRole($F{LoanNumber},”Borrower”,”agent.lastname”)
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByLoanAgentRole($F{LoanNumber},”Borrower”,”loanagent.remarks”) ***only field where you must type “loanagent”
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByLoanAgentRole($F{LoanNumber},”Borrower”,”address.address2”) *Note: if you use this scriptlet, you do not need to include the Loan Agent fields it calls for in the query. Also, this scriptlet only works for agents added to a Loan record under Loan Agents. It will not work for agents in Shipped To or Shipped By.
Cheap expression: (tip- watch Meredith’s video on Loan Agent Borrower variables: ("".equals($V{BD})?"":$V{BD}+"\n")+("".equals($V{BRB})?"":$V{BRB}+"\n")+("".equals($V{BI})?"":$V{BI}+"\n")+("".equals($V{BA})?"":$V{BA}+"\n")+("".equals($V{BA2})?"":$V{BA2}+"\n")+("".equals($V{BC})?"":$V{BC}+", ")+("".equals($V{BS})?"":$V{BS}+" ")+("".equals($V{BP})?"":$V{BP}+"\n")+($V{BCountry}==null?"":$V{BCountry})
Use the following scriptlet:
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByRole("gift","GiftNumber",$F{Gift_Number},"giftagent","Receiver","agent.FirstName")+((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByRole("gift","GiftNumber",$F{Gift_Number},"giftagent","Receiver","agent.LastName") Where “GiftNumber” is the name of the invoice field in the schema, and $F{Gift_Number} is the field’s name in the database/query.
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByRole("borrow","InvoiceNumber",$F{Invoice_Number},"borrowagent","Borrower","agent.FirstName")+((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).getByRole("borrow","InvoiceNumber",$F{Invoice_Number},"borrowagent","Borrower","agent.LastName") Where “InvoiceNumber” is the name of the invoice field in the schema, and $F{Invoice_Number} is the field’s name in the database/query.
NOTE: This scriptlet also works for exchanges, accessions, etc.
($F{Quantity}==null?"":$F{Quantity}+"").toString() + " " + ($F{Prep_Type}==null?"":$F{Prep_Type}+"") + " of " + ((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).calcLoanQuantity($F{Count}, $F{Quantity_Returned}, $F{Quantity_Resolved}) + " " + ($F{Prep_Type}==null?"":$F{Prep_Type}+"")
This will print preparations in this format (under Specimen Count):
((edu.ku.brc.specify.config.Scriptlet)$P{REPORT_SCRIPTLET}).aggregatePreps($F{RecordID},"%s - %d", "; ")
The most common uses of modifiers for replacing values are for the Sex pick list and dates (loan date, collected date, cataloged date, etc.) Here are a few example scenarios:
• A user wants sex pick list values of ‘M’, ‘F’, and ‘UN’ from the database to display as ‘Male’, ‘Female’, and ‘Unknown’ on the labels. The expression would appear as such:
$F{Sex}.equals("M") ? "Male" : $F{Sex}.equals("F") ? "Female" : $F{Sex}.equals("UN") ? "Sex Unknown" : ""
• A user is using a sex info field in Specify that can contain multiple values, such as ‘F’, ‘Female’, ‘M’, ‘Male’, and perhaps other data. Example of data in Specify:
The user wants to use a symbol for the sex instead of text, with any other data in field displaying after. So the expression must account for the multiple sex values:
($F{Sex_(info)}==null ? "": $F{Sex_(info)}.replace("Female","♀").replace("F", "♀").replace("Male", "♂").replace("M","♂"))
On the label, the data will display as:
($F{State}==null?"":$F{State}).replace("Alabama","AL").replace("Alaska","AK").replace("Arizona","AZ").replace("Arkansas","AR").replace("California","CA").replace("Colorado","CO").replace("Connecticut","CT").replace("Delaware","DE").replace("Florida","FL").replace("Georgia","GA").replace("Hawaii","HI").replace("Idaho","ID").replace("Illinois","IL").replace("Indiana","IN").replace("Iowa","IA").replace("Kansas","KS").replace("Kentucky","KY").replace("Louisiana","LA").replace("Maine","ME").replace("Maryland","MD").replace("Massachusetts","MA").replace("Michigan","MI").replace("Minnesota","MN").replace("Mississippi","MS").replace("Missouri","MO").replace("Montana","MT").replace("Nebraska","NE").replace("Nevada","NV").replace("New Hampshire","NH").replace("New Jersey","NJ").replace("New Mexico","NM").replace("New York","NY").replace("North Carolina","NC").replace("North Dakota","ND").replace("Ohio","OH").replace("Oregon","OR").replace("Pennsylvania","PA").replace("Rhode Island","RI").replace("South Carolina","SC").replace("South Dakota","SD").replace("Tennessee","TN").replace("Texas","TX").replace("Utah","UT").replace("Vermont","VT").replace("Virginia","VA").replace("Washington","WA").replace("West Virginia","WV").replace("Wisconsin","WI").replace("Wyoming","WY")
- Dates are saved in the database in the format “08/22/1994,” but the desired format is “22/August/1994.” The expression would then look like this:
($F{Collection_Date_(Day)}==null?"":$F{Collection_Date_(Day)}+" ").toString()+($F{Collection_Date_(Month)}==null?"":$F{Collection_Date_(Month)}+" ").toString().replace("10","October").replace("11","November").replace("12","December").replace("1","January").replace("2","February").replace("3","March").replace("4","April").replace("5","May").replace("6","June").replace("7","July").replace("8","August").replace("9","September") +($F{Collection_Date_(Year)}==null?"":$F{Collection_Date_(Year)}+"").toString()
- Dates are saved in the database in the format “08/22/1994,” but the desired format is “22/Aug/1994.” The expression would then look like this:
($F{Loan_Date_(Day)}==null?"":$F{Loan_Date_(Day)}+"/").toString()+($F{Loan_Date_(Month)}==null?"":$F{Loan_Date_(Month)}+"/").toString().replace("10","Oct").replace("11","Nov").replace("12","Dec").replace("1","Jan").replace("2","Feb").replace("3","Mar").replace("4","Apr").replace("5","May").replace("6","Jun").replace("7","Jul").replace("8","Aug").replace("9","Sep") +($F{Loan_Date_(Year)}==null?"":$F{Loan_Date_(Year)}+"").toString()
For example, your date looks like this 2012/7/9 and you want it to look like 2012/07/09. Note that the format is year/month/day here, you can switch it around as you wish. Use this expression:
"Date: " + ($F{Date_(Year)}==null?"":$F{Date_(Year)}+"").toString() +($F{Date_(Month)}==null?"":"/"+(($F{Date_(Month)}+"").toString().length() == 2 ? ($F{Date_(Month)}+"").toString(): "0" + $F{Date_(Month)}+"")).toString() + ($F{Date_(Day)}==null?"":"/" + (($F{Date_(Day)}+"").toString().length() == 2 ? ($F{Date_(Day)}+"").toString(): "0" + $F{Date_(Day)}+"")).toString() Below is an example of how the order of the date fields can be moved around:
"Det. Date: "+($F{Determined_Date_(Day)}==null?"":$F{Determined_Date_(Day)}+"-").toString()+($F{Determined_Date_(Month)}==null?"":$F{Determined_Date_(Month)}+"-").toString()+($F{Determined_Date_(Year)}==null?"":$F{Determined_Date_(Year)}+"").toString()
.replaceFirst(" 0+", " ")
("000000000".substring(0,9-$F{CatNo}.toString().length())+$F{CatNo}+"").toString()
*you will probably have to change the field (CatNo) to match whatever is in the database, and the number (9) as well if your catalog number format has less than 9 character length
.replaceAll("^, ","")
.trim().replaceAll(",$","").trim().replaceAll(",$","")
If you want to get rid of other extra characters, replace the comma in the expression above with that character.