Lesson 3 Inventory Management

Owned by Former user (Deleted)
Last updated: Oct 28, 2022 by Vlad Untu
6 min read

In this article we will build on top of Lesson 2 the Invoices, with the folowing two perspectives:

As a business problem

Adding the ability keep track of inventory for the purchases of products defined previously.

As a technical problem

Reading and writing entities using workflows.

Design

In order to keep track of inventory we will need an entity to register all changes to the inventory originating from multiple document types. One document type that will create changes in the inventory is the purchase invoice. When adding a purchase invoice, the inventory for each product on the invoice will go up, and when changing or deleting the invoice the inventory should align accordingly.

Another document type that can change the inventory is the sale invoice (this time when adding a sale invoice, the inventory for each product on the invoice will go down). In order to keep track of the documents that changed an inventory we will need an additional entity called document. Each invoices will have a relationship with this entity and every inventory record will store the key of the document that brought inventory changes.

In other words we need to create the following entities:

  1. document that has only the primary key (without other properties)
  2. inventory -
    1. with the properties:
      1. quantity of type: decimal
      2. description of type: string
    2. and relationships:
      1. inventory_product of type: many-to-one to product
      2. inventory_document of type: many-to-one to document

We also need to add a new relationship to the entity purchase_invoice. This relationship will be named purchase_invoice_document and will be of type many-to-one with the entity document.

Create and publish this entities the same way you did it in the first two lessons of this tutorial.

Create a new library

In order to read and write entities of type inventory (using workflows), we will first need to create a new library called InventoryService, that will be used by workflows to create or delete an inventory record.

The InventoryService will have one function and two methods:

  1. createDocument with empty parameters
  2. createIn with parameters:
    1. idDocument of type: int
    2. idProduct of type: int
    3. quantity of type: decimal
    4. description of type: string
  3. delete with only one parameter: idDocument of type int

To create a new library click the Develop app and then click on the Workflows menu.

In the page that opens click the Add button located above the list.

The first line that you have to enter is the keyword workflow followed by its name:

workflow InventoryService;

Then you have to add the following code:

/**
 You probably noticed already that the function is createDocument and the other two are the methods. 
 As the name suggests, this function creates and saves a new document entity and after that it 
 returns the document key.
*/
function createDocument() as int {
    var doc = CREATE document;
    PUT doc;
    return doc.key;
}

/**
  The createIn method creates an inventory entity that contributes to the overall of the product quantity, 
  in a way that increases its quantity.
*/
method createIn(idDocument as int, idProduct as int, quantity as decimal, description as string) {
    var i = CREATE inventory;
    i.id_document = idDocument;
    i.id_product = idProduct;
    i.quantity = quantity;
    i.description = description;
    PUT i;
}

/**
  The delete method receives (as parameter) the document's key for which the records of the inventory 
  entity should be deleted.
*/
method delete(idDocument as int) {
	/**
 	  In order to make the deletion possible we first need to FETCH the inventory records that has the 
      id_document equals with the document's key given as parameter.
	*/
    var inventories = FETCH inventory (key) {
        document TO id_document FILTER AND (key == ${idDocument})
    } **;
    
    foreach i in inventories {
		//After that we can delete the inventory records by their key.
        DELETE inventory(i.key);
    }
}

After you entered the code above click on the Save button.

Create an UI Listener

  • Vlad Untu De actualizat documentația. Nu mai exista UI Listener, s-a redenumit în Event listener.

After we created the library InventoryService we can now use it in the workflows to manage the inventory entity. But before using it in a workflow we first need to create a listener that watches on purchase_invoice entity for update, insert and delete.

To create an UI Listener click on Develop app, and then click on the UI Listener menu.

Here we have to click on the Add button located above the list. In the page that opens we need to fill in the field Entity (in our case with purchase_invoice).

After that we need to check: Before INSERT, Before UPDATE, Before INSERT/UPDATE and Before DELETE.

Click the Save button.

Using InventoryService

