Step 3: Depends on the type of info you want to edit, choose the corresponding tab below:
Click on ‘Go to molecule’ button to edit structure and structural info (MW, Formula, etc.)
Click on Edit button () to edit the info. (Or double click on any fields inside the Edit package area.
Search Chemicals
Searching Chemicals in Existing Database(s)
Make sure “Chemical Search” mode is on
Add a single chemical
Add Storagelocation if not exists: see
For chemicals from existing database
To change the lab related (person responsible, amount, etc.) info:
Click on Edit button () to edit the info. (or double click on any where around these info)
Step 1: choose what type of search: e.g “names, cas, supplier #”
Step 2: choose options: e.g. allwords,contain(~similarto),exact
Step 3: type is your search query, this can be name, CAS, supplier #, best with CAS and name
Step 4: choose database(s): choose one, or several (holding Ctrl while Left-click, or All databases (default)
Step 5: click Search
Video demonstration:
Searching Chemicals in Supplier Mode
Note: when Supplierssearch mode is on (see add chemical via suppliers), Open Enventory search includes chemicals from the local database(s) first
Supplier search mode
Search Chemicals using Structure
The default drawing program in Sciformation Vectormol. You can choose a different program by going to: Settings/Settings/Molecule editing/ and change “Structure Drawing Program” to “ChemDoodle (Javascript)” (recommended).
Step 1: choose search with Structures
Step 2: draw structure
Step 3: choose options: contain, similarto,exact, etc.
Step 4: click search
If it appears that the search is not accurate in ChemDraw plugin, you have to active the ChemDraw plugin:
a) Right click on the background of the chemdraw drawing window
b) Help/Activate my ChemDraw Plugin
c) Input your activation info the same (from email) as what you did for installation of Chemdraw
TODO
Step-by-step instruction here
For chemicals from suppliers
Step 1: change to supplier search and search chemical
Step 2: choose Createnewmoleculebasedondata
Step 3: add more info: amount, person responsible, etc.
This clip below is show the process of adding a new chemical from suppliers:
For custom chemicals or chemical that cannot be found from suppliers list, use Method below.
Add a single chemical from scratch
Step 1: choose New
Step 2: use CAS number (if exists) to pull info from suppliers
Step 1: search for the chemical of interest (see Search chemicals section)
Step 2: click Borrow button
Return a chemical
Step 1: go to Borrowedchemicals
Step 2: click Return button
In Terminal Mode
Borrow a chemical for users of that database
This function only works for users and chemical containers in the same database. For cross-databases (guest) borrowing, please see the below.
❗Computer that connected to the scanner must be ON
❗Barcode terminal window must be ON and ACTIVE, if not log in with terminal username and pass or contact your local admin
❗ Active barcode terminal look like below:
Step 1: scan your own barcode
Step 2: scan chemical barcode
Watch the terminal window to make sure the info is transmitted/recorded.
Borrow a chemical for guest users
Currently, this function is only available in 2020-09-26 or above.
Step 1: Follow the following guide to create a guest account
Step 2: Follow the step above in
Return a chemical
❗ Computer that connected to the scanner must be ON !!! Barcode terminal window must be ON and ACTIVE
Action: scan borrowed chemical barcode
Note: you don’t need to scan the your own barcode beforehand. The scan on a borrowed chemical will return the chemical.
Note: if the chemical is free and nobody has logged in, scanning the barcode of that chemical will bring up that chemical’s info in the system.
Delete multiple chemical containers
This function only available in .
Move multiple chemical containers
Step 1:
Login into OE and extract the list of chemical containers that need to be move. You don't need all of the details for each container, just:
- CAS number
- Names
- Chemical container barcode: most important thing as this is the lookup point
Edit Multiple Chemical Containers
If your institution has this option turned on, you can edit a list of chemicals automatically.
To know if this option is turned on, please log into Open Enventory with an admin account, then choose Settings (Top menu). Then if you see Import and Edit via Text File (Left menu) then this function is turned on for your institution.
To turn on this function, please see this page:
Users
User Permissions
Create a New User
Step 2
Open and add a new column with the name of the new location for each container. New LOCATION NAME MUST BE EXACT or OE would create a new storage location.
Skip this step if you want to move all of the chemical containers into the exact same location.
Step 3
Login into OE with an admin account if you have not done so in step 1
In Step 5 of Edit Multiple Chemical Containers , you would only need to choose the column for CAS number, Name and most importantly, chemical container barcodeand **chose the correct column for the new location for the storage_name
If you want to change all chemical container into the same new storage, simply typing the EXACT STORAGE NAME in the input instead of choosing the column for the storage_name
You first need to log in into Open Enventory with an account with admin permission
then:
Step 1: Choose Settings
Step 2: choose Import and Edit via Text File
Step 3: on the Import page:
Choose package in the table field:
Click on Choose file button to choose your prepared file
Choose the number of Lines to preview
Choose number of lines to skip (header lines)
Step 4: click the green check mark to start uploading the file
Step 5: match the appropriate column for each info
Most importantly: match Barcode for chemical container with the correct column on your prepared file. This is how Open Enventory use as ID of the container.
For chemical containers, it checks if the barcode exists in the current database, and not disposed:
If Yes, it will edit the info of that container.
If No (or no barcode in the data entry), it will add the entry as a new container.
Match the rest of the info in the red rectangle area as desired:
Preview of the data:
Step 6: click the green check mark
A similar screen as screenshot below should appear after success uploading
Example screenshot of successful uploading of multiple users
Do NOT create a user with username starting with "auto". Any user with this kind of username will be deleted when linking databases.
You first need to log in into Open Enventory with an account with admin permission
Step 1: Choose Settings
Step 2: Choose Users
Step 3: Click on the plus side button➕
Step 4: Add info such as username, password.
Username needs to be unique across the whole server, not just a single database.
Current password requirement is:
7 or more character
include at least 1 number
Step 5: add additional info such as name, locations
Step 6: Choose the appropriate permission
Choose one of the predefined permissions or "User defined" for customized permission
Step 7: Click on the green check mark: ✅
You should see the new user on the new screen if success:
Create a Guest Account
borrowing of chemicals by users from outside of the group (guests)
Currently, this function is only available in KV's version of OE2020-09-26 or above.
Details
The idea is each group should have one ‘user’ account designated for external users (users from outside of the group, guests). When someone outside of the group comes and borrowschemical containers, this account barcode should be scanned and following by the container barcode. This specific account (made by turning on a specific permission setting External borrow in Predefined permission) is set so that during checking out of a chemical container by this account, a pop-up window would open and ask for the specific info (borrower’s name, group, contact info, and the group member assisting during checkout). This info is then saved into the history entry of that exact checkout event.
Guide
Step 1: Create a new user, ‘guests_<database_name>’ for example.
While creating this user, choose the following ‘External borrow’ in the ‘Predefined permissions’
Step 2: Use this new account barcode in the OE barcode terminal. During checkout, a popup window like this should open to ask for more info:
The provided info will be appended to the history entry for that particular checking out event. Example:
Screen after successful creation of a new user
Choosing 'External borrow' in 'Predefined Permissions' for guests account
Popup window asking for more info during checking out of a chemical container by an external user
Chemical Container History entry log showing the provided contact info for guest users.
Storages/Locations
Create a storage location
The user must have Storage Administrator permission in order to create new storage
Step 1: After logging into Open Enventory, click on Settings on the top bar navigation.
Step 2: click on Storages on the Left.
Step 3: click on the ➕ button
Step 4: after adding the info, click the checkmark ( ✅ ) button
Create a list of storage
TO DO: to be constructed
Modify a single storage's information, permissions
TO DO: to be constructed
Modify info for a list of storage
TO DO: to be constructed
Merge two storage locations
TODO: add line-by-line instruction
Delete a storage
TO DO: to be constructed
Create Multiple Users
Do NOT create a user with username starting with "auto". Any user with this kind of username will be deleted when linking databases.
You first need to log in into Open Enventory with an account with admin permission
Step 1: Choose Settings
Step 2: Choose Import
Step 3: download the User template and fill out the sheet according to the instruction.
This is a copy of the Excel User Import template. It is better to download the template from Step 2 above
Step 4: on the Import page:
Choose user in the table field:
Click on Choose file button to choose your prepared excel user import file from step 3
Choose the number of Lines to preview
Choose number of lines to skip (header lines)
Step 5: click the green check mark to start uploading the file
Step 6: match the appropriate column for each info
Step 7: click the green check mark
A similar screen as screenshot below should appear after success uploading
The following should be changed to your own setting:
- adress: your Open Enventory web address
- databasename
- username
- password
Keep in mind that the password for Terminal user will be exposed in your bookmark link above
Change Chemical Location
Go to an active Terminal on your Open Enventory page. It should look similar to the following page.
Moving one single chemical
Scan user barcode (will say you are logged in)
2. Scan chemical container (will now show chemical information)
3. Scan new location barcode (will now say “updated and location field will show new location”)
Moving multiple containers to the same location
Check the 2 boxes “Stock-keeping mode” and “Set storage for all following containers” (the “Set storage for all following containers” will show up after you check “Stock-keeping mode”). Note: you can check by mouse or use the “Barcode for stock-keeping mode” list from Settings menu.
2. In typical terminal window of database of interest. Login by scanning user barcode
3. Scan the 1st chemical containers
4. Scan the new location. The window will show the new location and say the 1st container “… updated”
5. Scan the 2nd containers, 3rd container, 4th container, etc.
6. When Done, uncheck “Set storage for all following containers”
TODO
To be added to this tutorial
• How to add chemical that is already in the database
• How to add MSDS sheet
• How to delete chemicals (3 methods:
- normal log-in
- barcode scanner
- Mass deletion (admin account only)
• How to print chemical barcode
• How to change location (and other info) of many chemicals using barcode terminal
Chemicals
Delete a chemical container
TO DO: to be constructed
Database Backup and Restore
This is procedure to backup Open Enventory database and restore it later on the same server or migrating to a different server
For traditional LAMP stack
This guide is for Linux environment with Apache2, PHP and MariaDB (MySQL) (LAMP) stack:
Modify User Permission, Information
You first need to log in into Open Enventory with an account with admin permission
Step 1: Choose Settings
Inactivate a User
Different than , this will not remove the user from the database. It only disable the user from logging into the database
You first need to log in into Open Enventory with an account with admin permission
Step 1: Choose Settings
Change User Password
Change user password using an admin account
You first need to log in into Open Enventory with an account with admin permission
Get Information on a Chemical
Go to an active Terminal on your Open Enventory page. It should look similar to the following page.
Without any user logging in, scanning a chemical container barcode will bring up that chemical info.
Chemical Inventory
Save or Backup Database(s)
For chemicals and lab journal databases:
1. In Terminal window, type the following command:
Replace [italic_text] (including the square bracket) with specific info:
[user]: in most cases, use root (recommended)
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename
If you want to compress the SQL backup files, you can use this command instead:
| xz was added for compressing SQL file to .xz file
Make sure that you have .xz at the end of the file as above.
.xz files seem to have the best compression vs bzip2 and gzip for SQL files. You can see more info and :
For example usage of xz, see this .
2. Type in password for the user above and then Enter (nothing will show when you are typing the password)
For users info:
1. In Terminal window, type the following command:
Replace [italic_text] (including the square bracket) with specific info:
[user]: in most cases, use root (recommended)
[Path] and [backup_filename] : any path and filename
While this approach might work well in case of same MySQL or MariaDB version, if you migrate or upgrade SQL, you want to follow the instruction here for better users backup:
2. Type in password for the user above and then Enter (nothing will show when you are typing the password)
Restore
Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server
If you have compressed the SQL file as instruction above, you will need to decompress the file first.
This is for compressed file with .xz extension:
-v : verbose, give more info
k : to keep the compressed file, this will generated the uncompressed file .sql
Step 4: use ssh and terminal, login as terminal root and run:
Replace [italic_text] (including the square bracket) with specific info:
[database-name]: use the name of the database that you want to backup
[/path/backup-database]and [/path/backup-user]: any path and filename
Step 5: go to OE on web browser and login with mysql root account.
If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.If you don't know the old mysql root password, follow this tutorial to reset mysql root password.
Step 6: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.
Again, if you have migrate from MariaDB < 10.4 to MariaDB 10.4+, it might not work and we have to change the command in step 3 above. The reason is because the mysql.user table is deprecated in MariaDB 10.4. You can read more about it here.
For XAMPP users
While the general steps are similar, the exact paths to the executable command are different for Mac OS and Windows using XAMPP, please follow the corresponding guides below:
Save or Backup Database
For chemicals and lab journal databases:
1. In Terminal window, type the following command:
Replace [italic_text] (including the square bracket) with specific info:
[user]: in most cases, use root (recommended).
2. Type in password for the user above and then Enter (nothing will show when you are typing the password)
For users info:
1. In Terminal window, type the following command:
Replace [italic_text] (including the square bracket) with specific info:
[user]: in most cases, use root (recommended).
2. Type in password for the user above and then Enter (nothing will show when you are typing the password)
Restore
Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server
Step 2: if you have not done so.
Step 3: use ssh and terminal, login as terminal root and run:
Replace [italic_text] (including the square bracket) with specific info:
[database-name]: use the name of the database that you want to backup
Step 4: go to OE on web browser and login with mysql root account.
If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.
If you don't know the old mysql root password, follow this tutorial to .
Step 5: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.
Again, if you have migrate from MariaDB < 10.4 to MariaDB 10.4+, it might not work and we have to change the command in step 3 above. The reason is because the mysql.user table is deprecated in MariaDB 10.4. You can read more about it .
Save or Backup Database
For chemicals and lab journal databases:
1. Open a Window Powershell window, type the following command:
Step 2: Choose Users
Step 3: click on Details button ( ) on the same row of the user you need to change password
Step 4: click on Edit button () (or double click anywhere on the info window)
Step 5: change desired info or permission
Step 6: Click on the green check mark: ✅
Step 2: Choose Users
Step 3: click on Details button ( ) on the same row of the user of interest
Step 4: click on Edit button () (or double click anywhere on the info window)
Step 3: click on Details button ( ) on the same row of the user you need to change password
Step 4: click on Edit button () (or double click anywhere on the info window)
Step 5: type in the new password and repeat
Step 6: Click on the green check mark: ✅
Change one's own password
First, open up Open Enventory in any web browsers. Your screen should look as follows.
After logging in you should see the general window for searching chemicals.
Click on the “Settings” tab top and center of the page (outlined with a red box below).
This will change the left side bar. At the top of the left side bar you should see “Change password” Click here (the button is outlined with a red box below).
Your screen should have two boxes as shown below. Fill in both boxes press the “Change password” button right next to the text fields and your password has been changed.
Best Practice for SQL Users Backup
While the quick user backup as this one-line commandmysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql might work when you have same version for backup and restore SQL. In most cases, you want to implement something similar to the following bash script
Backup Users
Step 1: On your server, in terminal, prepare the following bash file using vi (or vim or any text-editor of choice)
Database
Create a database
1. Go to your Open Enventory Login page on your web browser
2. On the Login page, fill in the following info:
- Database: name for the new database
- Username: root
-
mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql
mysql -u root -p [database-name] < [/path/backup-database].sql #for database restore
mysql -u root -p mysql < [/path/backup-user].sql #for users restore, this might not work for mariabd 10.4+
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename
[Path] and [user_backup_filename] : any path and filename
[/path/to/backup-database]: any path and filename
Replace [italic_text] (including the square bracket) with specific info.
[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename
In the command above, this part is for the default XAMPP set up. If some error happens, you should find the executable file inside XAMPP folder in your system. You can try to follow this link.
2. Type in password for the user above and then Enter (nothing will show when you are typing the password)
For users info:
1. In Terminal window, type the following command:
Replace [italic_text] (including the square bracket) with specific info:
[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename
2. Type in password for the user above and then Enter (nothing will show when you are typing the password)
Restore
Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server
Step 3: use ssh and terminal, login as terminal root and run:
Replace [italic_text] (including the square bracket) with specific info:
[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename
Step 4: go to OE on web browser and login with mysql root account.
If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.
Step 5: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.
Again, if you have migrate from MariaDB < 10.4 to MariaDB 10.4+, it might not work and we have to change the command in step 3 above. The reason is because the mysql.user table is deprecated in MariaDB 10.4. You can read more about it here.
Step 2: Paste in the content of the file as follow:
PASSWORD : change to your root password or the password of the user that you use for the backup process. If you don't use root then change root on line 8 to your user
path : use your specified path to the output file
oe_backup-user-$(date +\%Y\%m\%d).sql : the output file will have the current date concatenated at the end of the file
This script above also not save oldroot user info or empty users. You can change it by modify the code on line 12
Step 3: make sure you (or the user that run the mariadb_user_backup.sh file has the sufficient permission (i.e. chown) and make the script file executable:
Step 4: Execute the script file to obtain the user backup file
If your new SQL server is newer than your old one, after importing both database(s) and users, you can run the following command in terminal to upgrade MySQL:
c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql
c:\Programs\XAMPP\mysql\bin\mysql -u root -p [database-name] < [/path/to/backup-database].sql #for database restore
c:\Programs\XAMPP\mysql\bin\mysql -u root -p mysql < [/path/to/backup-user].sql #for users restore, this might not work for mariabd 10.4+
vim mariadb_user_backup.sh
mariadb_user_backup.sh
#!/bin/sh
# This bash script is used to save users and permission from mysql database.
# Ref: https://stackoverflow.com/a/57878326
# explanation: https://wisdmlabs.com/blog/migrate-mysql-users-one-server-another/
HOSTNAME="localhost"
logininfo="-u root -pPASSWORD"
outfile="/path/oe_backup-user-$(date +\%Y\%m\%d).sql"
# The line below will save all users except 'root', empty name and specified ones
mysql ${logininfo} -B -N -e "SELECT CONCAT('\'',user,'\'@\'',host,'\'') from user where user != 'root' AND user != '' AND user not like 'auto%'" mysql | \
while read uh
do
echo "SHOW GRANTS FOR ${uh};"
done | mysql ${logininfo} -B -N | sed -e 's/$/;/' > ${outfile}
chmod u+x mariadb_user_backup.sh
./mariadb_user_backup.sh
mysql_upgrade # Upgrade MySQL data
systemctl restart mariadb # Restart mariadb
Password
:
your mysql
root
password
Change `database-name` in this picture to name of the new database that you want to create
3. Open Enventory will then create a new empty database
Connect databases
This allows for users in one database to see chemicals in another database and/or vice versa.
Note: in the default setting, OE only allows user in one database to see if other databases have chemical(s) but not revealing the chemical's location.
If you want to let users in one database to be able to see location of chemicals in another database, after connecting the databases, please follow the instruction in this link.
1. Go to your Open Enventory Login page on your web browser
2. Log into your database of interest using root as Username and your mysql root password as Password
Change "database-name" in this picture to name of the database of interest
3. Go to Settings on Top menus:
4. On the left side menu, choose Manage databases :
5. There are two types of connections:
2-way connections: users in one database can see/search chemicals in another database AND vice versa
1-way connection: users in one database can see/search chemicals in another database BUT NOT vice versa)
choose the desired connection and follow the instruction below.
Step 1: Choose as databases that you want to connect (hold Ctrl while choosing). You can choose more than 2. Click select all if you want to connect all databases
"romo" and "romo_test" are 2 databases that will be connected
Step 2: Click the button Connect selected databases
Step 3: Click the green check mark ✅
Using the rubric table, the databases with name in the bold format and listed horizontally in the first row of the table will be able to see/search chemical in database(s) (list vertically in the first column) that has checked check box
For example:
In the picture below, users in romo database will be able to see/search chemicals in romo_test database BUT users in romo_test database will NOT be able to see/search any chemicals in romo database.
Step 1: using the info above, make the connection of interest by checking the check boxes
Step 2: Click the green check mark ✅
Done! You can now search chemicals in other database(s) in the search page
Update (2020-02-01): KV's version of OE (on and after 2020-02-01) now accepts:
Excel files (*.xlsx and *.xls)
Comma-separated files (csv)
Tab-separated files (*.txt).
Prepare the Excel file, an example might look similar as below. A few important things:
Columns that are highlight in blue are important info.
You don't need all of the info that show in this example.
2. Login into your database using root user or any users with admin permission
3. Go to Settings
4. Go to Import Tab-separated text file (or Import in KV's version)
5. Choose Browse… and choose the exported text file.
Note: in Lines to skip, put 1 if you have a header rows, if not, choose 0
6. Click green check mark
7. Screens as below should show up, you can scroll down to see if they show up correctly (info appear correctly in each row)
8. Choose Column X for each content. If you choose None, that column will not be imported. You can also add a default value for each column that will apply for ALL items being imported.
Notes:
CAS number: most important info, OE will use this to look up other info (structures, MW, safety data, etc) from this cas #
9. Click green check mark. Screen similar as below should show. Note: line X:… will tell you which lines in the text file is being imported.
10. Depend of how many chemical containers are imported, this might take a long time, so please be patient.
Demo video
Author: Nathanyal Truax, Baylor University. You can check out his for other useful videos.
Empty cells are OK.
The amount need to be “xx Y” with xx is the amount and Y is the unit.
For unit, it is best to have all in lower case, for example, “ML” should be either “ml” or “mL”. It is best to convert all unit to lower case (use lower() command in excel).
Double check the CAS number column, Open Enventory uses CAS number as primary source to look up chemical info from the internet.
See step 9 for more info about each column in the excel sheet.
Name
: not too important
Empirical formula: do not import this since OE will use this to find the closest chemicals if CAS# is not present or wrong.
Amount: = size of the bottle + unit (format should be #+space+unit, for example: 5 g, 125 ml, 1 kg, etc)
Still available: the amount that left in the bottles (OK if not imported)
Locations: important. Note: Each location will have its own barcode
Compartments: = sub-location. Note: each compartment does NOT have its own barcode
Barcode: if you already have an existing barcode system, add it here.
Suppliers: = suppliers
Date open: could be in European style (dd.mm.yyyy) or American style (mm/dd/yy, etc.)
Go to an active Terminal on your Open Enventory page. It should look similar to the following page.
Scan user barcode (will say you are logged in)
Scan chemical container (will now show chemical information)
Scan barcode for “Delete” (will say “data set will be deleted in 3 seconds, then deleted)
Notes: If you manually change data (not using the scanner) you will have to click “update” after changing the data manually (mouse and keyboard).
Terminal Mode
All of the guides in this section require you to be in an active Terminal. Please contact your site admin for specific details on how to access a terminal page on Open Enventory.
A terminal site should look similar to this:
If you are an admin and need to set up a Terminal, please see the following page:
