search

ArctosDB / arctos

Arctos is a museum collections management system
https://arctos.database.museum
60 stars 13 forks source link

Discrepancy between portal page count and search page #2139

Closed Jegelewicz closed 5 years ago

Jegelewicz commented 5 years ago

@dustymc I have been sending our billing advance notices today and ran across this at UTEP:

Our portal number says 151,966 and the search database page says 151,800. I don’t have 100 encumbered records, but I do have quite a bit of stuff in the bulkloader. I haven’t uploaded anything for a few days, so the number should be up to date.

-Vicky

???

dustymc commented 5 years ago

The portal page is the number of records in the portal - note that not all collections are necessarily in portals, some portals contain administratively-unrelated specimens, etc., so that may not be a great place to get numbers for billing purposes.

The search screen lists the number of records available to it - it won't list encumbered specimens, so also not a good place to find this.

To get a believable answer, you have to ask as a user who can't be constrained by VPDs - and hope you can find everything associated with an institution, which isn't always as obvious as you might wish. Here's UTEP, I think....

select count(*) from cataloged_item,collection where cataloged_item.collection_id=collection.collection_id and guid_prefix like 'UTEP%';

  COUNT(*)
----------
    151969

Maybe we need a non-NULLable collection.billable_entity_name or something just for this??

Here are the "missing" UTEP specimens. FYI http://arctos.database.museum/info/encumbrances will provide encumbered counts.

UAM@ARCTOS>  select guid, encumbrances from flat where  guid like 'UTEP%' and guid not in (select guid from filtered_flat where guid like 'UTEP%');

GUID
------------------------------------------------------------------------------------------------------------------------
ENCUMBRANCES
------------------------------------------------------------------------------------------------------------------------
UTEP:ES:198-628
mask record

UTEP:ES:107-1337
mask record

UTEP:ES:49-409
mask record

UTEP:ES:60-103
mask record

UTEP:ES:24-13
mask record

UTEP:ES:60-66
mask record

UTEP:ES:60-62
mask record

UTEP:ES:49-405
mask record

UTEP:ES:5689-3.004
mask record

UTEP:ES:60-83
mask record

UTEP:HerpOS:38
mask record

UTEP:ES:60-283
mask record

UTEP:ES:60-303
mask record

UTEP:ES:51-146
mask record

UTEP:ES:60-63
mask record

UTEP:ES:197-1
mask record

UTEP:ES:60-249
mask record

UTEP:ES:60-286
mask record

UTEP:ES:107-1249
mask record

UTEP:ES:49-403
mask record

UTEP:ES:60-284
mask record

UTEP:ES:60-10
mask record

UTEP:ES:51-32
mask record

UTEP:ES:19-12
mask record

UTEP:ES:24-22
mask record

UTEP:ES:60-119
mask record

UTEP:ES:60-108
mask record

UTEP:Herp:166
mask record

UTEP:ES:173-2
mask record

UTEP:ES:198-636
mask record

UTEP:ES:51-103
mask record

UTEP:ES:60-86
mask record

UTEP:ES:198-629
mask record

UTEP:ES:60-59
mask record

UTEP:ES:198-635
mask record

UTEP:ES:60-19
mask record

UTEP:ES:49-406
mask record

UTEP:ES:200-74
mask record

UTEP:ES:197-6
mask record

UTEP:ES:196-6
mask record

UTEP:ES:197-8
mask record

UTEP:ES:60-270
mask record

UTEP:ES:60-272
mask record

UTEP:ES:60-101
mask record

UTEP:ES:200-114
mask record

UTEP:ES:60-213
mask record

UTEP:ES:60-196
mask record

UTEP:ES:60-48
mask record

UTEP:ES:60-65
mask record

UTEP:ES:60-27
mask record

UTEP:ES:50-53
mask record

UTEP:ES:77-23
mask record

UTEP:ES:60-4
mask record

UTEP:ES:19-10
mask record

UTEP:ES:60-61
mask record

UTEP:ES:60-311
mask record

UTEP:ES:198-631
mask record

UTEP:ES:60-57
mask record

UTEP:ES:60-20
mask record

UTEP:ES:51-114
mask record

UTEP:ES:196-3
mask record

UTEP:ES:60-197
mask record

UTEP:ES:60-80
mask record

UTEP:ES:60-206
mask record

UTEP:ES:60-6
mask record

