Database for inventory managment in Life Science Lab

May 7, 2020 2:09 AM

Our life science lab is closed so my boss decided to overhaul our inventory managment. At the moment we are only one group, but after the apocalypse is over this should expand to several more. We would like everybody to use the same system so we can pool our ressources more efficiently.

At the moment we are using what I guess everyone is using, a bunch of Excel files. We have a license for an electronic labbook software, Labfolder, which has material database functionality, but is too limited. I recently took a course in SQL, but it was focused only on querying existing databases.

We probably want to set up a SQL server, connect to it via Excel to do analysis on our spending or to display the data. What I don't know is how to get the data into the database. Should I use Access for that? I have heard not so great things about it, but I guess it would be the least painfull to set up and get people used to. And the dataset is quite small, atm there are about 800 orders per year.

How would you do that? Stick witch Excel? Access? Some other software?

posted by SweetLiesOfBokonon to Computers & Internet (5 answers total)

As much as it pains me to say it (as a professional developer who really doesn’t like most of the microsoft ecosystem), this is the kind of thing that sharepoint does really well. If your company has an office365 subscription, you may have sharepoint included already. It does a pretty good job of allowing you to define a form and present that form as a list, and at the data size that you’re talking about, it would probably work pretty well.
posted by jenkinsEar at 5:33 AM on May 7, 2020

Came in to suggest sharepoint + PowerBI, particularly if your level of experience is a course in SQL. $10/usr/month for PowerBI report creators, and free to view. If your lab is associated with a .edu, you may even get a cheaper rate on PowerBI.
posted by bfranklin at 6:09 AM on May 7, 2020 [1 favorite]

It's hard to tell from your question what specific functionality you need and if you're committed to using something generic that you can customize, but there are non-ELN database options tailored for laboratories. Our lab uses Quartzy, which is a web-based database system with both both free and paid versions. One of the nice things about it is that it's pretty easy for individual users to upload Excel files (as long as they're in the correct format) and add say, 50 new E. coli strains to the general database all in one go so that they're searchable by everyone. We don't use it for chemicals, but it catalogs all our biological samples and oligonucleotide orders - basically everything that should be permanently stored in the freezers.
posted by deludingmyself at 10:04 AM on May 7, 2020 [1 favorite]

I should say though: it's totally possible to use Quartzy for tracking all kinds of lab inventory. Their business model is to try to grab a share of laboratory purchasing by integrating into inventory management. We just mostly use the tool to not re-clone the same strains or re-order the same oligos, because that's way more painful than accidentally ordering a third case of pipettes or something.
posted by deludingmyself at 10:08 AM on May 7, 2020

Quartzy is pretty rad. It is very easy to add items--you start typing in the product number and if the product exists in its database it auto-fills the rest. Obviously you can create your own product entries as well. It makes re-ordering and adding and removing items simple, and keeps your inventory history so you can look up that one reagent you used three years ago for that one experiment that you now need to repeat. It also requires very little technical expertise to use, so you won't run into the issue of the lab member who knows SQL moving on and leaving the lab bereft.
posted by Anonymous at 4:16 PM on May 7, 2020