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:

erDiagram
    product {
        string name
        decimal price
        string bar_code
        int key PK "id_product"
    }

    supplier {
        string name 
        string address
        int key PK "id_supplier"
    }

    purchase_invoice {
       datetime date
       string number
       string terms
       int key PK "id_purchase_invoice"
       int id_supplier FK "id_supplier"
       int id_document FK "id_document"
    }

    purchase_invoice_item {
        decimal quantity
        decimal unit_price
        decimal amount
        int key PK "id_purchase_invoice_item"
        int id_purchase_invoice FK "id_purchase_invoice"
        int id_product FK "id_product"
    }

    document {
        int key PK "id_document"
    }

    inventory {
        decimal quantity
        string description
        int key PK "id_inventory"
        int id_product FK "id_product"
        int id_document FK "id_document"
    }

    purchase_invoice many--one supplier : issues
    purchase_invoice one--many purchase_invoice_item : contains
    purchase_invoice many--one document : references
    purchase_invoice_item many--one product : references
    inventory many--one product : has
    inventory many--one document : generates

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

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 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 for the function and methods in the InventoryService workflow, click on the Save button.

← Lesson 2 the Invoices
Lesson 4 the Events →