Bonus Base Project: Building an Inventory Management System in LibreOffice Base

In this advanced project, we’ll guide you through creating an Inventory Management System using LibreOffice Base. This system will help you track stock levels, record purchase orders, manage suppliers, and generate reports that give insights into stock movement and purchasing trends.

Whether you’re running a small business or managing a large warehouse, this system will give you the tools to efficiently handle your inventory and keep your operations running smoothly.

By the end of this project, you’ll have a robust inventory management system capable of handling stock tracking, purchasing, and reporting.

Step 1: Planning the Inventory Management System

First, we need to design the database structure. The system will consist of several related tables that store products, suppliers, purchase orders, and stock levels.

Key Tables

1. Products:
   • Stores product information, such as ProductID, ProductName, Description, UnitPrice, and StockLevel.
2. Suppliers:
   • Stores supplier details, including SupplierID, SupplierName, ContactName, PhoneNumber, and Email.
3. Purchase Orders:
   • Tracks product purchases with fields such as OrderID, SupplierID (linked to the Suppliers table), ProductID (linked to the Products table), OrderDate, and Quantity.
4. Stock Levels:
   • This table keeps track of ProductID, ReceivedQuantity, ShippedQuantity, and BalanceStock. This data will automatically update based on purchase orders and product shipments.

Step 2: Creating the Products Table

Let’s begin by creating the Products table to store basic product information.

Step-by-Step: Creating the Products Table

1. Open LibreOffice Base:
   • Create a new database and select Create a new database. Name it something like InventoryManagement.odb.
2. Go to Table Design:
   • In the Database Pane, click on Tables and then Create Table in Design View.
3. Define Fields for the Products Table:
   • Add the following fields:
     • ProductID (Integer): Primary key, auto-increment.
     • ProductName (Text): Name of the product.
     • Description (Text [Memo]): Product description.
     • UnitPrice (Decimal): Price per unit.
     • StockLevel (Integer): Current stock level.
4. Set the Primary Key:
   • Right-click the ProductID field and select Primary Key.
5. Save the Table:
   • Save the table as Products.

Step 3: Creating the Suppliers Table

Next, we’ll create a table to manage supplier information.

Step-by-Step: Creating the Suppliers Table

1. Go to Table Design:
   • In the Tables section, click Create Table in Design View.
2. Define Fields for the Suppliers Table:
   • Add the following fields:
     • SupplierID (Integer): Primary key, auto-increment.
     • SupplierName (Text): Supplier’s name.
     • ContactName (Text): Primary contact person.
     • PhoneNumber (Text): Supplier’s phone number.
     • Email (Text): Supplier’s email address.
3. Set the Primary Key:
   • Right-click the SupplierID field and select Primary Key.
4. Save the Table:
   • Save the table as Suppliers.

Step 4: Creating the Purchase Orders Table

The Purchase Orders table will track each product purchase made from suppliers. It will link the Suppliers and Products tables through foreign keys.

Step-by-Step: Creating the Purchase Orders Table

1. Go to Table Design:
   • In the Tables section, click Create Table in Design View.
2. Define Fields for the Purchase Orders Table:
   • Add the following fields:
     • OrderID (Integer): Primary key, auto-increment.
     • SupplierID (Integer): Foreign key, linked to the Suppliers table.
     • ProductID (Integer): Foreign key, linked to the Products table.
     • OrderDate (Date): Date of the order.
     • Quantity (Integer): Quantity of the product ordered.
3. Set the Primary Key:
   • Right-click the OrderID field and select Primary Key.
4. Save the Table:
   • Save the table as PurchaseOrders.

Step 5: Establishing Relationships Between Tables

Now that we have the Products, Suppliers, and Purchase Orders tables, we need to define relationships between them. This ensures that data entered in the Purchase Orders table references the correct product and supplier.

Step-by-Step: Defining Table Relationships

1. Open the Relationships Window:
   • Go to Tools > Relationships.
2. Add Tables to the Relationships Window:
   • Add the Products, Suppliers, and Purchase Orders tables.
3. Create Relationships:
   • Drag the SupplierID field from the Suppliers table to the SupplierID field in the Purchase Orders table.
   • Drag the ProductID field from the Products table to the ProductID field in the Purchase Orders table.
