New User Report Macintosh System - LONG! Part 1

[OogieM]

OK I did join NSIP and I want to report the problems, issues and comments esp, for the next Macintosh user who tries this.

First off I only joined after being assured here that I could provide data via spreadsheet. We won't re-hash how inadequate the NSIP web site is for letting people know there are alternatives to Pedigree Wizard on 32 bit PC systems.

After Mary verified my check I was initially told to go download the PW software. Had to re-iterate that I am a Mac or Linux only shop with no access to any PC systems.

Then I got the Excel Spreadsheet from the LP folks via the NSIP office.

Initially when I got it I saved it and made a copy so I could always get back to what was originally sent. Upon opening the spreadsheet I could see there were 4 panes or sheets, one for 2011 drop, one for 2010 drop 1 for 2009 drop and one sample. the year ones were empty, so I opened sample. Also empty. Looked at the font used and it's a font that is only in MS Office. I do not have MS software installed on my system so I don't have that font. I use Open office which is a free replacement for MS Office. OO can open, read and save Excel files. This is pretty normal for me when I get stuff sent from PC users. No problem, select all and change the font to one I do have on my machine. I can see the data entered but now the columns are not wide enough but again, that is pretty normal for me so I didn't worry about it. I can always look at any single cell to see what the data contains.

I currently keep all my sheep records in a spread sheet in a similar format. Each sheep has a row and all the data entry items are columns. My plan was to add all the extra NSIP columns I would need, populate it with the correct data, or copy it from fields I already keep and then when I needed to do a submittal, copy the data over to the official LP spreadsheet and submit it.

First big issue is creating all the NSIP ID numbers. I read the handout on changing LambPlan numbers (BBFFFFYYYYTTTTTT). We lose approximately 10% or more of our official federal scrapie tags a year. However my sheep are all registered with a unique 6 digit registration number from the American Black Welsh Mountain Sheep Association Registry, even my culls and butcher stock. That registration number never changes. Stillborn lambs also have a registration number that starts with D and then 6 digits. I verified that I could use my Registration numbers in the Lambplan ID in place of the 6 digit Tag number. Creating the numbers in the spreadsheet was interesting. I initially tried to create the main number then let the system increment by 1 (most of my registration numbers are in sequence). Unfortunately the numbers are too large and got changed to scientific notation. So I set the fields to text type and entered in all the numbers by hand.Then I cross referenced all the NSIP numbers with my paper registration papers to verify I had the sire and dam correct as well as the individual sheep. I really should re-verify this all again but I have not done that. I will before my next data run.

Then I added the columns for the rest of the NSIP data. When I started to populate the fields with my birth weight data I ran into another problem. The scale I use during lambing reads out in pounds and ounces. LP wants decimal pounds. I created columns to separate the pounds and ounces portion and a column that was the calculation of ((pounds * 16) + ounces)/16) to give me decimal pounds.

Our Association allows the recording of 30 day and 60 day weights. I have a lot of data but the days do not match LambPlan ranges. According to the Breeders Guidebook from LP the age ranges are:
Birth <24 hours
Weaning 40-120 days
Early Post-weaning 80-240 days
Post-weaning 160-340 days
Hogget 410-550 days
Adult 530-2315 days

But the spreadsheet I was sent had age classifications of
Birth data
3 Month Weaning
6 month early P Wean
9 month Post Weaning
12 month Yearling
18 Month Hogget
2 Years Old Adult

With no cross reference for the Yearling ages. I got that sorted an the correct set of days for the various ages is:
Birth <24 hours
Weaning 40-120 days
Early Post-weaning 80-240 days
Post-weaning 160-340 days
Yearling 290-430 days
Hogget 410-550 days
Adult 530-2315 days

Sadly this meant that a lot of my data is not going to be used because I had a lot of lambs recorded with 30 day weights that were weighed when they were between 25-35 days of age. That is too young for LP to use so I had to leave out those records.

For older weights I had to create columns in my spreadsheet that gave me the age in days for each weight taken and then if it fit one of the LP age ranges I entered the data into that column as well.

End part 1 too long to post

[OogieM]

Part 2


Pages 20-26 in the Breeders Guide gives most of the abbreviations. However there were a few I could not find

