Core LIMS Training: Velocity Templates

1. Core LIMS Training: Velocity Templates

2. Course Topics • What are Velocity Templates? • Custom Instrument Files • Custom Experiment Reports • Custom Inventory Reports • Custom E-mails Note: This training assumes that you have reviewed and understand Basic Administration Training

3. Velocity Templates for Reports • Velocity Template Language (VTL) is a simplified language that can be used to define customized reports in the LIMS • Four use cases currently in the LIMS • Create formatted file outputs from a LIMS experiment to be loaded into an instrument • Write customized experiment reports to a folder after experiment is published • Create customized inventory reports to run from Containers or Samples List Functions page • Custom e-mail reports that are activated by a trigger or set on a recurring alert • Enables admins more flexibility to define dynamic content by referencing methods already defined in underlying Java code • More documentation on VTL can be found at: http://velocity.apache.org/engine/releases/velocity-1.7/vtl-reference-guide.html

4. Creating a File for Instrument Input • LIMS Setup By Admin: • Add an association to the instrument Entity Type and the ci_InstrumentFile attribute (data type = Instrument Sequence File) on the experiment Entity Type • Add the attribute ci_velocity_template (data type = Velocity Context Text Area or Text - Generic) to the instrument Entity Type • Create the instrument record and fill in the ci_velocity_template with a report defined using VTL • End User Process: • Create new experiment record with desired instrument • Associate any required samples or containers • Click on Generate hyperlink on experiment record to create file • Click on View hyperlink to download and save file • Upload custom file to instrument and run experiment

5. Admin Setup – Update Experiment Entity Type Add the ci_InstrumentFile attribute to make a Generate hyperlink on the experiment record If not already there, add an association to the instrument you want to make a file for on the experiment entity type

6. Admin Setup – Update Instrument Entity Type If not already there, add the velocity attribute on the instrument entity type

7. Admin Setup – Create Instrument Record Enter a descriptive name for end users to select when creating an experiment Enter the velocity script that defines how the file is laid out in the instrument record

8. End User – Expt with Generate/View Hyperlinks Don’t forget to add containers or samples that you want to be in the instrument file first After it is generated, click on View to download/export the file Click on Generate to create or update the file Associated instrument record contains the file definition

9. End User – Generated File After it is generated, click on View hyperlink to view the file

10. Example Velocity Script for Instrument File • First line defines column header names in output file • ${tab} needed to define separators between columns • #end closes the loop • #foreach ($seq in $seqList) begins a loop over the experiment samples • Each $seq term is calling for data for each sample in the list • $seq.lot.barcode, $seq.container.barcode, $seq.cellRow, and $seq.cellCol call global variables • $seq.sample.getValue(“MW”) is calling a sample attribute – MW must be spelled exactly as the attribute is spelled in the LIMS Tip: Copy/paste scripts into Wordpad or a similar text editor to view code without scrolling

11. Velocity Template Tips References begin with $ and are used to get something Directives begin with # and are used to do something Avoid spaces within the references (or use an underscore) Attributes that are used for Velocity reports should be in capital letters and spelled exactly as they are in the LIMS For the header lines of code, some instruments require the “${tab}” term written out to denote the start of a new header. Some instruments can just have a actual space placed there by hitting the ‘Tab’ button.

12. Available Global Variables for Instrument Files • In addition to the above variables: • any attribute from other objects that are directly associated to an experiment • The variable name is in the format PREDICATE_ATTRIBUTENAME (replace all spaces, non-alphanumeric characters with ‘_’ and capitalize all characters). For example, the variable named $INSTRUMENT_METHOD_ACQUISITION_METHOD is constructed from the INSTRUMENT METHOD association to an experiment. ‘INSTRUMENT METHOD’ is the predicate and ‘Acquisition Method’ is an attribute of the Instrument Method object. • seqList (List of experiment samples)

13. Variables Available in seqList (for Instruments) 1 seqList is used to loop through a list of experiment samples

14. Variables Available in seqList (for Instruments) 2

15. Common Velocity Script Examples • Output a tab character: ${tab} • Loop through an experiment sample list and output the sample barcode: #foreach( $seq in $seqList ) $seq.sample.barcode #end • Conditional for a null value: #if(!$seq.sample.getValue(“MW”))No MW found#{else}$seq.sample.getValue(“MW”)#end • Output the loop counter: $foreach.count • Output a date in a specified format: $date.get('yyyyMMdd')

