Returning legacy accidentally pilfered barcodes to the correct institution.

[KyndallH]

Error Text

Copy the text from the page and paste it here It will not let me copy and paste. Screenshot:

Where it happened

Provide a link to or a description of the page where the error occurred.

https://arctos.database.museum/containerPositions.cfm?container_id=758233

I'm pretty sure it is because MSB scanned my barcodes into their box by accident, and now I can't fix it because we made it so people can't accidentally move other institutions' barcodes.

Steps to get there

Describe the actions that led to the error

Locate one of your tissues in a MSB freezer. Email them to let them know you are taking your barcode back. Scan the barcode back into your box. Get error message.

Problem

Community response to describe the problem that caused the error

Solution

Community response with directions for how to correct the problem

No clue. I want to keep people from moving my tissues, but I need to be able to fix legacy problems.

[campmlc]

I think @dusty will have to fix this one. Alternately, we can set time for a call next week, and you can temporarily give me UAM privileges or vice versa.

[KyndallH]

I'll hit you up later this week with the barcodes I find that need to be returned.

I just worry that you have barcodes that are the same - as in Teresa scanned a 225013 into your freezer box, but we also have a 225013.

[Jegelewicz]

I just worry that you have barcodes that are the same

That "dump box" is where I put barcodes for which there was no matching physical container. If you actually find that box (it used to be on the bookshelf in the student work room next to @campmlc office), it won't have anything in it and the box itself is sitting out in the universe - anything in there would be toast by now anyway...

[campmlc]

Our values are NK numbers. We have scannable labels that include the NK number, tissue type, and then the actual barcode is a separate label. But before restrictions were added, my students sometimes scanned the NK label instead of the barcode label, which is how this happened. Our barcodes are base 36 alphanumeric values with no meaning so we can avoid this.

[campmlc]

So your barcode series precisely overlaps our NK series, unfortunately. With the restrictions in place, this shouldn't happen going forward, but we need to find and fix any legacy ones, which means we'll need to give each other access.

[dustymc]

but we also have a 225013.

There can be only one, it's claimed by UAM, https://arctos.database.museum/EditContainer.cfm?container_id=2708714 is a mess - someone's done something horrible to a former position???!!

And that contains a UAM part....

https://arctos.database.museum/info/barcodeseries.cfm "all integers (above 1)" certainly covers 225013

[campmlc]

The barcode 225013 says it's MSB, but it should be UAM. I don't have access to restore it to UAM. All barcodes that are solely numeric in that series should belong to UAM. If these can all be restored to UAM ownership in bulk, then maybe Kyndall can move them where they need to go?

On Mon, Jan 9, 2023, 4:56 PM dustymc @.***> wrote:

• [EXTERNAL]*

but we also have a 225013.

There can be only one, it's claimed by UAM, https://arctos.database.museum/EditContainer.cfm?container_id=2708714 is a mess - someone's done something horrible to a former position???!!

And that contains a UAM part....

https://arctos.database.museum/info/barcodeseries.cfm "all integers (above 1)" certainly covers 225013

— Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/5452#issuecomment-1376509389, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADQ7JBFGN7Z44SPR4JWSM53WRSQRPANCNFSM6AAAAAATTPS7PE . You are receiving this because you were mentioned.Message ID: @.***>

[dustymc]

barcodes that are solely numeric

 create table temp_stolen_bc as select barcode,institution_acronym from container where is_number(barcode)=1 and institution_acronym!='UAM';
SELECT 129

temp_stolen_bc.csv.zip

[campmlc]

Can you convert them all to UAM so Kyndall can move them? Is that possible if they are scanned into MSB parents? I'll need to figure out the corresponding MSB barcodes for those NKs and figure out what is actually supposed to be in those positions.

On Mon, Jan 9, 2023, 7:10 PM dustymc @.***> wrote:

• [EXTERNAL]*

barcodes that are solely numeric

create table temp_stolen_bc as select barcode,institution_acronym from container where is_number(barcode)=1 and institution_acronym!='UAM'; SELECT 129

