Curatorial Search Request - search to download a subset of parts

[happiah-madson]

Describe what you are trying to accomplish
I am trying to download a subset of parts from 126 records. For some records, I am interested in all of the parts, but for other records, there are many additional parts that are not relevant for my curatorial work. Can you give @falco-rk and I a way to download just a subset of parts either based on barcode or by part identifier?

List the fields that you would like to see in your results table
catalog number
scientific name
part name
condition
disposition
quantity
in container
PLPath
Remarks
Arctos part ID
part identifier(s) [I know this is an attribute and may occur multiple times. In the most perfect world, I would like all of them.]

I also would love to be able to download the "Arctos Parts Bulkloader Template" for the subset of parts that I search for.

[dustymc]

Try this, and let me know if something isn't available.

First customize your results, turn on whatever 'flat' columns you want to see

Search, then

and play with the options. I think the merge-view will do what you want, although multiple part identifier(s) will be on separate lines.

If that doesn't work, go back to your search and...

Give me that URL and let me know what's missing and we should be able to figure it out.

[happiah-madson]

The problem I'm running into is that when I do my search based on Barcodes the found set is 125 records, and then when I go to view parts, it shows all parts, not just the subset that I searched for. Am I missing something obvious?

[dustymc]

Oh I see (kinda-maybe).

That's not possible there, record search is using the barcodes (or whatever it's fed) as a way to locate "whole records," it doesn't only find the matching bits.

If this involves a loan or something of the sort, there might be a download there.

Some possibly-useful information is available from container-land if that's what's going on.

I'm not sure how else I can help without context and data.

[happiah-madson]

Supplemental Table S2.xlsx

I'm trying to look at this subset of samples. Their dispositions are a mess in Arctos and I want to see their dispositions and location information so that I can go verify whether they exist in our freezers. We haven't completed a loan yet, so it's possible that that would be a way to deal with this, but also, we care a lot about individual parts, so being able to search for subsets of parts is low key a thing we need to be able to do.

What other context can I provide you?

[dustymc]

Thanks, I think that's a job for SQL (but please let me know if you see a way to turn it into a UI or if I'm just lost).

First I used listerizer to get a list of barcodes from a column.

Then I used a function to quote-delimit them

