Spreadsheets Module – Web

Spreadsheets

Spreadsheets and Queries are used for searching for specific field data and displaying the results in a spreadsheet. You can search either your individual, pedigree, or sample level fields.

To get to the Spreadsheets module, first choose Spreadsheets at the top of the page. You may then choose whether you would like to run an Individual, Pedigree, or Sample Spreadsheet.
The folders pane allows you to see the different folders for saving and loading spreadsheet formats.
Once a folder has been selected, all your saved spreadsheet formats in that folder will populate.
The New Spreadsheet button allows you to build a new Individual, Pedigree, or Sample Spreadsheet, depending on which Spreadsheet module was chosen. Once you build a spreadsheet you can save it as a format, then re-run and edit as needed.

The actions menu, which can be accessed by clicking the three bars in the Actions column, gives the option to run, edit, and delete a spreadsheet.

Run Spreadsheet – runs the specified spreadsheet.
Edit Format – allows you to edit the current format of the spreadsheet.
Delete Spreadsheet – deletes the specified spreadsheet.

When selecting Edit Format you will be brought to the following page, which will allow you to add any fields to the spreadsheet that you would like.

Editing Spreadsheets

To edit an existing format click the Edit Format option from the Actions menu. Here you can select which fields you wish to query, add logical operators to your search, and specify how the results are sorted.

The menu buttons located at the top, allow you to run the current spreadsheet, load a new format, or save the current spreadsheet as a new format.
The Fields pane lists the fields in your database. You can search for fields by typing a portion of the field name into the search bar.
The Columns pane is used to list the columns (fields) you wish displayed on the spreadsheet. The top to bottom ordering of fields in this pane will reflect as left to right columns on the spreadsheet results. You can change the order of the spreadsheet result columns by dragging each field to your desired location, or moving each field up or down using the green arrows located to the right of the column pane.
You can add these fields to the Sort pane or Query pane using the three icons located on the same line as the field when you mouse-over the field. To add to the Sort pane, click the icon with three lines and the arrow facing down. To add to the Query, choose the icon with the magnifying glass. To delete the field, click the red X.
The Sort pane determines the sort order of your query results. Drag fields from the Fields pane into this pane or use the Add to sort icon to copy the field into the Sort pane.
The Query pane is where you can add specific conditions to a field, e.g. the Last Name equals Smith.

Building Queries

Follow these steps to build a Spreadsheet Query:

Select which field(s) you want shown on the spreadsheet results – You can use the search field to quickly find the field you are looking for. Just type in any portion of the field name and click the magnifying glass icon on the right side of the search field.
Drag the field into the Columns and/or Query panes.
From the fields you have dragged into the Columns pane, use the icons available in the mouse-over menu to add it to the Sort or Query panes.
Select whether you would like to sort in Ascending (default) or Descending. You may sort by several fields. The first field will be the primary sort column, the second field the secondary, and so on.
Configure the logical operators, values, Boolean operators, and parentheses (to apply an order of operations) in the Query pane.

When carrying out a multiple fields query, use parentheses to define the order of operations for a query. The order of operations determines the queries that must be carried out first in a series of queries. For example, the below image shows a query that first filters individuals who were diagnosed with Breast cancer at the age of 45. The query then filters individuals who were diagnosed with Fallopian Tube cancer. Because this query uses the OR operator, the query returns individuals who meet either query criteria.

Logical Operators

The following logical operators are available to target your search results:

