Arctos: Stop duplicate accession numbers
All 40 comments
We can, but I think that exists by request.
guid_prefix,
accn_number,
count(*)
from
trans,
accn,
collection
where
trans.transaction_id=accn.transaction_id and
trans.collection_ID=collection.collection_ID
having
count(*) > 1
group by
guid_prefix,
accn_number
17 ;
GUID_PREFIX
------------------------------------------------------------
ACCN_NUMBER
------------------------------------------------------------------------------------------------------------------------
COUNT(*)
----------
MSB:Mamm
2005.113.Mamm
2
MSB:Mamm
1987.173.Mamm
2
MSB:Mamm
1987.143.Mamm
2
MSB:Mamm
1991.206.Mamm
2
MSB:Mamm
1998.640.Mamm
3
MSB:Mamm
2009.011.Mamm
2
MSB:Mamm
1984.083.Mamm
2
MSB:Mamm
1989.194.Mamm
2
MSB:Mamm
1994.237.Mamm
2
MSB:Mamm
1986.130.Mamm
2
MSB:Mamm
2002.885.Mamm
2
MSB:Mamm
2001.824.Mamm
2
MSB:Mamm
1982.032.Mamm
2
MSB:Mamm
1988.178.Mamm
2
MSB:Mamm
1989.185.Mamm
2
UWBM:Herp
2018-45
2
MSB:Para
2016.007.Para
2
MSB:Mamm
1987.146.Mamm
2
MSB:Mamm
2001.846.Mamm
2
MSB:Mamm
1989.189.Mamm
2
MSB:Mamm
1989.190.Mamm
2
MSB:Mamm
2007.021.Mamm
2
MSB:Mamm
2007.058.Mamm
2
MSB:Mamm
1994.275.Mamm
2
MSB:Mamm
2005.1066.Mamm
2
MSB:Mamm
1987.145.Mamm
2
MSB:Mamm
1990.199.Mamm
2
MSB:Mamm
2002.899.Mamm
2
MSB:Mamm
1997.581.Mamm
2
MSB:Mamm
2012.033.Mamm
2
MSB:Mamm
1982.028.Mamm
2
UTEP:HerpOS
1993.1
2
MSB:Mamm
1985.103.Mamm
2
MSB:Mamm
1986.122.Mamm
2
MSB:Mamm
1986.133.Mamm
2
MSB:Mamm
1999.753.Mamm
3
MSB:Mamm
2006.010.Mamm
2
MSB:Mamm
1990.203.Mamm
2
MSB:Mamm
2005.046.Mamm
2
MSB:Mamm
1994.251.Mamm
2
MSB:Mamm
1992.225.Mamm
2
DMNS:Bird
2013-148
2
MSB:Mamm
2000.780.Mamm
2
MSB:Mamm
1989.191.Mamm
2
MSB:Mamm
1994.267.Mamm
2
MSB:Mamm
2007.073.Mamm
2
MSB:Mamm
1986.140.Mamm
2
MSB:Mamm
1987.167.Mamm
2
UAM:ES
1979 P012
2
MSB:Para
2013.003.Para
2
UAM:EH
0643
2
MSB:Mamm
1987.141.Mamm
2
MSB:Mamm
1988.174.Mamm
2
MSB:Mamm
1993.228.Mamm
2
MSB:Mamm
1994.259.Mamm
2
MSB:Mamm
1994.246.Mamm
2
MSB:Mamm
1988.180.Mamm
2
MSB:Mamm
1982.014.Mamm
2
MSB:Mamm
1994.258.Mamm
2
MSB:Mamm
1999.735.Mamm
2
MSB:Mamm
1982.015.Mamm
2
MSB:Mamm
1987.171.Mamm
2
MSB:Mamm
1988.181.Mamm
2
MSB:Mamm
1987.164.Mamm
2
MSB:Mamm
2007.055.Mamm
2
MSB:Mamm
1995.392.Mamm
2
MSB:Mamm
1986.132.Mamm
2
DMNS:Bird
1986-2
2
DMNS:Mamm
2013-32
2
MSB:Mamm
1992.226.Mamm
2
70 rows selected.
dustymc
on 16 Jul 2018
How does a collection request that?
I would think that when someone enters an exact duplicate of an accession or loan Arctos would say "That accession/loan already exists, do you want to modify the existing accession/loan or do you want to create a new accession/loan?" with links to the either the existing accession/loan or the form to create a new accession/loan.
Jegelewicz
on 16 Jul 2018
It can't be by collection - we either prevent duplicates or we don't.
I think there is a "are you really sure?" notification, but that's in the forms and probably not terribly difficult to get around.
dustymc
on 16 Jul 2018
It can't be by collection - we either prevent duplicates or we don't.
Why can't it be by collection? The bulkloader returns an error when I have two UTEP:Ento:9999.1 accessions, so it sees the problem, why can't we do that at data entry rather than discovering it later? I'm not asking that the accession number alone be unique, but that the combination of institutional acronym, collection code, and accession number be unique. We do this with catalog numbers, why not accession/loan numbers?
I think there is a "are you really sure?" notification, but that's in the forms and probably not terribly difficult to get around.
As far as I know, there is absolutely no warning that you are creating a duplicate, even in the form. I just went into test and created a duplicate accession MSB:Para:0000.000.Para and there was no warning to me that this was a problem.
Jegelewicz
on 16 Jul 2018
Why can't it...
Rules vs. "suggestions." Catalog number (+metadata) is globally unique; there's a database rule which cannot be bypassed. The bulkloader is a "form" - it's providing a "suggestion" at a higher level and there's usually a way around those, even if nobody's discovered it yet.
For the sake of precision, (institutional acronym, collection code) is not unique and so can't possibly be used to form a unique index. (That's why guid_prefix exists.)
I can look at the forms if we go in that direction. I'd (strongly!) prefer to clean up the duplicates and implement a unique rule for transactions.
Maybe the warning is in loans?? I'm not sure there's an easy/enforceable way to make some types of transactions unique. Can we update existing data and add a unique index for all transactions?
dustymc
on 16 Jul 2018
So let's use guid_prefix +accession/loan number?
Jegelewicz
on 16 Jul 2018
Is that a vote for a hard rule?
dustymc
on 16 Jul 2018
I vote for a unique index for transactions. I have had the same problem as
Teresa with duplicate accession numbers. That should not be allowed to
happen, as it messes up projects and loans.
On Mon, Jul 16, 2018 at 3:06 PM, dustymc notifications@github.com wrote:
Is that a vote for a hard rule?
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1598#issuecomment-405347717,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AOH0hGHTnzdOh7Seh8aqo92hFU8TFpN3ks5uHOQagaJpZM4VGXgm
.
campmlc
on 16 Jul 2018
Well I think that makes three votes and that seems close enough to a consensus at the moment, so...
Unless someone stops this, we need to do something about the duplicates. Accns above, borrows are clean, here are duplicate loans:
guid_prefix,
loan_number,
count(*)
from
trans,
loan,
collection
where
trans.transaction_id=loan.transaction_id and
trans.collection_ID=collection.collection_ID
having
count(*) > 1
group by
guid_prefix,
loan_number
;
GUID_PREFIX
------------------------------------------------------------
LOAN_NUMBER
------------------------------------------------------------------------------------------------------------------------
COUNT(*)
----------
CUMV:Fish
12-14
2
CUMV:Fish
08-002
2
CUMV:Fish
10-012
2
CUMV:Fish
11-29
2
MSB:Mamm
--delete--
2
CUMV:Fish
07-030
2
CUMV:Fish
2016-018
2
DGR:Mamm
2005.269.Mamm
2
CUMV:Fish
05-015
2
DGR:Mamm
2007.357.Mamm
2
MSB:Mamm
1994.19810.Mamm
2
CUMV:Fish
10-006
2
CUMV:Fish
04-023
2
UAM:Mamm
2003.010.Mamm
2
DGR:Mamm
2003.145.Mamm
2
CUMV:Fish
11-19
2
DGR:Mamm
2005.240.Mamm
2
UAM:EH
2017-15.EH
2
DGR:Mamm
2006.309.Mamm
2
MSB:Mamm
2008.016.Mamm
2
CUMV:Fish
2016-11
2
CUMV:Fish
05-027
2
CUMV:Mamm
2014-09
2
DGR:Mamm
2004.211.Mamm
2
CUMV:Mamm
12-003
2
MSB:Mamm
2014.024.Mamm
2
CUMV:Fish
05-039
2
CUMV:Fish
05-038
2
So let's use guid_prefix +accession/loan number?
I like this. I absolutely want accession numbers to be unique. Count this as a 4th vote.
AJLinn
on 16 Jul 2018
I will tackle DGR loans. Can we make CUMV go away?
On Mon, Jul 16, 2018 at 3:41 PM, Angela Linn notifications@github.com
wrote:
So let's use guid_prefix +accession/loan number?
I like this. I absolutely want accession numbers to be unique. Count this
as a 4th vote.—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1598#issuecomment-405357968,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AOH0hO0HYj8pQbRjeT7ECEQBtoJD8bRzks5uHOxOgaJpZM4VGXgm
.
campmlc
on 16 Jul 2018
CUMV
Yea, I think https://github.com/ArctosDB/arctos/issues/1594 is approval for that - right @ccicero ?
dustymc
on 16 Jul 2018
I got rid of the UTEP:HerpOS dupe accn.
Jegelewicz
on 17 Jul 2018
UAM:EH accession and loan dupes are dealt with.
AJLinn
on 17 Jul 2018
I need to merge two duplicate loans, and delete one. But both have
shipments, and therefore child relationships. Is there a way to delete
shipments?
On Mon, Jul 16, 2018 at 7:35 PM, Angela Linn notifications@github.com
wrote:
UAM:EH accession and loan dupes are dealt with.
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1598#issuecomment-405412805,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AOH0hMiY0fRNm0z1Q2JO63eXde84uwAcks5uHSM1gaJpZM4VGXgm
.
campmlc
on 17 Jul 2018
delete shipments
No - let me know the transaction_id and I can.
dustymc
on 18 Jul 2018
Resurrecting this issue since I accidentally created three accessions with the same number today by hitting 'save' multiple times because it didn't seem like save was working. Add to next AWG agenda @mkoo . Where are we on cleaning up the dups? Yes, CUMV can go away (although @dustymc , there was an email from Charles about 'missing data' - did you ever respond to that?)
ccicero
on 17 Dec 2019
I don't remember anything from Charles.
Here's current data
col guid_prefix format a20;
col loan_number format a20;
col accn_number format a20;
select
guid_prefix,
loan_number,
count(*)
from
trans,
loan,
collection
where
trans.transaction_id=loan.transaction_id and
trans.collection_ID=collection.collection_ID
having
count(*) > 1
group by
guid_prefix,
loan_number
;
select
guid_prefix,
accn_number,
count(*)
from
trans,
accn,
collection
where
trans.transaction_id=accn.transaction_id and
trans.collection_ID=collection.collection_ID
having
count(*) > 1
group by
guid_prefix,
accn_number
;
GUID_PREFIX LOAN_NUMBER COUNT(*)
-------------------- -------------------- ----------
UTEP:Mamm 2016.1 2
UAM:Mamm 2003.010.Mamm 2
UMNH:Mamm 2019.14.Mamm 2
GUID_PREFIX ACCN_NUMBER COUNT(*)
-------------------- -------------------- ----------
MSB:Mamm 1987.143.Mamm 2
MSB:Mamm 1987.173.Mamm 2
MSB:Mamm 1991.206.Mamm 2
MSB:Mamm 2005.113.Mamm 2
MSB:Mamm 1998.640.Mamm 3
MSB:Mamm 2009.011.Mamm 2
MSB:Mamm 1986.130.Mamm 2
MSB:Mamm 2001.824.Mamm 2
MSB:Mamm 1989.185.Mamm 2
MSB:Mamm 1989.194.Mamm 2
MSB:Mamm 1994.237.Mamm 2
MSB:Mamm 2002.885.Mamm 2
MSB:Mamm 1982.032.Mamm 2
MSB:Mamm 1984.083.Mamm 2
MSB:Mamm 1988.178.Mamm 2
UWBM:Herp 2018-45 2
MSB:Mamm 1987.146.Mamm 2
MSB:Mamm 1989.189.Mamm 2
MSB:Mamm 1989.190.Mamm 2
MSB:Mamm 2001.846.Mamm 2
MSB:Mamm 1994.275.Mamm 2
MSB:Mamm 2007.058.Mamm 2
MSB:Mamm 2005.1066.Mamm 2
MSB:Mamm 1997.581.Mamm 2
MSB:Mamm 1987.145.Mamm 2
MSB:Mamm 1990.199.Mamm 2
MSB:Mamm 2002.899.Mamm 2
MSB:Mamm 1982.028.Mamm 2
MSB:Mamm 2012.033.Mamm 2
MSB:Mamm 2005.046.Mamm 2
MSB:Mamm 1985.103.Mamm 2
MSB:Mamm 1986.122.Mamm 2
MSB:Mamm 1986.133.Mamm 2
MSB:Mamm 1990.203.Mamm 2
MSB:Mamm 1992.225.Mamm 2
MSB:Mamm 1994.251.Mamm 2
MSB:Mamm 1999.753.Mamm 3
MSB:Mamm 2006.010.Mamm 2
DMNS:Bird 2013-148 2
MVZ:Fish 15509 2
MSB:Mamm 1986.140.Mamm 2
MSB:Mamm 1987.167.Mamm 2
MSB:Mamm 1989.191.Mamm 2
MSB:Mamm 1994.267.Mamm 2
MSB:Mamm 2000.780.Mamm 2
UAM:ES 1979 P012 2
MSB:Mamm 2007.073.Mamm 2
MSB:Para 2013.003.Para 2
MSB:Mamm 1987.141.Mamm 2
MSB:Mamm 1988.174.Mamm 2
MSB:Mamm 1988.180.Mamm 2
MSB:Mamm 1994.258.Mamm 2
MSB:Mamm 1994.259.Mamm 2
MSB:Mamm 1982.014.Mamm 2
MSB:Mamm 1994.246.Mamm 2
MSB:Mamm 1986.132.Mamm 2
MSB:Mamm 1987.164.Mamm 2
MSB:Mamm 1987.171.Mamm 2
MSB:Mamm 1988.181.Mamm 2
MSB:Mamm 1992.226.Mamm 2
MSB:Mamm 2007.055.Mamm 2
MSB:Mamm 1982.015.Mamm 2
MSB:Mamm 1995.392.Mamm 2
DMNS:Bird 1986-2 2
DMNS:Mamm 2013-32 2
UAM:Herb 2019.03.Herb 2
Thanks. I just deleted the MVZ:Fish duplicate. I think we should write directly to the collection contacts for those with dup accessions. @dustymc I'll put the email from Charles into a new issue.
ccicero
on 17 Dec 2019
What should be done with these duplicates, if they are actually separate accessions? Can I add a suffix _1 or _2 to each without messing up subsequent numbering systems?
campmlc
on 29 Jun 2020
Can I add a suffix _1 or _2....
Sure, it's just a string
.... without messing up subsequent numbering systems?
Probably not - "next" is always going to rely on "previous" being predictable in some manner, unless someone has some clever idea that's radically different than what we do now.
dustymc
on 29 Jun 2020
Most of these duplicates are older accessions. If the next available number
is based on the most recently used number, would it matter if I changed
some of these older ones?
On Mon, Jun 29, 2020 at 1:53 PM dustymc notifications@github.com wrote:
- [EXTERNAL]*
Can I add a suffix _1 or _2....
Sure, it's just a string
.... without messing up subsequent numbering systems?
Probably not - "next" is always going to rely on "previous" being
predictable in some manner, unless someone has some clever idea that's
radically different than what we do now.—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1598#issuecomment-651325321,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ADQ7JBGV4MIXAITC2STH63TRZDWL3ANCNFSM4FIZPATA
.
campmlc
on 29 Jun 2020
That varies a bit by collection/pattern, I think yours are filtering for the year in the "number" so that probably works.
based on the most recently used number
Just to be clear, this never happens - it would cause an implosion if you entered out of order.
dustymc
on 29 Jun 2020
Latest data:
guid_prefix,
loan_number,
count(*)
from
trans,
loan,
collection
where
trans.transaction_id=loan.transaction_id and
trans.collection_ID=collection.collection_ID
group by
guid_prefix,
loan_number
having
count(*) > 1
;
guid_prefix | loan_number | count
-------------+---------------+-------
DMNS:Mamm | OL-2006-57 | 2
DMNS:Bird | OL-2004-39 | 2
DMNS:Mamm | OL-2006-13 | 2
DMNS:Bird | OL-2006-7 | 2
DMNS:Bird | OL-2004-20 | 2
DMNS:Bird | OL-2004-53 | 2
DMNS:Mamm | OL-2004-16 | 2
DMNS:Mamm | OL-2006-17 | 2
DMNS:Bird | OL-2004-58 | 2
DMNS:Mamm | OL-2004-26 | 2
DMNS:Bird | OL-2004-32 | 2
DMNS:Bird | OL-2006-18 | 2
DMNS:Mamm | OL-2004-64 | 2
DMNS:Bird | OL-2006-51 | 2
DMNS:Mamm | OL-2004-7 | 2
DMNS:Bird | OL-2004-61 | 2
DMNS:Mamm | OL-2004-62 | 2
DMNS:Mamm | OL-2004-18 | 2
DMNS:Bird | OL-2006-45 | 2
UTEP:Mamm | 2016.1 | 2
DMNS:Bird | OL-2004-31 | 2
DMNS:Mamm | OL-2004-40 | 2
DMNS:Bird | OL-2006-6 | 2
DMNS:Mamm | OL-2006-2 | 2
DMNS:Mamm | OL-2004-46 | 2
DMNS:Bird | OL-2004-25 | 2
DMNS:Bird | OL-2004-27 | 2
DMNS:Bird | OL-2006-53 | 2
DMNS:Mamm | OL-2006-19 | 2
UMNH:Mamm | 2019.14.Mamm | 2
DMNS:Bird | OL-2004-12 | 2
NMU:Herp | 2019.01 | 2
DMNS:Mamm | OL-2006-9 | 2
DMNS:Bird | OL-2004-43 | 2
DMNS:Bird | OL-2006-47 | 2
DMNS:Mamm | OL-2004-66 | 2
DMNS:Bird | OL-2004-19 | 2
DMNS:Bird | OL-2004-24 | 2
DMNS:Mamm | OL-2006-43 | 2
DMNS:Bird | OL-2006-33 | 2
DMNS:Bird | OL-2006-49 | 2
DMNS:Bird | OL-2006-34 | 2
DMNS:Mamm | OL-2006-23 | 2
DMNS:Mamm | OL-2004-63 | 2
DMNS:Bird | OL-2006-10 | 2
DMNS:Bird | OL-2006-25 | 2
DMNS:Mamm | OL-2004-6 | 2
UAM:Mamm | 2003.010.Mamm | 2
DMNS:Bird | OL-2006-52 | 2
DMNS:Bird | OL-2004-5 | 2
DMNS:Mamm | OL-2006-48 | 2
DMNS:Bird | OL-2006-54 | 2
DMNS:Bird | OL-2006-37 | 2
DMNS:Bird | OL-2006-20 | 2
DMNS:Mamm | OL-2004-13 | 2
DMNS:Bird | OL-2006-13 | 2
select
guid_prefix,
accn_number,
count(*)
from
trans,
accn,
collection
where
trans.transaction_id=accn.transaction_id and
trans.collection_ID=collection.collection_ID
group by
guid_prefix,
accn_number
having
count(*) > 1
;
guid_prefix | accn_number | count
-------------+----------------+-------
DMNS:Bird | 1986-2 | 2
DMNS:Bird | 2013-148 | 2
DMNS:Mamm | 2013-32 | 2
MSB:Herp | 1036 | 2
MSB:Mamm | 1982.014.Mamm | 2
MSB:Mamm | 1982.015.Mamm | 2
MSB:Mamm | 1982.028.Mamm | 2
MSB:Mamm | 1982.032.Mamm | 2
MSB:Mamm | 1984.083.Mamm | 2
MSB:Mamm | 1985.103.Mamm | 2
MSB:Mamm | 1986.122.Mamm | 2
MSB:Mamm | 1986.130.Mamm | 2
MSB:Mamm | 1986.132.Mamm | 2
MSB:Mamm | 1986.133.Mamm | 2
MSB:Mamm | 1986.140.Mamm | 2
MSB:Mamm | 1987.141.Mamm | 2
MSB:Mamm | 1987.143.Mamm | 2
MSB:Mamm | 1987.145.Mamm | 2
MSB:Mamm | 1987.146.Mamm | 2
MSB:Mamm | 1987.164.Mamm | 2
MSB:Mamm | 1987.167.Mamm | 2
MSB:Mamm | 1987.171.Mamm | 2
MSB:Mamm | 1987.173.Mamm | 2
MSB:Mamm | 1988.174.Mamm | 2
MSB:Mamm | 1988.178.Mamm | 2
MSB:Mamm | 1988.180.Mamm | 2
MSB:Mamm | 1988.181.Mamm | 2
MSB:Mamm | 1989.185.Mamm | 2
MSB:Mamm | 1989.189.Mamm | 2
MSB:Mamm | 1989.190.Mamm | 2
MSB:Mamm | 1989.191.Mamm | 2
MSB:Mamm | 1989.194.Mamm | 2
MSB:Mamm | 1990.199.Mamm | 2
MSB:Mamm | 1990.203.Mamm | 2
MSB:Mamm | 1991.206.Mamm | 2
MSB:Mamm | 1992.225.Mamm | 2
MSB:Mamm | 1992.226.Mamm | 2
MSB:Mamm | 1994.237.Mamm | 2
MSB:Mamm | 1994.246.Mamm | 2
MSB:Mamm | 1994.251.Mamm | 2
MSB:Mamm | 1994.258.Mamm | 2
MSB:Mamm | 1994.259.Mamm | 2
MSB:Mamm | 1994.267.Mamm | 2
MSB:Mamm | 1994.275.Mamm | 2
MSB:Mamm | 1995.392.Mamm | 2
MSB:Mamm | 1997.581.Mamm | 2
MSB:Mamm | 1998.640.Mamm | 3
MSB:Mamm | 1999.753.Mamm | 3
MSB:Mamm | 2000.780.Mamm | 2
MSB:Mamm | 2001.824.Mamm | 2
MSB:Mamm | 2001.846.Mamm | 2
MSB:Mamm | 2002.885.Mamm | 2
MSB:Mamm | 2002.899.Mamm | 2
MSB:Mamm | 2005.046.Mamm | 2
MSB:Mamm | 2005.1066.Mamm | 2
MSB:Mamm | 2005.113.Mamm | 2
MSB:Mamm | 2006.010.Mamm | 2
MSB:Mamm | 2007.055.Mamm | 2
MSB:Mamm | 2007.058.Mamm | 2
MSB:Mamm | 2009.011.Mamm | 2
MSB:Mamm | 2012.033.Mamm | 2
MSB:Para | 2013.003.Para | 2
MVZObs:Herp | 15569 | 2
UAM:ES | 1979 P012 | 2
UWBM:Herp | 2018-45 | 2
Looks like this has been hanging out accumulating more data, causing problems, and blocking other development for about two years. Suggest we script-change one of the duplicate and implement unique indexes.
dustymc
on 5 Aug 2020
I'm Ok with that. @jldunnum ?
campmlc
on 5 Aug 2020
I fixed the UTEP:Mamm Loan
Jegelewicz
on 5 Aug 2020
OK as long as we kill the right one. I guess I could go through these and clean them up manually pretty quickly too.
Jonathan L. Dunnum Ph.D.
Senior Collection Manager
Division of Mammals, Museum of Southwestern Biology
University of New Mexico
Albuquerque, NM 87131
(505) 277-9262
Fax (505) 277-1351
MSB Mammals website: http://www.msb.unm.edu/mammals/index.html
Facebook: http://www.facebook.com/MSBDivisionofMammals
Shipping Address:
Museum of Southwestern Biology
Division of Mammals
University of New Mexico
CERIA Bldg 83, Room 204
Albuquerque, NM 87131
From: Mariel Campbell notifications@github.com
Sent: Tuesday, August 4, 2020 5:01 PM
To: ArctosDB/arctos arctos@noreply.github.com
Cc: Jonathan Dunnum jldunnum@unm.edu; Mention mention@noreply.github.com
Subject: Re: [ArctosDB/arctos] Stop duplicate accession numbers (#1598)
[EXTERNAL]
I'm Ok with that. @jldunnumhttps://github.com/jldunnum ?
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/1598#issuecomment-668867510, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA5HA57Y57PWXDWPOD3R7CHL5ANCNFSM4FIZPATA.
jldunnum
on 5 Aug 2020
the right one
That's almost certainly beyond my capabilities - I'd just append "_duplicate_{random_number}" or something if it comes down to that.
dustymc
on 5 Aug 2020
I just fixed the UMNH:Mamm one by adding "a" after 2019.14. That's probably more difficult than adding "a" at the end?
Jegelewicz
on 5 Aug 2020
Yeah, I'll go through them and cull what I can.
Jonathan L. Dunnum Ph.D.
Senior Collection Manager
Division of Mammals, Museum of Southwestern Biology
University of New Mexico
Albuquerque, NM 87131
(505) 277-9262
Fax (505) 277-1351
MSB Mammals website: http://www.msb.unm.edu/mammals/index.html
Facebook: http://www.facebook.com/MSBDivisionofMammals
Shipping Address:
Museum of Southwestern Biology
Division of Mammals
University of New Mexico
CERIA Bldg 83, Room 204
Albuquerque, NM 87131
From: dustymc notifications@github.com
Sent: Tuesday, August 4, 2020 5:38 PM
To: ArctosDB/arctos arctos@noreply.github.com
Cc: Jonathan Dunnum jldunnum@unm.edu; Mention mention@noreply.github.com
Subject: Re: [ArctosDB/arctos] Stop duplicate accession numbers (#1598)
[EXTERNAL]
the right one
That's almost certainly beyond my capabilities - I'd just append "duplicate{random_number}" or something if it comes down to that.
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/1598#issuecomment-668881263, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA4OG4GQP5G33RKMX2DR7CLWFANCNFSM4FIZPATA.
jldunnum
on 5 Aug 2020
UAM:2003.010.Mamm fixed.
KyndallH
on 5 Aug 2020
for MVZObs:Herp-- the one without the specimen attached seems easiest to
delete! (acc_id=21125837)
thanks
On Tue, Aug 4, 2020 at 4:45 PM Kyndall Hildebrandt notifications@github.com
wrote:
UAM:2003.010.Mamm fixed.
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1598#issuecomment-668883745,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AATH7UIHNDQASR6F4FXROPTR7CMRXANCNFSM4FIZPATA
.
mkoo
on 5 Aug 2020
All MSB double accessions except three have been fixed. Will work on 2000.780; 2002.885; and 2002.899 tomorrow once I'm back in the museum.
1982.014.Mamm
1982.015.Mamm
1982.028.Mamm
1982.032.Mamm
1984.083.Mamm
1985.103.Mamm
1986.122.Mamm
1986.130.Mamm
1986.132.Mamm
1986.133.Mamm
1986.140.Mamm
1987.141.Mamm
1987.143.Mamm
1987.145.Mamm
1987.146.Mamm
1987.164.Mamm
1987.167.Mamm
1987.171.Mamm
1987.173.Mamm
1988.174.Mamm
1988.178.Mamm
1988.180.Mamm
1988.181.Mamm
1989.185.Mamm
1989.189.Mamm
1989.190.Mamm
1989.191.Mamm
1989.194.Mamm
1990.199.Mamm
1990.203.Mamm
1991.206.Mamm
1992.225.Mamm
1992.226.Mamm
1993.228.Mamm
1994.237.Mamm
1994.246.Mamm
1994.251.Mamm
1994.258.Mamm
1994.259.Mamm
1994.267.Mamm
1994.275.Mamm
1995.392.Mamm
1997.581.Mamm
1998.640.Mamm
1999.735.Mamm
1999.753.Mamm
2001.824.Mamm
2001.846.Mamm
2005.046.Mamm
2005.1066.Mamm
2005.113.Mamm
2006.010.Mamm
2007.021.Mamm
2007.055.Mamm
2007.058.Mamm
2007.073.Mamm
2009.011.Mamm
2012.033.Mamm
Jonathan L. Dunnum Ph.D.
Senior Collection Manager
Division of Mammals, Museum of Southwestern Biology
University of New Mexico
Albuquerque, NM 87131
(505) 277-9262
Fax (505) 277-1351
MSB Mammals website: http://www.msb.unm.edu/mammals/index.html
Facebook: http://www.facebook.com/MSBDivisionofMammals
Shipping Address:
Museum of Southwestern Biology
Division of Mammals
University of New Mexico
CERIA Bldg 83, Room 204
Albuquerque, NM 87131
From: Jonathan Dunnum jldunnum@unm.edu
Sent: Tuesday, August 4, 2020 5:41 PM
To: ArctosDB/arctos arctos@noreply.github.com; ArctosDB/arctos reply@reply.github.com
Cc: Mention mention@noreply.github.com
Subject: Re: [ArctosDB/arctos] Stop duplicate accession numbers (#1598)
Yeah, I'll go through them and cull what I can.
Jonathan L. Dunnum Ph.D.
Senior Collection Manager
Division of Mammals, Museum of Southwestern Biology
University of New Mexico
Albuquerque, NM 87131
(505) 277-9262
Fax (505) 277-1351
MSB Mammals website: http://www.msb.unm.edu/mammals/index.html
Facebook: http://www.facebook.com/MSBDivisionofMammals
Shipping Address:
Museum of Southwestern Biology
Division of Mammals
University of New Mexico
CERIA Bldg 83, Room 204
Albuquerque, NM 87131
From: dustymc notifications@github.com
Sent: Tuesday, August 4, 2020 5:38 PM
To: ArctosDB/arctos arctos@noreply.github.com
Cc: Jonathan Dunnum jldunnum@unm.edu; Mention mention@noreply.github.com
Subject: Re: [ArctosDB/arctos] Stop duplicate accession numbers (#1598)
[EXTERNAL]
the right one
That's almost certainly beyond my capabilities - I'd just append "duplicate{random_number}" or something if it comes down to that.
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/1598#issuecomment-668881263, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA4OG4GQP5G33RKMX2DR7CLWFANCNFSM4FIZPATA.
jldunnum
on 5 Aug 2020
This complicates https://github.com/ArctosDB/arctos/issues/3427
Latest data - can I just append "duplicate" to all but one and figure out a unique index?
guid_prefix,
loan_number,
count(*)
from
trans,
loan,
collection
where
trans.transaction_id=loan.transaction_id and
trans.collection_ID=collection.collection_ID
group by
guid_prefix,
loan_number
having
count(*) > 1
order by guid_prefix
;
guid_prefix | loan_number | count
-------------+-------------+-------
DMNS:Bird | OL-2006-13 | 2
DMNS:Bird | OL-2004-39 | 2
DMNS:Bird | OL-2006-45 | 2
DMNS:Bird | OL-2004-31 | 2
DMNS:Bird | OL-2006-6 | 2
DMNS:Bird | OL-2004-25 | 2
DMNS:Bird | OL-2004-27 | 2
DMNS:Bird | OL-2006-53 | 2
DMNS:Bird | OL-2004-12 | 2
DMNS:Bird | OL-2004-43 | 2
DMNS:Bird | OL-2006-47 | 2
DMNS:Bird | OL-2004-19 | 2
DMNS:Bird | OL-2004-24 | 2
DMNS:Bird | OL-2006-33 | 2
DMNS:Bird | OL-2006-49 | 2
DMNS:Bird | OL-2006-34 | 2
DMNS:Bird | OL-2006-10 | 2
DMNS:Bird | OL-2006-25 | 2
DMNS:Bird | OL-2006-52 | 2
DMNS:Bird | OL-2004-5 | 2
DMNS:Bird | OL-2006-54 | 2
DMNS:Bird | OL-2006-37 | 2
DMNS:Bird | OL-2006-20 | 2
DMNS:Bird | OL-2006-7 | 2
DMNS:Bird | OL-2004-20 | 2
DMNS:Bird | OL-2004-53 | 2
DMNS:Bird | OL-2004-58 | 2
DMNS:Bird | OL-2004-32 | 2
DMNS:Bird | OL-2006-18 | 2
DMNS:Bird | OL-2006-51 | 2
DMNS:Bird | OL-2004-61 | 2
DMNS:Mamm | OL-2006-2 | 2
DMNS:Mamm | OL-2004-46 | 2
DMNS:Mamm | OL-2006-23 | 2
DMNS:Mamm | OL-2004-63 | 2
DMNS:Mamm | OL-2004-16 | 2
DMNS:Mamm | OL-2006-17 | 2
DMNS:Mamm | OL-2006-43 | 2
DMNS:Mamm | OL-2004-26 | 2
DMNS:Mamm | OL-2006-19 | 2
DMNS:Mamm | OL-2004-13 | 2
DMNS:Mamm | OL-2004-64 | 2
DMNS:Mamm | OL-2006-13 | 2
DMNS:Mamm | OL-2004-7 | 2
DMNS:Mamm | OL-2006-9 | 2
DMNS:Mamm | OL-2004-62 | 2
DMNS:Mamm | OL-2004-18 | 2
DMNS:Mamm | OL-2006-48 | 2
DMNS:Mamm | OL-2006-57 | 2
DMNS:Mamm | OL-2004-40 | 2
DMNS:Mamm | OL-2004-66 | 2
NMU:Herp | 2019.01 | 2
guid_prefix,
accn_number,
count(*)
from
trans,
accn,
collection
where
trans.transaction_id=accn.transaction_id and
trans.collection_ID=collection.collection_ID
group by
guid_prefix,
accn_number
having
count(*) > 1
order by guid_prefix
;
guid_prefix | accn_number | count
-------------+---------------+-------
DMNS:Bird | 1983-66 | 2
DMNS:Bird | 1986-2 | 2
DMNS:Bird | 2013-148 | 2
DMNS:Mamm | 2013-32 | 2
MSB:Herp | 1036 | 2
MSB:Para | 2013.003.Para | 2
NMMNH:Ento | 2019.037 | 2
NMMNH:Ento | 2019.038 | 2
NMMNH:Ento | 2019.039 | 2
NMMNH:Inv | 2019.036 | 2
NMMNH:Paleo | 2020.001 | 2
NMMNH:Paleo | 2020.002 | 2
UAM:ES | 1979 P012 | 2
UCM:Bird | 463 | 2
UWBM:Herp | 2018-45 | 2
@lin-fred @Nicole-Ridgwell-NMMNHS
Jegelewicz
on 11 Feb 2021
can I just append "duplicate" to all but one and figure out a unique index
I'm fine with that. I noticed our dups a couple weeks ago trying to do bulkloading and have been pushing our registrar to fix.
Whatever you do to prevent duplicates, can it also prevent duplicates where one has a space at the end and another doesn't? I noticed a few of our duplicate accessions weren't on your list and it's because one has a space at the end.
Nicole-Ridgwell-NMMNHS
on 11 Feb 2021
John just brought this to my attention. For the loans I must have double bulkloaded; the accessions will require a second look. I'll start cleaning up now, but @dustymc feel free to add duplicate to move this forward.
acdoll
on 11 Feb 2021
one has a space at the end
That could be incorporated into https://github.com/ArctosDB/arctos/issues/2678
dustymc
on 11 Feb 2021
I can't figure out how to do this with indexes so I built triggers, which means I can proceed with duplicate data. Introducing duplicates is no longer possible, and the remaining duplicates won't save without changing the accn/loan number.
Closing, for lack of better ideas.
Loans
guid_prefix | loan_number | count
-------------+-------------+-------
NMU:Herp | 2019.01 | 2
(1 row)
Accns
guid_prefix | accn_number | count
-------------+---------------+-------
MSB:Mamm | 2020.005.Mamm | 2
NMMNH:Ento | 2019.037 | 2
NMMNH:Ento | 2019.038 | 2
NMMNH:Ento | 2019.039 | 2
NMMNH:Inv | 2019.036 | 2
UAM:ES | 1979 P012 | 2
UWBM:Herp | 2018-45 | 2
Related issues
Jegelewicz
·
6Comments
dustymc
·
4Comments
Jegelewicz
·
5Comments
dustymc
·
7Comments
sharpphyl
·
7Comments
Most helpful comment
That could be incorporated into https://github.com/ArctosDB/arctos/issues/2678