16. Creating a Customized Experiment Report • LIMS Setup By Admin: • If missing, add an Experiment Report Entity Type under the Report Supertype with a the ci_velocity_template attribute • Create a new experiment velocity report record and fill in the ci_velocity_template attribute with a report defined using VTL • Add an association to the Experiment Report Entity Type on the Assay Entity Type using ON_PUBLISH_REPORT as the predicate • Create or edit Assay record to link specific assay record to report record • Modify or create Trigger Implementation Entity Type • Create a trigger record • Add trigger to experiment • End User Process: • Publish a completed experiment record of the specific assay defined • View attached file

17. Admin Setup –Experiment Report Entity Type If not already there, create an Experiment Report Entity Type under a Report Super Type and add a velocity template attribute

18. Admin Setup – Create Experiment Report Record Define report using Velocity

19. Admin Setup –Associate Report To Assay Type On the Assay Entity Type add an association to the experiment report with the ON_PUBLISH_REPORT context

20. Admin Setup –Associate Assay To Report On the Assay Record select the experiment record with the velocity report

21. Admin Setup –Trigger Entity Type If one does not already exist, you may need to create or modify a Trigger Entity Type under Trigger Impl Super Type Description, action type, and Implementation Class are required; if you only need these attributes you can use the Trigger Impl Entity Type

22. Admin Setup – Create Trigger Record Use a descriptive name for pull down menu Define event when report created Must use this class Optional: Define folder where the file will be output on server (you will need to create the folder on server) Can optionally filter reports to a sample type

23. Admin Setup – Add Trigger to Experiment Select the Triggers hyperlink for your experiment type Select the trigger you created in the Event Only section under ON_PUBLISH Click the Add Triggers button at the bottom of the page

24. End User – Create and Publish Experiment After the publish link is selected, the report will be automatically generated and attached to the experiment Click on the View File Hyperlink to view the report

25. Example Template For Experiment Report Source Barcode${tab}Area${tab}Injection Volume #foreach( $seq in $seqList) $seq.lot.barcode${tab}$seq.es.getData("area")${tab}$seq.es.getData("injection_volume") #end

26. Available Global Variables for Expt Reports • In addition to the above variables: • any attribute from other objects that are directly associated to an experiment • The variable name is in the format PREDICATE_ATTRIBUTENAME (replace all spaces, non-alphanumeric characters with ‘_’ and capitalize all characters). For example, the variable named $INSTRUMENT_METHOD_ACQUISITION_METHOD is constructed from the INSTRUMENT METHOD association to an experiment. ‘INSTRUMENT METHOD’ is the predicate and ‘Acquisition Method’ is an attribute of the Instrument Method object. • seqList (List of experiment samples)

27. Variables Available in seqList (for Experiments) 1 seqList is used to loop through a list of experiment samples

28. Variables Available in seqList (for Experiments) 2

29. Creating a Customized Inventory Report • LIMS Setup By Admin: • If missing, add the Inventory Velocity Report Supertype and the Container Inventory Velocity Report/Sample Inventory Velocity Report Entity Types • Create a new container or sample inventory velocity report record and fill in the ci_velocity_template attribute with a report defined using VTL • End User Process: • Transfer or paste in a list of containers or samples into a list functions page • Select the report from the Inventory Report option and Submit

30. Admin Setup – Check Velocity Entity Types Exist You will need Inventory Velocity Report Supertype with the 2 Entity Types below

31. Admin Setup – Create Velocity Report Record Report Name will appear in pull down menu on List Functions page Enter the velocity script that defines how the inventory report is displayed

32. End User – Call Report From List Functions Page Select the custom report with velocity script

33. End User – Report Will Be a Txt File • This allows admins to expose other columns of data not available in the default Inventory Report • Useful for creating reports with sample, lot, and container info together

34. Example Velocity Script for Inventory Report This velocity template creates the report shown on the previous slide

35. Available Global Variables for Inventory Reports • cellList (List of container cells /wells)

36. Variables Available in cellList cellList is used to loop through a list of container cells