select quotestring(
'OGLA52957,OGLA52958,OGLA52959,OGLA52960,OGLA52961,OGLA52962,OGLA52963,OGLA52964,OGLA52965,OGLA52966,OGLA52977,OGLA52978,OGLA52979,OGLA52980,OGLA52981,OGLA52982,OGLA52983,OGLA52984,OGLA52985,OGLA52986,OGLA52947,OGLA52948,OGLA52949,OGLA52950,OGLA52951,OGLA52952,OGLA52953,OGLA52954,OGLA52955,OGLA52956,OGLA51242,OGLA51243,OGLA51244,OGLA51245,OGLA51246,OGLA51247,OGLA51248,OGLA51249,OGLA51250,OGLA51251,OGLA51364,OGLA51368,OGLA51372,OGLA51376,OGLA51380,OGLA51384,OGLA51388,OGLA51392,OGLA51396,OGLA51400,OGLA51362,OGLA51366,OGLA51370,OGLA51374,OGLA51378,OGLA51382,OGLA51386,OGLA51390,OGLA51394,OGLA51398,OGLA51262,OGLA51263,OGLA51264,OGLA51265,OGLA51266,OGLA51267,OGLA51268,OGLA51269,OGLA51270,OGLA51271,OGLA51324,OGLA51328,OGLA51332,OGLA51336,OGLA51340,OGLA51344,OGLA51348,OGLA51352,OGLA51356,OGLA51360,OGLA51322,OGLA51326,OGLA51330,OGLA51334,OGLA51338,OGLA51342,OGLA51346,OGLA51350,OGLA51354,OGLA51358,OGLA51402,OGLA51252,OGLA51253,OGLA51255,OGLA51256,OGLA51257,OGLA51258,OGLA51259,OGLA51260,OGLA51261,OGLA51404,OGLA51408,OGLA51412,OGLA51416,OGLA51420,OGLA51424,OGLA51428,OGLA51432,OGLA51436,OGLA51440,OGLA51254,OGLA51406,OGLA51410,OGLA51414,OGLA51418,OGLA51422,OGLA51426,OGLA51430,OGLA51434,OGLA51438,OGLA53565,OGLA53569,OGLA53573,OGLA53577,OGLA53581,OGLA53585,OGLA53589,OGLA53593,OGLA53597,OGLA53601,OGLA53566,OGLA53570,OGLA53574,OGLA53578,OGLA53582,OGLA53586,OGLA53590,OGLA53594,OGLA53598,OGLA53602,OGLA53564,OGLA53568,OGLA53572,OGLA53576,OGLA53580,OGLA53584,OGLA53588,OGLA53592,OGLA53596,OGLA53600,Z00GP,Z00GQ,Z00GR,Z00GS,Z00GT,Z00GU,Z00GV,Z00GW,Z00GX,Z00GY,Z00HK,Z00HL,Z00HM,Z00HN,Z00HO,Z00HP,Z00HQ,Z00HR,Z00HS,Z00HT,Z00FU,Z00FV,Z00FW,Z00FX,Z00FY,Z00FZ,Z00G0,Z00G1,Z00G2,Z00G3,OGLA47587,OGLA47588,OGLA47589,OGLA47590,OGLA47591,OGLA47592,OGLA47593,OGLA47594,OGLA47595,OGLA47596,OGLA47597,OGLA47598,OGLA47599,OGLA47600,OGLA47601,OGLA47602,OGLA47603,OGLA47604,OGLA47605,OGLA47606,OGLA47567,OGLA47568,OGLA47569,OGLA47570,OGLA47571,OGLA47572,OGLA47573,OGLA47574,OGLA47575,OGLA47576,Z00H9,Z00HA,Z00HB,Z00HC,Z00HD,Z00HE,Z00HF,Z00HG,Z00HH,Z00HI,Z00I4,Z00I5,Z00I6,Z00I7,Z00I8,Z00I9,Z00IA,Z00IB,Z00IC,Z00ID,Z00GE,Z00GF,Z00GG,Z00GH,Z00GI,Z00GJ,Z00GK,Z00GL,Z00GM,Z00GN,OGLA48387,OGLA48388,OGLA48389,OGLA48390,OGLA48391,OGLA48392,OGLA48393,OGLA48394,OGLA48395,OGLA48396,OGLA48397,OGLA48398,OGLA48399,OGLA48400,OGLA48401,OGLA48402,OGLA48403,OGLA48404,OGLA48405,OGLA48406,OGLA48367,OGLA48368,OGLA48369,OGLA48370,OGLA48371,OGLA48372,OGLA48373,OGLA48374,OGLA48375,OGLA48376,Z00H8,Z00H6,Z00H7,Z00GZ,Z00H0,Z00H1,Z00H2,Z00H3,Z00H4,Z00H5,Z00I3,Z00I1,Z00I2,Z00HU,Z00HV,Z00HW,Z00HX,Z00HY,Z00HZ,Z00I0,Z00GD,Z00GB,Z00GC,Z00G4,Z00G5,Z00G6,Z00G7,Z00G8,Z00G9,Z00GA,OGLA50232,OGLA50599,OGLA50240,OGLA50233,OGLA50600,OGLA50241,OGLA50231,OGLA50598,OGLA50239,OGLA51276,OGLA51277,OGLA51278,OGLA51279,OGLA51280,OGLA51281,OGLA51288,OGLA51292,OGLA51296,OGLA51300,OGLA51304,OGLA51308,OGLA51286,OGLA51290,OGLA51294,OGLA51298,OGLA51302,OGLA51306,OGLA50280,OGLA50284,OGLA50288,OGLA50281,OGLA50285,OGLA50289,OGLA50279,OGLA50283,OGLA50287,OGLA50244,OGLA50248,OGLA50252,OGLA50245,OGLA50249,OGLA50253,OGLA50243,OGLA50247,OGLA50291,OGLA50256,OGLA50260,OGLA50264,OGLA51273,OGLA51274,OGLA51275,OGLA50257,OGLA50261,OGLA50265,OGLA51312,OGLA51316,OGLA51320,OGLA50255,OGLA50259,OGLA50263,OGLA51310,OGLA51314,OGLA51318,OGLA50272,OGLA50268,OGLA50276,OGLA51272,OGLA50273,OGLA50269,OGLA50277,OGLA51284,OGLA50271,OGLA50267,OGLA50275,OGLA51282'
)

then I coped that and pasted it into this:

select
    flat.cat_num,
    flat.scientific_name,
    specimen_part.part_name,
    specimen_part.condition,
    specimen_part.disposition,
    specimen_part.part_count,
    pp.barcode,
    getContainerParentage(pc.container_id) PLPath,
    specimen_part.part_remark,
    concat('https://arctos.database.museum/guid/',flat.guid,'/PID',specimen_part.collection_object_id) partID,
    pidrs.av as partidentifiers