For sex they use numbers 1 and 2 but no info on what the mapping is between female and male to the numbers

CM was not explained, In the spreadsheet it's near the various scrotal measurements.

The spreadsheet did not have a place to enter in the lambing ease scores as described on page 25 of the Breeders Guide

I got the answers to those questions from the NSIP office

1 = ram 2 = ewe,
CM is conception method, Natural, AI or ET
I was told to add a column for LAMBEASE anywhere except the first column.

I typically keep a lambing score for each ewe and each pregnancy. But the way I do it is different from the wayLambPlan does it. LP wants each individual lamb to have a lambing ease score. In my system a 5 is a perfect lambing with no problems, the found dry and nursed lambs, 1 is terrible. For LP 1 is unassisted, 2 is mild pull, 3 is hard pull, 4 is vet assistance and 5 is malpresentation. I had to go back to my original paddock lambing books to find the data for each lamb as my scores are a combination of both lambs. So under the LP data a ewe who had twins, first no problems and the second with say, a leg back got 2 scores, first lamb got a 1 and second got a 5. Under my scoring system this ewe would have gotten a 2 or 3 depending on how hard it was to pull the second lamb after the leg was corrected. We didn't have any with vet assistance and I had to pull out my hard pulls to be those in correct presentation but that were stuck. In my sheep that is almost always a ram lamb with horn buds 1/4-1/2 inch long that gets stuck on the pelvis. Most of my lambing assists are malpresentations, (head or legs back or something similar) and so got scores of 5 for LP.

With those all recorded I moved on to some of the other fields. RT also took a bit as I had to verify that I had not grafted any additional lambs onto any ewes. I have very few bottle babies (total 12 in 13 years) so at least they were easy to deal with.

I thought the field Status was optional but it isn't I put everyone CURRENT except the 3 stillborn lambs.

In the sample data the group for stage fields are all set to 1 even though many of the sample sheep did not have data recorded for older ages past early Post Weaning. I did this and then was told that if I didn't have any weight or other data for the later ages I should leave the group for stage fields blank. One thing I am still confused about is grouping in general. In one place in the Breeders Manual they say that you should group by management but in another place they say that you can't combine groups later. So in my case I'd really like the first year data to be by the 2 main groups of lambs, early and late but I combine and then manage them the same after that and would like the grouping for yearling and hogget to be all the same. My understanding is that is not possible. so I believe I will have to now try to track the groups separately. I need more clarification on that.

Total data entry time for my flock that currently consists of 195 ewes, rams and lambs (including 3 stillborns) was 18 hours.

Then it came time to try to enter in the data into the LP spreadsheet.

I ran into many problems here.

If I go to to sheet for 2011 drop and try to paste in birth dates for my 2011 lambs the years are changed to be 2007 and the days are reduced by one. I verified that every year is 4 less than it should be and every day is one less than it should be. Consistent error across all dates for any other characteristics like weaning weight or adult weight. A birthdate of 2011 is entered as 2007, 2005 becomes 2001 and so on. The month is correct but the day is one behind so a birthdate of 5/8/2011 becomes 5/7/2007 This behavior is consistent across all of the sheets for data entry.

If I try to copy the various NSIP ID numbers that I have assigned per the rules they are all changed to be scientific notation and are therefore inaccurate. I had to make them text fields not numbers in my system to get them to display properly. I was not able to enter in any data into the LP spreadsheet field for ID at all.

When I pasted in the weaning weights they are totally messed up either showing a weight of 2454.8 or of 0.0 no matter what I do. If I try to enter in a correct weight by hand it is changed to be either 0.0 or blank when I press return.

I went back and forth with the NSIP office trying to figure this all out. I got a clean copy of the LP spreadsheet and tried all sorts of things but could not figure it out at all. I did discover that many of the fields and cells are protected and some have formula's in them and I suspect that is the issue but I gave up. What I finally did instead is enter in all my data into my main spreadsheep file I use for all flock records. Then I saved a copy of it under a different name. Went in to OO and deleted the columns that are not applicable to NSIP and save as Excel format for sending on to LP. This is the first Excel spreadsheet I've ever had problems with. I do work for other companies and they all use MS Office Word and Excel and we transfer files, make changes and do edits back and forth all the time. Other than the normal issues of fonts not working and size of font issues I've never had a problem with the sort of pasting errors I saw with the LP one and I do a lot of conversions from MS stuff to Open Office back and forth for my computer consulting clients.