temp_stolen_bc.csv.zip https://github.com/ArctosDB/arctos/files/10378344/temp_stolen_bc.csv.zip

— Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/5452#issuecomment-1376627926, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADQ7JBBOWXGNG5GGVE7FBRLWRTAJ7ANCNFSM6AAAAAATTPS7PE . You are receiving this because you were mentioned.Message ID: @.***>

[dustymc]

Can you convert them

I can, but I'd rather hear from NMU, KNWR, MVZ, and whoever I didn't notice first.

scanned into MSB parents

And that - this needs some kind of actual full-stack solution before I get the scary password out.

corresponding MSB barcodes

And maybe that - I'd prefer not to make any messes messier by just yoinking thing out of the middle, assuming @KyndallH isn't in some great hurry to get these back.

[dustymc]

I have done everything I can, there's no safe anything I can do with the containers which have parents and/or children, attached here.

temp_stolen_bc(1).csv.zip

@campmlc @jldunnum these are all MSB please help.

@DerekSikes some of these claim to be yours.

@KyndallH please let me know if I can do something else.

[Jegelewicz]

This is only becoming more important as I bring new users into object tracking - what can we do to get them cleaned up - this seems like a very urgent issue?

[jldunnum]

Looking into what we can do with @campmlc

[campmlc]

Kyndall is no longer at UAM, and we need to rope her replacement in - does she have a Github account?

First, this is a good example of why we need the requested tool to generate a csv file of part locations and associated collection objects for a random list of barcodes - the only tool I can use to generate see what/where these things are is in Find Container: In Barcode - and I can only do a screenshot or Control P to print pdf (attached). Flatten Part Locations only shows me the three MSB:Bird collection objects, and leaves out any barcode that doesn't have a part.

My understanding of the background:
1) First, this is all legacy from over 5 years ago, so it shouldn't be happening now to cause a problem for incoming collections. If it is, there is something wrong with the checks that were put in place at the time.

2) All MSB barcodes are alphanumeric. But our NK field numbers printed on our cryovial labels also have a scannable barcode version that is used to enter data into our Excel inventory files, and this series of numeric values just happens to overlap with some of the UAM Arctos object tracking barcode series. (A good reason to not use purely numeric barcode series.)

3) The only time I've observed this to happen previously was over 5 years ago when a student accidentally scanned an NK number on the label instead of the MSB object tracking barcode - and I thought we caught that issue quickly (happened on first time scanning by a new grad student). Then Dusty put in constraints so that it couldn't happen again. So I really don't understand how these UAM barcodes came to be associated with MSB specimens, especially since the NK number is not the same as the UAM barcode for these.

Posssible solutions? 1) For the barcode 118463, this should just be restored to UAM and not MSB - looks like everything is where it is supposed to be? Verify with UAM collection manager?

2) For the MSB Bird ones, I can go find the actual barcodes on these and move the collection objects to the correct or anew MSB barcode, which will free up the UAM values so they can be restored to UAM control. But I'm not sure how to go back and figure out what UAM collection object they are supposed to be associated with. Can we track history on these? I assume this needs to be done then by someone at UAM?

3) For the Teresa dump box and MSB unknown ones, likely these were just NK numbes that were scanned by accident? So @dusty can you use the History for these barcodes and put them back where they are supposed to be, and change ownership back to UAM? Actually, I just checked History, and I see a UAM scan but no part location. However, since UAM has their barcodes archived in sequential numeric order, it should be easy to figure out where these need to go - for example, I would assume that UAM barcode 226803 will be the next slot after 226802 in the UAM collection.

Other ideas?

Find Containers.pdf

[megulbranson]

I am reviewing this thread to get up-to-speed on this issue.

1. For the barcode 118463, this should just be restored to UAM and not MSB - looks like everything is where it is supposed to be? Verify with UAM collection manager?

I was able to locate barcode 118463. It is in the correct location and should be restored to UAM.

[campmlc]

2. For the MSB Bird ones, I can go find the actual barcodes on these and move the collection objects to the correct or anew MSB barcode, which will free up the UAM values so they can be restored to UAM control. But I'm not sure how to go back and figure out what UAM collection object they are supposed to be associated with.