4. Enforce Referential Integrity:
   • In the Relationships dialog, check Update Cascade and Delete Cascade for both relationships. This ensures that any changes in the Suppliers or Products tables are reflected in the Purchase Orders table.
     (N.B. You can open the Relationships dialog by double-clicking on the arrows between the tables in the relationships diagram)
5. Save the Relationships:
   • Save and close the relationships window.

Step 6: Creating a Stock Management Form

Now that we have the core tables ready, we’ll create a form for managing product stocks and entering purchase orders.

Step-by-Step: Creating a Stock Management Form

1. Use the Form Wizard:
   • In the Forms section of the Database Pane, click Use Wizard to Create Form.
2. Select the Purchase Orders Table:
   • In the Form Wizard, select the PurchaseOrders table as the data source.
3. Choose Fields for the Form:
   • Select fields like OrderID, SupplierID, ProductID, OrderDate, and Quantity.
4. Create a Subform for Product Stock Levels:
   • Add a subform to display the current stock levels for the selected product. Link it to the Products table and display the ProductName and StockLevel.
5. Finish and Save:
   • Name the form StockManagementForm and click Finish.

Step 7: Writing Queries to Track Stock Levels and Purchases

Queries allow you to analyze your inventory data. We’ll create queries to check stock levels, flag low-stock items, and track product purchases.

Step-by-Step: Creating a Query to Check Stock Levels

1. Use the Query Wizard:
   • In the Queries section, click Use Wizard to Create Query.
2. Choose Fields:
   • Select fields from the Products table (e.g., ProductID, ProductName, StockLevel).
3. Add Criteria for Low Stock:
   • To create a low-stock alert, add criteria to the StockLevel field. For example:
     < 10
     
   • This will list products with fewer than 10 units in stock.
4. Save the Query:
   • Name the query LowStockAlert and click Finish.

Creating a Query to Track Purchases

1. Use the Query Wizard:
   • Create a new query using the PurchaseOrders table.
2. Select Fields:
   • Choose fields like ProductID, SupplierID, OrderDate, and Quantity to track product purchases over time.
3. Filter by Date Range:
   • Add a date range to the OrderDate field to filter purchases within a specific time period (e.g., last month):
     >= DATEADD('dd', -30, CURRENT_DATE)
4. Save the Query:
   • Name the query PurchaseTracking and click Finish.

Step 8: Generating Inventory Reports

Reports provide a summary of your data and are useful for keeping stakeholders informed about inventory status and purchasing trends. We’ll create reports that highlight stock movement and low-stock items.

Step-by-Step: Creating a Stock Movement Report

1. Use the Report Wizard:
   • In the Reports section, click Use Wizard to Create Report.
2. Select the PurchaseTracking Query:
   • Use the PurchaseTracking query as the data source.
3. Choose Fields for the Report:
   • Select fields like ProductName, SupplierName, OrderDate, and Quantity.
4. Group Data:
   • Group the data by ProductID to display all purchases for each product.
5. Save the Report:
   • Name the report StockMovementReport and click Finish.

Creating a Low Stock Alert Report

1. Use the Report Wizard:
   • Create a report based on the LowStockAlert query.
2. Select Fields:
   • Choose fields like ProductID, ProductName, and StockLevel.
3. Save the Report:
   • Name the report LowStockAlertReport and click Finish.

Step 9: Automating Stock Updates with Macros

To make your system more dynamic, you can use macros to automatically update stock levels when products are purchased or shipped.

Step-by-Step: Writing a Macro for Stock Updates

1. Enable Macros:
   • Go to Tools > Options > LibreOffice > Security and set macro security to a level that allows you to run your custom macros.
2. Open the Macro Editor:
   • Go to Tools > Macros > Organise Macros > LibreOffice Basic and create a new macro for stock updates.
3. Write the Macro:
   • Write a macro that updates the StockLevel in the Products table based on the quantities recorded in the PurchaseOrders table. For example:
     Sub UpdateStockLevel()
' Declare variables to hold database objects and query results
Dim oContext As Object
Dim oDB As Object
Dim oStatement As Object
Dim oResult As Object
Dim oUpdateStatement As Object

' Declare variables to hold field values from the PurchaseOrders table
Dim nProductID As Integer
Dim nQuantity As Integer

' Get the database context and open a connection
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oContext.getByName("InventoryManagement") ' Use the correct database name
oConnection = oDB.getConnection("","") ' Open the connection without username and password

