Production Lot Upload Data Preparation: Feedlot Health Management Services (FHMS)
Site Information Details
Feedlot Health Management Services (4) (FHMS)Vet: CSBV00001 (1)
Project Year: 2019
The site has eight (8) herds. The herds used for this project year are:
CSBH000001
CSBH000002
CSBH000003
CSBH000004
CSBH000005
CSBH000006
CSBH000007
CSBH000008
Access to current years documentation click on the appropriate link below:
FHMS 2020
FHMS 2021
FHMS 2022
FHMS 2023
Up to now we upload 1 year's data for 1 herd.
FHMS has a different data source structure.
All core production lot data, for all herds are in a single worksheet and no drug data is included.
In the appllication, for previous uploads, I would add a survey record to include the year and its herd.
At that point we could add the herd's core and associated drug data.
For FHMS data we need to make sure we have added all survey records for a year for all herds.
The upload process will upload data for all herds at once, not just one herd at a time.
You can check within the source worksheet of core field production lot data for available herds in the data.
To do this copy all the data in the column referring to the survey herd codes.
Next, in a new worksheet, paste the column of herd codes. There are many repeated herd codes.
To see just the unique herd codes, highlight the column of herd codes within the worksheet,
click on the menu-item Data, then click on button to "Remove Duplicates".
You will see the unique herd codes provided the site.
Production Lot Upload Data Preparation: Feedlot Health Management Services (FHMS)
Data Source Information
FHMS has 8 herds.Each spreadsheet within a workbook from FHMS represents all herds for a specific sampling year.
The core production lot fields resides in 1 worksheet within the workbook.
The drug information for bolus fields resides in a worksheet within the workbook.
The drug information for feed fields resides in a worksheet within the workbook.
The drug information for injectable fields resides in a worksheet within the workbook.
Because of this we need to upload all 4 worksheets before we attempt to integrate the data into production.
Each worksheet will be checked separately against its corresponding template.
Once checking is completed and all seems well, then we integrate the data.
All 4 uploaded datasets are necessary before the integrations step can take place within the application.
The core data when uploaded will be set to be uploaded as columns and drugs set to be mixed.
In actual fact there is no drug data in the core worksheet. It will upload in this format with no problem.
The drugs are listed as rows, not columns, within the sheet.
The drugs are set to its specific drug route when being uploaded i.e. Bolus, Feed or Injectable. No need to chose mixed!
Core field data has no drug data in the worksheet.
However, as mentioned above, all herds for FHMS for a specific year are available.
Because of this, make sure you have created a survey record for each herd for the year you are uploading and integrating.
If it is not done, then we have no way to associate the production lot data with a survey!
During the upload of the spreadsheet into the database, missing or implied core field data is filled in.
The rows of drug data represent 1 drug, usually a product name and active ingredient for 1 drug route.
All drug routes and associated drugs for a production lot are available within the three remaining drug worksheets.
There are several extra things you should do in the spreadsheet before uploading the data:
The season or "Quarter Closeout" field is available.
The seasons are defined in the following way:
Winter -> January 1 thru March 31
Spring -> April 1 thru June 30
Summer -> July 1 thru September 30
Fall -> October 1 thru December 31
Before creating and setting up a template, go thru all spreadsheets provided from the site.
Collect field names from each site and put them together. Review the field names to make sure they all
match. My experience is the field names do not match. To reduce the number of templates to create,
get the field names to match. The fields do not need to be in the same order.
The drug data to be uploaded requires a column to be added by the data preparation people.
The column added is "Total quantity of antimicrobial units" for the bolus, feed and injectable worksheets.
Each new column has a parent column we use as a guide for the values to insert into the appropriate worksheet.
For Bolus, the parent column is "Total quantity of antimicrobial used in mg".
In the new empty column inserted right beside this column add the value "mg" for all bolus records.
A similar process is used for the feed and injectable worksheets.
Production Lot Upload Data Preparation: Feedlot Health Management Services (FHMS)
Template Setup Suggestions
A four template setup is used for one or more years batch of data.For the current document, we assume place all known drug fields for each drug route resides in a separate specific drug data worksheet.
No data switches from core field columns to drug fields as rows for the setup of data for this site!
Four templates are created. The primary, top level template created represents the Core fields.
The secondary, second level child template created represents the Drug fields associated with a Core row of data.
One drug template is required for each drug route, you need to create separate templates for each drug route.
The Core Template is populated from the Target Table from the production lot table (core fields).
The Child Drug Template is populated from the Target Table from the drug table.
When setting up the core template, make sure a field is specified in the worksheet of core data
to match the csb_survey_main_id target field. In this way I can find the correct main id based on herd code.
When setting up each drug template, make sure a field is specified in the worksheet of drug data
to match the production lot code source field name. I use the unused field csb_survey_production_lot_drug_id target field.
In this way the production lot code field name is identified.
Here are the typical template settings used for FHMS.
Name: CVC 2019 - Core
Level: Top Level
Type: Core
Route: Multiple Drugs
Version: Version 1
By Row or Column: By Column
Method: Populate template fields from Target
|
||||||
|---|---|---|---|---|---|---|
| column_number | column_not_used | target_field_category | target_column_name | target_column_type | target_column_classify_field_setup | source_column_name |
| 1 | Not Used (1) | Core Field (0) | csb_survey_production_lot_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_id |
| 2 | Not Used (1) | Core Field (0) | csb_survey_main_id | bigint | Setup Standard Field (1) | feedlot_code |
| 3 | Not Used (1) | Core Field (0) | csb_record_group_id | bigint | Setup Standard Field (1) | csb_record_group_id |
| 4 | Not Used (1) | Core Field (0) | csb_account_id | bigint | Setup Standard Field (1) | csb_account_id |
| 5 | Not Used (1) | Core Field (0) | csb_survey_production_lot_upload_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_upload_id |
| 6 | Not Used (1) | Core Field (0) | csb_pl_template_id | integer | Setup Standard Field (1) | csb_pl_template_id |
| 7 | Used (0) | Core Field (0) | csb_pl_production_lot_code | varchar(128) | Setup Standard Field (1) | production_lot_code |
| 8 | Used (0) | Core Field (0) | csb_pl_sex_id | integer | Setup Lookup Field (2) | sex |
| 9 | Used (0) | Core Field (0) | csb_pl_animal_age_category_id | integer | Setup Lookup Field (2) | calf_or_yearling |
| 10 | Used (0) | Core Field (0) | csb_pl_percent_calves | numeric | Setup Standard Field (1) | age_mixed_pct_calves |
| 11 | Used (0) | Core Field (0) | csb_pl_percent_yearlings | numeric | Setup Standard Field (1) | age_mixed_pct_yearlings |
| 12 | Used (0) | Core Field (0) | csb_pl_origin_id | integer | Setup Lookup Field (2) | origin |
| 13 | Used (0) | Core Field (0) | csb_pl_brd_risk_id | integer | Setup Lookup Field (2) | brd_risk |
| 14 | Used (0) | Core Field (0) | csb_pl_n_animals_in | integer | Setup Standard Field (1) | animals_in |
| 15 | Used (0) | Core Field (0) | csb_pl_avg_date_in | date | Setup Standard Field (1) | average_date_in |
| 16 | Not Used (1) | Core Field (0) | csb_pl_source_weight_in_units_id | integer | Setup Lookup Field (2) | csb_pl_source_weight_in_units_id |
| 17 | Not Used (1) | Core Field (0) | csb_pl_source_weight_in_value | numeric | Setup Standard Field (1) | csb_pl_source_weight_in_value |
| 18 | Used (0) | Core Field (0) | csb_pl_avg_weight_in_kgs | numeric | Setup Multiple Target Fields (3) | average_weight_in_kg |
| 19 | Used (0) | Core Field (0) | csb_pl_avg_date_out | date | Setup Standard Field (1) | average_date_out |
| 20 | Used (0) | Core Field (0) | csb_pl_quarter_of_year_closeout_id | integer | Setup Lookup Field (2) | quarter_of_close_out |
| 21 | Not Used (1) | Core Field (0) | csb_pl_source_weight_out_units_id | integer | Setup Lookup Field (2) | csb_pl_source_weight_out_units_id |
| 22 | Not Used (1) | Core Field (0) | csb_pl_source_weight_out_value | numeric | Setup Standard Field (1) | csb_pl_source_weight_out_value |
| 23 | Used (0) | Core Field (0) | csb_pl_avg_weight_out_kgs | numeric | Setup Multiple Target Fields (3) | average_weight_out_kg |
| 24 | Used (0) | Core Field (0) | csb_pl_n_animals_dead | integer | Setup Standard Field (1) | animals_dead |
| 25 | Used (0) | Core Field (0) | csb_pl_n_animals_slaughtered | integer | Setup Standard Field (1) | animals_slaughtered |
| 26 | Used (0) | Core Field (0) | csb_pl_total_days_on_feed | numeric | Setup Standard Field (1) | average_days_on_feed |
| 27 | Used (0) | Core Field (0) | csb_pl_total_head_days | numeric | Setup Standard Field (1) | head_days |
Name: FHMS 2019 - Drugs
Level: Second Level
Type: Drug
Route: Bolus
Version: Version 1
By Row or Column: By Row
Method: Populate template fields from Target
| column_number | column_not_used | target_field_category | target_column_name | target_column_type | target_column_classify_field_setup | source_column_name |
| 1 | Not Used (1) | Core Field (0) | csb_survey_production_lot_drug_id | bigint | Setup Standard Field (1) | pen_id |
| 2 | Not Used (1) | Core Field (0) | csb_survey_production_lot_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_id |
| 3 | Not Used (1) | Core Field (0) | csb_survey_main_id | bigint | Setup Standard Field (1) | csb_survey_main_id |
| 4 | Not Used (1) | Core Field (0) | csb_record_group_id | bigint | Setup Standard Field (1) | csb_record_group_id |
| 5 | Not Used (1) | Core Field (0) | csb_account_id | bigint | Setup Standard Field (1) | csb_account_id |
| 6 | Not Used (1) | Core Field (0) | csb_survey_production_lot_upload_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_upload_id |
| 7 | Not Used (1) | Core Field (0) | csb_pl_template_id | integer | Setup Standard Field (1) | csb_pl_template_id |
| 8 | Not Used (1) | Core Field (0) | csb_pl_drug_ordinal | integer | Setup Standard Field (1) | csb_pl_drug_ordinal |
| 9 | Used (0) | Core Field (0) | csb_pl_drug_route_id | integer | Setup Lookup Field (2) | route_of_admin |
| 10 | Used (0) | Core Field (0) | csb_pl_source_drug_name | varchar(192) | Setup Standard Field (1) | active_ingredient |
| 11 | Used (0) | Core Field (0) | csb_pl_source_drug_value | numeric | Setup Standard Field (1) | total_quantity_of_antimicrobial_used_in_mg |
| 12 | Used (0) | Core Field (0) | csb_pl_source_drug_value_units_id | integer | Setup Lookup Field (2) | total_quantity_of_antimicrobial_units |
| 13 | Used (0) | Trade Name (2) | csb_pl_drug_trade_name_id | integer | Setup Standard Field (1) | product_name |
| 14 | Used (0) | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | integer | Setup Standard Field (1) | active_ingredient |
| 15 | Used (0) | Core Field (0) | csb_pl_drug_total_mg | numeric | Setup Standard Field (1) | total_quantity_of_antimicrobial_used_in_mg |
| 16 | Not Used (1) | Core Field (0) | csb_pl_drug_total_ml | numeric | Setup Standard Field (1) | csb_pl_drug_total_ml |
| 17 | Used (0) | Core Field (0) | csb_pl_drug_primary_reason_for_use | integer | Setup Lookup Field (2) | reason_for_use |
| 18 | Used (0) | Core Field (0) | csb_pl_drug_secondary_reason_respiratory | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 19 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_digestive | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 20 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_lameness | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 21 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_neurologic | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 22 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_mixed | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 23 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_mixed_description | varchar(128) | Setup Standard Field (1) | condition_antimicrobial_used_for |
Name: FHMS 2019 - Drugs
Level: Second Level
Type: Drug
Route: Feed
Version: Version 1
By Row or Column: By Row
Method: Populate template fields from Target
| column_number | column_not_used | target_field_category | target_column_name | target_column_type | target_column_classify_field_setup | source_column_name |
| 1 | Not Used (1) | Core Field (0) | csb_survey_production_lot_drug_id | bigint | Setup Standard Field (1) | lot_id |
| 2 | Not Used (1) | Core Field (0) | csb_survey_production_lot_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_id |
| 3 | Not Used (1) | Core Field (0) | csb_survey_main_id | bigint | Setup Standard Field (1) | csb_survey_main_id |
| 4 | Not Used (1) | Core Field (0) | csb_record_group_id | bigint | Setup Standard Field (1) | csb_record_group_id |
| 5 | Not Used (1) | Core Field (0) | csb_account_id | bigint | Setup Standard Field (1) | csb_account_id |
| 6 | Not Used (1) | Core Field (0) | csb_survey_production_lot_upload_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_upload_id |
| 7 | Not Used (1) | Core Field (0) | csb_pl_template_id | integer | Setup Standard Field (1) | csb_pl_template_id |
| 8 | Not Used (1) | Core Field (0) | csb_pl_drug_ordinal | integer | Setup Standard Field (1) | csb_pl_drug_ordinal |
| 9 | Used (0) | Core Field (0) | csb_pl_drug_route_id | integer | Setup Lookup Field (2) | route_of_admin |
| 10 | Used (0) | Core Field (0) | csb_pl_source_drug_name | varchar(192) | Setup Standard Field (1) | active_ingredient |
| 11 | Used (0) | Core Field (0) | csb_pl_source_drug_value | numeric | Setup Standard Field (1) | total_quantity_of_antimicrobial_ingredient_used_in_g |
| 12 | Used (0) | Core Field (0) | csb_pl_source_drug_value_units_id | integer | Setup Lookup Field (2) | total_quantity_of_antimicrobial_units |
| 13 | Not Used (1) | Trade Name (2) | csb_pl_drug_trade_name_id | integer | Setup Standard Field (1) | csb_pl_drug_trade_name_id |
| 14 | Used (0) | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | integer | Setup Standard Field (1) | active_ingredient |
| 15 | Used (0) | Core Field (0) | csb_pl_drug_total_mg | numeric | Setup Standard Field (1) | total_quantity_of_antimicrobial_ingredient_used_in_g |
| 16 | Not Used (1) | Core Field (0) | csb_pl_drug_total_ml | numeric | Setup Standard Field (1) | csb_pl_drug_total_ml |
| 17 | Used (0) | Core Field (0) | csb_pl_drug_primary_reason_for_use | integer | Setup Lookup Field (2) | reason_for_use |
| 18 | Used (0) | Core Field (0) | csb_pl_drug_secondary_reason_respiratory | integer | Setup Standard Field (1) | condition_antmicrobial_used_for |
| 19 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_digestive | integer | Setup Standard Field (1) | condition_antmicrobial_used_for |
| 20 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_lameness | integer | Setup Standard Field (1) | condition_antmicrobial_used_for |
| 21 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_neurologic | integer | Setup Standard Field (1) | condition_antmicrobial_used_for |
| 22 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_mixed | integer | Setup Standard Field (1) | condition_antmicrobial_used_for |
| 23 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_mixed_description | varchar(128) | Setup Standard Field (1) | condition_antmicrobial_used_for |
Name: FHMS 2019 - Drugs
Level: Second Level
Type: Drug
Route: Injectable
Version: Version 1
By Row or Column: By Row
Method: Populate template fields from Target
| column_number | column_not_used | target_field_category | target_column_name | target_column_type | target_column_classify_field_setup | source_column_name |
| 1 | Not Used (1) | Core Field (0) | csb_survey_production_lot_drug_id | bigint | Setup Standard Field (1) | lot_id |
| 2 | Not Used (1) | Core Field (0) | csb_survey_production_lot_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_id |
| 3 | Not Used (1) | Core Field (0) | csb_survey_main_id | bigint | Setup Standard Field (1) | csb_survey_main_id |
| 4 | Not Used (1) | Core Field (0) | csb_record_group_id | bigint | Setup Standard Field (1) | csb_record_group_id |
| 5 | Not Used (1) | Core Field (0) | csb_account_id | bigint | Setup Standard Field (1) | csb_account_id |
| 6 | Not Used (1) | Core Field (0) | csb_survey_production_lot_upload_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_upload_id |
| 7 | Not Used (1) | Core Field (0) | csb_pl_template_id | integer | Setup Standard Field (1) | csb_pl_template_id |
| 8 | Not Used (1) | Core Field (0) | csb_pl_drug_ordinal | integer | Setup Standard Field (1) | csb_pl_drug_ordinal |
| 9 | Used (0) | Core Field (0) | csb_pl_drug_route_id | integer | Setup Lookup Field (2) | route_of_admin |
| 10 | Used (0) | Core Field (0) | csb_pl_source_drug_name | varchar(192) | Setup Standard Field (1) | active_ingredient |
| 11 | Used (0) | Core Field (0) | csb_pl_source_drug_value | numeric | Setup Standard Field (1) | total_quantity_of_antimicrobial_used_in_ml |
| 12 | Used (0) | Core Field (0) | csb_pl_source_drug_value_units_id | integer | Setup Lookup Field (2) | total_quantity_of_antimicrobial_units |
| 13 | Not Used (1) | Trade Name (2) | csb_pl_drug_trade_name_id | integer | Setup Standard Field (1) | product_names |
| 14 | Used (0) | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | integer | Setup Standard Field (1) | active_ingredient |
| 15 | Not Used (1) | Core Field (0) | csb_pl_drug_total_mg | numeric | Setup Standard Field (1) | csb_pl_drug_total_mg |
| 16 | Used (0) | Core Field (0) | csb_pl_drug_total_ml | numeric | Setup Standard Field (1) | total_quantity_of_antimicrobial_used_in_ml |
| 17 | Used (0) | Core Field (0) | csb_pl_drug_primary_reason_for_use | integer | Setup Lookup Field (2) | reason_for_use |
| 18 | Used (0) | Core Field (0) | csb_pl_drug_secondary_reason_respiratory | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 19 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_digestive | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 20 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_lameness | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 21 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_neurologic | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 22 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_mixed | integer | Setup Standard Field (1) | condition_antimicrobial_used_for |
| 23 | Not Used (1) | Core Field (0) | csb_pl_drug_secondary_reason_mixed_description | varchar(128) | Setup Standard Field (1) | condition_antimicrobial_used_for |