Latest update to merit badge list in my scouting tools is, once again, broken

My Scouting Tools
,
1

The newly updated “council merit badge counselor listing” is once again broken.

my.scouting.org | menu | (council) | roster > reports | council merit badge counselor listing | run > export to CSV

Anyone who has more than some limit of characters in their list of MBs gets split into multiple columns. They end up missing the first character in each of the additional columns. For example, I have a person who has the following in each of the columns:

FIRST COLUMN: Programming, Coin collecting, Camping, Backpacking, Automotive Maintenance, Collections, Cooking, Digital Technology, Disabilities Awareness, Dog Care, Drafting, Electronics, Emergency Preparedness, Engineering, Exploration, Fingerprinting, Fire Safety,

SECOND COLUMN: ishing, Gardening, Hiking, Home Repairs, Inventing, Metalwork, Model Design and Building, Pets, Photography, Pioneering, Plumbing, Robotics, Safety, Scouting Heritage, Signs, Signals, and Codes, Space Exploration, Surveying, Truck Transportation,

THIRD COLUMN: eather, Wilderness Survival, Wood Carving, Woodwork, Architecture

I’m not familiar with the “ishing” merit badge nor the “eather” merit badge. Presumably they might be (F)ishing and perhaps (W)eather?

Honestly I don’t understand why this is so complicated. It seems like every change to this report has brought with it new problems / bugs. Then we have to wait weeks for a fix which, itself, includes new and different bugs. I’m not trying to be mean, I’m just expressing the frustration of those trying to use this data.

Suggestions:

  1. Export all the badges in one field
  2. SORT the badges alphabetically
  3. Use a different separator other than comma (I suggest the vertical bar, |, but semicolon would be fine too) since we have badges with commas in the names (e.g. Signs, Signals, and Codes)
  4. Actually test stuff before you deploy it (I’ll happily volunteer to be a guinea pig for you)
2 Likes
2

James - you cannot.

There is a character limit in a CSV file in Excel. So, that’s why they had to be separated. Considering no one has reported this issue in the last month since the fix was deployed, I am guessing you are the first to run into it!

I will report the issue to BSA IT in the morning.

3

Ronald,

You absolutely, unquestionably, demonstrably CAN put the name of every merit badge (and much, much more) in a single field. You could put 10 copies of every merit badge in a field and be fine.

“Total number of characters that a cell can contain: 32,767 characters”

The limit for the number of characters in a field in Excel is 32K (yes, that’s true for CSV files as well). The total characters for the names of every merit badge combined is less than 2500 bytes (considerably less than the limit). Do they have a different excuse they’d like to offer?

I tested the limit myself. I created a CSV file (happy to share if you like) that has 2 fields. The first is the word ‘hello’ and the second is composed of blocks of 1000 of each of the characters a-z and A-F (a total of 32 distinct characters). Each block of 1000 letters is followed by a space. At the end is the word END. You can open this file in excel and it works just fine. That would be 32035 bytes which is well over 10 TIMEs more than necessary to hold the names of every merit badge.

If you happen to have perl installed, you can test this for yourself using the following.

perl -e "print 'hello,'; for ($i = 0; $i < 32; $i++) {print chr($i < 26 ? ord('a')+$i : ord('A')+$i-26) x 1000 . ' ';} print 'END';" > x.csv

Here is, for example, a CSV file that contains the names of every current merit badge (properly separated with vertical bars) all in one field. Feel free to load that in excel and test for yourself whether it works.