I've done this, and guess what - these actually are three vials of bird tissue with UAM barcodes. I've emailed our curator to ask what the situation is.

[campmlc]

3. For the Teresa dump box and MSB unknown ones, likely these were just NK numbes that were scanned by accident? So @dusty can you use the History for these barcodes and put them back where they are supposed to be, and change ownership back to UAM? Actually, I just checked History, and I see a UAM scan but no part location. However, since UAM has their barcodes archived in sequential numeric order, it should be easy to figure out where these need to go - for example, I would assume that UAM barcode 226803 will be the next slot after 226802 in the UAM collection.

I have checked these by searching on the corresponding MSB NK numbers, and all the MSB tissues are accounted for an in position in collection. All associated parts except last two are UAM, and the last two don't seem to have any parts associated. These need to be restored to UAM ownership, and hopefully @megulbranson can then scan them back into where they belong at UAM?

[megulbranson]

3. However, since UAM has their barcodes archived in sequential numeric order, it should be easy to figure out where these need to go

So far this is true. I found 225013 and 225029 in box C49216, in sequential order in relation to the rest of the vials in that box. If these could be assigned back to UAM I will be able to scan them back into their location.

[campmlc]

From what I can see in the history, these were all scanned into MSB in Dec Jan 2014-15.

[campmlc]

OK, I've re-barcoded the three MSB:Bird samples that did indeed have UAM barcodes. I used the scary red box to replace the barocdes, which means that UAM barcodes 150250, 150253, and 150255 no longer exist - which is OK because the samples they were applied to are actually MSB Bird parts from a collaborative project ("these are msb’s samples, collected in collab with mccracken when he was at uaf") per our curator. All the others in this list need to be restored to UAM. 150251,150252,150256,150257,150258,230530,231443,118463,226795,226803,225013,225029,240489,240492 @dustymc @megulbranson @Jegelewicz

[megulbranson]

Thank you @campmlc!

[dustymc]

@megulbranson let me know specifics (CSV is nice) if you want something updated, giving you back your containers while they're deep in MSB territory probably won't do you much good!

[megulbranson]

Okay thanks @dustymc, I will review the list and send you a CSV. I know some of them will be easily located in our freezers but I haven't checked the whole list yet.

[megulbranson]

All the others in this list need to be restored to UAM. 150251,150252,150256,150257,150258,230530,231443,118463,226795,226803,225013,225029,240489,240492

@dustymc could you restore all of these to UAM? I was able to find 226795, 226803, 225013, and 225029 in our freezers and am ready to scan them into their current locations. The others I am still looking for but knowing what specimens are associated with them or seeing the object tracking history may be helpful, and right now I can't see anything about these containers on Arctos. Thanks!

[dustymc]

@megulbranson for those in containers you don't have access to, changing institution is not going to make them available to you. I think I'll need to give them to you and update them to be in a UAM container (eg barcode 0) at the same time.

Here's container data and current path.

select
container.*,
getContainerParentage(container_id)
from container where barcode in ('150251','150252','150256','150257','150258','230530','231443','118463','226795','226803','225013','225029','240489','240492');

 container_id | parent_container_id | container_type |          label           | description |         last_date          |          container_remarks          | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units |                                                                                                                                                                                        getcontainerparentage                                                                                                                                                                                        
