Lesson 3 Inventory Management

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.

Now, the ER diagram shoud look like this:

In other words we need to create the following entities:

1. document that has only the primary key (without other properties)
2. inventory

• with the properties:
  • quantity of type: decimal
  • description of type: string
• and relationships:
  • inventory_product of type: many-to-one to product
  • 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.

For more details, read the documentation - Develop App section, Workflows page.

The InventoryService will have one function and two methods:

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

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

workflow InventoryService;

Then you need to add the function and methods to the workflow on the next lines:

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. Notice that variables are declared using the var keyword.

For more detalis, check the documentation - Workflow language section.

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

After that we define a foreach so we can delete the inventory records by their key.

method delete(idDocument as int) {
    var inventories = FETCH inventory (key) {
        document TO id_document FILTER AND (key == ${idDocument})
    } **;

    foreach i in inventories {
        DELETE inventory(i.key);
    }
}

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

Create an 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.

Using InventoryService

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

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.

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 Event 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)