Better way to count quantity from parts?

[mkoo]

Guessing this is a bug OR it could just be becuase flat is getting a make-over OR I need a new SQL or summary stat
Derek had requested this last year I believe.

Expected behavior: In the WriteSQL, I should get a sum of counts with
select sum(individualcount) from flat where guid_prefix ='MVZ:Fish'

but get zero

Tried a query via search page: https://arctos.database.museum/search.cfm?guid_prefix=MVZ%3AFish and in Results selected 'individual count' and no values show up

But it should include this, no? (from MVZ:Fish:1)

[dustymc]

See #4032 (it's an Attribute, because a bunch of attempts to calculate it failed), #5520 (there could be a bot, if some way to calculate could be specified and resources allow some tolerable schedule), and https://github.com/ArctosDB/arctos/issues?q=is%3Aissue+%22individual+count%22 (this has come up a bunch of times - often by breaking something! - and I'm very open to just about anything).

[mkoo]

hmm, how about just some sql help? See my line above which worked last year or so-- is that term ('individualcount') changed now? I probably just need a csv that i can sum myself unless you are saying something else-- is is available as a value?

Longterm-- have as a stats service with a light updating schedule?

[dustymc]

Yes it's all changed from ~a year ago. (It was making some sort of unjustified collection-type-based assumptions, now it's just saying NULL when it knows nothing.)

Longterm

I think I can say with some confidence that what we're doing isn't great! There's been lots of discussion, but I'm not sure anyone ever really understood anyone else so starting that up again doesn't seem entirely unreasonable. Killing the attribute and magicking a value for flat from parts seems like the proper solution from here (may be a limited view), but distilling that down to something that can be expressed with SQL has been elusive.

That discussion might get a lot more approachable if #4707 was really resolved, but it keeps wandering off as well.

I don't know how you want to calculate individualcount (seems to vary wildly across collections) but here's a guess:

select sum(coll_object.lot_count) from 
coll_object 
inner join specimen_part on coll_object.collection_object_id=specimen_part.collection_object_id
inner join cataloged_item on specimen_part.derived_from_cat_item=cataloged_item.collection_object_id
inner join collection on cataloged_item.collection_id=collection.collection_id
where
collection.guid_prefix='MVZ:Fish' and
specimen_part.part_name='whole organism'
;

  sum  
-------
 17717
(1 row)

[mkoo]

Longterm is consulting with collections that use lots to help inform #4707

Shortterm-- nothing fancy! that works (good guess since there are not a lot of parts and bits) Just objects attached to a record (I know that has nuances too)
Thanks!

[DerekSikes]

Killing the attribute ...

does that mean getting rid of the individual count attribute? I'll reiterate that it's nuts to have to keep track of how many spiders are in a vial in two different places so am in favor of this being kept track of in only 1 place.

[mkoo]

Killing the attribute ...

does that mean getting rid of the individual count attribute? I'll reiterate that it's nuts to have to keep track of how many spiders are in a vial in two different places so am in favor of this being kept track of in only 1 place.

No not at all but only because I dont see an universal part to count across all the collection types @DerekSikes @dustymc I see what the problem is -- many attributes have quantity so tallying up the count is part attribute dependent. This is similar to our burgeoning attribute code table and how to access the data in some ways.

the SQL works of course but someone else would want to replace 'whole organism' with vials or something.

This is a common enough request that we should find a way to fish out (pun alert) the quantity from the json blob.
Ex. MVZ:Fish:101 's parts blob:
[{"id":28715293,"sf":null,"pn":"whole organism","cd":"good","lc":7,"dp":"in collection","rk":null,"bc":null,"cp":null,"pa":[{"at":"preservation","av":"ethanol, 100%","au":null,"dd":"2021-07-01","dr":null,"ar":"Added from original part whole organism (100% ethanol); 100% ethanol.","dm":null}]}]

give me "7"!

I'm going to rename this thread as there's a better way to deal with this! (also flat does have a count just not obvious)

[dustymc]

tallying up the count is part attribute dependent

... and loan status, and disposition, and maybe moon phase - IDK, but it's more than I could keep up with.

If there's some actionable, affordable recipe that survives exposure to reality, I can probably find a way to use it. (The bot option might allow very liberal ideas of 'affordable,' and #4707 should have significant impacts on cost.) If not, the attribute will remain available (unless everyone who cares about this can settle on a recipe, I suppose).

[dustymc]

Don't think there's anything to do here?