--------------+---------------------+----------------+--------------------------+-------------+----------------------------+-------------------------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       144335 |            15768966 | cryovial       | 118463                   |             | 2023-10-30 18:14:41.343979 |                                     | 118463  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ LN2FRZR4 ] Nitrogen Freezer 4 (freezer):[ LN24E1 ] LN2 Frzr4 E1 (freezer position):[ C58391 ] C58391 (freezer rack):[ C58651 ] 1st slot (freezer rack position):[ C57064 ] C57064 (freezer box):[  ] 24 (position):[ 118463 ] 118463 (cryovial)
      2630335 |            16237778 | cryovial       | NK 168884 tissue in buff |             | 2023-10-30 18:14:41.343979 | EIMI two-piece labels, 30 Nov 2007. | 150251  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSB ] Museum of Southwestern Biology (institution):[ DGR ] Genomic Resources (collection):[ DGRCERIA324 ] 324 Cryo Room (room):[ DGRC05 ] CRYO-05 (cryotank):[ DGRC5H153 ] CRYO-5-H153 (freezer rack):[  ] 3 (position):[ DGR12825 ] DGR12825 (freezer box):[  ] 18 (position):[ 150251 ] NK 168884 tissue in buff (cryovial)
      2630336 |            16237795 | cryovial       | NK 168895 tissue in buff |             | 2023-10-30 18:14:41.343979 | EIMI two-piece labels, 30 Nov 2007. | 150252  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSB ] Museum of Southwestern Biology (institution):[ DGR ] Genomic Resources (collection):[ DGRCERIA324 ] 324 Cryo Room (room):[ DGRC05 ] CRYO-05 (cryotank):[ DGRC5H153 ] CRYO-5-H153 (freezer rack):[  ] 3 (position):[ DGR12825 ] DGR12825 (freezer box):[  ] 35 (position):[ 150252 ] NK 168895 tissue in buff (cryovial)
      2630340 |            16148820 | cryovial       | NK 172665 RBC            |             | 2023-10-30 18:14:41.343979 | EIMI two-piece labels, 30 Nov 2007. | 150256  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSB ] Museum of Southwestern Biology (institution):[ DGR ] Genomic Resources (collection):[ DGRCERIA324 ] 324 Cryo Room (room):[ DGRC05 ] CRYO-05 (cryotank):[ DGRC5H140 ] CRYO-5-H140 (freezer rack):[  ] 1 (position):[ DGR17598 ] DGR17598 (freezer box):[  ] 7 (position):[ 150256 ] NK 172665 RBC (cryovial)
      2630341 |            16148996 | cryovial       | NK 172665 RBC            |             | 2023-10-30 18:14:41.343979 | EIMI two-piece labels, 30 Nov 2007. | 150257  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSB ] Museum of Southwestern Biology (institution):[ DGR ] Genomic Resources (collection):[ DGRCERIA324 ] 324 Cryo Room (room):[ DGRC05 ] CRYO-05 (cryotank):[ DGRC5H140 ] CRYO-5-H140 (freezer rack):[  ] 2 (position):[ DGR17599 ] DGR17599 (freezer box):[  ] 83 (position):[ 150257 ] NK 172665 RBC (cryovial)
      2630342 |            16155110 | cryovial       | NK 172671 rojos          |             | 2023-10-30 18:14:41.343979 | EIMI two-piece labels, 30 Nov 2007. | 150258  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSB ] Museum of Southwestern Biology (institution):[ DGR ] Genomic Resources (collection):[ DGRCERIA324 ] 324 Cryo Room (room):[ DGRC05 ] CRYO-05 (cryotank):[ DGRC5H145 ] CRYO-5-H145 (freezer rack):[  ] 1 (position):[ DGR15658 ] DGR15658 (freezer box):[  ] 25 (position):[ 150258 ] NK 172671 rojos (cryovial)
      2708714 |            19420682 | cryovial       | 225013                   |             | 2023-10-30 18:14:41.343979 | Labels for tube tops, DataMatrix.   | 225013  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ DGR14149 ] Teresa Dump Box (freezer box):[  ] 7 (position):[ 225013 ] 225013 (cryovial)
      2708730 |            19420681 | cryovial       | 225029                   |             | 2023-10-30 18:14:41.343979 | Labels for tube tops, DataMatrix.   | 225029  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ DGR14149 ] Teresa Dump Box (freezer box):[  ] 6 (position):[ 225029 ] 225029 (cryovial)
      2710496 |            19420684 | cryovial       | 226795                   |             | 2023-10-30 18:14:41.343979 | Labels for tube tops, DataMatrix.   | 226795  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ DGR14149 ] Teresa Dump Box (freezer box):[  ] 9 (position):[ 226795 ] 226795 (cryovial)
      2710504 |            19420685 | cryovial       | 226803                   |             | 2023-10-30 18:14:41.343979 | Labels for tube tops, DataMatrix.   | 226803  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ DGR14149 ] Teresa Dump Box (freezer box):[  ] 10 (position):[ 226803 ] 226803 (cryovial)
      2714231 |            12558123 | cryovial       | 230530                   |             | 2023-10-30 18:14:41.343979 | Labels for tube tops, DataMatrix.   | 230530  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ LN2FRZR3 ] Nitrogen Freezer 3 (freezer):[ LN23F1 ] LN2 Frzr3 F-1 (freezer position):[ C54079 ] C54079 (freezer rack):[ C54393 ] 12th slot (freezer rack position):[ C39106 ] C39106 (freezer box):[  ] 57 (position):[ 230530 ] 230530 (cryovial)
      2715144 |            19420680 | cryovial       | 231443                   |             | 2023-10-30 18:14:41.343979 | Labels for tube tops, DataMatrix.   | 231443  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ DGR14149 ] Teresa Dump Box (freezer box):[  ] 5 (position):[ 231443 ] 231443 (cryovial)
      2850779 |            19783828 | cryovial       | 240489                   |             | 2023-10-30 18:14:41.343979 |                                     | 240489  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSBUNKNOWN ] MSB UNKNOWN (unknown):[ 240489 ] 240489 (cryovial)
      2850782 |            19783828 | cryovial       | 240492                   |             | 2023-10-30 18:14:41.343979 |                                     | 240492  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ MSBUNKNOWN ] MSB UNKNOWN (unknown):[ 240492 ] 240492 (cryovial)
