How did we get here? What is the reason for a "template".

The Beef Cattle Survey data depends on uploading of spreadsheet data. Data sources other than direct entry can be problematic. Spreadsheet data needs to fit into the database. By "fit" it is meant the fields have to line up and be of the same data type. As well it may require a translation of text into an integer match the database structure lookup lists used.

We have five different sites providing spreadsheet data. All of the sites are to follow strict protocols how the spreadsheet should be setup. It will not happen. We have already seen it is the case. So we have developed a mechanism to upload a spreadsheet of data, then check structure and then match fields in the database.
Template management is where we setup a template for a specific site or data source and describe how the spreadsheet data is to be interpreted when uploaded into the database.

Template: A Summary

A template is a list of columns which describes db strucutures for data we want loaded into the production databases.
Each template represents one and only one drug route: bolus, feed or injectable.
Each template can apply to one or more spreadsheets of data to be uploaded.
A spreadsheet can be selected to check against a template to make sure the data matches what we can use in production.
Once a template is setup you will be able to check if a new template meets the requirements for use.
Templates are necessary when the final upload of data occurs into the production database.
Templating is how we provide a map from a sites spreadsheet of data into our standardized data tables.

Who gets to setup templates?

Only users designated by Dr. Sheryl Gow as Project Managers will have access to Template Management.

How to begin?

Once the form opens go to "Select Group" drop down list. Pick a group. Remember a group is one or more veterinarians from a site providing the data. Once a group is selected then "Select Template". A template depends on the group. All templates are related to a specific group so don't be concerned. It is looked after.
Once you have made your selections, click on the Get button. It is a little button below with a green top and an orange bottom. After getting the data you will see a list of fields for the template.
If you do not see a list of fields, we will describe to get an initial structure to populate your template.

How to add a new template name?

If you selected a group, the templates are listed you will need to add a new template. To do this click on the add button (+) just to the right of Select Template drop down list. A panel of information appears.
Enter the new template name. Make it so you can recognize the group, year and drug route.
Select a drug for the template.
Please note when uploading a new spreadsheet of data do so for each group and separate out the different drug routes. There will be more instruction in the future how to upload spreadsheet data.

How to populate a new template from an existing uploaded table structure?

We are in the situation where a new template has been created but there is no description of the fields listed. Here we describe how to do this.
First, click on get template list by clicking on Green and Orange button
Next click on the + button with a blue background. It is the button to the left of the double + button. A panel should appear to the right. At the top of the panel it will say the following:
"Production Lot Upload History: Initial Template Source"
If you see this you have what you need to populate the template with an initial structure.
On the Production Lot Upload History panel you will see a Green and Orange (get) button. click it.
A list should appear below. In the list are a list of previously uploaded spreadsheets of data. These uploaded data must match the drug you selected for the template. Chose a table.
Once you have done this click on the Lightning button. You will be prompted if you want to continue to populate the template with the structure of this table. Click on Yes and proceed.
Once you do this you will see a list of fields for the template.

How to setup the spreadsheet structure to match the standardized database structure?

Here is where the magic happens. Each column or field is now listed based on the uploaded spreadsheet table. On the left we list the "Source" columns. On the right we list the "Target" columns. We know what the target column is and we can select it. A list of all the target, standardized database columns, will appear in a drop down list so we do not make mistakes. I call these the "Core" fields.
It may be a spreadsheet has drug data. The drug usage data is listed as either a Trade Name or an Active Ingredient name. If a field is either one of these then change the "Field Category" above the Source and Target sections. Save the change clicking the little save button above (looks like a floppy disk for those old enough to remember what that is).
Depending on the field category chosen you will see a Trade Name panel or Active Ingredient panel appear to the the right of the field name. The drug route and source will be populated for you. You will specify the drug from the standardized drug list provided. Chose it and save.
Please note the drug list matches the drug route chosen for the template. It is very important and makes things tighter when it comes to the final upload and integration into the database.

How to setup each row of the template: more details about core fields, column names and types!

Each row of the template represents a description of the field when you upload a spreadsheet of data.
The left hand side is labelled as "Source" and these are the columns names at the time the spreadsheet was uploaded.
The Source column information can not be changed. It is static and is there for reference and is important at checking time.
The right hand side is labeled as "Target". These columns will match the field names in the production database tables.
The first thing you should do is chose what category a field is. I mentioned it above but it is important.
The first field is called ssrow and should be set as a core field.
The ssrow field is set by the program to be a primary key. The remaining fields match what is in the spreadsheet.
For ssrow click on the check box to check "Column Not Used". It is used for checking but is not uploaded into production.
Press the save button above. Save the change and then move to the next row which represents another field in the spreadsheet.
Chose if the field is a Core field. Let us assume for a moment we are working on a core field and the field is to be used.
We then go to Target Column name. A drop down box list of field names appears. These names are standardized names in our final target table.
Chose the appropriate field name. Once you completed that move to Column Type. From the drop down box chose a type.
The types listed are typical for the PostGreSQL database engine we use to store the data. If you have trouble chosing a name or field type,
contact Dr. Sheryl Gow or other Project Administrators for more information of what to do. It is important!.

How to setup each row of the template: more details about core fields which are to become an integer in a lookup table!