UTEP:ES:200-11
mask record

UTEP:ES:60-77
mask record

UTEP:ES:198-634
mask record

UTEP:ES:60-71
mask record

UTEP:Mamm:7890
mask record

UTEP:ES:198-627
mask record

UTEP:ES:36-22
mask record

UTEP:ES:60-5
mask record

UTEP:ES:60-210
mask record

UTEP:ES:60-282
mask record

UTEP:ES:60-37
mask record

UTEP:ES:107-1256
mask record

UTEP:ES:77-20
mask record

UTEP:ES:51-93
mask record

UTEP:ES:60-2
mask record

UTEP:ES:60-104
mask record

UTEP:ES:60-1
mask record

UTEP:ES:60-79
mask record

UTEP:ES:60-53
mask record

UTEP:ES:60-18
mask record

UTEP:ES:197-4
mask record

UTEP:ES:200-160
mask record

UTEP:ES:60-60
mask record

UTEP:ES:60-281
mask record

UTEP:ES:60-8
mask record

UTEP:ES:60-288
mask record

UTEP:ES:60-67
mask record

UTEP:ES:196-8
mask record

UTEP:ES:60-31
mask record

UTEP:ES:200-4
mask record

UTEP:ES:77-22
mask record

UTEP:HerpOS:433
mask record

UTEP:ES:5689-89.001
mask record

UTEP:ES:60-70
mask record

UTEP:ES:60-107
mask record

UTEP:ES:5689-89.017
mask record

UTEP:ES:60-14
mask record

UTEP:ES:60-17
mask record

UTEP:ES:60-84
mask record

UTEP:Herp:753
mask record

UTEP:ES:51-104
mask record

UTEP:ES:49-404
mask record

UTEP:ES:60-128
mask record

UTEP:ES:60-180
mask record

UTEP:ES:60-54
mask record

UTEP:ES:60-75
mask record

UTEP:ES:196-7
mask record

UTEP:ES:60-81
mask record

UTEP:ES:60-7
mask record

UTEP:ES:60-195
mask record

UTEP:ES:60-145
mask record

UTEP:ES:51-42
mask record

UTEP:ES:200-5
mask record

UTEP:ES:60-102
mask record

UTEP:ES:198-632
mask record

UTEP:ES:5689-2.005
mask record

UTEP:ES:198-637
mask record

UTEP:ES:49-411
mask record

UTEP:ES:60-330
mask record

UTEP:ES:60-9
mask record

UTEP:ES:49-401
mask record

UTEP:ES:60-285
mask record

UTEP:ES:60-223
mask record

UTEP:ES:196-5
mask record

UTEP:ES:50-57
mask record

UTEP:ES:196-4
mask record

UTEP:ES:49-407
mask record

UTEP:ES:60-3
mask record

UTEP:ES:196-9
mask record

UTEP:ES:50-111
mask record

UTEP:ES:196-2
mask record

UTEP:ES:60-58
mask record

UTEP:ES:51-89
mask record

UTEP:ES:49-402
mask record

UTEP:ES:24-53
mask record

UTEP:ES:49-400
mask record

UTEP:ES:60-78
mask record

UTEP:ES:198-633
mask record

UTEP:ES:60-69
mask record

UTEP:ES:197-7
mask record

UTEP:ES:60-72
mask record

UTEP:ES:60-16
mask record

UTEP:ES:60-82
mask record

UTEP:ES:49-408
mask record

UTEP:ES:60-312
mask record

UTEP:ES:198-630
mask record

UTEP:ES:60-73
mask record

UTEP:ES:60-49
mask record

UTEP:ES:197-2
mask record

UTEP:ES:60-87
mask record

UTEP:ES:196-1
mask record

UTEP:ES:60-304
mask record

UTEP:ES:197-3
mask record

UTEP:ES:60-74
mask record

UTEP:ES:5689-89.013
mask record

UTEP:ES:200-6
mask record

UTEP:ES:60-64
mask record

UTEP:ES:197-5
mask record

UTEP:ES:49-410
mask record

UTEP:ES:60-76
mask record

UTEP:ES:5689-94.012
mask record

166 rows selected.
Jegelewicz commented 5 years ago

Maybe we need a non-NULLable collection.billable_entity_name or something just for this??