(14 rows)

Here are the contents, these are also going to cause problems:

select
container.*
from container 
inner join container parent on container.parent_container_id=parent.container_id where parent.barcode in ('150251','150252','150256','150257','150258','230530','231443','118463','226795','226803','225013','225029','240489','240492');

 container_id | parent_container_id |  container_type   |           label            | description |      last_date      | container_remarks | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units 
--------------+---------------------+-------------------+----------------------------+-------------+---------------------+-------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------
      2608633 |              144335 | collection object | UAM Mammals 100025 tissues |             | 2007-09-19 11:08:45 |                   |         |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 | 
       350487 |             2708714 | collection object | UAM Mammals 42337 liver    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
        12868 |             2708730 | collection object | UAM Mammals 43399 tissues  |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
       349921 |             2710496 | collection object | UAM Mammals 43006 liver    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
       349929 |             2710504 | collection object | UAM Mammals 42682 liver    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
     12985158 |             2850779 | collection object | 240489                     |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
     12948961 |             2850782 | collection object | 240492                     |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 

and history

create table temp_lost_hist as 
select
container_history.*
from container_history 
inner join container  on container.container_id=container_history.container_id where container.barcode in ('150251','150252','150256','150257','150258','230530','231443','118463','226795','226803','225013','225029','240489','240492');

temp_lost_hist.csv

