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 - Password: your mysql root password

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.

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

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.

Done! You can now search chemicals in other database(s) in the search page

Searching Chemicals in Existing Database(s)

Step 1: choose what type of search: e.g “names, cas, supplier #”

Step 2: choose options: e.g. all words, contain (~ similar to), 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

Search Chemicals using Structure

Step 1: choose search with Structures

Step 2: draw structure

Step 3: choose options: contain, similar to, exact, etc.

Step 4: click search

For traditional LAMP stack

This guide is for Linux environment with Apache2, PHP and MariaDB (MySQL) (LAMP) stack:

Save or Backup Database(s)

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

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:

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 (optional):

Step 4: use ssh and terminal, login as terminal root and run:

Step 5: go to OE on web browser and login with mysql root account.

Step 6: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

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:

1. 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.

   • 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.

   • Location column: remove all commas ( , ) if there is any. Dashes ( - ) are ok. Update (2020-02-01): for KV's version of OE, if you updated to version after 2020-02-01, this has been fixed.

2. Copy all of the cells in this file (Ctrl-C for Windows, Cmd-C for Mac) and paste (Ctrl-V for Windows, Cmd-V for Mac) them into Notepad (Windows) or TextEdit (Mac)

3. Save this new file as your-file-name.txt. The “.txt” part is important.

Save As… the file using Window Formatted Text (for Mac OS)

For Windows, Office 2016 of Office 365, use “Text (Tab delimited) (*.txt)” option:

4. Login into your database using root user or any users with admin permission

5. Go to Settings

6. Go to Import Tab-separated text file (or Import in KV's version)

7. Choose Browse… and choose the exported text file.

8. Click green check mark

9. Screens as below should show up, you can scroll down to see if they show up correctly (info appear correctly in each row)

10. 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.

11. 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.

12. Depend of how many chemical containers are imported, this might take a long time, so please be patient.

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)

Step 2: Paste in the content of the file as follow:

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

Restore Users

Similar to the guide .

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:

Step 1: Search chemical in the database:

Step 2: Click on the structure

Step 3: Depends on the type of info you want to edit, choose the corresponding tab below:

For chemicals from existing database

In Inventory Mode

Borrow a chemical

Step 1: search for the chemical of interest (see Search chemicals section)

Create a storage location

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

Modify a single storage's information, permissions

Modify info for a list of storage

Merge two storage locations

Delete a storage

Go to an active Terminal on your Open Enventory page. It should look similar to the following page.

1. Scan user barcode (will say you are logged in)

2. Scan chemical container (will now show chemical information)

3. 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).

See Borrow and Return Chemicals in Terminal Mode

SETTING FOR BARCODE TERMINAL USER

1. To have a barcode terminal client (stand-alone computer), create a user with permission as below and log in into OE using this user.

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:

User Permissions

Create a user

Change user password

Change user password using an admin account

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.

Create a list of users

Modify a user's information, permissions

Delete a user

Inactivate a user

Go to an active Terminal on your Open Enventory page. It should look similar to the following page.

Moving one single chemical

1. 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

1. 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”

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.