"All possible MBs","Camping | Citizenship in the Community | Citizenship in the Nation | Citizenship in the World | Communication | Cooking | Cycling | Emergency Preparedness | Environmental Science | Family Life | First Aid | Hiking | Lifesaving | Personal Fitness | Personal Management | Sustainability | Swimming | American Business | American Cultures | American Heritage | American Labor | Animal Science | Animation | Archaeology | Archery | Architecture | Art | Astronomy | Athletics | Automotive Maintenance | Aviation | Backpacking | Basketry | Bird Study | Bugling | Canoeing | Chemistry | Chess | Climbing | Coin Collecting | Collections | Composite Materials | Computers | Crime Prevention | Dentistry | Digital Technology | Disabilities Awareness | Dog Care | Drafting | Electricity | Electronics | Energy | Engineering | Entrepreneurship | Exploration | Farm Mechanics | Fingerprinting | Fire Safety | Fish and Wildlife Management | Fishing | Fly Fishing | Forestry | Game Design | Gardening | Genealogy | Geocaching | Geology | Golf | Graphic Arts | Home Repairs | Horsemanship | Indian Lore | Insect Study | Inventing | Journalism | Kayaking | Landscape Architecture | Law | Leatherwork | Mammal Study | Medicine | Metalwork | Mining in Society | Model Design and Building | Motorboating | Moviemaking | Music | Nature | Nuclear Science | Oceanography | Orienteering | Painting | Pets | Photography | Pioneering | Plant Science | Plumbing | Pottery | Programming | Public Health | Public Speaking | Pulp and Paper | Radio | Railroading | Reading | Reptile and Amphibian Study | Rifle Shooting | Robotics | Rowing | Safety | Salesmanship | Scholarship | Scouting Heritage | Scuba Diving | Sculpture | Search and Rescue | Shotgun Shooting | Signs, Signals, and Codes | Skating | Small Boat Sailing | Snow Sports | Soil and Water Conservation | Space Exploration | Sports | Stamp Collecting | Surveying | Textile | Theater | Traffic Safety | Truck Transportation | Veterinary Medicine | Water Sports | Weather | Welding | Whitewater | Wilderness Survival | Wood Carving | Woodwork"

2 Likes
4

Yup. Loaded fine for me. :^)

If you use CSV, you end up with two cells: All Possible MBs and the pipe-separated list of all the MB names.

I haven’t tried it in Google sheets yet, but I suspect it would work there, as well.

3 Likes
5

It is a limitation with the library supplied with the report writer software.

2 Likes
6

So they did have a different excuse. :slight_smile: This one at least makes some sense, although it’s still ridiculous. I say, drag them kicking and screaming into the 20th century (so they are only 20+ years behind)!

How quickly can they fix their “off by one” error so at least the badge names are complete? Or do we have to wait another month to find out which new bugs they introduce trying to fix this one?

Any chance they will use a reasonable separator (NOT! a comma) between badge names? It’s not the first time I’ve raised this issue.

I do, by the way, appreciate that they added the street address back into the report. It would be nice if they included all that in the release notes.

2 Likes
7

If I remember correctly, in a post about the plan to retire Scoutnet/PAS BSA was hoping that PAS would be tired by the end of this year (2021). This affects when Merit Badge Counselors’ reports.

I assume Merit Badge Counselor data is still being entered in Scoutnet/PAS, moved to Scoutnet/AKELA, then to Scoutbook. Advancement data input appears to be going in the opposite direction.

As a side note, I am seeing 2012 and 2017 posts on the internet where “ScoutNET” (note the capitulation) is used as the name of the Scouts BSA advancement database.

BSA’s plan for retiring the Peoplesoft software/database based applications in Scoutnet/PAS which include moving membership and merit badge counselor information (I assume to Scoutnet/AKELA) has been published online. There are other applications and data that need to be moved, for example:

I believe Peoplesoft was founded in 1987 and overtaken by Oracle. I have not been able to find what what “PAS” stands for.

Related forum discussion and blog articles:

8

  1. Dropping the first character in the second and following columns is strange.

  2. Breaking data into chunks may have been needed for database storage. See ChunkText function in

  1. There might be an issue related to Microsoff Excel (and similar CSV file readers), related to the non-use of double-quotes, see RFC 4180 paragraphs 5-7.

  1. I am wondering if BSA is having issues with transferring data between different types of databases.

  2. Since BSA said they fixed it once. If they had to revert to an erller version, we may have to wait for the fix to be put back in.

9

I assume you are using Excel which has a 256 character cell width limit. Thus a long CSV field would be divided into chunks when opened in Excel. This appears to be independent of creating a single wrapped cell in a PDF or a single long-field in a CSV file.

References:

10

That’s a typical “off by one” error. Programmers make that sort of “edge case” error all the time. Good programmers check their code for this type of error because it’s so common.