37. Inventory Report Example With Association This container inventory example calls an association Container Type${tab}Container Barcode${tab}Location${tab}Cell${tab}Well${tab}Amount${tab}Sample${tab}Lot${tab}Number of Compounds${tab}Includes Lots #foreach( $container in $containerList) #set( $containerFormat = ${container.getValue("Container Format")} ) #set( $cellMap = ${formatToCellMap.get($containerFormat)} ) #foreach( $cell in $container.getCellList()) #set( $cellNum = ${cell.getCell()} ) #foreach( $content in $cell.getContents()) ${container.getEntityTypeName()}${tab}${container.getBarcode()}${tab}${container.getFullLocation()}${tab}${cell.getCell()}${tab}${cellMap.get("$cellNum")}${tab}${cell.getAmountString()}${tab}${content.getLot().getSample().getBarcode()}${tab}${content.getLot().getBarcode()}${tab}${content.getLot().getSample().getValue("Number of Compounds")}${tab}#foreach( $smallMol in $content.getLot().getChildAssociations("SMALL MOLECULE LOT")) ${smallMol.getBarcode()},#end${tab} #end #end #end

38. Sample Inventory Report Example Container Type${tab}Container Barcode${tab}Location${tab}Cell${tab}Amount${tab}Sample${tab}Lot${tab}Concentration #foreach( $infoMap in $cellList)${infoMap.container.getEntityTypeName()}${tab}${infoMap.container.getBarcode()}${tab}${infoMap.container.getFullLocation()}${tab}${infoMap.cell.getCell()}${tab}${infoMap.cell.getAmountString()}${tab}${infoMap.lot.getSample().getBarcode()}${tab}${infoMap.lot.getBarcode()}${tab}${infoMap.cellContent.getConcentrationDisplay()} #end

39. Creating Customized E-mails • Multiple ways that formatted e-mails can be sent • Option 1: Velocity Template on a Trigger • Define the custom message on trigger record • Option 2: Velocity Template on a Message Linked to an Alert • Define the custom message on a message record and execute on a recurring schedule

40. Velocity Templates on Triggers • LIMS Setup By Admin: • If missing, add the ci_velocity_template attribute and an association to a message on the trigger entity • Create message record with distribution list only • Create trigger record with association to message and velocity template filled out • Define trigger on the entity that will invoke the trigger • End User Process: • User performs action defined by trigger • Users on distribution list receive e-mail report

41. Setup for Option 1 (VT on Trigger Entity) Add the ci_velocity_template attribute to a trigger entity type if not already there Also need an association to the message entity type

42. Setup for Option 1 (Create Message Record) If you have a Velocity Template defined on the trigger, leave the Implementation Class blank (any hardcoded messages in the Implementation Class will take precedent over the velocity template) Create a new message record Add any user accounts that need to receive the message automatically

43. Setup for Option 1 (Create Trigger Record) Create a new trigger record Enter when trigger can occur – this will add the trigger to trigger pull down menus for entities Enter message written with Velocity/standard HTML • Select an association to message record you just created • Message record will also display the association now

44. Setup for Option 1 (Define Trigger on Entity) Select Triggers hyperlink on Entity Type you want to trigger a message from • Select the trigger records you created earlier to define triggers on the entity type • Only Action Types that are listed on the trigger record will be selectable

45. End User Performs Action This example a user can create a new QA Sample record to trigger a message

46. End User Gets E-mail

47. Velocity Templates on Messages With Alerts • LIMS Setup By Admin: • Add the ci_velocity_template attribute to a message • Create message record with distribution list and velocity template filled out • Create alert record and associate to message • Set the alert time • End User Process: • Users on distribution list receive e-mail report

48. Setup for Option 2 (VT on Message Entity) Add the ci_velocity_template attribute to a message entity type if not already there

49. Setup for Option 2 (Create Message Record) Required to have some SQL select statement for the Custom Data Message, but the velocity template is not required to use the results returned from the query Create a new message record Enter Velocity Template If VT is null, then you will get an unformatted table of all data from the SQL query Add any user accounts that need to receive the message automatically

50. Setup for Option 2 (Create Alert Record) Enter when alert will occur Default = daily Weekly enter day of the week as number (Monday=1) Monthly enter day of the month as number Create a new alert record • Select an association to message record you just created • Message record will also display the association now