AwardTracker: Handling Empty Surnames

[BowtieBob]

Environment

What version of MekHQ does your issue apply to? 0.47.17
What operating system are you using? Windows
What java version are you using? Java vendor AdoptOpenJDK 11.0.9.1

Description

While updating the AwardTracker with my campaign info, I get an error saying the 'date-type' column has duplicate entries. None of the dates/scenario names match.

Files

Attached zip contains my campaign file and award tracker file
AwardTracker.zip
customs.zip

[BowtieBob]

I noticed that the formula in my 'date-type' column is:
=_xlfn.CONCAT(TEXT([@Date],"yyyy-mm-dd"),[@type])

After looking up what _xfln means, apparently this is a formula that my version of Excel does note have (I am running Office 2016). If I unhide all of the sheets and replace "_xlfn.CONCAT" with "CONCATENATE" in the entire workbook, it seems to be working for me now.

[BowtieBob]

Ok, it partially works. My units aren't all showing up correctly:

[Windchild292]

Concat is apparently Excel 2019+. I'll swap that one over first, then look into the key issue.

Edit: where are you finding the use of a concatenate?

[Windchild292]

Your campaign loads fine for me

[BowtieBob]

I think it is loading correctly for me as well after fixing the CONCAT error. I was noticing that not all of my forces were showing in the list, but after looking into it more, it seems like it leaves out units with no kills.

[BowtieBob]

Concat is apparently Excel 2019+. I'll swap that one over first, then look into the key issue.

Edit: where are you finding the use of a concatenate?

I didn't find any Concatenates, I just replaced the concat with concatenate

Of course, it goes back to concat every time I refresh the data...

[BullseyeSmith]

This is the first time opening Slack in almost a year (got a new time-consuming job), and out of curiosity I search "award" to see if there has been any chat of my contributions back in 47.6, and low & behold @BowtieBob was talking about it just 3 hours ago!

1. Please keep in mind this was built as a concept example of what can be done within MHQ to help automate personnel decorations. My hope was that it would inspire master coders like @Windchild292 (you Da Man!) to hopefully add it to there hit list, or push me to get back to learning java and add it myself. That said, it was made as a patch, not a fully supported feature. These are elaborate tables to show the results of rolls. MHQ will eventually show you the rewards presented within the scenario & mission resolution phases based on these rolls in the background.
2. The _xlfn. in a formula does indeed mean you are using an outdated version of Excel. The Campaign Kills tab utilizes Power Query, Power Pivot, and Pivot Tables to convert the single .cpnx file into all the required tables and relations needed to display the data. The coloring of the cells to highlight the kill-based awards earned are the result of complex conditional formatting rules. These rules also use the latest formulas like CONCAT. It would take a lot of effort to make this backwards-compatible for older version of Excel, so I encourage you to upgrade your Microsoft Office Suite.
3. You observed correctly that the Campaign Kills tab only shows people that are/were part of a unit that received credit for a kill during a campaign. Not only will you not see people with no kills, but if you have shuffled around infantry members or vehicle crews to consolidate survivors for the next mission, you will see them loaded against the "(blank)" unit on the bottom of the sheet. This is good because everyone that took part in a kill should be credited for a kill. This is bad because you see 28 kills for a full infantry unit when there was only 1 enemy unit killed. Also, since it loads by battalion-company-lance-unit-personnel for the length of a campaign(s), whoever is currently part of the unit is credited with the kill. This will provide misleading data if you have swapped pilots or have a whole new vehicle crew. The patch for this is to use the AwardTracker immediately following a mission before rearranging crew members. Falling behind on paperwork leads to headaches. ;)
4. I see that surnames now display as "[Table]". This means that somewhere between 47.6 and now, the surname field became more than a single value. Probably something to do with tracking maiden names. I'll take a look to see if I can correct that.

[BowtieBob]

@BullseyeSmith I love the award tracker, it is much better than trying to count all the kills manually! I use Excel a LOT, and I've made some pretty elaborate spreadsheets, but this thing is impressive and I still haven't figured out how it actually works. Luckily, I am able to run it by swapping out the Concat formulas each time, so it's not that big of a deal. If it is problem to update the formulas to work with Concatenate instead, then I would recommend updating the AwardGuide.md to list Excel 2019 and newer as being required (it currently states 2016 and newer).

[Windchild292]

That was my bad, I'll swap it over to say 2019 (mistake on reading the version 😅)

As for surnames, this is a clan file so they would likely be a blank tag. Surnames can be blank, single word, or multi-word.

[BowtieBob]

In case you are interested, I made a macro enabled version of the AwardTracker that is compatible with Excel 2016. Basically, I just added a button that refreshes the data and replaces the CONCAT formulas with CONCATENATE. Since I am unable to update my version of Excel, this was a much better option that manually updating the formulas each time.

AwardTracker.zip

[BullseyeSmith]

@BowtieBob The conditional formatting within the pivot table also uses CONCAT. It uses it 11 times. If you try to change them to CONCATENATE, it will exceed the length limitation. You can change the first 9, but trying to change number 10 exceeds the limit. It only affects the mission awards, not the scenario awards, which is why you see the different shades of blue but not the other colors. Attached is what your Campaign Kills report looks like in Excel 365.

[BowtieBob]

I managed to update the formulas in the conditional formatting to remove the CONCAT errors, but for some reason it still isn't highlighting the rows correctly.

[BullseyeSmith]

It is probably because you only think you updated them. You can build the formula with CONCATENATE instead of CONCAT in a regular cell, and you can copy & paste that formula back into the conditional formatting, and it will let you hit Okay without giving any error message, but I bet a shiny nickel that if you go back to the conditional formatting formula, it reverted back to CONCAT because the additional 55 characters from adding the 11 "ENATE" pushed the conditional formatting beyond its max allowed length, and therefore didn't really save any change.

[BowtieBob]

Actually, I took out the CONCAT completely and replaced it with "&" between the variables. It evaluates correctly in a regular cell, and it isn't reverting to CONCAT in the conditional format. I need to play with it more though. I've never used conditional formatting with a pivot table before, so I might just be missing something.

[BowtieBob]

Ok, it looks like I got the conditional formatting working:

For some reason the conditional formats didn't like the formulas that were being used, so I went in and massively simplified them to get the same effect. Basically instead of selecting all of the values and adding them together, it just looks for the value in the subtotal column.
AwardTracker_xl2016.zip

I also have another version that only shows the latest campaign:
AwardTracker_xl2016_v2.zip