Do note that the reason you can't see these because there's now a VPD on containers, which also means the unique key is (barcode, institution) - it may be simpler for you to just consider this (probably not including the UAM part in MSB containers - that does need recovered) an irreconcilable mess and create new containers (or that may be a terrible idea, I don't know!).

Let me know what I can do, I'm just trying to step very carefully so I don't somehow make this worse.

[campmlc]

Looks like all are scanned into MSB except one scanned into MVZ. These are all legitimately UAM barcode values which share NK number barcodes with MSB. Since the UAM collection is physically organized numerically based on these barcodes, I do not want to force UAM to assign new barcodes which would cause their samples to be physically as well as virtually out of order.

Can the position history stack be reverted to their UAM location, accommodating whatever permissions needed to make that happen? That would be ideal. We now have the lost_history csv to track these down. Once that is done, and the UAM vials are removed from MSB positions, I can scan the correct MSB barcode into the same MSB position. But I can't do that until the UAM barcode is removed. I assume the same situation applies to the MVZ vial, but that should be confirmed.

[campmlc]

How recently was this temp_lost_hist file generated? I'm having trouble interpreting it. For example, the most recent scan date for barcode 226803 shows it as being in the MSB location" [ DGR14149 ] Teresa Dump Box (freezer box):[ ] 10 (position):[ 226803 ] 226803 (cryovial)" - but I do not see it there when I look at box DGR14149. Is that because permissions were changed, but the positions were not?

[megulbranson]

How recently was this temp_lost_hist file generated? I'm having trouble interpreting it. For example, the most recent scan date for barcode 226803 shows it as being in the MSB location" [ DGR14149 ] Teresa Dump Box (freezer box):[ ] 10 (position):[ 226803 ] 226803 (cryovial)" - but I do not see it there when I look at box DGR14149. Is that because permissions were changed, but the positions were not?

I don't think so, I still do not have permissions for this container.

I think I'll need to give them to you and update them to be in a UAM container (eg barcode 0) at the same time.

Just to clarify, If you do this I will be able to use the current barcodes, and I can rescan them into their locations as I find them? This would work for me.

[dustymc]

do not have permissions for this container.

Yes, that's what I'm trying to avoid. Things are going to be somehow weird for anyone who doesn't have permissions for the entire stack - the container, plus parents and children and (in some UI) siblings of all of those things - which very quickly gets into thousands of containers and untold variations of weird.

If you do this I will be able to use the current barcodes, and I can rescan them into their locations as I find them?

Yes. I'd move them out of any possibility of being involved in any sort of complexity, and then they'd be available for you to do whatever you want with them. Just give me a barcode (container ID, whatever) to use as a parent if that works for you.

I would have to do the same thing for any contents - those MSB parts would end up in some MSB-owned "to be filed" sort of container, which I can create or use if someone will direct me to one.

[megulbranson]

Just give me a barcode (container ID, whatever) to use as a parent if that works for you.

Could you put them in barcode value ROOM20 please?

[dustymc]

ROOM20

No problem, but I need to move the parts first. @campmlc where would you like these parts:

select
container.*
from container 
inner join container parent on container.parent_container_id=parent.container_id 
where parent.barcode in ('150251','150252','150256','150257','150258','230530','231443','118463','226795','226803','225013','225029','240489','240492')
and container.institution_acronym != 'UAM';

 container_id | parent_container_id |  container_type   |           label           | description |      last_date      | container_remarks | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units 
--------------+---------------------+-------------------+---------------------------+-------------+---------------------+-------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------
       350487 |             2708714 | collection object | UAM Mammals 42337 liver   |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
        12868 |             2708730 | collection object | UAM Mammals 43399 tissues |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
       349921 |             2710496 | collection object | UAM Mammals 43006 liver   |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
       349929 |             2710504 | collection object | UAM Mammals 42682 liver   |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
     12985158 |             2850779 | collection object | 240489                    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
     12948961 |             2850782 | collection object | 240492                    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
(6 rows)

[campmlc]

DGR324B But not the UAM parts?

[dustymc]

But not the UAM parts?

There are no UAM parts in the data I provided, but maybe I'm still lost.

• I can move 350487 (an MSB part, but with a UAM-ish label) into DGR324B,
• I can assign ownership to UAM and leave it where it is while I move it's parent (2708714) to ROOM20
• I can probably do something else...

Help...

[campmlc]

If it has a UAM label, it is a UAM part, and it should end up back in UAM, so that should work? Try with one and see?

[campmlc]

Or were those two different options. . . I'm very confused. All I can say is that any barcode that is purely numeric and which does not have a DGR or some other letter in front of it belongs to UAM, and it and the parts it contains should be moved back to UAM. They were never at MSB. They were virtually moved because their barcode number series overlap our NK number series, and the NK label was accidentally scanned at MSB.

[campmlc]

Can we do a quick call? This is too hard to do via GitHub. I'll send link?

[campmlc]

Link sent via email

[dustymc]

parts it contains should be moved back to UAM.

That's what I needed (I hope!), and your explanation makes me think that's the right action.

First move the two MSB-owned and MSB-looking parts to DGR324B:

update container set parent_container_id=(select container_id from container where barcode='DGR324B') where container_id in (12985158,12948961);
UPDATE 2

Now confirm what's left in the list of barcodes:

select
container.*
from container 
inner join container parent on container.parent_container_id=parent.container_id where parent.barcode in ('150251','150252','150256','150257','150258','230530','231443','118463','226795','226803','225013','225029','240489','240492');

 container_id | parent_container_id |  container_type   |           label            | description |      last_date      | container_remarks | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units 
--------------+---------------------+-------------------+----------------------------+-------------+---------------------+-------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------
      2608633 |              144335 | collection object | UAM Mammals 100025 tissues |             | 2007-09-19 11:08:45 |                   |         |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 | 
       350487 |             2708714 | collection object | UAM Mammals 42337 liver    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
        12868 |             2708730 | collection object | UAM Mammals 43399 tissues  |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
       349921 |             2710496 | collection object | UAM Mammals 43006 liver    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
       349929 |             2710504 | collection object | UAM Mammals 42682 liver    |             | 2015-11-03 17:28:08 |                   |         |          |       |        |        | MSB                 |             |                |             |                               |                  |        |              |                 | 
(5 rows)

and give the 4 that aren't already owned by UAM to UAM

arctosprod@arctos>> update container set institution_acronym='UAM' where container_id in (350487,12868,349921,349929);
UPDATE 4
Time: 169.053 ms

now move the containers out of any confounded hierarchy to ROOM20

 update container set parent_container_id=(select container_id from container where barcode='ROOM20') where barcode in ('150251','150252','150256','150257','150258','230530','231443','118463','226795','226803','225013','225029','240489','240492');

UPDATE 14

[dustymc]

Sorry @campmlc I was updating!

@megulbranson please confirm everything's where it should be, I think this can be closed if so, and if not then let's all talk.

[campmlc]

So where did the MSB parts with labels as follows end up? NK 168884 tissue in buff NK 168895 tissue in buff NK 172665 RBC NK 172665 RBC NK 172671 rojos

[dustymc]

So where did the MSB parts with labels as follows end up?

select
container.*,
getContainerParentage(container_id)
from container where label in ('NK 168884 tissue in buff','NK 168895 tissue in buff','NK 172665 RBC','NK 172665 RBC','NK 172671 rojos');

 container_id | parent_container_id | container_type |          label           | description |         last_date          |          container_remarks          | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units |                                                                                     getcontainerparentage                                                                                      
--------------+---------------------+----------------+--------------------------+-------------+----------------------------+-------------------------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      2630335 |                   1 | cryovial       | NK 168884 tissue in buff |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150251  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150251 ] NK 168884 tissue in buff (cryovial)
      2630336 |                   1 | cryovial       | NK 168895 tissue in buff |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150252  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150252 ] NK 168895 tissue in buff (cryovial)
      2630340 |                   1 | cryovial       | NK 172665 RBC            |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150256  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150256 ] NK 172665 RBC (cryovial)
      2630341 |                   1 | cryovial       | NK 172665 RBC            |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150257  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150257 ] NK 172665 RBC (cryovial)
      2630342 |                   1 | cryovial       | NK 172671 rojos          |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150258  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150258 ] NK 172671 rojos (cryovial)
