PROCESSOR: Error with Tree Species Audit View Affected Trees output

[lbross]

This button pops a table of trees associated with species codes missing from the Tree Species table. This is the query that generates the table:
SELECT DISTINCT b.fvs_tree_id,b.fvs_species AS FVS_SpCd,a.SpCd AS FIA_SpCd,a.*
FROM tree a,fvsouttreetemp2 b
WHERE MID(a.fvs_tree_id,1,2)='EC'
AND a.fvs_tree_id=b.fvs_tree_id AND a.biosum_cond_id=b.biosum_cond_id AND a.spcd=777 AND TRIM(b.fvs_species)='011'

I suspect the issue is the clause that is looking for the fvs_variant in the fvs_tree_id. The fvs_tree_id hasn't included the fvs_variant for several years. If I remove this clause, would that make the query work? I guess the question is if the fvs_variant needs to be included in the query? Can you have the same biosum_cond_id in > 1 variant?

If I can fix this by updating the query, I can get it into v5.11.2. If I need to find another way to get the fvs_variant into the query, than I propose hiding this button until after the conversion of tables in the master.mdb to SQLite.

[jsfried]

I concur with proposed solution and timeline (contingent on what is found)

[lbross]

Do you remember if a biosum_cond_id can exist in > 1 variant? I have a vague recollection that it CAN but not sure why. I don't know how to empirically answer this question. If we're not sure, the safest approach is to temporarily hide the button. Obviously it's not widely used since this is just surfacing now. There is also no reference to the button in the User Guide.

[jsfried]

It cannot happen. A biosum_cond_id links to a condition which links to a single plot. A plot can only be in one variant. Am also OK with deferring until after SQLite completion and hiding button for now. I have used it rarely-- it was helpful as a diagnostic when I needed it, so I recommend deferring not eliminating.

[lbross]

OK! Updating the query was easier than hiding the button. This will be in v5.11.2. I added an item on our to-do list to revisit this during the master.mdb conversion.

[lbross]

Notes to tester:

1. You may have to change the spcd value on a tree in the tree table so that you get a failure in the tree species audit unless you have a project that already fails.
2. The problem occurred in v5.11.1 when using the "View Affected Trees" button on the Processor tree audit screen. It never displayed any trees. May want to test in both v5.11.1 and v5.11.2 to see the difference.