We continue with the right hand side "Target" columns.
Sometimes a spreadsheet of data will contain a column with text in it. The text often represents categories.
The easiest way to explain it is by using an example.
A typical spreadsheet may have a column called sex. In the column may be male, female or some sex category.
As you go thru the rows of the spreadsheet to be uploaded, under the sex column you will see Male or Female or male or female of m or f.
These text values do not "fit" into the database. The database stores an integer from 1 to n or some integer range.
Each integer represents a value. Let us say 1 represents Male and 2 represents Female.
We have a complication when it comes to uploading this data into the production table.
We need to convert Male or male or m into a 1 then upload the value of 1 into the data table.
As well we need to convert Female or female or f into a 1 then upload the value of 2 into the data table.
Hmm!.
What do we do? For each column in the spreadsheet we can identify a column as a "lookup" value.
Click on Is a lookup? and make sure it is checked and then save your change.
Once you make the save, and the target field name has been changed to match the production table, you will see a drop down list appear.
The top of the little panel which appears has the actual target list. In our example 1 is Male and 2 is Female and only these values.
Below the target lookup list you will see a list of substitute values. For instanace male is not recognized to we will add male
to the substitute list below. Make sure you have selected Male on the main list and now the db system will recognize male as well.
You now add m in a similar manner. Once you do this then scroll down to Female in the main target lookup list. Now add substitutes
female and f below. The db system will now recognize all variations of male or female and we will be able to convert these values into integers
at final upload time. The production lot table at the moment has 5 lookup tables. By setting up the template appropriately we can do the upload.
One final note, it is not necessary to change field type for a field set as a Lookup field

How to setup each row of the template: more details non-core tables i.e. drug data!

We continue with the right hand side "Target" columns.
All spreadsheets of data should contain columns of numbers representing the amount of drug used.
As you scroll thru the template rows you will notice some column headers with drug names in them.
When you notice this you go to the field category and chose a non core cateogry. There are 2 categories to chose from.
They are Active Ingredient or Trade Name.
Some data comes as trade names and other spreadsheets of data are sent as an active ingredient. We have built in the flexibility.
to be able upload the data from the spreadsheet into the proper standarzied drug names we have.
What are the standardized drugs? There are three drug routes as we call it: bolus, feed and injectable.
There are 2 standard drug tables for each route: an active ingredient table and a trade name table.
Trade names are joined to active ingredients. So if we see a trade name appear we connect to the trade name list for that route.
If you chose Active Ingredient then the standard active ingredient list appears for that route.
Once you designate a template row as an active ingredient or trade name field, then an list appropriate list will appear.
For example, of the template is used for feed, then the feed drug list will appear. Most of the system information appears.
The user will have to actually chose the proper drug name for the list and save. You will do this for all designated drug columns.
The data will be stored in another production db table which is joined to the data with core information.

Setting up template target column ranges!

For the selected target column you will see a edit box to enter a range for the field.
Ranges are specified for numbers only for now. Possibly in the future we will allow date field ranges as well, but not now.
A typical range example for a number may be: 1 thru 100
There are several important things to remember:
The "thru" keyword is required. If we do not find the word thru an error will occur and user prompted to make a change.
There must be a number to the left of the the thru keyword.
There must be a number to the right of the thru keyword.
Once all of that is specified then you can save the target field specification.
During the data checking process if a range is specified then values in the spreadsheet will be checked against the specified range.

Setting up template target column lists!

For the selected target column you will see a edit box to enter a list of values.
A typical list example for a string may be: 'Left', 'Right', 'Up', 'Down'.
A typical list example for a number may be: 1,2,3,4
The most important thing to remember is a list is separated by a comma (,).
The other thing to remember is when we have a list of strings, each value in list needs to be surrounded by single quotes (').
Once all of that is specified then you can save the target field specification.
During the data checking process if a list is specified then values in the spreadsheet will be checked against the specified list.

Setting up template target column transformation!

Setting up transformations is useful at final upload. For example, the production lot data tables requires data in kgs.
Often data comes in pounds. A transformation will be required when uploading the data.
You will see a little transformation window appear for a column which requires a transformation.
For selected target column enter a transformation. A transformation will need to be of the following format:
t = s * 0.453592
What the transform means is the target value (t) will be set to (=) the source value (s) times (*) 0.453592
If at upload time a transformation is specified the transformation will be performed then transformed data will be uploaded into the production data table.
The list of available operators you can use at this time are: * or + or - or /

Can we check if a template is setup properly and if so how do we do it?

As you work on a new template you can check to see if minimal requirements are met to be a valid template.
To do this you will see a little button under the "i" or information button. It looks like 2 blue boomerangs!
Click on it. On the right half of the screen you will see a screen pop out.
The top half of the screen lists the set of core fields in the target table. We need all of these fields to match up!
Here you will see if all core field names are matched. If not, you may have to work on your spreadsheet to get the appropriate data available.
On the bottom of the screen you see check for drug fields. We do not know how many fields will be available in a spreadsheet.
However, one thing we can check is to make sure you have at least 1 column designated as either a Trade Name or Active Ingredient field.
You will be able to determine it by the # of drugs listed = 0.
A couple of things to note, you do not have to open a selected template. Just select a group and a template and click on the check button!
The final note you can be working on the template and do a check at any time. I recommend this as you setup your template.
You can keep informed how you are doing with respect to the template setup.

Template is setup, now what?

Reminder a template is a list of columns which describes db strucutures for data we want loaded into the production database.
Each template represents one and only one drug route: bolus, feed or injectable.
A spreadsheet can be selected to be used for check if the data matches what we can use in production.
Templates are necessary when the final upload of data occurs into the production database.
Templating is how we provide a map from a sites spreadsheet of data into our standardized data tables.

The other things you should know!

Duplicating an existing template is important.
Adding a single template column to a template is important.
Deleting a single template column to a template is important.
Populating a template by overwriting the existing template is important.
Setting up transformations is important.