(5 rows)

I think those should be

• given to MSB, and
• moved to DGR324B

??

[campmlc]

Yes, I think I figured this one out previously - these appear to be UAM barcodes on MSB birds that are physically in MSB collections. If so, I can re-barcode these with MSB barcodes, assuming I can find them? @megulbranson can you confirm if they are or are not where they should be at UAM? Barcodes: 150251 150252 150256 150257 150258

[campmlc]

If they are in DGR324B, I can't see them there. I do see the two MSBUNKNOWN barcodes, but I have no idea what they are, and they aren't linked to any parts.

[campmlc]

Once we hear from UAM and I can check my freezers tomorrow, hopefully we can close?

[megulbranson]

I checked 226803 and I am now able to find it, I am out of the office for the afternoon but will move it back to it's correct location tomorrow to match it's physical location, and let you both know if I come across anything weird with the others.

[megulbranson]

I'll have time tomorrow to Zoom if needed.

[megulbranson]

Yes, I think I figured this one out previously - these appear to be UAM barcodes on MSB birds that are physically in MSB collections. If so, I can re-barcode these with MSB barcodes, assuming I can find them? @megulbranson can you confirm if they are or are not where they should be at UAM? Barcodes: 150251 150252 150256 150257 150258

I was not able to physically track these down at UAM.

