Species code generation in Template generator not producing 3 letter code as expected

[atcooper1]

Code seems to be generating 4 and 5 letter codes for a species (when the 3 letter code is available).
Species codes are not static, they are generated based on Location/country/area/site subset and abundance.
Species codes for the templates should be based on the 1st letter of the Genus and first 2 letters of the Species Name of an individual species. However, where there are duplications, the 3 letter code should be assigned to the most abundant species (for that particular sub-setted region) and a 4 letter code (i.e. First letter of genus and first 3 letters of species) assigned to the lesser abundant species. If there are still duplications with the 4 letter codes, then the same rule is applied where the more abundant species (from that sub-setted region) is assigned the 4 letter code and the lesser abundant species is given the 5 letter code (i.e. first letter of Genus and first 4 letters of species).

[bpasquer]

I could not reproduce an example of 4 or 5 letter code generated when 3 letter code was available but had only checked a limited umber of templates
@atcooper1 could you send me an example template pls.
However, I think that the attribution of the letter code fails at taking the abundance in the area into account.

[atcooper1]

Here is a list of all species from a NSW template that could have been assigned a 3 letter code, but weren't. Also attached is the original template generator code that orders by abundance (based on the area/locations/countries selected) and then assigned codes. Happy to site down and go through this code to ensure the same rules are applied if needed?
Cryptic fish examples.xlsx
template generator SQL code.docx

[bpasquer]

@atcooper1 I think you attached the wrong xlsx file. This one is the list of missing species provided by James

[atcooper1]

3 letter code example error.xlsx
Apologies, try this file

[bpasquer]

After investigation it appears that the problem is due to the filtering on method that happens after the query to list species in the area and their abundances is executed.As a result it generates letter codes for a much larger set of species than for a single method, thus allocating a large number of 4/5 letter codes.

[LizziOh]

I've test a template export for the location of Lord Howe Island and the area Tasmania. The code generator seem much better to me now. there are a few codes taht seem odd, for example in the attached excel 3 fish are highlighted yellow have for letter codes when the 3 letter version of the code seems available from the list.... eg. Ostorhinchus norfolcensis could have been "ono" instead or "onor". Any clues on this?
m1speciesLHI.xlsx