' Create SQL statement to retrieve product IDs and quantities from the PurchaseOrders table
oStatement = oConnection.createStatement()
oResult = oStatement.executeQuery("SELECT ProductID, SUM(Quantity) AS TotalOrdered FROM PurchaseOrders GROUP BY ProductID")

' Loop through the results from the PurchaseOrders table
While oResult.next()
' Retrieve ProductID and the total Quantity ordered
nProductID = oResult.getInt(1) ' First column is ProductID
nQuantity = oResult.getInt(2) ' Second column is TotalOrdered Quantity

' Create a new statement to update the StockLevel in the Products table
oUpdateStatement = oConnection.createStatement()

' Update the StockLevel by adding the Quantity from the PurchaseOrders table to the existing StockLevel
oUpdateStatement.executeUpdate("UPDATE Products SET StockLevel = StockLevel + " & nQuantity & " WHERE ProductID = " & nProductID)
Wend

' Close the result set and connection
oResult.close()
oConnection.close()

MsgBox "Stock levels have been updated successfully!"
End Sub
4. Run the Macro:
   • Test the macro by adding new purchase orders and checking if the stock levels update automatically.

How the Macro Works:

1. Database Connection:
   • oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext"): Creates the service to interact with the database.
   • oDB = oContext.getByName("InventoryManagement"): Opens the database named InventoryManagement. Ensure this matches your database file.
   • oConnection = oDB.getConnection("",""): Establishes a connection to the database.
2. SQL Query to Retrieve Data from PurchaseOrders Table:
   • oStatement = oConnection.createStatement(): Prepares an SQL statement.
   • The query SELECT ProductID, SUM(Quantity) AS TotalOrdered FROM PurchaseOrders GROUP BY ProductID retrieves the total quantity ordered for each product, grouping by ProductID.
3. Looping through Purchase Orders:
   • While oResult.next(): Loops through each record in the result set (each product and its total ordered quantity).
   • nProductID = oResult.getInt(1) and nQuantity = oResult.getInt(2): These lines get the values of ProductID and the summed Quantity.
4. Updating the Stock Level:
   • oUpdateStatement.executeUpdate(...): This SQL query updates the StockLevel in the Products table by adding the total ordered quantity for each product.
5. Closing the Connection:
   • After all updates are made, the result set and connection to the database are closed to free up resources.

This macro will run the stock level updates automatically when executed, ensuring the StockLevel in the Products table reflects the quantities ordered in the PurchaseOrders table.

Learning to write macros

Writing macros in LibreOffice can significantly enhance your productivity by automating repetitive tasks. LibreOffice uses a programming language called LibreOffice Basic, which is similar to VBA (Visual Basic for Applications) used in Microsoft Office. The best way to learn programming in LibreOffice Basic is through a combination of practice, exploring built-in tools, and following the many tutorials available online.

Here are some useful resources to get started:

• LibreOffice Basic Guide: This official guide provides a thorough introduction to writing macros, covering everything from basic syntax to advanced topics like interacting with documents and database operations. You can find the guide here: LibreOffice Basic Guide
• LibreOffice Wiki – Macros: The LibreOffice Wiki contains numerous examples and detailed explanations on how to use Basic to write macros, including interacting with the spreadsheet and text document components. It’s a great place for learning more about specific topics: LibreOffice Wiki – Macros
• Macro Examples by Andrew Pitonyak: Andrew Pitonyak’s book on macros is an invaluable resource, containing many real-world examples of how to write macros in LibreOffice. His website provides downloadable examples for learning: Pitonyak’s Macro Information
• LibreOffice Macros Getting Started Guide: The official Getting Started Guide for LibreOffice Macros is a great place to start learning about programming in LibreOffice Check it out here: LibreOffice Macros Getting Started

By starting with these resources and gradually building your own macros, you’ll be able to automate tasks in LibreOffice efficiently. Be sure to experiment with the macro recorder to generate basic scripts, and then modify them in the LibreOffice Basic editor as you learn more!

Final Thoughts

By completing this advanced project, you’ve built a powerful Inventory Management System using LibreOffice Base. You can now track products, suppliers, and purchase orders, while generating reports to monitor stock levels and purchasing trends. This system is scalable and can be expanded with more features, such as shipment tracking and automated stock alerts.

This project showcases the flexibility of LibreOffice Base for managing business processes, and it can be adapted to suit different inventory management needs.