from
    flat
    inner join specimen_part on flat.collection_object_id=specimen_part.derived_from_cat_item
    inner join coll_obj_cont_hist on specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id
    inner join container pc on coll_obj_cont_hist.container_id=pc.container_id
    inner join container pp on pc.parent_container_id=pp.container_id
    left outer join (
        select collection_object_id,string_agg(attribute_value,' | ') av from specimen_part_attribute where attribute_type='part identifier' group by collection_object_id
    ) pidrs on specimen_part.collection_object_id=pidrs.collection_object_id
where pp.barcode in (
    'OGLA52957','OGLA52958','OGLA52959','OGLA52960','OGLA52961','OGLA52962','OGLA52963','OGLA52964','OGLA52965','OGLA52966','OGLA52977','OGLA52978','OGLA52979','OGLA52980','OGLA52981','OGLA52982','OGLA52983','OGLA52984','OGLA52985','OGLA52986','OGLA52947','OGLA52948','OGLA52949','OGLA52950','OGLA52951','OGLA52952','OGLA52953','OGLA52954','OGLA52955','OGLA52956','OGLA51242','OGLA51243','OGLA51244','OGLA51245','OGLA51246','OGLA51247','OGLA51248','OGLA51249','OGLA51250','OGLA51251','OGLA51364','OGLA51368','OGLA51372','OGLA51376','OGLA51380','OGLA51384','OGLA51388','OGLA51392','OGLA51396','OGLA51400','OGLA51362','OGLA51366','OGLA51370','OGLA51374','OGLA51378','OGLA51382','OGLA51386','OGLA51390','OGLA51394','OGLA51398','OGLA51262','OGLA51263','OGLA51264','OGLA51265','OGLA51266','OGLA51267','OGLA51268','OGLA51269','OGLA51270','OGLA51271','OGLA51324','OGLA51328','OGLA51332','OGLA51336','OGLA51340','OGLA51344','OGLA51348','OGLA51352','OGLA51356','OGLA51360','OGLA51322','OGLA51326','OGLA51330','OGLA51334','OGLA51338','OGLA51342','OGLA51346','OGLA51350','OGLA51354','OGLA51358','OGLA51402','OGLA51252','OGLA51253','OGLA51255','OGLA51256','OGLA51257','OGLA51258','OGLA51259','OGLA51260','OGLA51261','OGLA51404','OGLA51408','OGLA51412','OGLA51416','OGLA51420','OGLA51424','OGLA51428','OGLA51432','OGLA51436','OGLA51440','OGLA51254','OGLA51406','OGLA51410','OGLA51414','OGLA51418','OGLA51422','OGLA51426','OGLA51430','OGLA51434','OGLA51438','OGLA53565','OGLA53569','OGLA53573','OGLA53577','OGLA53581','OGLA53585','OGLA53589','OGLA53593','OGLA53597','OGLA53601','OGLA53566','OGLA53570','OGLA53574','OGLA53578','OGLA53582','OGLA53586','OGLA53590','OGLA53594','OGLA53598','OGLA53602','OGLA53564','OGLA53568','OGLA53572','OGLA53576','OGLA53580','OGLA53584','OGLA53588','OGLA53592','OGLA53596','OGLA53600','Z00GP','Z00GQ','Z00GR','Z00GS','Z00GT','Z00GU','Z00GV','Z00GW','Z00GX','Z00GY','Z00HK','Z00HL','Z00HM','Z00HN','Z00HO','Z00HP','Z00HQ','Z00HR','Z00HS','Z00HT','Z00FU','Z00FV','Z00FW','Z00FX','Z00FY','Z00FZ','Z00G0','Z00G1','Z00G2','Z00G3','OGLA47587','OGLA47588','OGLA47589','OGLA47590','OGLA47591','OGLA47592','OGLA47593','OGLA47594','OGLA47595','OGLA47596','OGLA47597','OGLA47598','OGLA47599','OGLA47600','OGLA47601','OGLA47602','OGLA47603','OGLA47604','OGLA47605','OGLA47606','OGLA47567','OGLA47568','OGLA47569','OGLA47570','OGLA47571','OGLA47572','OGLA47573','OGLA47574','OGLA47575','OGLA47576','Z00H9','Z00HA','Z00HB','Z00HC','Z00HD','Z00HE','Z00HF','Z00HG','Z00HH','Z00HI','Z00I4','Z00I5','Z00I6','Z00I7','Z00I8','Z00I9','Z00IA','Z00IB','Z00IC','Z00ID','Z00GE','Z00GF','Z00GG','Z00GH','Z00GI','Z00GJ','Z00GK','Z00GL','Z00GM','Z00GN','OGLA48387','OGLA48388','OGLA48389','OGLA48390','OGLA48391','OGLA48392','OGLA48393','OGLA48394','OGLA48395','OGLA48396','OGLA48397','OGLA48398','OGLA48399','OGLA48400','OGLA48401','OGLA48402','OGLA48403','OGLA48404','OGLA48405','OGLA48406','OGLA48367','OGLA48368','OGLA48369','OGLA48370','OGLA48371','OGLA48372','OGLA48373','OGLA48374','OGLA48375','OGLA48376','Z00H8','Z00H6','Z00H7','Z00GZ','Z00H0','Z00H1','Z00H2','Z00H3','Z00H4','Z00H5','Z00I3','Z00I1','Z00I2','Z00HU','Z00HV','Z00HW','Z00HX','Z00HY','Z00HZ','Z00I0','Z00GD','Z00GB','Z00GC','Z00G4','Z00G5','Z00G6','Z00G7','Z00G8','Z00G9','Z00GA','OGLA50232','OGLA50599','OGLA50240','OGLA50233','OGLA50600','OGLA50241','OGLA50231','OGLA50598','OGLA50239','OGLA51276','OGLA51277','OGLA51278','OGLA51279','OGLA51280','OGLA51281','OGLA51288','OGLA51292','OGLA51296','OGLA51300','OGLA51304','OGLA51308','OGLA51286','OGLA51290','OGLA51294','OGLA51298','OGLA51302','OGLA51306','OGLA50280','OGLA50284','OGLA50288','OGLA50281','OGLA50285','OGLA50289','OGLA50279','OGLA50283','OGLA50287','OGLA50244','OGLA50248','OGLA50252','OGLA50245','OGLA50249','OGLA50253','OGLA50243','OGLA50247','OGLA50291','OGLA50256','OGLA50260','OGLA50264','OGLA51273','OGLA51274','OGLA51275','OGLA50257','OGLA50261','OGLA50265','OGLA51312','OGLA51316','OGLA51320','OGLA50255','OGLA50259','OGLA50263','OGLA51310','OGLA51314','OGLA51318','OGLA50272','OGLA50268','OGLA50276','OGLA51272','OGLA50273','OGLA50269','OGLA50277','OGLA51284','OGLA50271','OGLA50267','OGLA50275','OGLA51282'
    )