According to Ronald, it’s a bug in the library they use.

This has nothing to do with Excel. It’s not an Excel bug, it’s a BSA bug.

  1. Each field may or may not be enclosed in double quotes (however
    some programs, such as Microsoft Excel, do not use double quotes
    at all). If fields are not enclosed with double quotes, then
    double quotes may not appear inside the fields. For example:

    "aaa","bbb","ccc" CRLF
zzz,yyy,xxx

That’s completely antiquated horse manure. Excel handles quotes just fine in CSV files. For example, the sample CSV I posted above has quotes around both fields. Load that in Excel and see for yourself that it works.

They certainly don’t seem to be very capable at doing that.

I’d just like them to get it right once.

As I said above in my original post, I’d be happy to be a tester for them. Unfortunately rather than avail themselves of help (we call that “using your resources” in leadership training courses I’ve attended) they would prefer to play everything close to the vest and not use any free help. Hence, we’ll likely get yet another failed attempt at solving an incredibly simple issue … exporting the list of merit badges in a coherent and consistent way.

1 Like
11

No, it absolutely does not. Excel has a stated limit of 32767 characters per column / field (see discussion and links above). It works fine with data to this limit. I tested it myself.

As a demonstration I also created the CSV data with all the possible merit badge names posted above and loaded it into Excel as well. It works just fine. It appears as a SINGLE FIELD in Excel just as it should.

The is absolutely, unquestionable, decisively, demonstrably, most assuredly NOT AN EXCEL issue!

1 Like
12

I’m not sure what you’re getting at here. When I imported the CSV exemplar that @JamesBrown13 posted into Excel, it resulted in two cells of data, as I noted above.

I did not see any indication that the data had been divided in some manner. Setting the cell contents to “wrap” in Excel, everything appeared to be in the cell. Saving just the contents of the second cell (containing the MB names) as a text file, I was able to re-import it, treating the “pipes” as delimiters, and ended up with one MB name in each cell. That suggests that there was no loss of data. Maybe I’m misunderstanding what you’re describing?

2 Likes
13

Correct. Thanks Charley.

Yes, this works fine in Excel. As posted above, apparently this is a bug in the library they use. Plus, of course, their internal “off-by-one” error in terms of skipping the first character of the next block of MB names when they split the list.

1 Like
14

Just FYI … (in case anyone cares)

Based on what I see in the exported file I’m guessing that the broken library they use has an arbitrary limit of 255 characters per field. That’s a typical cutoff point in computer science (8 bit value).

I’m basing this on an entry with 65 MBs in 4 fields. The field character counts are: 245, 246, 253, 246.

In each case, the name of the following MB would have put them over 255 characters.

245 + Citizenship in the Nation
246 + Fish and Wildlife Management
253 + Sports

Updated to add: I found one with 256 characters in a field. That either means they have TWO “off by one” errors and there isn’t actually a limit (most likely), or the limit is actually 256 which is odd.

1 Like
15

Sorry to change the subject, but I don’t know of any person that is qualified to counsel/teach 30 subjects, much less than the 130 someone mentioned above. Unless we’re in a unit 250 miles from the nearest city, there should be no need authorize anyone for more than 10 subjects.
The Merit Badge system should have a red-flag requiring a district or council person click to override any number over 8 subjects. This reduces the tendency of a scout to get all MBs from one or two people.

16

Peter,

The Guide to Advancement allows an MBC to bring in a Subject Matter Expert to “teach” the Merit Badge while the MBC signs the Blue Card. I know of one MBC registered for over 80 MBs who does this frequently.

1 Like
17

And this is why the MBC position should be ended - trust the SMs to get the best experts for their units

18

Not all SMs have enough contacts to cover all of the MBs a Scout may want to work on. Having MBC lists available allows SMs to easily find people who can work with a Scout on any MB.

2 Likes
19

that is the theory “Yes” - it is not the reality as you well know

1 Like
20

Without the MBC list, I would have to tell my Scouts they can only work on about 30 of the 137 MBs. The list is important. I believe it is the restriction that SMs can’t find subject matter expert and sign off on MBs that you object to.

1 Like