Now that we created the UI Listener for the purchase_invoice entity, we can use the InventoryService that we created earlier. The creation of the UI Listener generated some workflows. The one that interests us is one that it's called PurchaseInvoiceOperations.

To edit this workflow go to Develop app, and then click on Workflows menu. In the list that appear find the workflow named PurchaseInvoiceOperations, select that row and click the Edit button located above the list (next to Add button).

Here you will find the following methods:

  • onBI – that will be executed before inserting a purchase_invoice into the database

  • onBU – that will be executed before updating a purchase_invoice

  • onBIU – that will be executed before inserting and updating a purchase_invoice

  • onBD – that will be executed before deleting a purchase_invoice entity.

In order to use the InventoryService we first need to import it in the workflow. To do that add the following line after the name of the workflow:

import InventoryService alias is;

Now we can refer to this service using its alias.

The code for this four methods should be like this:

/**
  The method onBIU will be used to calculate the amount for each invoice item, by multiplying the unit_price 
  with the quantity.
*/
method atomic onBIU(e as ENTITY purchase_invoice) {
    foreach ii in CHILD e purchase_invoice_purchase_invoice_item {
        ii.amount = ii.unit_price * ii.quantity;
    }
}

/**
  When we create a new purchase_invoice we need also to assign it a document entity. In order to do that, 
  before inserting the new entity to the database, we have have to create a document entity. To do that 
  we will use the function createDocument of the library InventoryService.  
*/
method atomic onBI(e as ENTITY purchase_invoice) {
    e.id_document = is->createDocument();

	/**
      For each purchase_invoice_item of the purchase_invoice we need to create an inventory entity.
	  To do that we will use the method createIn that we created earlier on InventoryService.
    */
    foreach ii in CHILD e purchase_invoice_purchase_invoice_item {
        is->createIn(e.id_document, ii.id_product, ii.quantity, "new purchase invoice");
    }
}

/** 
  The method onBU will first delete all inventory records based on the id_document of the invoice and then 
  will create an inventory entity for each invoice item.
*/
method atomic onBU(e as ENTITY purchase_invoice) {
    is->delete(e.id_document);
    
    foreach ii in CHILD e purchase_invoice_purchase_invoice_item {
        is->createIn(e.id_document, ii.id_product, ii.quantity, "add item in purchase invoice");
    }
}

/**
  The method onBD will delete all inventory records based on the id_document of the invoice, before deleting 
  the invoice.
*/
method atomic onBD(e as ENTITY purchase_invoice) {
    is->delete(e.id_document);
}

Update the search view of the product

Now that we set up how the inventory entity is created or deleted, we need a way to see the inventory for each product. In order to do that, we need to modify the FETCH of the product search view created in Lesson 1 the Products.

Go to the Develop app, and then click on Search view menu.

In page that opens locate the search view with the title "Products" and name "productSearch", check it in and click the Edit button located above the list.

Select the Fetch tab, and enter the following fetch query:

FETCH product(key GROUP BY, name, price, bar_code) {
    inventory TO id_product LINK TYPE LEFT (SUM(quantity))
}
FILTER AND (${nameArg})

Now you should be able to keep track of your product inventory.

Test your knowledge

So far we created a way to track the inventory changes produced by purchase invoices. To test what you've learned, we advise you to create another type of invoice called sale_invoice. This invoice is similar with purchase_invoice with some differences.

To help you out, here are some tips:

  • instead of supplier entity, create and use another entity called customer (this one will have same properties like supplier entity);

  • instead of purchase_invoice_item, create and use an entity called sale_invoice_item (similar with purchase_invoice_item);

  • in the InventoryService add another method called createOut, which creates an inventory entity that contributes to the overall of the product quantity, in a way that decreases its quantity;

  • create another UI Listener, but this time for the sale_invoice entity;

  • modify the methods of the generated workflow (called SaleInvoiceOperations) in order to keep track of inventory changes produced by this new document (sale_invoice)