sql_dlm_20240813100842125_669.csv

[campmlc]

I think the problem is that the tools exist partially in between the main search and object tracking. I need a similar tool to find part locations for a list of barcodes that are not in the same container. We can find GUID FROM BARCODE in the Barcodes and Containers Tools Menu. Then we have to leave object tracking and do a search on the guids. Then we have to go to the Search results Tools: View and Download: Parts to see ALL the parts for these guids, and then somehow filter them back down to the parts we originally wanted specific location and disposition info on.
It would be great if we could get a tool in object tracking to find PART FROM BARCODE that would provide the information in a similar format to Parts View and Download, but only for the requested list of barcodes. Or we somehow combine these tools?

[campmlc]

@happiah-madson happy to go over the tools used to find samples for a loan. But I start with the GUID request and work backwards to parts and barcodes. Sounds like you are wanting to do the reverse, which is more challenging because of above.

[happiah-madson]

First I used listerizer to get a list of barcodes from a column.

omg, whaaaaaaaaat!!!! We've been doing this with =textjoin in excel! huge fan of this.

Also, this .csv is exactly what I was looking for. Thank you!! I will put on my thinking cap and ruminate about whether there is a logical UI for this, but I was low key expect to be a SQL thing.

[dustymc]

Done?

[campmlc]

Could we pretty please add this as a tool to Barcodes and Containers? It could be "Barcode: Part Location", and it could be in the same format as the existing tools https://arctos.database.museum/tools/barcode2guid.cfm and https://arctos.database.museum/DataServices/getContainerFromBarcode.cfm, except that it would be nice if all these were standardized to allow an input csv file as well as copy/paste csv list. The output would be exactly what is shown above in sql_dlm_20240813100842125_669.csv, except I request that Guid be added in addition to catnum.
?
This would be SOOOO helpful