Operator	Description
begins with	Search for an item where the specified field contains data that begins with the search criteria. For example, if the query field for individuals is the Weight field, and you specify Weight begins with 17, then only those individuals for whom the weight begins with a 17 (17 or 170-179) are returned in the search.
contains	Search for an item where the specified field contains any instance of the search criteria. For example, if the query field for individuals is the Weight field, and you specify Weight contains 75
does not begin with	Search for an item where the specified field contains data that does not begin with the search criteria. For example, if the query field for individuals is the Weight field, and you specify Weight does not begin with 17, then only those individuals for whom the weight does not begin with a 17 (0-16, 18 -169, and so on) are returned in the search.
does not contain	Search for an item where the specified field does not contain any instance of the search criteria. For example, if the query field for individuals is the Weight field, and you specify Weight does not contain 75, then all individuals who have a weight in which 75 does not appear (0-74, 76-174, 176-274, and so on) are returned in the search.
does not end with	Search for an item where the specified field contains data that does not end with the search criteria. For example, if the query field for individuals is the Weight field, and you specify Weight does not end with 75, then only those individuals for whom the weight does not end with a 75 (0-74, 76-174, 176-274, and so on) are returned in the search.
does not equal	Search for an item that meets any criteria other than the specified value. For example, if the query field for individuals is the Weight field, and you specify Weight ≠ 175, then only those individuals whose weight does not equal 175
ends with	Search for an item where the specified field contains data that ends with the search criteria. For example, if the query field for individuals is the Weight field, and you specify Weight ends with 75, then only those individuals for whom the weight ends with a 75 (75, 175, 275, and so on) are returned in the search.
equals	Search for an exact match. For example, if the query field for individuals is the Weight field, and you specify the search criteria as Weight = 175, then only those individuals whose weight is exactly 175 are returned in the search.
in	Allows for comma separated values to be entered into a field so that multiple items can be searched and returned in the search results. For example, if the query field for individuals is the Weight field, and you specify Weight in 75, 80, 85, 90, then all individuals whose weight is exactly 75, 80, 85, or 90 are returned in the search. Note: You can copy and paste a list of values from .csv file into a query of this type.
is blank	Search for an item where the specified field contains absolutely no data. For example, if the query field for individuals is the Weight field, and you specify Weight is blank, only those individuals for whom data has not been entered into the Weight field are returned in the search. Note: A zero (0) is an actual data value. This means that if a zero (0) has been entered into the Weight field for an individual, then the individual is not returned in the search.
is greater than	Search for an item that is greater than the specified value. For example, if the query field for individuals is the Weight field, and you specify Weight > 175, then only those individuals whose weight is greater than 175 are returned in the search.
is greater than or equal to	Search for an item that is greater than the specified value or is an exact match for the specified value. For example, if the query field for individuals is the Weight field, and you specify Weight > 175, then those individuals whose weight is exactly 175 or greater are returned in the search.
is less than	Search for an item that is less than the specified value. For example, if the query field for individuals is the Weight field, and you specify Weight < 175, then only those individuals whose weight are less than 175 are returned in the search.
is less than or equal to	Search for an item that is less than the specified value or is an exact match for the specified value. For example, if the query field for individuals is the Weight field, and you specify Weight < 175, then those individuals whose weight is exactly 175 or less are returned in the search.
is not blank	Search for an item where the specified field contains any valid data. For example, if the query field for individuals is the Weight field, and you specify Weight is not blank, all individuals for whom data has been entered into the Weight field are returned in the search. Note: A zero (0) is an actual data value. This means that if a zero (0) has been entered into the Weight field for an individual, then the individual is returned in the search.
is not in	Allows for comma separated values to be entered into a field so that multiple items can be searched and excluded from the search results. For example, if the query field for individuals is the Weight field, and you specify Weight is not in 75, 80, 85, 90, then all individuals whose weight is not exactly 75, 80, 85, or 90 are returned in the search. Note: You can copy and paste a list of values from a .csv file into a query of this type.
is not like	A pattern matching search based on the keyword “like.” An underscore (_) is used to match exactly one character, and the percent sign (%) is used to indicate any number of characters. For example, if the query field for individuals is the Weight field, then to search for all individuals whose weight is not like 75, select “is not like” as the operator and enter %75 in the Value field. The % indicates that any number of characters can come before 75, for example, 0 -74, 176 – 184, 186 – 274, and so on. Note: You can use the % anywhere in the search criteria, for example, 75% or 7%5.
like	A pattern matching search based on the keyword “like.” An underscore (_) is used to match exactly one character, and the percent sign (%) is used to indicate any number of characters. For example, if the query field for individuals is the Weight field, then to search for all individuals whose weight is like 75, select “like” as the operator and enter %75 in the Value field. The % indicates that any number of characters can come before 75, for example, 75, 175, 275, and so on. Note: You can use the % anywhere in the search criteria, for example, 75% or 7%5.

Spreadsheet Results

The Menu Buttons give you many different options.
- Edit – edits the format of the current spreadsheet
- Load Format – loads a different spreadsheet format
- Save Format – saves the current spreadsheet format
- Export – allows you to export the current spreadsheet data
- Batch Letters – allows you to generate letters to everyone in the spreadsheet
- Count – counts all the individuals, pedigrees, or samples (depending on the selected type) in the spreadsheet
- Undo – allows any changes to be undone if any data was added through the spreadsheet
- Redo – allows you to redo any changes you made
When right-clicking on an individual, if it is an individual spreadsheet, you will be given the option to open the pedigree or the individual datasheet.

You can resize the column headings by hovering over the line between column headings until a vertical line icon with arrows appears, and dragging the icon to the column width you would like. If you right-click a column heading, you can filter the data using the specified column.

Editing Data within the Spreadsheet

From the spreadsheet you have the ability to edit the field. You can do this by going to the individual you would like to add data for, and going to the field and inputting the data.

Loading, Saving, and Exporting Formats

To load a different spreadsheet while in another spreadsheet, first click Load Fmt. A pop-up screen will then appear with previously saved spreadsheet formats to choose from.