Hi again John, even in this case, data are not mine, so i have to check which is the right report because they told me about this problem after inserting some data from a list with child list, so i supposed it was a problem related the presence of two taxa with same ID. how can i avoid the problem? how can i manage lists for future? is it a good thing to create lists without duplicated ID, without taxa in two or more lists? Thanks...

Hi Carlo

I haven't used child lists and I don't know if anyone else is using them. I don't really understand child lists and child taxa and have not seen any documentation describing them.

However, I don't think you can create two taxa with the same ID because a primary key constraint on the database would not allow it.

Are you able to answer the question from John about which report shows duplicates?

Jim Bacon.

Hi Carlo

Just because we don't know how to use child lists doesn't make them useless! I'm sure they are very useful.

If you look at the query being executed by the report, in the file reports/library/occurrences/occurrence_list.xml, you will realise that the first number is the occurrence id and you cannot have two occurrences with the same id so it must be the joins in the query causing the records to appear twice.

Creating a child list results in a new taxon list which shares some taxa with the parent list. In the taxa_taxon_lists table you will see something like the following:

id     | taxon_list_id | taxon_id | taxon_meaning_id | preferred | deleted
116495 | 101           | 116103   | 51416            | TRUE      | FALSE 
116496 | 101           | 116104   | 51417            | TRUE      | FALSE 
116497 | 101           | 116105   | 51418            | TRUE      | FALSE
118402 | 105           | 116103   | 51416            | TRUE      | FALSE

I have made list 105 as a child of 101 and it shares one taxon with id 116103

Now look at the join in the report

JOIN taxa_taxon_lists ttlpref ON ttlpref.taxon_meaning_id=ttl.taxon_meaning_id AND ttlpref.preferred='t' AND ttlpref.deleted=false

If ttl.taxon_meaning_id is 51416 then you can see two rows above meet the filter condition, hence the duplication.

Now what happens if you delete the child list? The taxon list is marked as deleted. However, because deletion is not cascaded, the records in the taxa_taxon_list table are unaffected. This is why you continue to have duplicate rows.

To reslove your problem you could run an update on the taxa_taxon_lists table and set deleted = true where the taxon_list_id = the id of your deleted child list. Alternatively you could improve the report query so that it handles child lists correctly.

Regards
Jim Bacon.