All,

When Biotics version 5.5 is pushed out to users later this week (scheduled for Wednesday night, Aug 20), one change is that it will require working list names within a working list type (e.g., element list, EO list, reference list, etc.) to be unique. In Biotics 4, under some circumstances it was possible to create two working lists with the same name, although you usually got a warning about it.  Therefore you may have lists with duplicate names.  Below is a query you can run to find out.

You have two options if this query reveals lists with the same name:

OPTION 1.  Do nothing!  During the upgrade to v.5.5, if dupes are found, the name of the most recently updated list will stay the same. The duplicate name(s) will have the list creator's username and rec_last_mod_date appended to the name.  Example

       Two EO lists named fish_EOs.  After the 5.5. upgrade, their names will be:

• fish_EOs
• fish_EOs_jsmith_2013-05-18

       If another EO working list with the same name was modified by jsmith on the same day, it would become fish_EOs_jsmith_2013-05-18_1

OPTION 2:  If the query finds duplicate names, change one of them.  You can change them before or after the upgrade, but the query only works if you run it BEFORE the upgrade.

Notes:

1. 1. Duplicates are only checked within the same working list type. So, if you have an EO list named temp_list and and Element list named temp_list, no problem.
2. 2. The check for duplicates is case-insensitive, so it will consider "FISH_LIST" a duplicate of "fish_list"
3. 3. The query returns two rows for each pair of duplicates (three rows for triplets, etc.) So if you get 8 result rows, that means 4 pairs of lists with the same name.
4. You can't see the user name of the list owner, only the ID. Contact me if you find duplicates and need to know who the lists belong to.
   

Here's the query.  Please submit a support ticket if you have any questions.

Donna

select a.working_list_type, a.working_list_name as listname_1, a.user_id as user1, a.rec_last_mod_date as mod_date1,
b.working_list_name as listname_2, b.user_id as user2, b.rec_last_mod_date as mod_date2
FROM working_list a, working_list b
WHERE a.working_list_name = b.working_list_name
and a.working_list_type = b.working_list_type
and a.working_list_id <> b.working_list_id
order by a.working_list_name

Just FYI, Query Builder didn't like the table security_users. But the query worked fine in SQL+.

I have modified the query, removing the security_users table. For security reasons, this table is not queryable (shouldn't be able to do so in SQL+, either, so that's interesting). Anyone who tried running the query and got an error, please try again with the new version.

Donna

 

Maine's all set.  Thanks Donna.