[campmlc]

OK, I found a blank space where one should have been in object tracking. When I get in tomorrow I'll see if the physical vial is there. I assume these are collaborations between the Witt lab and UAM? So we should try to figure out if they shoud be here or returned to UAM.

[campmlc]

NK 172665 BC150257 RBC University of Alaska Museum check MSB box DGR17599 pos 83

[dustymc]

can't see them there

See https://github.com/ArctosDB/arctos/issues/5452#issuecomment-2305584653, you can't see things if you don't own the stack.

two MSBUNKNOWN barcodes,

Not possible?!?

Anyway: https://github.com/ArctosDB/arctos/issues/5452#issuecomment-2305863254 still seems right, and those DO NOT have children

select
container.*,
content.*,
getContainerParentage(container.container_id) as label_stack,
getContainerParentage(content.container_id) as in_label_stack
from container 
left outer join container content on content.parent_container_id=container.container_id
where container.label in ('NK 168884 tissue in buff','NK 168895 tissue in buff','NK 172665 RBC','NK 172665 RBC','NK 172671 rojos');
 container_id | parent_container_id | container_type |          label           | description |         last_date          |          container_remarks          | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units | container_id | parent_container_id | container_type | label | description | last_date | container_remarks | barcode | print_fg | width | height | length | institution_acronym | number_rows | number_columns | orientation | positions_hold_container_type | last_update_tool | weight | weight_units | weight_capacity | weight_capacity_units |                                                                                          label_stack                                                                                           | in_label_stack 
--------------+---------------------+----------------+--------------------------+-------------+----------------------------+-------------------------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------+--------------+---------------------+----------------+-------+-------------+-----------+-------------------+---------+----------+-------+--------+--------+---------------------+-------------+----------------+-------------+-------------------------------+------------------+--------+--------------+-----------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------
      2630342 |                   1 | cryovial       | NK 172671 rojos          |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150258  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       |              |                     |                |       |             |           |                   |         |          |       |        |        |                     |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150258 ] NK 172671 rojos (cryovial)          | 
      2630336 |                   1 | cryovial       | NK 168895 tissue in buff |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150252  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       |              |                     |                |       |             |           |                   |         |          |       |        |        |                     |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150252 ] NK 168895 tissue in buff (cryovial) | 
      2630341 |                   1 | cryovial       | NK 172665 RBC            |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150257  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       |              |                     |                |       |             |           |                   |         |          |       |        |        |                     |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150257 ] NK 172665 RBC (cryovial)            | 
      2630340 |                   1 | cryovial       | NK 172665 RBC            |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150256  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       |              |                     |                |       |             |           |                   |         |          |       |        |        |                     |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150256 ] NK 172665 RBC (cryovial)            | 
      2630335 |                   1 | cryovial       | NK 168884 tissue in buff |             | 2024-08-22 16:18:48.126881 | EIMI two-piece labels, 30 Nov 2007. | 150251  |          |       |        |        | UAM                 |             |                |             |                               |                  |        |              |                 |                       |              |                     |                |       |             |           |                   |         |          |       |        |        |                     |             |                |             |                               |                  |        |              |                 |                       | [ UAF ] U. Alaska, Fairbanks (institution):[ 1962YukonDr ] University of Alaska Museum (building):[ ROOM20 ] UAM Genomic Resources rm 20 (room):[ 150251 ] NK 168884 tissue in buff (cryovial) | 
(5 rows)

I'd move and repatriate them, but I'd have to claim those integer barcodes for MSB first, and that seems like it would probably cause more problems than it'd solve.

Perhaps the easiest thing to do is to delete them?? They don't claim to contain parts, and this doesn't seem like something that would happen pre-catalog. All the data is above, let me know if that seems least-evil, it would be really great to resolve this even if not entirely satisfactorily.