Yes. I need an easy way to get the ACTUAL number of records in Arctos for every INSTITUTION as this is how we bill. No running sql, no adding up stuff, no "ask Dusty to run the report, just a single number for billing purposes. Please?!

dustymc commented 5 years ago

for every INSTITUTION

If that (or anything else in collection) is a useful key, I can easily build a form around it.


UAM@ARCTOS> select distinct INSTITUTION from collection order by INSTITUTION;

INSTITUTION
------------------------------------------------------------------------------------------------------------------------
Alabama Museum of Natural History
Angelo State Natural History Collections (ASNHC)
Arkansas Center for Biodiversity Collections (ACBC)
Austin Peay State University (APSU)
Burke Museum of Natural History and Culture (UWBM)
Chicago Academy of Sciences (CHAS)
College of the Atlantic (COA)
Denver Museum of Nature and Science (DMNS)
Harold W. Manter Laboratory of Parasitology Collection (HWML)
Kenai National Wildlife Refuge, Alaska (KNWR)
Kenelm W. Philip Lepidoptera Collection (KWP)
Kenneth S. Norris Center for Natural History, University of California, Santa Cruz (UCSC)
Moore Laboratory of Zoology (MLZ)
Museum of Southwestern Biology (MSB)
Museum of Southwestern Biology Division of Genomic Resources (DGR)
Museum of Vertebrate Zoology (MVZ)
Natural History Museum of Utah (UMNH)
New Mexico Museum of Natural History and Science (NMMNHS)
Northern Michigan University (NMU)
Ohio Wesleyan University (OWU)
PSU Mamm
U.S. National Biomonitoring Specimen Bank (NBSB)
U.S. National Parasite Collection (USNPC)
University of Alaska Museum (UAM)
University of California Sagehen Creek Field Station
University of Colorado Museum of Natural History (UCM)
University of Montana Zoological Museum (UMZM)
University of Nevada, Reno (UNR)
University of New Mexico (UNM)
University of Texas at El Paso Biodiversity Collections (UTEP)
University of Wyoming Museum of Vertebrates (UWYMV)
Western New Mexico University (WNMU)

32 rows selected.

If not we can add a column.

Jegelewicz commented 5 years ago

Those all make sense except PSU Mamm - what the heck is that? Also, Alabama should have the code (ALMNH) and University of California Sagehen Creek Field Station should have (UC-SCFS), possible?

dustymc commented 5 years ago

make sense...


select
  institution,
  count(*) c
from
  collection,
  cataloged_item
where
  collection.collection_id=cataloged_item.collection_id
group by institution order by institution
 10  ;

INSTITUTION
------------------------------------------------------------------------------------------------------------------------
     C
----------
Alabama Museum of Natural History
      9630

Angelo State Natural History Collections (ASNHC)
       101

Austin Peay State University (APSU)
      7010

Burke Museum of Natural History and Culture (UWBM)
     65246

Chicago Academy of Sciences (CHAS)
     68731

College of the Atlantic (COA)
      1282

Denver Museum of Nature and Science (DMNS)
    107194

Harold W. Manter Laboratory of Parasitology Collection (HWML)
     26111

Kenai National Wildlife Refuge, Alaska (KNWR)
     10446

Kenelm W. Philip Lepidoptera Collection (KWP)
     85409

Kenneth S. Norris Center for Natural History, University of California, Santa Cruz (UCSC)
       373

Moore Laboratory of Zoology (MLZ)
     65003

Museum of Southwestern Biology (MSB)
    543481

Museum of Southwestern Biology Division of Genomic Resources (DGR)
      3273

Museum of Vertebrate Zoology (MVZ)
    738334

Natural History Museum of Utah (UMNH)
     86228

Northern Michigan University (NMU)
      2849

Ohio Wesleyan University (OWU)
       805

U.S. National Biomonitoring Specimen Bank (NBSB)
       321

U.S. National Parasite Collection (USNPC)
       811

University of Alaska Museum (UAM)
   1533156

University of Colorado Museum of Natural History (UCM)
    101720

University of Montana Zoological Museum (UMZM)
     21991

University of Nevada, Reno (UNR)
      9406

University of New Mexico (UNM)
      1560

University of Texas at El Paso Biodiversity Collections (UTEP)
    151969

University of Wyoming Museum of Vertebrates (UWYMV)
      9500

Western New Mexico University (WNMU)
      8606

...except

new Issue - that's Lam/VPD-stuff.