The file I created from scratch was again looked at by NSIP then I e-mailed it off to LP in Australia. I have not heard back whether they got it run on yesterday's run or not, I hope so. I got it sent to them on their Sunday afternoon.

So far total time spent is up to 24 hours. I am anxious to see what I get back from this first run of data.

[Judy Lewman]

. . . Looked at the font used and it's a font that is only in MS Office. I do not have MS software installed on my system so I don't have that font. I use Open office which is a free replacement for MS Office. OO can open, read and save Excel files. . .

Good heavens, Oogie! Compared to you, I'm a computer idiot. But I've submitted 14 years of records to NSIP/LambPlan with our Mac and not experienced any of the data entry problems you describe . . . the spreadsheet's refusal to accept your dates as entered (noted in Part 2) is especially bizzare. I have no experience with Open Office. We're still running Snow Leopard OS and use Numbers from iWorks '08 to open, read, edit, convert, save, and share Excel files with PC users. No problems at all using the template received from LambPlan . . . all the fonts are right there, etc., etc.

I've been impressed with LambPlan's fast turnaround (expecting our 4th report soon) as well as Stephen's patience with the few newbie errors we've made. Here's hoping that things begin to smooth out for you going forward.

[OogieM]

[the spreadsheet's refusal to accept your dates as entered (noted in Part 2) is especially bizzare.

Yes I know. I also am on Snow Leopard.

As I said this is the only Excel spreadsheet I've ever had any problems with and they are baffling.

[Judy Lewman]

Oogie, if you'd like to send me the template you received from LambPlan I'd be happy to try and make it misbehave as you've described. Another option would be for me to send you the template that I received several months back. Perhaps it's been modified in some fashion to make it so unmanageable???

[OogieM]

Sent just now and yes, I'd love to see the one you got.

[OogieM]

Attached are several images showing what I see on my system
This is a screenshot showing the LambPlan spreadsheet and my own one I saved in excel format
http://flic.kr/p/adDbgq

This one shows what happens when I copy the first birthdate on my list and paste into the sample data for LambPlan
http://flic.kr/p/adDbiu

This one shows what got pasted when I make the column wide enough to actually see the data
http://flic.kr/p/adDbk7

[Judy Lewman]

Must be gremlins, Oogie

The LambPlan template you sent this afternoon works like a charm on this end. Updated spreadsheets on the way.

[OogieM]

The other spreadsheet you sent also has the same failure mode for me. I can't open the numbers version you sent back, the excel version looks the same as what I sent in except a bunch of the columns are missing. They were hidden in teh one Isent you so unless you expanded it and copied those you missed the data.

Anyway, as I said I did find a way to do it, I saved a copy of mine as a new file name, deleted the columns that don't apply to NSIP and saved as excel format.

[Judy Lewman]

OK, Oogie, since things are still sticky on your end let’s let this ride for now. Thankfully you managed to get your records submitted to LambPlan in spite of all the headaches. I’ll send a PDF later today with screen shots of your data arranged in a format that you may want to consider using going forward. Not to imply that my way is the only way, but it’s working. As mentioned earlier, I’ve just submitted data for my 4th LambPlan evaluation and they haven’t booted me out of NSIP yet

A few suggestions that may be helpful for others also working on a Mac should they experience difficulties using the template from LambPlan:

- If the order in which columns appear on a self-generated Excel spreadsheet are arranged to match LambPlan’s template, it will save time for Stephen as he can then drop in all of your data at once rather than pasting each column separately. For this reason, I'd insert any columns you wish to include for additional traits (Lambing Ease, etc.) to the right of those in which you've entered other data.

- Use a separate tab/sheet in your spreadsheet file for each year, i.e. all animals born in the same year on a sheet titled “2011 Drop,” etc.

- Be sure to note the date weighed for each weight entered (those columns titled WDDYY, EPDDMM, etc.).

- Go ahead and enter all lambs born for previous years if you have the data (NSIP/LambPlan does not charge you for this). The more history you can provide the better as it helps in building pedigrees and will, over time, increase the accuracies of your EBVs/FBVs.